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

  • Статья

Начиная с SQL Server 2019 (15.x) CU 13 база данных, которая принадлежит группе доступности SQL Server AlwaysOn, может участвовать в качестве одноранговой топологии транзакций реплика. В статье описывается, как настроить такой сценарий с двумя одноранговыми узлами, каждый из которых находится в своей группе доступности.

Скрипты в этом примере используют хранимые процедуры T-SQL.

Роли и имена

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

Peer1

  • Node1: первичная реплика для первой группы доступности
  • Node2: вторичная реплика для первой группы доступности
  • MyAG: имя первой группы доступности
  • MyDBName: база данных узла Peer1, используемая для публикации
  • Dist1: удаленный распространитель
  • P2P_MyDBName: имя публикации
  • MyAGListenerName: прослушиватель группы доступности

Peer2

  • Node3: первичная реплика для второй группы доступности
  • Node4: вторичная реплика для второй группы доступности
  • MyAG2: имя второй группы доступности
  • MyDBName: база данных для публикации
  • Dist2: удаленный распространитель
  • P2P_MyDBName: имя публикации
  • MyAG2ListenerName: прослушиватель группы доступности

Необходимые компоненты

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

  • Два экземпляра SQL Server для размещения баз данных распространителя.

  • Для всех экземпляров сервера требуется поддерживаемый выпуск — Enterprise или Developer.

  • Для всех экземпляров сервера требуется поддерживаемая версия — SQL Server 2019 (15.x) CU13 или более поздней версии.

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

  • Установка репликации SQL Server на всех экземплярах SQL Server.

    Чтобы узнать, установлена ли репликация на любом из экземпляров, выполните следующий запрос.

    USE master;   
GO   
DECLARE @installed int;   
EXEC @installed = sys.sp_MS_replication_installed;   
SELECT @installed;

    Примечание.

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

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

Настройка распространителя и удаленного издателя (Peer1)

В этом разделе описана настройка первого однорангового узла (Peer1) в группе доступности.

  1. Запустите sp_adddistributor, чтобы настроить распространение на Dist1. Используйте аргумент @password =, чтобы указать пароль для подключения удаленного издателя к распространителю. Используйте этот пароль на каждом удаленном издателе при настройке удаленного распространителя.

    USE master;  
GO  
EXEC sys.sp_adddistributor  
 @distributor = 'Dist1',  
 @password = '<Strong password for distributor>';

  2. Создайте базу данных распространителя на распространителе.

    USE master;
GO  
EXEC sys.sp_adddistributiondb  
 @database = 'distribution',  
 @security_mode = 1;

  3. Настройте удаленный издатель для узлов Node1 и Node2.

    Параметр @security_mode определяет, как агенты репликации подключаются к текущей первичной реплике.

    • 1 — с использованием проверки подлинности Windows.
    • 0 — с использованием проверки подлинности SQL Server. Требуется указать @login и @password. Эти имя для входа и пароль должны быть действительными для каждой вторичной реплики.

    Примечание.

    Если какие-либо измененные агенты репликации будут запускаться на компьютере, отличном от распространителя, то чтобы использовать проверку подлинности Windows для подключения к первичной реплике, потребуется настроить проверку подлинности Kerberos для передачи данных между главными компьютерами реплик. Использование учетных данных SQL Server для подключения к текущей первичной реплике не требует проверки подлинности Kerberos.

    USE master;  
GO  
EXEC sys.sp_adddistpublisher  
 @publisher = 'Node1',  
 @distribution_db = 'distribution',  
 @working_directory = '\\MyReplShare\WorkingDir',  
 @security_mode = 1
USE master;  
GO  
EXEC sys.sp_adddistpublisher  
 @publisher = 'Node2',  
 @distribution_db = 'distribution',  
 @working_directory = '\\MyReplShare\WorkingDir',  
 @security_mode = 1

Настройка исходного издателя (Node1)

  1. Настройте исходный издатель для удаленного распространения (Node1). Укажите для @password то же значение, которое использовалось при запуске sp_adddistributor на распространителе для настройки распространения.

    EXEC sys.sp_adddistributor  
@distributor = 'Dist1',  
@password = '<Password used when running sp_adddistributor on distributor server>'

  2. Включите базу данных для репликации.

    USE master;  
GO  
EXEC sys.sp_replicationdboption  
 @dbname = 'MyDBName',  
 @optname = 'publish',  
 @value = 'true';

Настройка узла вторичной реплики в качестве издателя репликации (Node2)

Настройте распространение на каждом узле вторичной реплики (Node2) для первой группы доступности. Укажите для @password то же значение, которое использовалось при запуске sp_adddistributor на распространителе для настройки распространения.

EXEC sys.sp_adddistributor  
   @distributor = 'Dist1',  
   @password = '<Password used when running sp_adddistributor on distributor server>'

