Зеркалирование или mirroring в MS SQL Server 2014 всё ещё есть. Это очень полезное решение для повышения доступности базы данных. Если ваш сервер упал, посыпались винты, сгорел ЦОД, то возможность быстро переключиться на резервный сервер, не занимаясь восстановлением сервера или БД из бэкапа, экономит кучу времени, денег и нервов. Однако, не следует рассматривать зеркалирование как замену резервному копированию, так как оно не спасает от случайного удаления данных.
Классическое использование данного решения, - у вас есть два ЦОДа, в каждом из них находится свой экземпляр MS SQL Server. Кроме того, иногда зеркалирование помогает проводить регламентные работы, когда нежелательно надолго останавливать БД и перезагружать сервер.
Режимы зеркалирования
В Microsoft SQL Server 2014 есть три режима зеркалирования:
- асинхронный,
- синхронный без автоматического восстановления,
- синхронный с автоматическим восстановлением.
Асинхронный
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), при этом лог транзакций жмётся.
Настройка серверов
Версия 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
На зеркале создаём пользователя MirrorServerUser:
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'
Восстановление после сбоев
Если необходимо изменить роли сервера:
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
Теперь вы можете настроить зеркалирование баз данных и управлять переключением ролей серверов.
P.S.
Говорят, что в Microsoft SQL Server 2017 снова вернули зеркалирование, буду проверять.
Источник:
https://internet-lab.ru/mirroring_mssql_2014
Если вам понравилась статья, то ставьте 👍🏻 каналу.
Пишите комментарии, задавайте вопросы, подписывайтесь.
