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

Tsql теория > Инструкции по администрированию зеркального отображения базы данных
26.02.2013 12:37:29


Наиболее часто встречающиеся слова в статье:

[CERTIFICATE] [MIRROR_TEST] [сервере] [DATABASE] [ENCRYPTION] [database] [главном] [PARTNER] [Создадим] [PASSWORD]


Статья:

Для связи серверов друг с другом на обоих машинах создаются контрольные точки, открываются порты на соединение, создаются пользователи, сертификаты и пр.
Создадим контрольные точки, для авторизации мы будем использовать сертификат сгенерированный MS SQL сервером (так же можно использовать и другие сертификаты).

1. Создаём сертификат на главном сервере и сохраним его в паку D:\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 = '08/15/2011',
EXPIRY_DATE = '08/15/2021';
GO
BACKUP CERTIFICATE PrincipalServerCert TO FILE = 'D:\Certs\PrincipalServerCert.cer'


2. Создадим контрольную точку DBMirrorEndPoint на главном сервере.
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
) 


3. Создаём сертификат и контрольную точку DBMirrorEndPoint на зеркале, по аналогии с главным.
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 = '08/15/2011',
EXPIRY_DATE = '08/15/2021';
GO
BACKUP CERTIFICATE MirrorServerCert TO FILE = 'D:\Certs\MirrorServerCert.cer'

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
)


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

4. Копируем сертификаты с одного на другой сервак, чтобы в папке D:\Certs лежало по 2 сертификата.


5. Создадим на главном сервере пользователя MirrorServerUser, этого пользователь привязываем к сгенерированному и скопированному с зеркального сервера сертификату MirrorDBCertPub 
USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = 'MirrorServerUser')
CREATE LOGIN MirrorServerUser WITH PASSWORD = 'секретныйпароль2'
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 = 'D:\Certs\MirrorServerCert.cer'
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO MirrorServerUser
GO


6. Создадим на резервном сервере пользователя PrincipalServerUser, этого пользователь привязываем к сгенерированному и скопированному с главного сервера сертификату PrincipalDBCertPub 
USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = 'PrincipalServerUser')
CREATE LOGIN PrincipalServerUser WITH PASSWORD = 'секретныйпароль2'
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 = 'D:\Certs\PrincipalServerCert.cer'
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO PrincipalServerUser
GO


Связь между серверами настроена!

Часть 2. Настройка баз данных. 
Здесь нам надо будет снять бэкап с рабочей базы, поднять его на зеркальном сервере в режиме NORECOVERY и включить режим зеркалирования. 
Зеркалируемая база данных должна иметь модель восстановления FULL.

1. Снимаем бэкап рабочей БД.
BACKUP DATABASE [MIRROR_TEST] TO DISK = N'D:\MIRROR_TEST.bak' 
WITH FORMAT, INIT, NAME = N'MIRROR_TEST-Full Database Backup',STATS = 10


2. Поднимаем его на зеркальном (скрипт подразумевает, что файл бэкапа перенесён на зеркальный сервак на диск D)
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'


3. Для запуска зеркалирования на зеркальном сервере выполняем:
ALTER DATABASE MIRROR_TEST SET PARTNER = 'TCP://MSSQLMAINSERV:5022'

4. Затем на главном:
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 "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

Сделайте бэкап журнала с базы на главном сервере и восстановите его на зеркальном (опять же в режиме NORECOVERY).
Бэкап:
BACKUP LOG MIRROR_TEST TO DISK = 'D:\MIRROR_TEST.trn'

Восстановление:
RESTORE LOG MIRROR_TEST 
FROM DISK = 'D:\MIRROR_TEST.trn' WITH NORECOVERY


Часть 3. Восстановление после сбоев. Изменение ролей. 
Изменить роли сервера, чтобы зеркальный стал главным и наобород можно через GUI кликнов правой кнопкой по базе —Task  Mirror  Failover или же через команду T-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