Настройка базы данных распространителя репликации в группе доступности AlwaysOn

Область применения: SQL Server

В этой статье описывается настройка баз данных распространителя репликации SQL Server в группе доступности AlwaysOn.

В SQL Server 2017 CU6 и SQL Server 2016 SP2-CU3 реализована поддержка базы данных распространителя репликации в группе доступности с применением следующих механизмов.

• Группа доступности для базы данных распространителя должна иметь прослушиватель. При добавлении распространителя издатель использует в качестве имени распространителя имя прослушивателя.
• При создании заданий репликации имя прослушивателя также используется в качестве имени распространителя. Моментальные снимки репликации, средство чтения журнала и задания агента распространения (принудительная подписка), созданные на сервере распространения, создаются во всех вторичных репликах группы доступности для базы данных распространения.

Примечание.

Задания агента распространения для владельцев подписок по запросу создаются на сервере подписчика, а не сервере распространения.

• Новое задание отслеживает состояние (первичное или вторичное в группе доступности) баз данных распространителя и в зависимости от этого состояния отключает или включает задания репликации.

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

Поддерживаемые сценарии

• Настройка базы данных распространителя, которая будет включена в группу доступности.
• Настройка репликации, в том числе публикаций и подписок до и после отработки отказа в группе доступности.
• Задания репликации, выполняемые до и после отработки отказа.
• Удаление репликации на стороне распространителя и издателя, когда база данных распространителя находится в группе доступности.
• Добавление или удаление узлов для существующей группы доступности базы данных распространителя.
• Распространитель может иметь несколько баз данных распространителя. Каждая база данных распространителя может входить в собственную группу доступности или не принадлежать ни одной такой группе. Несколько баз данных распространителя могут использовать одну группу доступности.
• Издатель и распространитель должны размещаться на отдельных экземплярах SQL Server.
• Если прослушивателю группы доступности, в которой размещается база данных распространителя, назначен порт, отличный от порта по умолчанию, необходимо назначить псевдоним для прослушивателя и этого порта.

Ограничения или исключения

• Локальный распространитель (где сервер издателя также является распространителем) не поддерживается. Издатель и распространитель должны быть отдельными экземплярами SQL Server. Эти экземпляры могут размещаться в одних и тех же наборах узлов. Локальный распространитель не поддерживается по следующим причинам:

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

• Не поддерживаются издатели Oracle.

• Репликация слиянием не поддерживается.

• Не поддерживается репликация транзакций с обновлением подписчика напрямую или посредством очереди.

• Одноранговая репликация не поддерживается до SQL Server 2019 (15.x) CU 17

• Все экземпляры SQL Server 2017, в которых размещаются реплики баз данных распространителя, должны использовать версию SQL Server 2017 с накопительным обновлением 6 или более позднюю.

• Все экземпляры SQL Server 2016, в которых размещаются реплики баз данных распространителя, должны использовать версию SQL Server 2016 с пакетом обновления 2 (SP2) и накопительным обновлением 3 или более позднюю.

• Все экземпляры SQL Server, на которых размещаются реплики баз данных распространителя, должны иметь одну и ту же версию, за исключением коротких промежутков времени, связанных с обновлением.

• База данных распространителя должна находиться в полной модели восстановления.

• Чтобы обеспечить восстановление и усечение журнала транзакций, следует настроить резервное копирование полного журнала и журнала транзакций.

• Для группы доступности базы данных распространителя должен быть настроен прослушиватель.

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

• Двунаправленная репликация транзакций не поддерживается.

• В SSMS база данных распространителя не отображается как синхронизируемая или синхронизированная, если она входит в группу доступности.

  Примечание.

  Прежде чем выполнять какие-либо хранимые процедуры репликации (например, sp_dropdistpublisher, sp_dropdistributiondb, sp_dropdistributor, sp_adddistributiondb или sp_adddistpublisher) для вторичной реплики, убедитесь, что эта реплика полностью синхронизирована.

• Все вторичные реплики в группе доступности базы данных распространителя должны быть доступны для чтения. Если вторичная реплика недоступна для чтения, свойства распространителя в SQL Server Management Studio определенной вторичной реплики будут недоступны, однако репликация будет работать правильно.

• Все узлы в группе доступности базы данных распространителя должны использовать одну и ту же учетную запись домена для выполнения агента SQL Server. Этой учетной записи домена должны быть назначены одинаковые разрешения на каждом узле.

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

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

• Вносить изменения в задания репликации с использованием хранимых процедур msdb или SQL Server Management Studio необходимо для всех реплик, участвующих в группе доступности базы данных распространителя.

• При использовании пользовательского профиля для любого агента его необходимо создать вручную на всех вторичных репликах с помощью процедуры sp_add_agent_profile. Профиль должен иметь один и тот же идентификатор для всех реплик.

