Настройка зеркалирования базы для MS SQL

Администрирование - Архивирование (backup)

Очень удобный способ, когда нам нужна не просто резервная копия, а "горячая" замена серверов.

Для настройки зеркалирования на MSSQL для начала необходимо настроить  доступ между ними.
Один сервер у нас будет основной, а второй зеркальный.
Первое что надо сделать, это настроить доступ между этими серверами, разрешить порты (по молчанию 1433-1434) и наши порты, которые мы укажем в настройках.
ВАЖНО Наша база данных должна иметь модель восстановления “FULL”
И так
1.  Создаем сертификат и контрольную точку «DBMirrorEndPoint» на основном сервере, указав в сертификате пароль, дату актуальности и сохраним его в папку (для примера в D:\Certificate), также укажем порт для соединения (для примера 5022)

USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'наш пароль'
GO
IF NOT EXISTS (select 1 from sys.databases where [is_master_key_encrypted_by_server] = 1)
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY 
GO
IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'PrincipalServerCert')
CREATE CERTIFICATE PrincipalServerCert
WITH SUBJECT = 'Principal Server Certificate',
START_DATE = '17/05/2017',
EXPIRY_DATE = '17/05/2027';
GO
BACKUP CERTIFICATE PrincipalServerCert TO FILE = 'D:\Certificate\PrincipalServerCert.cer'

GO
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirrorEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE PrincipalServerCert, ENCRYPTION = REQUIRED
,ROLE = ALL
)

2.  Создаем сертификат и контрольную точку «DBMirrorEndPoint» на зеркале, аналогично основному

USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'наш пароль'
GO
IF NOT EXISTS (select 1 from sys.databases where [is_master_key_encrypted_by_server] = 1)
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY 
GO
IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'MirrorServerCert')
CREATE CERTIFICATE MirrorServerCert
WITH SUBJECT = 'Mirror Server Certificate',
START_DATE = '17/05/2017',
EXPIRY_DATE = '17/05/2021';
GO
BACKUP CERTIFICATE MirrorServerCert TO FILE = = 'D:\Certificate\MirrorServerCert.cer'
GO
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirrorEndPoint
STATE=STARTED AS TCP (LISTENER_PORT = 5023)
FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE MirrorServerCert, ENCRYPTION = REQUIRED
,ROLE = ALL
)

У нас есть сертификаты и контрольные точки, для соединения 2 серверов необходимо создать юзеров на обоих, привязав их к нашим сертификатам.

3. Копируем сертификаты с одного сервера на другой, в папке с сертификатами (у нас ‘D:\Certificate\’) должно быть по 2 сертификата.

4. Создаем на основном сервере юзера «MirrorUser», этого юзера привязываем к сертификату из зеркала «MirrorServerCert»

USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = 'MirrorUser')
CREATE LOGIN MirrorUser WITH PASSWORD = 'пароль юзера'
IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE name = 'MirrorUser')
CREATE USER MirrorUser;
IF NOT EXISTS(SELECT 1 FROM sys.certificates WHERE name = 'MirrorDBCertPub')
CREATE CERTIFICATE MirrorDBCertPub AUTHORIZATION MirrorUser
FROM FILE = 'D:\Certificate\MirrorServerCert.cer'
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO MirrorUser
GO

5. Создаем на зеркале юзера «PrincipalUser», этого юзера привязываем к сертификату из основного сервера
«PrincipalDBCertPub»

USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = 'PrincipalUser')
CREATE LOGIN PrincipalUser WITH PASSWORD = 'пароль юзера'
IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE name = 'PrincipalUser')
CREATE USER PrincipalUser;
IF NOT EXISTS(SELECT 1 FROM sys.certificates WHERE name = 'PrincipalDBCertPub')
CREATE CERTIFICATE PrincipalDBCertPub AUTHORIZATION PrincipalUser
FROM FILE = 'D:\Certificate\PrincipalServerCert.cer'
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO PrincipalUser
GO

Связь между серверами готова. Теперь надо настроить базы данных.

1. Делаем бэкап рабочей базы.
BACKUP DATABASE [Наша база] TO DISK = N'D:\Наша база.bak' 
WITH FORMAT, INIT, NAME = N'MIRROR_TEST-Full Database Backup',STATS = 10
2. Поднимаем бэкап на зеркале

Перенесем файл бэкапа на зеркало (у нас, в корень диска D), укажем путь к файлам БД

