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

В данной инструкции рассмотрим возможности MS SQL Server по обеспечению отказоустойчивости баз данных. Основными технологиями для этого являются зеркалирование (mirroring), а также создание групп высокой доступности AlwaysOn. Но группы высокой доступности требуют приобретение лицензий редакции Enterprise, что во многих случаях экономически нецелесообразно. Остановимся подробней на возможностях зеркалирования и его настройке.

Помните, что нельзя рассматривать зеркалирование в качестве замены резервного копирования, так как оно не спасает от случайного удаления данных.

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

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

Асинхронный

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

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

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

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

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

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

Мы будем настраивать зеркалирование в синхронном режиме без автоматического восстановления, что потребует развертывания только одного дополнительного сервера. Версия MS SQL Server 2019 на главном и зеркальном серверах должна быть одинаковая. Начинаем с создания сертификатов. На обоих серверах создаём локальный каталог, в нашем примере он находится по пути 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 = 'MainServer'
)
	CREATE CERTIFICATE MainServer
	WITH SUBJECT = 'Main Server Certificate', 
	START_DATE = '01/01/2024',
	EXPIRY_DATE = '01/01/2030';
GO
BACKUP CERTIFICATE MainServer
TO FILE = 'C:\certs\MainServer.cer'

Затем на зеркальном сервере создадим сертификат по аналогии:

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 = 'MirrorServer'
)
	CREATE CERTIFICATE MirrorServer
	WITH SUBJECT = 'Mirror Server Certificate',
	START_DATE = '01/01/2024',
	EXPIRY_DATE = '01/01/2030';
GO
BACKUP CERTIFICATE MirrorServer
TO FILE = 'C:\certs\MirrorServer.cer'

Копируем созданные сертификаты на оба сервера в папку C:\Certs. Cоздаем на каждой стороне точку прослушивания DBMirrorEndPoint, слушающей порт TCP 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 MainServer, 
ENCRYPTION = REQUIRED, 
ROLE = ALL
)

И на зеркальном:

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 MirrorServer, 
ENCRYPTION = REQUIRED, 
ROLE = ALL
)

Создаем пользователей SQL сервера и назначаем права для возможности подключения. На главном сервере:

 
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\MirrorServer.cer'
	GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint 
	TO MirrorServerUser
GO

И на зеркале:

USE MASTER
GO
IF NOT EXISTS (
	SELECT 1 FROM sys.syslogins WHERE name = 'MainServerUser'
)
	CREATE LOGIN MainServerUser WITH PASSWORD = 'пароль'
IF NOT EXISTS (
	SELECT 1 FROM sys.sysusers WHERE name = 'MainlServerUser'
)
	CREATE USER MainServerUser;
IF NOT EXISTS (
	SELECT 1 FROM sys.certificates WHERE name = 'MainDBCertPub'
)
	CREATE CERTIFICATE MainDBCertPub 
	AUTHORIZATION MainServerUser
	FROM FILE = 'C:\certs\MainServer.cer'
	GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint 
	TO MainServerUser
GO

На этом начальную настройку серверов можно считать законченной.

Создание зеркала базы данных

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

В нашем примере будем использовать базу mirror_test. Делаем полный бэкап базы данных плюс бэкап лога транзакций:

BACKUP DATABASE [mirror_test] 
TO DISK = N'\\backup_share\mirror_test.bak' WITH FORMAT, 
INIT, NAME = N'mirror_test-Full',STATS = 10

BACKUP LOG [mirror_test]  
TO DISK = N'\\backup_share\mirror_test.trn'

Восстанавливаем бэкап в режиме NORECOVERY на зеркальном сервере, указав актуальные пути к файлам бэкапа и расположения БД:

RESTORE DATABASE [mirror_test] 
FROM  DISK = N'\\backup_share\mirror_test.bak' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 10,
MOVE N'mirror_test' 
TO N'S:\SQL_Bases\mirror_test.mdf',
MOVE N'mirror_test_log' 
TO N'E:\SQL_Log\mirror_test_log.ldf'

RESTORE LOG [mirror_test] 
FROM  DISK = N'\\backup_share\mirror_test.trn' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 10

Настраиваем репликацию тестовой БД через ранее созданные точки прослушивания. Выполняем на зеркальном сервере подключение:

ALTER DATABASE mirror_test SET PARTNER = 'TCP://MAINSERVER_IP:5022'

Затем на главном:

ALTER DATABASE mirror_test SET PARTNER = 'TCP://MIRRORSERVER_IP:5022'

Зеркалирование настроено. В случае возникновения ошибок на этом этапе наиболее вероятно, что бэкап журналов сильно отстает от актуального состояния рабочей базы. Выполните повторный бэкап журнала и его восстановление на зеркале в режиме NORECOVERY. После этого повторите попытку подключения. Рассмотрим полезные команды в случае возникновения сбоев на главном сервере. Если необходимо переключить главную реплику:

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

Также технологию зеркалирования можно использовать для выполнения переезда крупных баз на новый сервер практически без простоя и технологического окна. Для этого только необходимо обеспечить доступность порта 5022 через интернет либо VPN туннель.