Зеркалирование базы данных на MS SQL Server 2005/2008

Автор:   Мукан Андрей, mukan@andriy.co

Основной принцип, используемый в зеркалировании баз данных - это связь между двумя экземплярами MSSQL сервера (как правило, физически это два сервера, находящиеся в локальной сети либо в Интернет) и поддержание полной идентичности базы данных на обоих серверах. Один из серверов выступает как главный, к нему подключены пользователи базы данных. Второй сервер является зеркальным и не поддерживает в этом состоянии подключений к базе данных. Главный сервер при изменениях в базе данных доставляет зеркальному журнал с транзакциями, благодаря которому вторая база, зеркальная, всегда идентична основной, главной базе. Зеркалирование позволяет сохранить работоспособность базы данных при выходе из строя одного из сервера базы данных.

Зеркалирование поддерживается 3 режимами работы:

  • Асинхронный режим - изменения в базе данных главного сервера доставляются зеркальному не моментально, при этом главный сервер способен продолжать работу, не дожидаясь подтверждения о выполнении транзакции от зеркального. Минус такого решения очевиден - при внезапном сбое главного сервера и переходе зеркального сервера на роль главного возможна утрата части данных. Плюс - нет падения в производительности.
  • Синхронный режим - изменения, происходящие в основной базе данных после каждой транзакции немедленно доставляются в журнале транзакций зеркальному серверу. Основной сервер готов продолжать работу после успешного внесения изменений в базу данных на обеих серверах. Минус решения - существенное падение быстродействия при связи между серверами через медленные каналы связи (Интернет), в локальных сетях и при небольших нагрузках падение быстродействия несущественно. Плюс такого решения - полная идентичность базы данных на обоих серверах в любой момент времени
  • Защищенный синхронный режим - изменение ролей серверов при сбое главного может происходить автоматически, при использовании третьего сервера-наблюдателя. Минус решения - необходимо использовать дополнительно третий сервер, плюс - полная идентичность базы данных на главном и зеркальном серверах, а также автоматическое управление ее статусом на каждом из серверов.

Изменение ролей можно проводить вручную в интерфейсе SQL Managemebt Studio либо при выполнении скрипта - см. далее. При изменении ролей зеркальный сервер становится главным и готов принимать подключения клиентов. Главный же сервер после восстановления работоспособности становится зеркальным.

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

Предварительная подготовка

  • Имеем 2 сервера с установленными MSSQL Server 2008. Функция зеркалирования начала стабильно работать и в SQL Server 2005 после установки SP4, поэтому тем, у кого установлен SQL Server 2005 необходимо скачать с сайта производителя и установить данный сервис-пак. Автору статьи удавалось настроить зеркалирование на SQL Server 2005 SP4, однако при, казалось бы, абсолютно одинаковых условиях аналогичная настройка на других парах серверов не всегда приводила к успеху, венчая кропотливую работу ошибкой MSSQLServer_1418 (описание ниже). На SQL Server 2008 процент успеха повыше.
  • Адрес главного сервера - 192.168.1.11, адрес зеркального - 192.168.1.12. Имя базы данных на главном сервере - TCU_DB
  • Будем использовать проверку подлинности соединения по сертификатам, как наиболее простой способ аутентификации в среде Интернет. В локальной сети также можно настроить зеркалирование через интерфейс SQL Server Management Studio (Task -> Mirror) по Windows или SQL Server аутентификации. Прочтите Использование сертификатов для зеркального отображения базы данных
  • Откройте в брандмауэре на обоих серверах входящий и исходящий TCP порт 5022. Также откройте порты TCP/UDP 1433-1434. Активируйте в SQL Server Configuration Manager протокол Named Pipes
  • Выполняйте в SSMS (New Query для каждого блока скриптов) скрипты из таблицы по-порядку, в строгой последовательности. Для каждого сервера берите скрипты из соответсвующего столбца таблиц. Выполнение каждого блока скриптов должно заканчиваться примерно таким сообщением "Command(s) completed successfully". Если в результате выполнения скриптов вы видите сообщения красным текстом, значит, возникли ошибки.
  • На главном сервере в SQL Server Management Studio щелчок правой кнопкой мышина базе TCU_DB -> Properties -> Options -> Recovery model:Full -> OK

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

ГЛАВНЫЙ (PRINCIPAL). 192.168.1.11 ЗЕРКАЛО (MIRROR). 192.168.1.12
1. Создаем контрольные точки и сертификаты в папках C:\Certs на главном и на зеркальном серверах
  1. --> This is the setup of the PRINCIPAL (master) in a mirroring environment
  2. USE master
  3. GO
  4. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
  5. GO
  6. --> Create a name not using "master" or "principal", remember: the roles can
  7. --  switch but the certificates remain the same.
  8. CREATE CERTIFICATE SQLSrv1_mirroring_cert
  9.   WITH SUBJECT = 'SQLSrv1 certificate',
  10.    START_DATE = '2011-01-01 00:00:00',
  11.    EXPIRY_DATE = '2030-01-01 00:00:00'
  12. GO
  13. CREATE ENDPOINT Mirroring_Endpoint
  14.   STATE = STARTED
  15.   AS TCP (
  16.    LISTENER_PORT = 5022,
  17.    LISTENER_IP = ALL)
  18.   FOR DATABASE_MIRRORING (
  19.    AUTHENTICATION = CERTIFICATE SQLSrv1_mirroring_cert,
  20.    ENCRYPTION = REQUIRED ALGORITHM AES,
  21.    ROLE = ALL)
  22. GO
  23. --> Backup certificate and transfer to SQLSrv2
  24. BACKUP CERTIFICATE SQLSrv1_mirroring_cert TO FILE = 'C:\Certs\SQLSrv1_mirroring_cert.cer';
  25. GO
  1. --> This is the setup of the MIRROR (slave) in a mirroring environment
  2. USE master
  3. GO
  4. --> Make sure a master key is set
  5. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
  6. GO
  7. -->
  8. CREATE CERTIFICATE SQLSrv2_mirroring_cert
  9.   WITH SUBJECT = 'SQLSrv2 certificate',
  10.    START_DATE = '2011-01-01 00:00:00',
  11.    EXPIRY_DATE = '2030-01-01 00:00:00'
  12. GO
  13. CREATE ENDPOINT Mirroring_Endpoint
  14.   STATE = STARTED
  15.   AS TCP (
  16.    LISTENER_PORT = 5022,
  17.    LISTENER_IP = ALL)
  18.   FOR DATABASE_MIRRORING (
  19.    AUTHENTICATION = CERTIFICATE SQLSrv2_mirroring_cert,
  20.    ENCRYPTION = REQUIRED ALGORITHM AES,
  21.    ROLE = ALL)
  22. GO
  23. --> Backup certificate and transfer to SQLSrv1
  24. BACKUP CERTIFICATE SQLSrv2_mirroring_cert TO FILE = 'C:\Certs\SQLSrv2_mirroring_cert.cer';
  25. GO
2. Обмениваемся сертификатами между серверами. На каждом сервере должно быть по 2 сертификата в папке C:\Certs
3. Создаем пользователей, привязанных к сертификатам
  1. USE master
  2. GO
  3. --> Create a login for the other server process
  4. CREATE LOGIN SQLSrv2_mirroring_login WITH PASSWORD = 'password';
  5. GO
  6. --> Create a user for the new login
  7. CREATE USER SQLSrv2_mirroring_user FOR LOGIN SQLSrv2_mirroring_login;
  8. GO
  9. --> Associate the certificate with the user.
  10. CREATE CERTIFICATE SQLSrv2_cert
  11.   AUTHORIZATION SQLSrv2_mirroring_user
  12.   FROM FILE = 'C:\Certs\SQLSrv2_mirroring_cert.cer'
  13. GO
  14. --> Grant the user access to the endpoint
  15. GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [SQLSrv2_mirroring_login]
  16. GO
  1. USE master
  2. GO
  3. --> Create a login for the other server process
  4. CREATE LOGIN SQLSrv1_mirroring_login WITH PASSWORD = 'password'
  5. GO
  6. --> Create a user for the new login
  7. CREATE USER SQLSrv1_mirroring_user FOR LOGIN SQLSrv1_mirroring_login;
  8. GO
  9. --> Associate the certificate with the user.
  10. CREATE CERTIFICATE SQLSrv1_mirroring_cert
  11.   AUTHORIZATION SQLSrv1_mirroring_user
  12.   FROM FILE = 'C:\Certs\SQLSrv1_mirroring_cert.cer'
  13. GO
  14. --> Grant the user access to the endpoint
  15. GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [SQLSrv1_mirroring_login]
  16. GO
4. Создаем Full Backup базы данных в корневой папке диска C:\
  1. --> Create a FULL BACKUP of each database to be mirrored
  2. BACKUP DATABASE TCU_DB TO DISK = 'c:\TCU_DB_backup.bak'
5. Переносим полученный бэкап базы данных с главного сервера на зеркальный, в корень диска C:\
6. Восстанавливаем БД из бэкапа (Если положение файлов БД на зеркальном сервере отличается от расположения файлов БД на главном, то можно использовать интерфейс SSMS, в котором указать положение файлов БД либо использовать в скрипте "MOVE", например "RESTORE DATABASE [TCU_DB] FROM DISK = 'C:\TCU_DB_backup.bak' WITH NORECOVERY, MOVE N'TCU_DB' TO N'D:\SQL\TCU_DB.mdf', MOVE N'TCU_DB_log' TO N'D:\SQL\TCU_DB_log.ldf'"):
  1. USE MASTER
  2. GO
  3. --> Copy a backup of the database(s) to be mirrored
  4. --> Restore them using NORECOVERY
  5. RESTORE DATABASE [TCU_DB]
  6.   FROM DISK = N'C:\TCU_DB_backup.bak'
  7.   WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
  8. GO
7. Запускаем зеркалирование на зеркале:
  1. USE MASTER
  2. GO
  3. --> Set up partnering between the mirror and the principal (use a fully qualified name or an IP address)
  4. ALTER DATABASE TCU_DB SET PARTNER ='tcp://192.168.1.11:5022'
  5. GO
8. Запускаем зеркалирование на главном:
  1. USE master
  2. GO
  3. --> Restore these databases to the MIRROR using NORECOVERY
  4. --> Set up partnering between the mirror and the principal (use a fully qualified name or an IP address)
  5. --> Set up partnering between the principal and the mirror (use a fully qualified name or an IP address)
  6. ALTER DATABASE TCU_DB SET PARTNER ='tcp://192.168.1.12:5022'
  7. GO

Примечание 1:

Если после выполнения пункта 8 вы получите сообщения "Msg 1416, Level 16, State 31, Line 1 Database "TCU_DB" is not configured for database mirroring.", то необходимо повторить выполнение пунктов 7 и 8, но с минимальным промежутком времени между ними.

Примечание 2:

Если после выполнения пункта 8 вы получите сообщения "Error: 1478. The mirror database 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." или "Msg 1412, Level 16, State 0, Line 4 The remote copy of database has not been rolled forward to a point in time that is encompassed in the local copy of the database log.", то необходимо сделать бэкап журнала базы данных на главном сервере с последующим его восстановлением на зеркальном:

ГЛАВНЫЙ (PRINCIPAL). 192.168.1.11 ЗЕРКАЛО (MIRROR). 192.168.1.12
  1. BACKUP LOG [TCU_DB] TO DISK = N'C:\TCU_DB_transaction_log_backup.trn' WITH NOFORMAT, NOINIT, NAME = N'TCU_DB-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
  2. GO
Переносим полученный бэкап журнала с главного сервера на зеркальный, в корень диска C:\
  1. RESTORE LOG [TCU_DB] FROM DISK = N'C:\TCU_DB_transaction_log_backup.trn' WITH FILE = 1,  NORECOVERY, NOUNLOAD, STATS = 10
  2. GO

После этого повторите выполнение пунктов 7 и 8

Примечание 3:

Если после выполнения пункта 8 вы получите сообщение "The server network address "TCP://192.168.1.12:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.", то проверьте доступность зеркального сервера по этому адресу, открытость порта TCP 5022 в брандмауэре, прослушивание этого порта на зеркальном и главном серверах с помощью команды netstat -abn, а также попробуйте подключиться по этому порту с одного сервера на другой, используя команду telnet 192.168.1.11 5022 и telnet 192.168.1.12 5022.

Дополнительные ссылки для решения возможных проблем:

Изменение ролей серверов. Восстановление базы на зеркальном сервере

  • Изменение ролей (Главный <-> Зеркало):
    • Либо в SQL Server Management Studio на главном сервере правая кнопка мыши на базе данных TCU_DB -> Task -> Mirror -> Failover
    • Либо скрипт:
      1. ALTER DATABASE TCU_DB SET PARTNER FAILOVER
  • Восстановление базы на зеркальном сервере после отказа главного. При восстановлении допускается утеря данных, о которой шла речь в начале статьи, поэтому будьте внимательны и осторожны:
    • При выполнении принудительного восстановления зеркальная база становится главной и сможет принимать подключения клиентов:
      1. ALTER DATABASE TCU_DB SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
    • Бывшая главная база после восстановления автоматически станет зеркальной, но будет ожидать команды на продолжение зеркалирования. Для этого необходимо выполнить скрипт в SSMS:
      1. ALTER DATABASE TCU_DB SET PARTNER RESUME
  • При отказе зеркальной базы после ее восстановления предпринимать каких либо действий не нужно - база синхронизируется с главной в кратчайшее время