• Настройка распространителя на издателе должна осуществляться с помощью скриптов. Использование мастера репликации не поддерживается. При этом можно использовать мастеры репликации и страницы свойств репликации для других целей.

• Настройка группы доступности для баз данных распространителя может осуществляться только с помощью скриптов.

• Настройка баз данных распространителя в группе доступности должна осуществляться только в рамках новой конфигурации репликации. Переключение существующей базы данных распространителя в группу доступности не поддерживается. Кроме того, после исключения базы данных распространителя из группы доступности она больше не может использоваться в качестве действительной базы данных распространителя и должна быть удалена.

Архитектура конфигурации

В приведенных в этой статье примерах используются следующие имена серверов и параметры.

• DIST1, DIST2, DIST3 — это серверы распространителя;
• PUB — это сервер издателя;
• после формирования группы доступности для базы данных распространителя прослушиватель получает имя DISTLISTENER;
• DIST1 выступает в качестве первичной реплики группы доступности для базы данных распространителя.

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

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

Рабочий процесс распространителя

1. Настройте DIST1, DIST2 и DIST3 в качестве распространителя с использованием sp_adddistributor @@servername. Укажите пароль для distributor_admin с помощью @password. Значения @password должны быть одинаковыми для DIST1, DIST2 и DIST3.

2. Создайте базу данных распространителя на сервере DIST1 с использованием sp_adddistributiondb. База данных распространителя имеет имя distribution. Измените модель восстановления базы данных с простой distribution на полную.

3. Создайте группу доступности для базы данных distribution с репликами на серверах DIST1, DIST2 и DIST3. Рекомендуется сделать все реплики синхронными. Настройте вторичные реплики как доступные для чтения. На этот момент для группы доступности базы данных распространителя DIST1 является первичной репликой, а DIST2 и DIST3 — вторичными репликами.

4. Настройте прослушиватель с именем DISTLISTENER для группы доступности.

5. Чтобы обеспечить восстановление и усечение журнала транзакций, следует настроить резервное копирование полного журнала и журнала транзакций.

6. На серверах DIST2 и DIST3 выполните команду:

   EXEC sys.sp_adddistributiondb @database = 'distribution';
   

7. Чтобы добавить PUB в качестве издателя на сервер DIST1, выполните команду:

   EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
   

   Значение @working_directory не должно зависеть от сетевого пути к серверам DIST1, DIST2 и DIST3.

8. В DIST2 и DIST3, если реплика доступна для чтения в качестве вторичной, выполните команду:

   EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
   

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

   EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
   

   Значение @working_directory должно быть таким же, как и на предыдущем шаге.

Рабочий процесс издателя

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

EXEC sys.sp_adddistributor @distributor = 'DISTLISTENER', @password = '<distributor_admin password>' ;

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

Удаление издателя и распространителя

В этом примере из находящейся в группе доступности базы данных распространителя удаляются издатель и распространитель.

Рабочий процесс издателя

На сервере PUB удалите все подписки и публикации для этого издателя, после чего вызовите sp_dropdistributor.

Рабочий процесс распространителя

В этом примере сервер DIST1 является текущей первичной репликой для группы доступности базы данных distribution. Серверы DIST2 и DIST3 являются вторичными репликами.

1. На серверах DIST2 и DIST3 выполните команду:

   EXEC sys.sp_dropdistpublisher @publisher = 'PUB', @no_checks = 1;
   

2. На DIST1 выполните следующую команду:

   EXEC sys.sp_dropdistpublisher @publisher = 'PUB';
   

3. Удалите группу доступности.

4. На серверах DIST2 и DIST3 переведите базу данных distribution в режим read_write, выполнив ее восстановление.

   RESTORE DATABASE [distribution] WITH RECOVERY, KEEP_REPLICATION;
   

5. Чтобы удалить базу данных distribution и сохранить каталог моментальных снимков, выполните команду:

   EXEC sys.sp_dropdistributiondb @database = 'distribution' , @former_ag_secondary = 1;
   

Эта процедура удаляет все несвязанные задания в этой реплике.

1. Чтобы удалить базу данных distribution на сервере DIST1, выполните команду:

   EXEC sys.sp_dropdistributiondb @database = 'distribution';
   

2. Если в группе доступности нет других баз данных распространителя, выполните sp_dropdistributor на серверах DIST1, DIST2 и DIST3.

Добавление реплики в группу доступности базы данных распространителя

В этом примере добавляется новый распространитель в существующую конфигурацию репликации с базой данных распространителя в группе доступности. В этом примере существующая база данных распространителя находится в группе доступности. Серверы DIST1 и DIST2 являются распространителями, distribution — это база данных распространителя в группе доступности, а PUB — это издатель. Добавьте сервер DIST3 в качестве реплики в группу доступности.

