在可用性组中配置两个对等数据库

项目
06/02/2023

从 SQL Server 2019 (15.x) CU 13 开始，属于 SQL Server Always On 可用性组的数据库可以作为对等数据库加入对等事务复制拓扑。本文介绍如何使用两个对等数据库（每个对等数据库都在其自己的可用性组中）配置此方案。

本示例中的脚本使用 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 实例。
所有服务器实例都需要支持的版本 - 企业版或开发人员版。
所有服务器实例都需要支持的版本 - 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)。

运行 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)

在第一个可用性组的每个次要副本主机 (Node2) 上，配置分发。为 @password 指定在分发服务器上运行 sp_adddistributor 时使用的相同的值来设置分发。

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

使数据库成为可用性组的一部分并创建侦听器 (Peer1)

在目标主要副本上，创建数据库作为成员数据库的可用性组。
为可用性组创建 DNS 侦听器。复制代理使用侦听器连接到当前的主要副本。以下示例会创建一个名为 MyAGListenerName 的侦听器。
```
ALTER AVAILABILITY GROUP 'MyAG'
ADD LISTENER 'MyAGListenerName' (WITH IP (('<ip address>', '<subnet mask>') [, PORT = <listener_port>]));   
```
注意

在上面的脚本中，方括号 ([ ... ]) 中的信息是可选的。使用它来指定 TCP 端口的非默认值。请勿包括大括号。

将原始发布服务器重定向到 AG 侦听器名称 (Peer1)

在 Peer1 的分发服务器上，将原始发布服务器重定向到 AG 侦听器名称。

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

注意

在上面的脚本中，,<port> 是可选的。仅在使用非默认端口时才需要。然后，不要包括尖括号 <>。

在原始发布服务器 Node1 上创建对等发布 (Peer1)

以下脚本会为 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)。

运行 sp_adddistributor 以在 Dist2 上配置分发。使用 @password = 指定远程发布服务器用于连接到分发服务器的密码。配置远程分发服务器时，请在每个远程发布服务器上使用此密码。
```
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 和 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)

在 Node3 上，配置远程分发。为 @password 指定在分发服务器上运行 sp_adddistributor 时使用的相同的值来设置分发。
```
EXEC sys.sp_adddistributor  
@distributor = 'Dist2',  
@password = '<Password used when running sp_adddistributor on distributor server>' 
```

启用数据库复制。

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)

在目标主要副本上，创建数据库作为成员数据库的可用性组。
为可用性组创建 DNS 侦听器。复制代理使用侦听器连接到当前的主要副本。以下示例会创建一个名为 MyAG2ListenerName 的侦听器。
```
ALTER AVAILABILITY GROUP 'MyAG2'
ADD LISTENER 'MyAG2ListenerName' (WITH IP (('<ip address>', '<subnet mask>') [, PORT = <listener_port>]));   
```
注意

在上面的脚本中，方括号 ([ ... ]) 中的信息是可选的。使用它来指定 TCP 端口的非默认值。请勿包括大括号。

将原始发布服务器重定向到 AG 侦听器名称 (Peer2)

在 Peer2 的分发服务器上，将原始发布服务器重定向到 AG 侦听器名称。

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';

Share via

在可用性组中配置两个对等数据库

角色和名称

Peer1

Peer2

先决条件

配置分发服务器和远程发布服务器 (Peer1)

在原始发布服务器 (Node1) 上配置发布服务器

将次要副本主机配置为复制发布服务器 (Node2)

使数据库成为可用性组的一部分并创建侦听器 (Peer1)

将原始发布服务器重定向到 AG 侦听器名称 (Peer1)

在原始发布服务器 Node1 上创建对等发布 (Peer1)

使对等发布与可用性组 (Peer1) 兼容

配置分发服务器和远程发布服务器 (Peer2)

配置发布服务器 (Peer2)

将次要副本主机配置为复制发布服务器 (Node4)

使数据库成为可用性组的一部分并创建侦听器 (Peer2)

将原始发布服务器重定向到 AG 侦听器名称 (Peer2)

创建对等发布 (Peer2)

使对等发布与可用性组 (Peer2) 兼容

创建从 Peer1 到 Peer2 的可用性组侦听程序的推送订阅

创建从 Peer2 到可用性组侦听程序 (Peer1) 的推送订阅

配置链接服务器

后续步骤

反馈

反馈

其他资源