Пример последовательности действий в типичной ситуации

Ситуация: основной (главный) сервер обесточен либо вышел из строя. Требуется подключить клиентов базы данных к резервной (зеркальной) базе данных

Действия по подключению к резервной базе:

  • Подключаемся к серверу-зеркалу и чтобы восстановить зеркальную базу выполняем скрипт в SSMS:
  • ALTER DATABASE TCU_DB SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
  • Рестарт служб SQL Server
  • База должна иметь статус (Principal, Synchronized). Если же база имеет статус "In Recovery" и он не изменяется после перезапуска служб MSSQL, то следует перезагрузить компьютер. Если статус базы "Principal, Disconnected", то, возможно, могут быть трудности при последущем возврате к обычной схеме работы. Вопрос этот полностью не изучен, возможно, будут дополнения к статье. Пока приходилось полностью восстанавливать зеркалирование с самого начала.
  • После этого клиенты могут работать с базой на зеркальном сервере, ставшего теперь основным

После ремонта и(или) включения бывшего основного сервера:

  • Подключаемся к нему и открываем SSMS:
  • База имеет статус (Mirror, Suspended / Restoring...)
  • Выполняем скрипт:
  • ALTER DATABASE TCU_DB SET PARTNER RESUME
  • Получаем результат - Command(s) completed successfully.
  • Рестарт служб SQL Server
  • База должна иметь статус (Mirror, Synchronized / Restoring...)
  • Используйте в SSMS инструмент мониторинга за зеркалированием баз (как - описано ниже), чтобы убедиться, что данный экземпляр синхронизирован с главной базой. Требуется некоторое время, чтобы изменения в главной базе были перенесены в зеркальную.
  • Переходим на зеркальный сервер (который в данный момент является главным):
  • База имеет статус (Principal, Synchronized)
  • Делаем бэкап базы на всякий случай (по желанию).
  • Меняем роли серверов. Для этого выполняем скрипт:
  • ALTER DATABASE TCU_DB SET PARTNER FAILOVER
  • Статус базы становится (Mirror, Synchronized / Restoring...)
  • Переходим на бывший основной, который опять стал основным, статус базы - (Principal, Synchronized)

После этого клиенты могут работать с базой на главном сервере так же, как и до возникновения данной ситуации.

Заключение

SQL Server Management Studio предоставляет инструмент для мониторинга процесса зеркалирования и синхронизации баз на серверах. Для этого в SSMS зеркального сервера сделайте щелчок правой кнопкой мыши на базе данных TCU_DB -> Tasks -> Launch Database Mirroring Monitor -> Register mirrored database

Если у вас возникла необходимость очистить базу данных от созданных сертификатов, пользователей и логинов, а также в тех случаях, когда необходимо начать настройку заново, выполните в SQL Server Management Studio следующий код:

  1. USE MASTER
  2. GO
  3. IF EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'SQLSrv2_cert')
  4. DROP CERTIFICATE SQLSrv2_cert
  5. GO
  6. IF EXISTS (SELECT 1 FROM sys.sysusers WHERE name = 'SQLSrv1_mirroring_user')
  7. DROP USER SQLSrv1_mirroring_user
  8. GO
  9. IF EXISTS (SELECT 1 FROM sys.sysusers WHERE name = 'SQLSrv2_mirroring_user')
  10. DROP USER SQLSrv2_mirroring_user
  11. GO
  12. IF EXISTS (SELECT 1 FROM sys.syslogins WHERE name = 'SQLSrv1_mirroring_login')
  13. DROP LOGIN SQLSrv1_mirroring_login
  14. GO
  15. IF EXISTS (SELECT 1 FROM sys.syslogins WHERE name = 'SQLSrv2_mirroring_login')
  16. DROP LOGIN SQLSrv2_mirroring_login
  17. GO
  18. IF EXISTS (SELECT * FROM sys.endpoints WHERE name = 'Mirroring_Endpoint')
  19. DROP ENDPOINT Mirroring_Endpoint
  20. GO
  21. IF EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'SQLSrv1_mirroring_cert')
  22. DROP CERTIFICATE SQLSrv1_mirroring_cert
  23. GO
  24. IF EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'SQLSrv2_mirroring_cert')
  25. DROP CERTIFICATE SQLSrv2_mirroring_cert
  26. GO

Статья включена в следующие темы:

Вверх