Включение базы данных в группу доступности и создание прослушивателя (Peer1)

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

  2. Создайте для группы доступности прослушиватель DNS. Агент репликации подключается с помощью прослушивателя к текущей первичной реплике. В следующем примере создается прослушиватель с именем MyAGListenerName.

    ALTER AVAILABILITY GROUP 'MyAG'
ADD LISTENER 'MyAGListenerName' (WITH IP (('<ip address>', '<subnet mask>') [, PORT = <listener_port>]));

    Примечание.

    В приведенном выше скрипте сведения в квадратных скобках ([ ... ]) являются необязательными. Они позволяют указать TCP-порт, отличный от значения по умолчанию. Не используйте квадратные скобки.

Перенаправление исходного издателя на имя прослушивателя группы доступности (Peer1)

В распространителе для узла Peer1 перенаправьте исходного издателя на прослушиватель группы доступности с указанным именем.

USE distribution;   
GO   
EXEC sys.sp_redirect_publisher    
@original_publisher = 'Node1',   
@publisher_db = 'MyDBName',   
@redirected_publisher = 'MyAGListenerName,<port>';

Примечание.

В приведенном выше скрипте параметр ,<port> является необязательным. Он необходим, только если не используются порты по умолчанию. Включать угловые скобки <> не нужно.

Создание одноранговой публикации (Peer1) на исходном издателе — Node1

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

EXEC master..sp_replicationdboption  @dbname=  'MyDBName'   
        ,@optname=  'publish'   
        ,@value=  'true'  
GO
DECLARE @publisher_security_mode smallint = 1
EXEC [MyDBName].dbo.sp_addlogreader_agent @publisher_security_mode = @publisher_security_mode
GO
DECLARE @allow_dts nvarchar(5) = N'false'
DECLARE @allow_pull nvarchar(5) = N'true'
DECLARE @allow_push nvarchar(5) = N'true'
DECLARE @description nvarchar(255) = N'Peer-to-Peer publication of database MyDBName from Node1'
DECLARE @enabled_for_p2p nvarchar(5) = N'true'
DECLARE @independent_agent nvarchar(5) = N'true'
DECLARE @p2p_conflictdetection nvarchar(5) = N'true'
DECLARE @p2p_originator_id int = 100
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @repl_freq nvarchar(10) = N'continuous'
DECLARE @restricted nvarchar(10) = N'false'
DECLARE @status nvarchar(8) = N'active'
DECLARE @sync_method nvarchar(40) = N'NATIVE'
EXEC [MyDBName].dbo.sp_addpublication @allow_dts = @allow_dts, @allow_pull = @allow_pull, @allow_push = @allow_push, @description = @description, @enabled_for_p2p = @enabled_for_p2p, @independent_agent = @independent_agent, @p2p_conflictdetection = @p2p_conflictdetection, @p2p_originator_id = @p2p_originator_id, @publication = @publication, @repl_freq = @repl_freq, @restricted = @restricted, @status = @status, @sync_method = @sync_method
GO
DECLARE @article nvarchar(256) = N'tbl0'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl0'
DECLARE @destination_table nvarchar(256) = N'tbl0'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl0'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, @source_owner = @source_owner, @type = @type
GO
DECLARE @article nvarchar(256) = N'tbl1'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl1'
DECLARE @destination_table nvarchar(256) = N'tbl1'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl1'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, 
@source_owner = @source_owner, @type = @type
GO

Обеспечение совместимости одноранговой публикации с группой доступности (Peer1)

Чтобы сделать публикацию совместимой с группой доступности, на исходном издателе (Node1) выполните следующий скрипт.

USE MyDBName
GO
DECLARE @publication sysname = N'P2P_MyDBName' 
DECLARE @property sysname = N'redirected_publisher' 
DECLARE @value sysname = N'MyAGListenerName,<port>' 
EXEC MyDBName..sp_changepublication @publication = @publication, @property = @property, @value = @value 
GO

Примечание.

В приведенном выше скрипте параметр ,<port> является необязательным. Он необходим, только если не используются порты по умолчанию.

После выполнения описанных выше действий группа доступности будет готова к включению в топологию одноранговой репликации. На следующих этапах будет проведена настройка отдельной группы доступности в качестве второго однорангового узла (Peer2) в топологии одноранговой репликации.

Настройка распространителя и удаленного издателя (Peer2)

Этот раздел описывает настройку второго однорангового узла (Peer2) в другой группе доступности.

  1. Запустите sp_adddistributor, чтобы настроить распространение на Dist2. Используйте аргумент @password =, чтобы указать пароль для подключения удаленного издателя к распространителю. Используйте этот пароль на каждом удаленном издателе при настройке удаленного распространителя.

    USE master;  
