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

Статья
06/02/2023

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

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

Роли и имена

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

Peer1

Node1: реплика 1 в группе доступности MyAG.
Node2: реплика 2 в группе доступности MyAG.
MyAG: имя группы доступности, которая будет создана на узлах Node1 и Node2.
MyAGListenerName: имя прослушивателя группы доступности.
Dist1: имя экземпляра удаленного распространителя.
MyDBName: имя базы данных.
P2P_MyDBName: имя публикации.

Peer2

Node3: отдельный сервер, на котором размещен экземпляр SQL Server по умолчанию.
Dist2: имя экземпляра удаленного распространителя.
MyDBName: имя базы данных.
P2P_MyDBName: имя публикации.

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

Два экземпляра SQL Server на отдельных физических или виртуальных серверах для размещения группы доступности. Группа доступности, где будет содержаться одноранговый узел базы данных.
Один экземпляр 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)

Запустите sp_adddistributor, чтобы настроить распространение на Dist1. Используйте аргумент @password =, чтобы указать пароль для подключения удаленного издателя к распространителю. Используйте этот пароль на каждом удаленном издателе при настройке удаленного распространителя.
```
USE master;  
GO  
EXEC sys.sp_adddistributor  
 @distributor = 'Dist1',  
 @password = '<Strong password for distributor>';  
```
Создайте базу данных распространителя на распространителе.
```
USE master;
GO  
EXEC sys.sp_adddistributiondb  
 @database = 'distribution',  
 @security_mode = 1;  
```
Настройте 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)

Настройте исходный издатель для удаленного распространения (Node1). Укажите для @password то же значение, которое использовалось при запуске sp_adddistributor на распространителе для настройки распространения.
```
exec sys.sp_adddistributor  
@distributor = 'Dist1',  
@password = '<Password used when running sp_adddistributor on distributor server>' 
```

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

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

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

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

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

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

На предполагаемой первичной реплике создайте группу доступности, включив в нее базу данных как рядовую базу.
Создайте для группы доступности прослушиватель DNS. Агент репликации подключается с помощью прослушивателя к текущей первичной реплике. В следующем примере создается прослушиватель с именем MyAGListername.
```
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

Примечание.

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

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

Настройка распространения на распространителе.

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

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

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

Настройте Node3 в качестве удаленного издателя на распространителе Dist2.

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

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

Настройте удаленное распространение на узле Node3.

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

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

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

Создание одноранговой публикации (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

Создание принудительной подписки с узла Peer1 на узел Peer2

На этом шаге создается принудительная подписка из группы доступности на отдельный экземпляр SQL Server.

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

EXEC [MyDBName].dbo.sp_addsubscription 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'Node3'
 , @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'Node3'
 , @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 на прослушиватель группы доступности

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

Важно!

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

@subscriber = N'MyAGListenerName,<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';

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

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