Зеркалирование баз данных на MS SQL

Доброго дня. Решил я описать здесь свой опыт настройки зеркалирования БД. Не имея, до недавнего времени, подобного профита, я начал сёрфить интернет в поисках информации на этот счёт. И постараюсь оформить пост как пошаговая инструкция рассказать об основных моментах, в общем что бы ничего лишнего.

Интро
Для резервирования БД рассматривали 2 варианта:
— репликации
— зеркалирование
Репликации отпали, потому, как некоторые таблицы не могут реплицироваться и вообще специально под это дело надо сразу предусматривать структуру базу данных.
Зеркалирование работает на ура! В результате тестов клал основную базу, переводил зеркальную в главную. После того как поднимал главную — та автоматом становилась зеркальной, менял их местами. Всё прошло без сучка, без задоринки. (Дай бог ей долгого здравия!)

Вообще есть 3 режима зеркалирования:
— защищённый с автоматическим восстановлением
— защищённый с ручным восстановлением
— не защищённый/асинхронный
Защищённый отличаются от асинхронного тем, что не ждут подтверждения принятия транзакции на зеркальном сервере, а продолжают работать и набрасывают в очередь новые и новые транзакции.
Защищённый с автоматическим восстановлением требует для автоматического восстановления использовать 3-й сервер (следящий) и в принципе полезен только если у вас в приложении можно указать резервный сервер для переключения в случае когда не работает основной. Поскольку мне было жалкао засарять следящими серверами информационное пространство и приложения работающие с базой тоже не имело возможности переключаться самостоятельно.
Я настраивал базы на работу в защищённом режиме с ручным восстановлением.

Вот хорошая инструкция на TechNet'е.
А здесь в картинках показано как это сделать через GUI.

Часть 1. Настройка связи сервера.
Для связи серверов друг с другом на обоих машинах создаются контрольные точки, открываются порты на соединение, создаются пользователи, сертификаты и пр.
Создадим контрольные точки, для авторизации мы будем использовать сертификат сгенерированный MS SQL сервером (так же можно использовать и другие сертификаты).

1. Создаём сертификат на главном сервере и сохраним его в паку D:\Certs
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 = '08/15/2011',
EXPIRY_DATE = '08/15/2021';
GO
BACKUP CERTIFICATE PrincipalServerCert TO FILE = 'D:\Certs\PrincipalServerCert.cer'


2. Создадим контрольную точку DBMirrorEndPoint на главном сервере.
USE MASTER
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
)

3. Создаём сертификат и контрольную точку 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 = '08/15/2011',
EXPIRY_DATE = '08/15/2021';
GO
BACKUP CERTIFICATE MirrorServerCert TO FILE = 'D:\Certs\MirrorServerCert.cer'

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
)


Сертификаты и контрольные точки мы создали. Теперь чтобы серверы могли между собой общаться, на каждом серваке нужно создать учётные записи и привязать их к сертификатам.

4. Копируем сертификаты с одного на другой сервак, чтобы в папке D:\Certs лежало по 2 сертификата.


5. Создадим на главном сервере пользователя MirrorServerUser, этого пользователь привязываем к сгенерированному и скопированному с зеркального сервера сертификату MirrorDBCertPub
USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = 'MirrorServerUser')
CREATE LOGIN MirrorServerUser WITH PASSWORD = 'секретныйпароль2'
IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE name = 'MirrorServerUser')
CREATE USER MirrorServerUser;
IF NOT EXISTS(SELECT 1 FROM sys.certificates WHERE name = 'MirrorDBCertPub')
CREATE CERTIFICATE MirrorDBCertPub AUTHORIZATION MirrorServerUser
FROM FILE = 'D:\Certs\MirrorServerCert.cer'
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO MirrorServerUser
GO


6. Создадим на резервном сервере пользователя PrincipalServerUser, этого пользователь привязываем к сгенерированному и скопированному с главного сервера сертификату PrincipalDBCertPub
USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = 'PrincipalServerUser')
CREATE LOGIN PrincipalServerUser WITH PASSWORD = 'секретныйпароль2'
IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE name = 'PrincipalServerUser')
CREATE USER PrincipalServerUser;
IF NOT EXISTS(SELECT 1 FROM sys.certificates WHERE name = 'PrincipalDBCertPub')
CREATE CERTIFICATE PrincipalDBCertPub AUTHORIZATION PrincipalServerUser
FROM FILE = 'D:\Certs\PrincipalServerCert.cer'
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO PrincipalServerUser
GO

Связь между серверами настроена!

Часть 2. Настройка баз данных.
Здесь нам надо будет снять бэкап с рабочей базы, поднять его на зеркальном сервере в режиме NORECOVERY и включить режим зеркалирования.
Зеркалируемая база данных должна иметь модель восстановления FULL.

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

2. Поднимаем его на зеркальном (скрипт подразумевает, что файл бэкапа перенесён на зеркальный сервак на диск D)
RESTORE DATABASE [MIRROR_TEST]
FROM DISK = 'D:\MIRROR_TEST.bak' WITH NORECOVERY
,MOVE N'MIRROR_TEST' TO N'D:\MSSQL_DB\MIRROR_TEST.mdf'
,MOVE N'MIRROR_TEST_log' TO N'D:\MSSQL_DB\MIRROR_TEST_log.ldf'


3. Для запуска зеркалирования на зеркальном сервере выполняем:
ALTER DATABASE MIRROR_TEST SET PARTNER = 'TCP://MSSQLMAINSERV:5022'

4. Затем на главном:
ALTER DATABASE MIRROR_TEST SET PARTNER = 'TCP://MSSQLMIRRORSERV:5023'

Если вылезет ошибка типа:

The mirror database, “MIRROR_TEST”, has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)

или

The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

Сделайте бэкап журнала с базы на главном сервере и восстановите его на зеркальном (опять же в режиме NORECOVERY).
Бэкап:
BACKUP LOG MIRROR_TEST TO DISK = 'D:\MIRROR_TEST.trn'

Восстановление:
RESTORE LOG MIRROR_TEST
FROM DISK = 'D:\MIRROR_TEST.trn' WITH NORECOVERY

Часть 3. Восстановление после сбоев. Изменение ролей.
Изменить роли сервера, чтобы зеркальный стал главным и наобород можно через GUI кликнов правой кнопкой по базе — Task — Mirror — Failover или же через команду T-SQL
ALTER DATABASE MIRROR_TEST SET PARTNER FAILOVER

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

Если же грохнулась главная база, то чтобы оживить резервную нужно выполнить принудительное восстановление
ALTER DATABASE MIRROR_TEST SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

правда в этом случае существует риск потерять некоторые данные (про это много написано здесь)

При выполнении принудительного восстановления зеркальная база становится главной, а бывшая главная после восстановления автоматически станет зеркальной, ожидающей разрешения продолжить сеанс зеркалирования. Для чего нужно выполнить
ALTER DATABASE MIRROR_TEST SET PARTNER RESUME
Вот вроде и всё! Пока работает 8-)