GO  
EXEC sys.sp_adddistributor  
 @distributor = 'Dist2',  
 @password = '<Strong password for distributor>';

  2. Создайте базу данных распространителя на распространителе.

    USE master;
GO  
EXEC sys.sp_adddistributiondb  
 @database = 'distribution',  
 @security_mode = 1;

  3. Настройте удаленный издатель для узлов Node3 и Node4.

    Параметр @security_mode определяет, как агенты репликации подключаются к текущей первичной реплике.

    • 1 — с использованием проверки подлинности Windows.
    • 0 — с использованием проверки подлинности SQL Server. Требуется указать @login и @password. Эти имя для входа и пароль должны быть действительными для каждой вторичной реплики.

    Примечание.

    Если какие-либо измененные агенты репликации будут запускаться на компьютере, отличном от распространителя, то чтобы использовать проверку подлинности Windows для подключения к первичной реплике, потребуется настроить проверку подлинности Kerberos для передачи данных между главными компьютерами реплик. Использование учетных данных SQL Server для подключения к текущей первичной реплике не требует проверки подлинности Kerberos.

    USE master;  
GO  
EXEC sys.sp_adddistpublisher  
 @publisher = 'Node3',  
 @distribution_db = 'distribution',  
 @working_directory = '\\MyReplShare\WorkingDir2',  
 @security_mode = 1
USE master;  
GO  
EXEC sys.sp_adddistpublisher  
 @publisher = 'Node4',  
 @distribution_db = 'distribution',  
 @working_directory = '\\MyReplShare\WorkingDir2',  
 @security_mode = 1

Настройка издателя (Peer2)

  1. Создайте удаленное распространение на узле Node3. Укажите для @password то же значение, которое использовалось при запуске sp_adddistributor на распространителе для настройки распространения.

    EXEC sys.sp_adddistributor  
@distributor = 'Dist2',  
@password = '<Password used when running sp_adddistributor on distributor server>'

  2. Включите базу данных для репликации.

    USE master;  
GO  
EXEC sys.sp_replicationdboption  
 @dbname = 'MyDBName',  
 @optname = 'publish',  
 @value = 'true';

Настройка узла вторичной реплики в качестве издателя репликации (Node4)

Настройте распространение на каждом узле вторичной реплики (Node4) для второй группы доступности. Укажите для @password то же значение, которое использовалось при запуске sp_adddistributor на распространителе для настройки распространения.

EXEC sys.sp_adddistributor  
   @distributor = 'Dist2',  
   @password = '<Password used when running sp_adddistributor on distributor server>'

Включение базы данных в группу доступности и создание прослушивателя (Peer2)

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

  2. Создайте для группы доступности прослушиватель DNS. Агент репликации подключается с помощью прослушивателя к текущей первичной реплике. В следующем примере создается прослушиватель с именем MyAG2ListenerName.

    ALTER AVAILABILITY GROUP 'MyAG2'
ADD LISTENER 'MyAG2ListenerName' (WITH IP (('<ip address>', '<subnet mask>') [, PORT = <listener_port>]));

    Примечание.

    В приведенном выше скрипте сведения в квадратных скобках ([ ... ]) являются необязательными. Они позволяют указать TCP-порт, отличный от значения по умолчанию. Не используйте квадратные скобки.

Перенаправление исходного издателя на имя прослушивателя группы доступности (Peer2)

В распространителе для узла Peer2 перенаправьте исходного издателя на прослушиватель группы доступности с указанным именем.

USE distribution;   
GO   
EXEC sys.sp_redirect_publisher    
@original_publisher = 'Node3',   
@publisher_db = 'MyDBName',   
@redirected_publisher = 'MyAG2ListenerName,<port>';

Примечание.

В приведенном выше скрипте параметр ,<port> является необязательным. Он необходим, только если не используются порты по умолчанию. Включать угловые скобки <> не нужно.

Создание одноранговой публикации (Peer2)

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

На узле Node3 выполните следующую команду, чтобы создать одноранговую публикацию.

EXEC master..sp_replicationdboption  @dbname=  'MyDBName'   
        ,@optname=  'publish'   
        ,@value=  'true'  
GO
DECLARE @publisher_security_mode smallint = 1
EXEC [MyDBName].dbo.sp_addlogreader_agent @publisher_security_mode = @publisher_security_mode
GO