Рабочий процесс распространителя

1. Сервер DIST3 должен быть настроен в качестве распространителя с использованием sp_adddistributor @@servername. Пароль distributor_admin необходимо задавать с использованием параметра @password. Пароль должен совпадать с указанным для серверов DIST1 и DIST2.

2. Добавьте сервер DIST3 в группу доступности для текущей базы данных распространителя.

3. На сервере DIST3 выполните команду:

   EXEC sys.sp_adddistributiondb @database = 'distribution';
   

4. В DIST3, если реплика доступна для чтения в качестве вторичной, выполните команду:

   EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
   

   Если реплика недоступна для чтения в качестве вторичной, выполните отработку отказа таким образом, чтобы реплика стала первичной, и выполните команду:

   EXEC sys.sp_adddistpublisher @publisher = 'PUB', @distribution_db = 'distribution', @working_directory = '<network path>';
   

   Значение @working_directory должно совпадать с указанным для серверов DIST1 и DIST2.

5. На сервере DIST3 необходимо повторно создать связанные серверы для подписчиков.

Удаление реплики из группы доступности базы данных распространителя

В этом примере распространитель удаляется из текущей группы доступности базы данных распространителя и при этом не затрагиваются другие реплики в этой группе. В этом примере база данных распространителя находится в группе доступности. Серверы DIST1, DIST2 и DIST3 являются распространителями, distribution — это база данных распространителя в группе доступности, а PUB — это издатель. Удалите сервер DIST3 из группы доступности.

Рабочий процесс распространителя

1. Убедитесь, что в группе доступности базы данных distribution сервер DIST3 является вторичным.

2. Удалите сервер DIST3 из группы доступности базы данных distribution.

3. На сервере DIST3 переведите базу данных distribution в режим read_write, выполнив ее восстановление. Например, выполните следующую команду:

   RESTORE DATABASE distribution WITH RECOVERY, KEEP_REPLICATION;
   

4. Чтобы удалить все несвязанные задания на сервере DIST3, выполните команду:

   EXEC sys.sp_dropdistpublisher @publisher = 'PUB', @no_checks = 1;
   

5. На сервере DIST3 выполните команду:

   EXEC sys.sp_dropdistributiondb @database = 'distribution', @former_ag_secondary = 1;
   

6. На сервере DIST3 выполните команду:

   EXEC sys.sp_dropdistributor;
   

Удаление издателя из группы доступности базы данных распространителя

В этом примере издатель удаляется из текущей группы доступности базы данных распространения для распространителя, и при этом не затрагиваются другие издатели, обслуживаемые этой группой. В этом примере в существующей конфигурации база данных распространителя находится в группе доступности. Серверы DIST1, DIST2 и DIST3 являются распространителями, distribution — это база данных распространителя в группе доступности, а PUB1 и PUB2 — это издатели, обслуживаемые базой данных distribution. В этом примере удаляется издатель PUB1.

Рабочий процесс издателя

На сервере PUB1 удалите все подписки и публикации для этого издателя, после чего вызовите sp_dropdistributor.

Рабочий процесс распространителя

Сервер DIST1 является текущей первичной репликой для группы доступности базы данных distribution.

1. На серверах DIST2 и DIST3 выполните команду:

   EXEC sys.sp_dropdistpublisher @publisher = 'PUB1',  @no_checks = 1;
   

2. На DIST1 выполните следующую команду:

   EXEC sys.sp_dropdistpublisher @publisher = 'PUB1';
   

3. На этом этапе могут возникать несвязанные задания, относящиеся к издателю PUB1 на серверах DIST2 или DIST3. При выполнении отработки отказа на серверы DIST2 и DIST3 такие несвязанные задания, относящиеся ко всем публикациям сервера PUB1, будут удалены заданием Monitor and sync replication agent jobs.

Добавление подписки

В этом примере описывается настройка сведения о подписчике на распространителях. В этом примере добавляется подписчик. DIST1 является первичной репликой базы данных распространителя в группе доступности, а DIST2 и DIST3 — ее вторичные реплики. SUB — это имя подписчика.

Рабочий процесс издателя

На сервере PUB добавьте подписку для подписчика SUB обычным образом.

Рабочий процесс распространителя

На серверах DIST2 и DIST3 добавьте связанный сервер для SUB, если он еще не был зарегистрирован для DIST2 или DIST3. Ниже приводится пример кода TSQL для создания связанного сервера:

EXEC master.dbo.sp_addlinkedserver@server =N'SUB', @srvproduct=N'SQL Server';
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname = N'SUB', @useself = N'True',@locallogin = NULL,@rmtuser =N ULL,@rmtpassword = NULL;

