Дзеркалювання бази даних на 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

Теми, що посилаються на статтю:

Вгору