-- Note – Make sure that the value for @p2p_originator_id is different from Peer1.
DECLARE @allow_dts nvarchar(5) = N'false'
DECLARE @allow_pull nvarchar(5) = N'true'
DECLARE @allow_push nvarchar(5) = N'true'
DECLARE @description nvarchar(255) = N'Peer-to-Peer publication of database MyDBName from Node3'
DECLARE @enabled_for_p2p nvarchar(5) = N'true'
DECLARE @independent_agent nvarchar(5) = N'true'
DECLARE @p2p_conflictdetection nvarchar(5) = N'true'
DECLARE @p2p_originator_id int = 1
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @repl_freq nvarchar(10) = N'continuous'
DECLARE @restricted nvarchar(10) = N'false'
DECLARE @status nvarchar(8) = N'active'
DECLARE @sync_method nvarchar(40) = N'NATIVE'
EXEC [MyDBName].dbo.sp_addpublication @allow_dts = @allow_dts, @allow_pull = @allow_pull, @allow_push = @allow_push, @description = @description, @enabled_for_p2p = @enabled_for_p2p, @independent_agent = @independent_agent, @p2p_conflictdetection = @p2p_conflictdetection, @p2p_originator_id = @p2p_originator_id, @publication = @publication, @repl_freq = @repl_freq, @restricted = @restricted, @status = @status, @sync_method = @sync_method
GO
DECLARE @article nvarchar(256) = N'tbl0'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl0'
DECLARE @destination_table nvarchar(256) = N'tbl0'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl0'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, @source_owner = @source_owner, @type = @type
GO

DECLARE @article nvarchar(256) = N'tbl1'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl1'
DECLARE @destination_table nvarchar(256) = N'tbl1'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl1'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, 
@source_owner = @source_owner, @type = @type
GO

Обеспечение совместимости одноранговой публикации с группой доступности (Peer2)

Чтобы сделать публикацию совместимой с группой доступности, на исходном издателе (Node3) выполните следующий скрипт:

USE MyDBName
GO
DECLARE @publication sysname = N'P2P_MyDBName' 
DECLARE @property sysname = N'redirected_publisher' 
DECLARE @value sysname = N'MyAG2ListenerName,<port>' 
EXEC MyDBName..sp_changepublication @publication = @publication, @property = @property, @value = @value 
GO

Примечание.

В приведенном выше скрипте параметр ,<port> является необязательным. Он необходим, только если не используются порты по умолчанию.

Создание принудительной подписки с узла Peer1 на прослушиватель группы доступности для Peer2

Чтобы создать принудительную подписку с однорангового узла Peer1 на прослушиватель группы доступности узла Peer2, выполните на Node1 следующую команду.

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

Важно!

Приведенный ниже скрипт указывает имя прослушивателя группы доступности для подписчика.

@subscriber = N'MyAGListenerName,<port>'

Примечание.

В приведенном выше скрипте параметр ,<port> является необязательным. Он необходим, только если не используются порты по умолчанию. Включать угловые скобки <> не нужно.

EXEC [MyDBName].dbo.sp_addsubscription 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'MyAG2Listener,<port>' 
 , @destination_db = N'MyDBName'
 , @subscription_type = N'push'
 , @sync_type = N'replication support only'
GO

EXEC [MyDBName].dbo.sp_addpushsubscription_agent 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'MyAG2Listener,<port>' 
 , @subscriber_db = N'MyDBName'
 , @job_login = null 
 , @job_password = null
 , @subscriber_security_mode = 1
 , @frequency_type = 64
 , @frequency_interval = 1
 , @frequency_relative_interval = 1
 , @frequency_recurrence_factor = 0
 , @frequency_subday = 4
 , @frequency_subday_interval = 5
 , @active_start_time_of_day = 0
 , @active_end_time_of_day = 235959
 , @active_start_date = 0
 , @active_end_date = 0
 , @dts_package_location = N'Distributor'
GO

Создание принудительной подписки с узла Peer2 на прослушиватель группы доступности (Peer1)

Чтобы создать принудительную подписку с однорангового узла Peer2 на прослушиватель группы доступности (Peer1), выполните на Node3 следующую команду.

Важно!

Приведенный ниже скрипт указывает имя прослушивателя группы доступности для подписчика.

@subscriber = N'MyAGListenerName,<port>'

Примечание.

В приведенном выше скрипте параметр ,<port> является необязательным. Он необходим, только если не используются порты по умолчанию. Включать угловые скобки <> не нужно.

EXEC [MyDBName].dbo.sp_addsubscription 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'MyAGListenerName,<port>'
 , @destination_db = N'MyDBName'
 , @subscription_type = N'push'
 , @sync_type = N'replication support only'
GO

EXEC [MyDBName].dbo.sp_addpushsubscription_agent 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'MyAGListenerName,<port>'
 , @subscriber_db = N'MyDBName'
 , @job_login = null
 , @job_password = null
 , @subscriber_security_mode = 1
 , @frequency_type = 64
 , @frequency_interval = 1
 , @frequency_relative_interval = 1
 , @frequency_recurrence_factor = 0
 , @frequency_subday = 4
 , @frequency_subday_interval = 5
 , @active_start_time_of_day = 0
 , @active_end_time_of_day = 235959
 , @active_start_date = 0
 , @active_end_date = 0
 , @dts_package_location = N'Distributor'
GO

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

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

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';

Следующие шаги