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

Олег

  • 27 марта 2020
Microsoft SQL Server 2019

Зеркалирование или mirroring в Microsoft SQL Server 2019 есть. Нас пугали, что эта опция будет недоступна, но, к счастью системных администраторов, этого не произошло.

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

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

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

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

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

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

sql

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

На главном сервере создаю тестовую базу данных mirror_test.

sql

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

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

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

sql

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

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

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

C:\certs

sql

На главном сервере создаём сертификат PrincipalServerCert.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 = 'PrincipalServerCert'
)
	CREATE CERTIFICATE PrincipalServerCert
	WITH SUBJECT = 'Principal Server Certificate', 
	START_DATE = '03/26/2020',
	EXPIRY_DATE = '03/26/2030';
GO
BACKUP CERTIFICATE PrincipalServerCert 
TO FILE = 'C:\certs\PrincipalServerCert.cer'

При генерации сертификата START_DATE устанавливайте на "вчера", чтобы сертификат был уже действительным, а EXPIRY_DATE — на несколько лет вперёд.

sql

После выполнения скрипта на главном сервере появляется файл C:\certs\PrincipalServerCert.cer.

sql

На главном сервере создаём контрольную точку 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
	)

sql

На зеркале создаём сертификат MirrorServerCert.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 = 'MirrorServerCert'
)
	CREATE CERTIFICATE MirrorServerCert
	WITH SUBJECT = 'Mirror Server Certificate',
	START_DATE = '03/26/2020',
	EXPIRY_DATE = '03/26/2030';
GO
BACKUP CERTIFICATE MirrorServerCert 
TO FILE = 'C:\certs\MirrorServerCert.cer'

При генерации сертификата START_DATE устанавливайте на "вчера", чтобы сертификат был уже действительным, а EXPIRY_DATE — на несколько лет вперёд.

sql

После выполнения скрипта на зеркале появляется файл C:\certs\MirrorServerCert.cer.

sql

На зеркале создаём контрольную точку 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
	)

sql

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

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

sql

На главном сервере создаём пользователя 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

sql

На зеркале создаём пользователя 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

sql

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

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

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

Делаем бэкап:

BACKUP DATABASE [mirror_test] 
TO DISK = N'\\MIRROR_SERVERNAME\c$\distrib\mirror_test.bak' WITH FORMAT, 
INIT, NAME = N'mirror_test-Full Database Backup',STATS = 10

BACKUP LOG [mirror_test]  
TO DISK = N'\\MIRROR_SERVERNAME\c$\distrib\mirror_test.trn'

sql

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

RESTORE DATABASE [mirror_test] 
FROM  DISK = N'C:\distrib\mirror_test.bak' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 10,
MOVE N'mirror_test' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\mirror_test.mdf',
MOVE N'mirror_test_log' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\mirror_test_log.ldf'

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

sql

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

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

sql

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

ALTER DATABASE mirror_test SET PARTNER = 'TCP://MIRROR_SERVERNAME:5023'

sql

Если произошла ошибка:

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'\\MIRROR_SERVERNAME\c$\distrib\mirror_test.trn'

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

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

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

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

и на главном:

ALTER DATABASE mirror_test SET PARTNER = 'TCP://MIRROR_SERVERNAME:5023'

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

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

Теги

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

 

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

Maintenance Plans — резервное копирование и обслуживание баз данных в Microsoft SQL Server 2014

Олег

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

Теги

Почитать