RESTORE DATABASE [Наша база]
FROM DISK = 'D:\ Наша база.bak' WITH NORECOVERY
,MOVE N'MIRROR_TEST' TO N'D:\MSSQL_DB\Наша база.mdf'
,MOVE N'MIRROR_TEST_log' TO N'D:\MSSQL_DB\Наша база_log.ldf'
3. Запускаем зеркалирование на зеркале

ALTER DATABASE MIRROR_TEST SET PARTNER = 'TCP://основной сервер:5022'

4. Потом на основном

ALTER DATABASE MIRROR_TEST SET PARTNER = 'TCP://зеркало:5023'
5. Для того чтобы подключатся с 1С к любой из баз необходимо применить асинхронный режим зеркалирования 

— Task — Mirror — Hight performance (asynchronous)  
(— Задачи — Создать зеркальное отображение — Высокая производительность (асинхронный))

Теперь можно работать на двух базах одновременно. Данные будут передаватся между ними

Изменить роли сервера можно в
— Task — Mirror — Failover  
(— Задачи — Создать зеркальное отображение — Отработка отказа)

Если основная БД упала, то нужно оживить зеркало запустив
ALTER DATABASE MIRROR_TEST SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
После этой команды зеркальная база становится основной, а основная после решения проблем станет зеркальной и будет синхронизироваться з основной

См. также

Комментарии
1. Михаил Максимов (МихаилМ) 19.05.17 22:03 Сейчас в теме
автор так спешил что забыл указать, для каких версий мс скл сие пригодно. или на какой версии автор тренировался.
а также забыто , что будет при реструктуризации
2. Дмитрий Королев (ArchLord42) 58 20.05.17 05:56 Сейчас в теме
Есть несколько нюансов, которые не описал автор:

- Для того чтобы не производить ручную обработку отказа (ALT ER DATABASE MIRROR_TEST SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS), можно поставить третий - Witness (следящий) сервер, который будет эти заниматься в автоматическом режиме.

- Как верно заметил Михаил (1), есть особенность реструктуризации, а точнее ее невозможность в некоторых случая, будучи в режиме зеркалирования.
Вводится ограничение на создание новых справочников, доков, регистров, ПВХ, вы попросту не сможете обновится с ошибкой:
Операция не может быть выполнена для базы данных "test", так как она участвует в сеансе зеркального отображения или группе доступности. Некоторые операции недопустимы для баз данных, участвующих в сеансе зеркального отображения или группе доступности.

Для того, чтобы обновить 1С, вам будет нужно отключить зеркалирования, а после включить обратно (проще всего сделать скрип на вкл и выкл)

- Зеркалирование доступно с версии >= 2005 MSSQL

- Во избежании коллизий и потерь данных, не рекомендуется работать сразу в 2х базах в асинхронном режиме, т.к. по сути это будет работа с 2х разных ИБ подключенных к одной БД, что чревато, но синхронный режим замедляет (не сильно) работу, т.к. ждет окончания транзакций во второй базе тоже.

- В синхронном режиме, к резервной базе доступа нет, она всегда находится в режиме восстановления, но в версиях MSSQL >= 2012 при подключении через Microsoft SQL Server Native Client (так же используется сервером 1С) происходит редирект на основную базу.

- Если поднять Windows Server Failover Clustering (WSFC), лучше использовать AlwaysOn (легко гуглится)
Spacer; angur; WellMaster; METAL; EliasShy; cleaner_it; AlexGroovy; Denis_Viktorovich; MsDjuice; user747571; Danila-Master; h00k; Lem0n; МихаилМ; +14 Ответить
3. Юрий Семенчук (MsDjuice) 87 22.05.17 12:16 Сейчас в теме
(2)
чтобы обновить 1С, вам будет нужно отключить зеркалирования, а после включить обратно (проще всего сделать скрип на вкл и выкл)

Это при каждом обновлении будет проблема такая, или иногда?
4. Дмитрий Королев (ArchLord42) 58 22.05.17 18:40 Сейчас в теме
(3) да, только если вы добавляете новые объекты МД, который я описал выше.
т.е. если вы будете реквизит к справочнику добавлять то все спокойно обновится, но если вы добавите новый справочник то придется отключать.
12. Дмитрий Королев (ArchLord42) 58 25.05.17 06:24 Сейчас в теме
(2) (9)
а точнее ее невозможность в некоторых случая, будучи в режиме зеркалирования.
Вводится ограничение на создание новых справочников, доков, регистров, ПВХ, вы попросту не сможете обновится с ошибкой:


Действительно я был не совсем корректен по поводу обновлений в первом посте,все же не каждый раз нужно отрубать зеркалирование, действительно в некоторых случаях обновляется без проблем и дело не в стабильности как писали выше, при определенных обстоятельствах (видимо на моем опыте чаще всего это и попадалось) не удается обновить без отключения.

В общем я решил проверить на тестовой базе, запустил еще раз тестовую базу и вот результат:
1) попытался создать первый док - ошибка.
2) отрубил зеркалирование, обновился запустил зеркалирование.
3) Добавил второй док - все ок
4) Начал добавлять разные объекты, все ок опять же.

при добавлении первого дока идет еще какие-то изменения в БД (скрин)

ЗЫ. Написанное мной в первом посте по поводу обновлений, это мой личный опыт работы с двумя конфигурациями на БСП, возможно при некоторых обстоятельствах возможность обновить отсутствует, при других вы сможете спокойно обновляться.
Прикрепленные файлы:
5. Михаил Петухов (oldfornit) 22.05.17 18:55 Сейчас в теме
чем это лучше использования always-on MS SQL?
6. Дмитрий Королев (ArchLord42) 58 22.05.17 19:10 Сейчас в теме
(5)
т.к. подключение идет к кластеру, а не к конкретной базе, при отказе одной БД юзеры, которые ни чего не проводили\записывали ничего не почувствуют, при условии что развернут кластер 1С (если БД и 1С на одном сервере находятся).
Можно юзать резевную БД, но только в режими readonly.

ЗЫ не заметил, что вы наоборот спросили, ответ : ничем, только если ценой развертывания и возможности географического распределения серверов.
7. Мортум Святой (Mortum) 23.05.17 21:45 Сейчас в теме
Always-on вроде домен требует, а зеркалирование, как уже написали, не позволит добавлять новые таблицы. Лучше уж настроить transaction log shipping с нужным интервалом времени, за который не жалко потерять данные. Из минусов только потеря части данных с момента последнего бэкапа логов и ручное переключение на резервный сервер.
8. Дмитрий Королев (ArchLord42) 58 24.05.17 08:41 Сейчас в теме
(7)домен ему априори нужен т.к. все таки главное требование это фейл овер кластер винды, что намного затратнее)
9. Юрий Семенчук (MsDjuice) 87 24.05.17 14:56 Сейчас в теме
Пробовали добавлять новые таблицы: справочники, документы с реквизитами разных типов. Все отлично переносит. Может у кого-то работало нестабильно, нам же на наших базах не удалось воссоздать проблему.
10. Дим Дим (demaxxx) 24.05.17 19:43 Сейчас в теме
А есть где нибудь инструкция, как делать быстрое зеркало базы в рамках одного сервера?
Т.е. быстро делать снимок и сразу же заливать в другую для экспериментов. Вручную или по расписанию.
11. Дмитрий Королев (ArchLord42) 58 25.05.17 05:53 Сейчас в теме
(10) Вы можете установить два именных экземпляра (instance) на один сервер и работать с ними.
Установка в целом стандартная, только в момент выбора параметров экземпляра, нужно просто переключится на именованный экземпляр ну и назвать его там inst1\inst2 в рамках 2х экземпляров можно развернуть зеркалирование, только кроме как для тестов смысла в этом нет.
Прикрепленные файлы:
13. Пользователь (user762605) 30.05.17 14:38 Сейчас в теме
1. Зеркалирование - жалкая попытка убежать от невозможности реплицирования. Ничем по сути от него не отличающаяся. Зеркалирование не дает преимущества, которое написано ниже по отношению к AlwaysOn (который требует всего то втащить сервер с mssql в домен).
2. Репликация (sic!) 1Сной базы не возможна ни один вариант из 3х предлагаемых не подходит. (Это вы поймете прочитав элементарное описание технологий)
Не удивляйтесь при использовании 1 и 2, если увидите "база разрушена/повреждена" и т.п.
3. Единственный выход - alwayson, который дает хоть какое то подобие failover для 1С базы. Опять же надо решить вопрос с бэкапированием.
В добавок к alwayson есть softpoint, который позволит шуршать довольно быстренько. В обход softpoint'а можете заставить своих разрабов перевести все запросы на чтение к secondary mssql-серверу (идеальный мир, ну).
Оставьте свое сообщение