通过

将一个对等数据库配置为可用性组的一部分

  • 项目

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

本示例中的脚本使用 T-SQL 存储过程。

角色和名称

本部分介绍加入本文复制拓扑的各种元素的角色和名称。

Peer1

  • Node1：MyAG 可用性组中的副本 1。
  • Node2：MyAG 可用性组中的副本 2。
  • MyAG：将在 Node1 和 Node2 上创建的可用性组的名称。
  • MyAGListenerName：可用性组侦听程序的名称。
  • Dist1：远程分发服务器实例的名称。
  • MyDBName：数据库的名称。
  • P2P_MyDBName：发布名称。

Peer2

  • Node3：托管 SQL Server 的默认实例的独立服务器。
  • Dist2：远程分发服务器实例的名称。
  • MyDBName：数据库的名称。
  • P2P_MyDBName：发布名称。

先决条件

  • 在单独的物理服务器或虚拟服务器上托管可用性组的两个 SQL Server 实例。 可用性组将包含对等数据库。

  • 一个 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)

  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)

在每个辅助副本主机上配置分发。 为 @password 指定在分发服务器上运行 sp_adddistributor 时使用的相同的值来设置分发。

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

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

  1. 在目标主要副本上，创建数据库作为成员数据库的可用性组。

  2. 为可用性组创建 DNS 侦听器。 复制代理使用侦听器连接到当前的主要副本。 以下示例会创建一个名为 MyAGListername 的侦听器。

    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> 是可选的。 仅在使用非默认端口时才需要。 然后，不要包括尖括号 <>。

完成上述步骤后，可用性组已准备好加入对等拓扑。 接下来的步骤将配置要加入的 SQL Server (Peer2) 的独立实例。

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

  1. 在分发服务器上配置分发。

    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 配置为分发服务器 Dist2 上的远程发布服务器

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

配置发布服务器 (Peer2)

  1. 在 Node3 上，配置远程分发。

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

  2. 在 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';

后续步骤

将对等发布数据库配置为可用性组的一部分