Добавление подписки по запросу

Рабочий процесс подписчика

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

Пример кода T-SQL для создания базы данных распространителя в группе доступности

Следующий скрипт включает базу данных распространителя в группе доступности.

--- WorkFlow to Enable Distribution Database In AG.

-- SECTION 1 ---- CONFIGURE THE DISTRIBUTOR SERVERS

-- Step1 - Configure the Distribution DB nodes (AG Replicas) to act as a distributor
:Connect SQLNode1
EXEC [sys].[sp_adddistributor]
    @distributor = @@SERVERNAME,
    @password = 'Pass@word1';
GO
:Connect SQLNode2
EXEC [sys].[sp_adddistributor]
    @distributor = @@SERVERNAME,
    @password = 'Pass@word1';
GO

-- Step2 - Configure the Distribution Database
:Connect SQLNode1
USE [master];
EXEC [sys].[sp_adddistributiondb]
    @database = 'DistributionDB',
    @security_mode = 1;
GO
ALTER DATABASE [DistributionDB] SET RECOVERY FULL;
GO
BACKUP DATABASE [DistributionDB] TO DISK = 'NUL';
GO
-- Step 3 - Create AG for the Distribution DB.
:Connect SQLNode1
USE [master];
GO
CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = WINDOWS NEGOTIATE,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
GO

:Connect SQLNode2
USE [master];
GO
CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = WINDOWS NEGOTIATE,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
GO

:Connect SQLNode1
-- Create the Availability Group
CREATE AVAILABILITY GROUP [DistributionDB_AG]
FOR DATABASE [DistributionDB]
REPLICA ON
N'SQLNode1' WITH (ENDPOINT_URL = N'TCP://SQLNode1.contoso.com:5022', 
	 FAILOVER_MODE = AUTOMATIC, 
	 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	 BACKUP_PRIORITY = 50, 
	 SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), 
	 SEEDING_MODE = AUTOMATIC),
N'SQLNode2' WITH (ENDPOINT_URL = N'TCP://SQLNode2.contoso.com:5022', 
	 FAILOVER_MODE = AUTOMATIC, 
	 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
	 BACKUP_PRIORITY = 50, 
	 SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), 
	 SEEDING_MODE = AUTOMATIC);
GO


:Connect SQLNode2
ALTER AVAILABILITY GROUP [DistributionDB_AG] JOIN;
GO  
ALTER AVAILABILITY GROUP [DistributionDB_AG] GRANT CREATE ANY DATABASE;
GO

--STEP4 - Create the Listener for the Availability Group. This is very important.
:Connect SQLNode1

USE [master]
GO
ALTER AVAILABILITY GROUP [DistributionDB_AG]
ADD LISTENER N'DistributionDBList' (
WITH IP
((N'10.0.0.8', N'255.255.255.0')) , PORT=1500);
GO

-- STEP 5 - Enable SQLNode2 also as a Distributor
:Connect SQLNode2
EXEC sys.sp_adddistributiondb @database = 'DistributionDB', @security_mode = 1;
GO

--STEP 6 - On all Distributor Nodes Configure the Publisher Details 
:Connect SQLNode1
EXEC sys.sp_adddistpublisher @publisher = 'SQLNode4', @distribution_db = 'DistributionDB', 
	@working_directory = '\\sqlfileshare\Dist_Work_Directory\';
GO
:Connect SQLNode2
EXEC sys.sp_adddistpublisher @publisher = 'SQLNode4', @distribution_db = 'DistributionDB', 
	@working_directory = '\\sqlfileshare\Dist_Work_Directory\';
GO

-- SECTION 2 ---- CONFIGURE THE PUBLISHER SERVER
:Connect SQLNode4
EXEC sys.sp_adddistributor @distributor = 'DistributionDBList', -- Listener for the Distribution DB.	
	@password = 'Pass@word1';
GO

-- SECTION 3 ---- CONFIGURE THE SUBSCRIBERS 
-- On Publisher, create the publication as one would normally do.
-- On the Secondary replicas of the Distribution DB, add the Subscriber as a linked server.
:Connect SQLNode2
EXEC master.dbo.sp_addlinkedserver @server = N'SQLNODE5', @srvproduct = N'SQL Server';
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQLNODE5', @useself = N'True',
	@locallogin = NULL,@rmtuser = NULL,@rmtpassword = NULL;

Связанный контент

• Публикация данных и объектов базы данных
• Защита распространителя
• Просмотр и изменение свойств издателя и распространителя
• Отключение публикации и распространения
• Разрешение применения репликации для базы данных (среда SQL Server Management Studio)