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

19.05.2017 13 Comments

Администрирование, Администрирование данных 1С, Архивирование (backup)

, , ,

Очень удобный способ, когда нам нужна не просто резервная копия, а «горячая» замена серверов.

Для настройки зеркалирования на MSSQL для начала необходимо настроить  доступ между ними.
Один сервер у нас будет основной, а второй зеркальный.
Первое что надо сделать, это настроить доступ между этими серверами, разрешить порты (по молчанию 1433-1434) и наши порты, которые мы укажем в настройках.
ВАЖНО Наша база данных должна иметь модель восстановления “FULL”
И так
1.  Создаем сертификат и контрольную точку «DBMirrorEndPoint» на основном сервере, указав в сертификате пароль, дату актуальности и сохраним его в папку (для примера в D:Certificate), также укажем порт для соединения (для примера 5022)

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 = '17/05/2026',
EXPIRY_DATE = '17/05/2027';
GO
BACKUP CERTIFICATE PrincipalServerCert TO FILE = 'D:CertificatePrincipalServerCert.cer'

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
)

2.  Создаем сертификат и контрольную точку «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 = '17/05/2026',
EXPIRY_DATE = '17/05/2026';
GO
BACKUP CERTIFICATE MirrorServerCert TO FILE = = 'D:CertificateMirrorServerCert.cer'
GO
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
)

У нас есть сертификаты и контрольные точки, для соединения 2 серверов необходимо создать юзеров на обоих, привязав их к нашим сертификатам.

3. Копируем сертификаты с одного сервера на другой, в папке с сертификатами (у нас ‘D:Certificate’) должно быть по 2 сертификата.

4. Создаем на основном сервере юзера «MirrorUser», этого юзера привязываем к сертификату из зеркала «MirrorServerCert»

USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = 'MirrorUser')
CREATE LOGIN MirrorUser WITH PASSWORD = 'пароль юзера'
IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE name = 'MirrorUser')
CREATE USER MirrorUser;
IF NOT EXISTS(SELECT 1 FROM sys.certificates WHERE name = 'MirrorDBCertPub')
CREATE CERTIFICATE MirrorDBCertPub AUTHORIZATION MirrorUser
FROM FILE = 'D:CertificateMirrorServerCert.cer'
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO MirrorUser
GO

5. Создаем на зеркале юзера «PrincipalUser», этого юзера привязываем к сертификату из основного сервера
«PrincipalDBCertPub»

USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = 'PrincipalUser')
CREATE LOGIN PrincipalUser WITH PASSWORD = 'пароль юзера'
IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE name = 'PrincipalUser')
CREATE USER PrincipalUser;
IF NOT EXISTS(SELECT 1 FROM sys.certificates WHERE name = 'PrincipalDBCertPub')
CREATE CERTIFICATE PrincipalDBCertPub AUTHORIZATION PrincipalUser
FROM FILE = 'D:CertificatePrincipalServerCert.cer'
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO PrincipalUser
GO

Связь между серверами готова. Теперь надо настроить базы данных.

1. Делаем бэкап рабочей базы.
BACKUP DATABASE [Наша база] TO DISK = N’D:Наша база.bak’ 
WITH FORMAT, INIT, NAME = N’MIRROR_TEST-Full Database Backup’,STATS = 10
2. Поднимаем бэкап на зеркале

Перенесем файл бэкапа на зеркало (у нас, в корень диска D), укажем путь к файлам БД

RESTORE DATABASE [Наша база]
FROM DISK = ‘D: Наша база.bak’ WITH NORECOVERY
,MOVE N’MIRROR_TEST’ TO N’D:MSSQL_DBНаша база.mdf’
,MOVE N’MIRROR_TEST_log’ TO N’D:MSSQL_DBНаша база_log.ldf’
3. Запускаем зеркалирование на зеркале

ALTER DATABASE MIRROR_TEST SET PARTNER = ‘TCP://основной сервер:5022’

4. Потом на основном

ALTER DATABASE MIRROR_TEST SET PARTNER = ‘TCP://зеркало:5023’
5. Для того чтобы подключатся с 1С к любой из баз необходимо применить асинхронный режим зеркалирования 

— Task — Mirror — Hight performance (asynchronous)  
(— Задачи — Создать зеркальное отображение — Высокая производительность (асинхронный))

Теперь можно работать на двух базах одновременно. Данные будут передаватся между ними

Изменить роли сервера можно в
— Task — Mirror — Failover  
(— Задачи — Создать зеркальное отображение — Отработка отказа)

Если основная БД упала, то нужно оживить зеркало запустив
ALTER DATABASE MIRROR_TEST SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
После этой команды зеркальная база становится основной, а основная после решения проблем станет зеркальной и будет синхронизироваться з основной

Related Posts

