null

Репликация MS SQL: Mirroring/AlwaysON по сертификатам + Скрипты

17 June 2020 0 Maxim Zhukov

Давно собирался поделиться наработками по настройке зеркалирования (мирроринга, сейчас Basic Always ON Group) в Microsoft SQL Server.

Наработки помогут быстро (почти copy-paste) настроить репликацию между мастером и слейвом, поменяйте только на свои названия и особенности окружения.

Для универсальности аутентификацию между системами будем делать на сертификатах сгенерированных самим MS SQL Server. Это позволяет настроить Mirroring/AlwaysOn между доменной и НЕ доменной машиной, или машинах в разных доменах. То есть это универсальный вариант.

Ранее в другой заметке (доступно по ссылке) я писал о разнице технологий и их применения касательно Microsoft Failover Cluster (отказоустойчивый кластер) SQL SERVER и Мирроринга/Зеркалирования/AlwaysON (синонимы)

Термины

Итак, договоримся о терминологии:

  • Мастер Master - главный узел С КОТОРОГО идёт репликация
  • Слейв Миррор Slave - резервный узел НА КОТОРЫЙ идёт репликация
  • Сертификат Мастера – сертификат сгенерированный на главном узле (мастере)
  • Сертификат Миррора – сертификат сгенерированный на миррор (слейв) узле

Подготовка окружения и описания параметров в скриптов

Перед параметризацией и копированием скриптов прочтите и замените под своё окружение

  • название файлов сертификатов в скрипте для мастера
HOST_MASTER1_cert
  • и для миррора
HOST_MIRROR1_cert
  • путь и название на файловой системе для выгрузки сертификата в скрипте
C:\Mirroring\ HOST_MASTER1_cert.cer

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

  • Пароль сертификата – в примере S#xyAdm1n
  • Название EndPoint для мирроринга
EndPoint Mirroring

 

  • Логин и пользователя для учетной записи на мастер сервере для авторизации миррор
HOST_MIRROR1_login
  • *порт ( по умолчанию LISTENER_PORT=5022)
  • *Стартовая дата – дата начала действия сертификатов START_DATE

Описание действий будет выполняться по пунктам с указанием места выполнения кода 

1. Мастер – создание сертификата

USE master;  
CREATE CERTIFICATE HOST_MASTER1_cert
WITH SUBJECT = 'HOST_MASTER1 certificate',
START_DATE = '05/06/2020'
GO

2. Мастер - создание конечной точки EndPoint 

CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT=5022, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_MASTER1_cert
, ROLE = ALL);
GO

3. Мастер - 'экспорт сертификата на файловую систему

BACKUP CERTIFICATE HOST_MASTER1_cert TO FILE = 'C:\Mirroring\HOST_MASTER1_cert.cer';
GO

4. Копирование сертификата с Мастера на Миррор

Скопируйте полученный на предыдущем шаге файл C:\Mirroring\HOST_MASTER1_cert.cer с  Мастер системы на Миррор

Далее проделываем аналогичные действия на мирроре по этому опишу в одном шаге

5. Миррор - – создание сертификата, конечной точки и экспорт сертификата

CREATE CERTIFICATE HOST_mirror1_cert
WITH SUBJECT = 'HOST_mirror1_certificate',
START_DATE ='05/06/2020'
GO

 

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

AS TCP (

LISTENER_PORT=5022
, LISTENER_IP = ALL

)

FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_mirror1_cert
, ROLE = ALL

);
go

BACKUP CERTIFICATE HOST_MIRROR1_cert TO FILE = 'C:\Mirroring\HOST_MIRROR1_cert.cer';

go

6.Копирование сертификата с Миррора на Мастер

Скопируйте полученный на предыдущем шаге файл C:\Mirroring\HOST_MIRROR1_cert.cer с  Миррора системы на Мастер

 

К этому моменту на обоих системах включены конечные точки зеркалирования (Mirroring Endpoints) и находятся перекрестно сертификаты каждой из систем

7. Мастер - создание пользователя и логина

USE master;


CREATE LOGIN HOST_MIRROR1_login WITH PASSWORD = 'S#xyAdm1n';

GO

CREATE USER HOST_mirror1_user FOR LOGIN HOST_MIRROR1_login;

GO

8. Мастер - ассоциация сертификата с Миррора с пользователем

CREATE CERTIFICATE HOST_mirror1_cert

AUTHORIZATION HOST_mirror1_user

FROM FILE = 'C:\Mirroring\HOST_mirror1_cert.cer'

GO

9. Мастер - выдача прав

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_MIRROR1_login];

GO

10. Миррор - создание пользователя, ассоциация сертификата Мастера с пользователем и выдача прав.

USE master;

CREATE LOGIN HOST_MASTER1_login WITH PASSWORD = 'S#xyAdm1n';

GO

 

CREATE USER HOST_MASTER1_user FOR LOGIN HOST_MASTER1_login;

GO


CREATE CERTIFICATE HOST_MASTER1_cert

AUTHORIZATION HOST_MASTER1_user

FROM FILE = 'C:\Mirroring\HOST_MASTER1_cert.cer'

GO



GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_MASTER1_login];

GO

12. Настройка зеркалирования с использованием пользователя и пароля

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

Пользуйтесь на здоровье!