Зеркалирование двух серверов друг на друга в Microsoft SQL Server 2019

Олег

28 апреля 2022

Database mirroring — полезное решение для повышения доступности базы данных. Если ваш сервер упал, посыпались винты, сгорел ЦОД, то возможность быстро переключиться на резервный сервер экономит время, деньги и нервы.

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

Я уже писал статьи о настройке зеркалирования:

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

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

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

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

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

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

sql — Режимы зеркалирования (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 на зеркальный сервер. В данном режиме работы данные записываются сразу на два сервера в режиме транзакции. Тем самым обеспечивается синхронность данных и мы можем быть уверены в том, что в случае падения главного сервера мы не потеряем данные на зеркальном.

Подготовка базы данных

Имеем два сервера:

d-1с00
m-1с00

Версия MS SQL Server на обоих серверах должна быть одинаковая. У меня MS SQL Server 2019 (15.0.4198.2).

Мы будем настраивать зеркалирование в синхронном режиме без автоматического восстановления.

На сервере d-1с00 есть база store_hrm30, будем её зеркалировать на сервер m-1с00.
На сервере m-1с00 есть база erp, будем её зеркалировать на сервер d-1с00.

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

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

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

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

Для настройки зеркалирования обеспечиваем доступ между серверами. На обоих серверах создаём папку:

C:\certs

На сервере d-1с00 создаём сертификат D_ServerCert.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 = 'D_ServerCert'
)
	CREATE CERTIFICATE D_ServerCert
	WITH SUBJECT = 'D Server Certificate', 
	START_DATE = '03/26/2022',
	EXPIRY_DATE = '03/26/2032';
GO
BACKUP CERTIFICATE D_ServerCert 
TO FILE = 'C:\certs\D_ServerCert.cer'

При генерации сертификата START_DATE устанавливайте на "вчера", чтобы сертификат был уже действительным, а EXPIRY_DATE — на несколько лет вперёд. После выполнения скрипта на сервере d-1с00 появляется файл C:\certs\D_ServerCert.cer.

На сервере d-1с00 создаём контрольную точку DBMirrorEndPoint на порту 5022, связанную с сертификатом D_ServerCert:

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

На сервере m-1с00 создаём сертификат M_ServerCert.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 = 'M_ServerCert'
)
	CREATE CERTIFICATE M_ServerCert
	WITH SUBJECT = 'M Server Certificate', 
	START_DATE = '03/26/2022',
	EXPIRY_DATE = '03/26/2032';
GO
BACKUP CERTIFICATE M_ServerCert 
TO FILE = 'C:\certs\M_ServerCert.cer'

При генерации сертификата START_DATE устанавливайте на "вчера", чтобы сертификат был уже действительным, а EXPIRY_DATE — на несколько лет вперёд. После выполнения скрипта на сервере m-1с00 появляется файл C:\certs\M_ServerCert.cer.

На сервере m-1с00 создаём контрольную точку DBMirrorEndPoint на порту 5022, связанную с сертификатом M_ServerCert:

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

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

C:\certs\D_ServerCert.cer
C:\certs\M_ServerCert.cer

На сервере d-1с00 создаём пользователя M_ServerUser. Не забываем указать свой пароль. Привязываем к нему сертификат сервера m-1c00.

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

На сервере m-1с00 создаём пользователя D_ServerUser. Не забываем указать свой пароль. Привязываем к нему сертификат сервера d-1c00.

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

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

Настраиваем зеркало с первого сервера на второй

На сервере d-1c00 делаем полный бэкап базы данных store_hrm30 плюс бэкап лога транзакций, бэкапим сразу на второй сервер по сети:

BACKUP DATABASE [store_hrm30] 
TO DISK = N'\\m-1c00\e$\store_hrm30.bak' WITH FORMAT, 
INIT, NAME = N'store_hrm30-Full Database Backup',STATS = 10

BACKUP LOG [store_hrm30]  
TO DISK = N'\\m-1c00\e$\store_hrm3.trn'

На сервере m-1c00 восстанавливаем бэкап:

RESTORE DATABASE [store_hrm30] 
FROM  DISK = N'E:\store_hrm30.bak' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 10,
MOVE N'store_hrm30' 
TO N'E:\SQL\MSSQL15.MSSQLSERVER\MSSQL\DATA\store_hrm30.mdf',
MOVE N'store_hrm30_log' 
TO N'E:\SQL\MSSQL15.MSSQLSERVER\MSSQL\DATA\store_hrm30_log.ldf'

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

На сервере m-1c00 включаем зеркалирование:

ALTER DATABASE store_hrm30 SET PARTNER = 'TCP://d-1c00:5022'

На сервере d-1c00 включаем зеркалирование:

ALTER DATABASE store_hrm30 SET PARTNER = 'TCP://m-1c00:5022'

Настраиваем зеркало со второго сервера на первый

По аналогии делаем зеркало базы данных erp, принцип тот же, только сервера меняются местами.

На сервере m-1c00 делаем полный бэкап базы данных erp плюс бэкап лога транзакций, бэкапим сразу на второй сервер по сети:

BACKUP DATABASE [erp] 
TO DISK = N'\\d-1c00\e$\erp.bak' WITH FORMAT, 
INIT, NAME = N'erp-Full Database Backup',STATS = 10

BACKUP LOG [erp]  
TO DISK = N'\\d-1c00\e$\erp.trn'

На сервере d-1c00 восстанавливаем бэкап:

RESTORE DATABASE [erp] 
FROM  DISK = N'E:\erp.bak' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 10,
MOVE N'erp' 
TO N'E:\SQL\MSSQL15.MSSQLSERVER\MSSQL\DATA\erp.mdf',
MOVE N'erp_log' 
TO N'E:\SQL\MSSQL15.MSSQLSERVER\MSSQL\DATA\erp_log.ldf'

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

На сервере d-1c00 включаем зеркалирование:

ALTER DATABASE erp SET PARTNER = 'TCP://m-1c00:5022'

На сервере m-1c00 включаем зеркалирование:

ALTER DATABASE erp SET PARTNER = 'TCP://d-1c00:5022'

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

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

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

BACKUP LOG [erp]  
TO DISK = N'\\d-1c00\e$\erp.trn'

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

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

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

ALTER DATABASE erp SET PARTNER = 'TCP://m-1c00:5022'

и на главном:

ALTER DATABASE erp SET PARTNER = 'TCP://d-1c00:5022'

Зеркалирование настроено.

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

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

ALTER DATABASE erp SET PARTNER FAILOVER

Если сломалась зеркальная база, то после возобновления её работы зеркало восстанавливается автоматически.

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

ALTER DATABASE erp SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

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