13 Comments

  1. МихаилМ

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

    а также забыто , что будет при реструктуризации

    Reply ↓
  2. ArchLord42

    Есть несколько нюансов, которые не описал автор:

    — Для того чтобы не производить ручную обработку отказа (ALT ER DATABASE MIRROR_TEST SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS), можно поставить третий — Witness (следящий) сервер, который будет эти заниматься в автоматическом режиме.

    — Как верно заметил Михаил (1), есть особенность реструктуризации, а точнее ее невозможность в некоторых случая, будучи в режиме зеркалирования.

    Вводится ограничение на создание новых справочников, доков, регистров, ПВХ, вы попросту не сможете обновится с ошибкой:

    Операция не может быть выполнена для базы данных «test», так как она участвует в сеансе зеркального отображения или группе доступности. Некоторые операции недопустимы для баз данных, участвующих в сеансе зеркального отображения или группе доступности.

    Для того, чтобы обновить 1С, вам будет нужно отключить зеркалирования, а после включить обратно (проще всего сделать скрип на вкл и выкл)

    — Зеркалирование доступно с версии >= 2005 MSSQL

    — Во избежании коллизий и потерь данных, не рекомендуется работать сразу в 2х базах в асинхронном режиме, т.к. по сути это будет работа с 2х разных ИБ подключенных к одной БД, что чревато, но синхронный режим замедляет (не сильно) работу, т.к. ждет окончания транзакций во второй базе тоже.

    — В синхронном режиме, к резервной базе доступа нет, она всегда находится в режиме восстановления, но в версиях MSSQL >= 2012 при подключении через Microsoft SQL Server Native Client (так же используется сервером 1С) происходит редирект на основную базу.

    — Если поднять Windows Server Failover Clustering (WSFC), лучше использовать AlwaysOn (легко гуглится)

    Reply ↓
  3. MsDjuice

    (2)

    чтобы обновить 1С, вам будет нужно отключить зеркалирования, а после включить обратно (проще всего сделать скрип на вкл и выкл)

    Это при каждом обновлении будет проблема такая, или иногда?

    Reply ↓
  4. ArchLord42

    (3) да, только если вы добавляете новые объекты МД, который я описал выше.

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

    Reply ↓
  5. oldfornit

    чем это лучше использования always-on MS SQL?

    Reply ↓
  6. ArchLord42

    (5)

    т.к. подключение идет к кластеру, а не к конкретной базе, при отказе одной БД юзеры, которые ни чего не проводилизаписывали ничего не почувствуют, при условии что развернут кластер 1С (если БД и 1С на одном сервере находятся).

    Можно юзать резевную БД, но только в режими readonly.

    ЗЫ не заметил, что вы наоборот спросили, ответ : ничем, только если ценой развертывания и возможности географического распределения серверов.

    Reply ↓
  7. Mortum

    Always-on вроде домен требует, а зеркалирование, как уже написали, не позволит добавлять новые таблицы. Лучше уж настроить transaction log shipping с нужным интервалом времени, за который не жалко потерять данные. Из минусов только потеря части данных с момента последнего бэкапа логов и ручное переключение на резервный сервер.

    Reply ↓
  8. ArchLord42

    (7)домен ему априори нужен т.к. все таки главное требование это фейл овер кластер винды, что намного затратнее)

    Reply ↓
  9. MsDjuice

    Пробовали добавлять новые таблицы: справочники, документы с реквизитами разных типов. Все отлично переносит. Может у кого-то работало нестабильно, нам же на наших базах не удалось воссоздать проблему.

    Reply ↓
  10. demaxxx

    А есть где нибудь инструкция, как делать быстрое зеркало базы в рамках одного сервера?

    Т.е. быстро делать снимок и сразу же заливать в другую для экспериментов. Вручную или по расписанию.

    Reply ↓
  11. ArchLord42

    (10) Вы можете установить два именных экземпляра (instance) на один сервер и работать с ними.

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

    Reply ↓
  12. ArchLord42

    (2) (9)

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

    Вводится ограничение на создание новых справочников, доков, регистров, ПВХ, вы попросту не сможете обновится с ошибкой:

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

    В общем я решил проверить на тестовой базе, запустил еще раз тестовую базу и вот результат:

    1) попытался создать первый док — ошибка.

    2) отрубил зеркалирование, обновился запустил зеркалирование.

    3) Добавил второй док — все ок

    4) Начал добавлять разные объекты, все ок опять же.

    при добавлении первого дока идет еще какие-то изменения в БД (скрин)

    ЗЫ. Написанное мной в первом посте по поводу обновлений, это мой личный опыт работы с двумя конфигурациями на БСП, возможно при некоторых обстоятельствах возможность обновить отсутствует, при других вы сможете спокойно обновляться.

    Reply ↓
  13. user762605

    1. Зеркалирование — жалкая попытка убежать от невозможности реплицирования. Ничем по сути от него не отличающаяся. Зеркалирование не дает преимущества, которое написано ниже по отношению к AlwaysOn (который требует всего то втащить сервер с mssql в домен).

    2. Репликация (sic!) 1Сной базы не возможна ни один вариант из 3х предлагаемых не подходит. (Это вы поймете прочитав элементарное описание технологий)

    Не удивляйтесь при использовании 1 и 2, если увидите «база разрушена/повреждена» и т.п.

    3. Единственный выход — alwayson, который дает хоть какое то подобие failover для 1С базы. Опять же надо решить вопрос с бэкапированием.

    В добавок к alwayson есть softpoint, который позволит шуршать довольно быстренько. В обход softpoint’а можете заставить своих разрабов перевести все запросы на чтение к secondary mssql-серверу (идеальный мир, ну).

    Reply ↓

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *