Настройка зеркалирования в Microsoft SQL Server 2014

Олег

  • 31 января 2018
Microsoft SQL Server

Зеркалирование или mirroring в MS SQL Server 2014 всё ещё есть. Это очень полезное решение для повышения доступности базы данных. Если ваш сервер упал, посыпались винты, сгорел ЦОД, то возможность быстро переключиться на резервный сервер, не занимаясь восстановлением сервера или БД из бэкапа, экономит кучу времени, денег и нервов. Однако, не следует рассматривать зеркалирование как замену резервному копированию, так как оно не спасает от случайного удаления данных.

Вот статья про настройку резервного копирования.

Одно из классических использований данного решения - это когда у вас есть два ЦОДа, а в каждом из них находится свой экземпляр MS SQL Server. Кроме того, иногда зеркалирование помогает проводить регламентные работы, когда нежелательно надолго останавливать БД и перезагружать сервер.

Режимы зеркалирования

В Microsoft SQL Server 2014 есть три режима зеркалирования:

  • асинхронный,
  • синхронный без автоматического восстановления,
  • синхронный с автоматическим восстановлением.
Режимы зеркалирования (Operation mode)
Режимы зеркалирования (Operation mode)

Асинхронный

High performance (asynchronous) - Commit changes at the principal and then transfer them to the mirror.

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

Синхронный без автоматического восстановления

High safety without automatic failover (synchronous) - Always commit changes at both the principal and mirror.

Для работы в синхронном режиме без автоматического восстановления достаточно двух серверов: главного (principal) и зеркального (mirror). Если главный сервер упал, то на зеркальный можно переключиться в ручном режиме. В данном режиме работы данные записываются сразу на два сервера в режиме транзакции. Тем самым обеспечивается синхронность данных и мы можем быть уверены в том, что в случае падения главного сервера мы не потеряем данные на зеркальном.

Синхронный с автоматическим восстановлением

High safety with automatic failover (synchronous) - Requires a witness server instance. Commit changes at both the principal and mirror if both are available. The witness controls automatic failover to the mirror if the principal become unavailable.

Для работы в синхронном режиме с автоматическим восстановлением требуется три сервера: главный (principal), зеркальный (mirror) и свидетель (witness). Если главный сервер упал, то свидетель автоматически сделает failover на зеркальный сервер. В данном режиме работы данные записываются сразу на два сервера в режиме транзакции. Тем самым обеспечивается синхронность данных и мы можем быть уверены в том, что в случае падения главного сервера мы не потеряем данные на зеркальном.

Дальше следует инструкция по настройке зеркалирования в синхронном режиме без автоматического восстановления.

Модель восстановление FULL

Зеркалируемая база данных должна иметь модель восстановления FULL. При этом постоянно растёт лог транзакций. Чтобы в один прекрасный момент не кончилось место на сервере, необходимо настроить регулярное резервное копирование (full backup), при этом лог транзакций жмётся.

Переводим необходимые нам БД на модель восстановления FULL. Делаем полный бэкап. После бэкапа модель восстановления применится. Если БД уже настроена на FULL, то пропускаем этот пункт.

Настройка серверов

Версия MS SQL Server на обоих серверах должна быть одинаковая. Для настройки зеркалирования обеспечиваем доступ между серверами.

На обоих серверах создаём папку:

C:\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 = '01/15/2018',
EXPIRY_DATE = '01/15/2028';
GO
BACKUP CERTIFICATE PrincipalServerCert TO FILE = 'C:\certs\PrincipalServerCert.cer'

На главном сервере создаём контрольную точку DBMirrorEndPoint на порту 5022:

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)

На зеркале создаём сертификат:

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 = '01/15/2018',
EXPIRY_DATE = '01/15/2028';
GO
BACKUP CERTIFICATE MirrorServerCert TO FILE = 'C:\certs\MirrorServerCert.cer'

На зеркале создаём контрольную точку DBMirrorEndPoint на порту 5023:

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)

Копируем сертификаты с одного сервера на другой, чтобы на каждом сервере в папке C:\certs было по два сертификата:

C:\certs\MirrorServerCert.cer
C:\certs\PrincipalServerCert.cer

На главном сервере создаём пользователя MirrorServerUser:

USE MASTER
GO
IF NOT EXISTS (SELECT 1 FROM sys.syslogins WHERE name = 'MirrorServerUser')
CREATE LOGIN MirrorServerUser WITH PASSWORD = 'пароль'
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 = 'C:\certs\MirrorServerCert.cer'
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO MirrorServerUser
GO

На зеркале создаём пользователя PrincipalServerUser:

USE MASTER
GO
IF NOT EXISTS (SELECT 1 FROM sys.syslogins WHERE name = 'PrincipalServerUser')
CREATE LOGIN PrincipalServerUser WITH PASSWORD = 'пароль'
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 = 'C:\certs\PrincipalServerCert.cer'
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO PrincipalServerUser
GO

Сервера настроены. В данном примере срок действия сертификатов - 10 лет.

Настраиваем зеркало базы данных

Делаем полный бэкап базы данных, потом делаем бэкап лога транзакций. Восстанавливаем на зеркальном сервере в режиме NORECOVERY и включаем режим зеркалирования. Это можно также сделать в GUI.

Делаем полный бэкап:

BACKUP DATABASE [MIRROR_TEST] TO DISK = N'\\MSSQLMIRRORSERV\D$\MIRROR_TEST.bak' 
WITH FORMAT, INIT, NAME = N'MIRROR_TEST-Full Database Backup',STATS = 10

Восстанавливаем его на зеркале:

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'

Выполняем на зеркале:

ALTER DATABASE MIRROR_TEST SET PARTNER = 'TCP://MSSQLMAINSERV:5022'

Выполняем на главном сервере:

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 "MIRROR_TEST" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

то нужно сделать бэкап лога транзакций на главном сервере:

BACKUP LOG MIRROR_TEST TO DISK = N'\\MSSQLMIRRORSERV\D$\MIRROR_TEST.trn'

и восстановить его на зеркале в режиме NORECOVERY:

RESTORE LOG MIRROR_TEST FROM DISK = 'D:\MIRROR_TEST.trn' WITH NORECOVERY

потом повторить на зеркале:

ALTER DATABASE MIRROR_TEST SET PARTNER = 'TCP://MSSQLMAINSERV:5022'

и на главном:

ALTER DATABASE MIRROR_TEST SET PARTNER = 'TCP://MSSQLMIRRORSERV:5023'

Восстановление после сбоев

Если необходимо изменить роли сервера:

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

Дополнительно

Удалить зеркальную БД из зеркала:

ALTER DATABASE [MIRROR_TEST] SET PARTNER OFF 
RESTORE DATABASE [MIRROR_TEST] WITH RECOVERY

Разобрать всё на зеркале:

DROP CERTIFICATE PrincipalDBCertPub
DROP USER PrincipalServerUser
DROP LOGIN PrincipalServerUser
-- Удалить руками сертификаты с C:\certs
DROP ENDPOINT DBMirrorEndPoint
DROP CERTIFICATE MirrorServerCert
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
DROP MASTER KEY

Разобрать всё на главном principal:

DROP CERTIFICATE MirrorDBCertPub 
DROP USER MirrorServerUser
DROP LOGIN MirrorServerUser
-- Удалить руками сертификаты с C:\certs
DROP ENDPOINT DBMirrorEndPoint
DROP CERTIFICATE PrincipalServerCert
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
DROP MASTER KEY

Что мешает жаться логу транзакций:

SELECT name, log_reuse_wait_desc FROM sys.databases

Статус зеркалирования:

SELECT DB_NAME (database_ID), mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc, mirroring_partner_name, mirroring_witness_state_desc, mirroring_witness_name 
FROM sys.database_mirroring

Приостановка:

ALTER DATABASE [MIRROR_TEST] SET PARTNER SUSPEND

Продолжение:

ALTER DATABASE [MIRROR_TEST] SET PARTNER RESUME

Ссылки

Разделы руководства по зеркальному отображению баз данных

 

Теги

💰 Поддержать проект

 

Похожие материалы

Почитать