大网数据广告活动
数据库与应用安全

在可用性组中配置 SQL Server 分发数据库

· 52CE 我爱测

- 分发数据库 AG 需要具有侦听器。 当发布服务器添加分发服务器时,它将侦听器名称用作分发服务器名称。 - 将侦听器名称作为分发服务器名称创建复制作业。 - 新的作业监视分发数据库的状态(在 AG 中为主要或次要)并基于分发数据库的状态禁用或启用复制作业。

本文介绍在 Always On 可用性组 (AG) 中如何设置 SQL Server 复制分发数据库。

SQL Server 2017 CU6 和 SQL Server 2016 SP2-CU3 通过以下机制,引入对 AG 中复制分发数据库的支持:

  • 分发数据库 AG 需要具有侦听器。 当发布服务器添加分发服务器时,它将侦听器名称用作分发服务器名称。
  • 将侦听器名称作为分发服务器名称创建复制作业。
  • 新的作业监视分发数据库的状态(在 AG 中为主要或次要)并基于分发数据库的状态禁用或启用复制作业。

根据以下所述的步骤配置好 AG 中的分发数据库后,在分发数据库 AG 故障转移前后,复制配置和运行时作业都可以正确运行。

支持的方案

  • 将分发数据库配置到 AG 中。
  • 在 AG 故障转移前后配置复制(例如发布和订阅)。
  • 复制作业在故障转移前后运行。
  • 当分发数据库在 AG 中时,删除分发服务器和发布服务器上的复制。
  • 为现有分发数据库 AG 添加或删除节点。
  • 分发服务器可能具有多个分发数据库。 每个分发数据库都可以在各自的 AG 中,但不能在任意 AG 中。 多个分发数据库可以共享一个 AG。
  • 发布服务器和分发服务器都需要在单独的 SQL Server 实例上。
  • 如果托管分发数据库的可用性组的侦听器配置为使用非默认端口,则需要为侦听器和非默认端口设置别名。

限制或排除

  • 不支持本地分发服务器。 例如,发布服务器和分发服务器必须为不同的 SQL Server 实例。 使用自身作为分发服务器(也称为 本地分发服务器)的发布服务器无法支持 AG 中的分发数据库。

  • 不支持 Oracle 发布服务器。

  • 不支持合并复制。

  • 不支持使用即时或排队更新订阅服务器的事务复制。

  • 不支持对等复制。

  • 托管分发数据库副本的所有 SQL Server 实例必须为 SQL Server 2017 CU 6 或更高版本。

  • 托管分发数据库副本的所有 SQL Server 实例的版本必须相同,除非进行升级的时间范围较窄。

  • 分发数据库必须处于完全恢复模式。

  • 要恢复和允许事务日志截断,请配置完整备份和事务日志备份。

  • 分发数据库 AG 必须具有已配置的侦听器。

  • 分发数据库 AG 中的次要副本可以是同步的,也可以是异步的。 建议和首选同步模式。

  • 不支持双向事务复制。

  • 分发数据库添加到可用性组后,SSMS 不会将分发数据库显示为同步/同步。

    NOTE 在次要副本上执行任何复制存储过程之前(例如 sp_dropdistpublisher、sp_dropdistributiondb、sp_dropdistributor、sp_adddistributiondb、sp_adddistpublisher),请确保此副本已完全同步。

  • 分发数据库 AG 中的所有次要副本都必须为可读取。

  • 分发数据库 AG 中的所有节点都需要使用相同的域帐户来运行 SQL Server 代理,并且该域帐户需要对每个节点都具有相同的特权。

  • 如果任何复制代理在代理帐户下运行,则代理帐户需要存在于分发数据库 AG 的每个节点中,并且对每个节点都具有相同的特权。

  • 在参与分发数据库 AG 的所有副本中,更改分发服务器或分发数据库属性。

  • 在参与分发数据库 AG 的所有副本中,通过 msdb 存储过程或 SQL Server Management Studio 更改复制作业。

  • 需要使用脚本完成在发布服务器上分配分发服务器。 无法使用复制向导。 支持将复制向导和属性表用于其他用途。

  • 只能通过脚本完成为分发数据库配置 AG。

  • 在 AG 中设置分发数据库需要是新的复制配置。 不支持将现有分发数据库切换到 AG。 同样,一旦将分发数据库从 AG 中移除,它就不能再作为有效的分发数据库运行,应该将其弃用。

配置体系结构

本文中的示例使用了以下服务器名称和设置。

  • DIST1、DIST2、DIST3 为分发服务器;
  • PUB 为发布服务器;
  • 分发数据库 AG 形成后,侦听器的名称为 DISTLISTENER;
  • DIST1 是用于分发数据库 AG 的初始主要副本。

配置分发服务器、分发数据库和发布服务器

此例配置新的分发服务器和发布服务器并将分发数据库置于 AG。

分发服务器工作流

  1. 通过 sp_adddistributor @@servername 将 DIST1、DIST2、DIST3 配置为分发服务器。 通过 @password 为 distributor_admin 指定密码。 DIST1、DIST2、DIST3 的 @password 应完全相同。

  2. 通过 sp_adddistributiondb 在 DIST1 上创建分发数据库。 分发数据库的名称为 distribution。 将 distribution 数据库的恢复模式从简单更改为完全。

  3. 使用 DIST1、DIST2 和 DIST3 上的副本,为 distribution 数据库创建 AG。 首选将所有的副本都设为同步。 将次要副本配置为可读取或允许读取。 此时,分发数据库是 AG,DIST1 是主要副本,DIST2 和 DIST3 是次要副本。

  4. 为 AG 配置名为 DISTLISTENER 的侦听器。

  5. 要恢复和允许事务日志截断,请配置完整备份和事务日志备份。

  6. 在 DIST2 和 DIST3 上,运行:

    sp_adddistributiondb 'distribution'
    
  7. 若要在 DIST1 上将 PUB 添加为发布服务器,请运行:

    sp_adddistpublisher @publisher= 'PUB', @distribution_db= 'distribution', @working_directory= '<network path>'
    

    @working_directory 的值应为独立于 DIST1、DIST2 和 DIST3 的网路路径。

  8. 在 DIST2 和 DIST3 上,运行:

    sp_adddistpublisher @publisher= 'PUB', @distribution_db= 'distribution', @working_directory= '<network path>'
    

    @working_directory 的值应与上一步相同。

发布服务器工作流

若要在 PUB 上将 distribution 数据库 AG 侦听器添加为分发服务器,请运行:

sp_adddistributor @distributor = 'DISTLISTENER', @password = <distributor_admin password> 

@password 的值应该为在分发服务器工作流中配置分发服务器时指定的值。

删除分发服务器和发布服务器

此示例在分发数据库位于 AG 时,删除发布服务器和分发服务器。

发布服务器工作流

在 PUB 上,删除此发布服务器的所有订阅和发布,然后调用 sp_dropdistributor。

分发服务器工作流

在此示例中,DIST1 是 distribution 数据库 AG 当前的主要副本。 DIST2 和 DIST3 是次要副本。

  1. 在 DIST2 和 DIST3 上,运行:

    sp_dropdistpublisher 'PUB',  @no_checks = 1
    
  2. 在 DIST1 上,运行:

    sp_dropdistpublisher 'PUB'
    
  3. 删除 AG。

  4. 在 DIST2 和 DIST3 上,通过恢复使数据库还原,将 distribution 数据库更改为 read_write 模式。

    RESTORE DATABASE distribution WITH RECOVERY, KEEP_REPLICATION
    
  5. 若要删除 distribution 数据库并保留快照目录,请运行:

    sp_dropdistributiondb 'distribution' , @former_ag_secondary=1
    

此过程将删除此副本的所有无关联作业。

  1. 若要在 DIST1 上删除 distribution 数据库,请运行

    sp_dropdistributiondb 'distribution'
    
  2. 如果 AG 中没有其他分发数据库,则在 DIST1、DIST2 和 DIST3 上运行 sp_dropdistributor。

向分发数据库 AG 添加副本

此例通过 AG 中的分发数据库向现有复制配置添加新的分发服务器。 在此例中,现有分发数据库在 AG 中。 DIST1 和 DIST2 是分发服务器,distribution 是 AG 中的分发数据库,PUB 是发布服务器。 将 DIST3 添加为 AG 中的副本。

分发服务器工作流

  1. 应通过 sp_adddistributor @@servername 将 DIST3 配置为分发服务器。 应通过 @password 参数指定 distributor_admin 的密码。 此密码应与为 DIST1 和 DIST2 指定的密码相同。

  2. 向当前分发数据库的 AG 添加 DIST3。

  3. 在 DIST3 上,运行:

    sp_adddistributiondb 'distribution'
    
  4. 在 DIST3 上,运行:

    sp_adddistpublisher @publisher= 'PUB', @distribution_db= 'distribution', @working_directory= '<network path>'
    

    此 @working_directory 值应与为 DIST1 和 DIST2 指定的值相同。

  5. 在 DIST3 中,必须重新创建指向订阅服务器的链接服务器。

从分发数据库 AG 删除副本

此例从当前分发服务器 AG 删除分发服务器,但不会影响分发数据库 AG 中的其他副本。 在此例中,分发数据库在 AG 中。 DIST1、DIST2 和 DIST3 是分发服务器,distribution 是 AG 中的分发数据库,PUB 是发布服务器。 从 AG 删除 DIST3。

分发服务器工作流

  1. 请确保 DIST3 是 distribution 数据库 AG 的次要副本。

  2. 从 distribution 数据库 AG 删除 DIST3。

  3. 在 DIST3 上,通过恢复使数据库还原,将 distribution 数据库更改为 read_write 模式。 例如,运行以下命令:

    RESTORE DATABASE distribution WITH RECOVERY, KEEP_REPLICATION.
    
  4. 若要删除在 DIST3 上的所有孤立作业,请运行:

    sp_dropdistpublisher 'PUB', @no_checks = 1
    
  5. 在 DIST3 上,运行:

    sp_dropdistributiondb 'distribution', @former_ag_secondary=1
    
  6. 在 DIST3 上,运行:

    sp_dropdistributor
    

从分发数据库 AG 删除发布服务器

此例从分发服务器的当前分发数据库 AG 中删除发布服务器,但不会影响此分发数据库 AG 服务的其他发布服务器。 在此例中,现有配置具有 AG 中的分发数据库。 DIST1、DIST2 和 DIST3 是分发服务器,distribution 是 AG 中的分发数据库,PUB1 和 PUB2 是 distribution 数据库服务的发布服务器。 此示例从这些分发服务器中删除 PUB1。

发布服务器工作流

在 PUB1 上,删除此发布服务器的所有订阅和发布,然后调用 sp_dropdistributor。

分发服务器工作流

DIST1 是 distribution 数据库 AG 当前的主要副本。

  1. 在 DIST2 和 DIST3 上,运行:

    sp_dropdistpublisher 'PUB1',  @no_checks = 1
    
  2. 在 DIST1 上,运行:

    sp_dropdistpublisher 'PUB1'
    
  3. 此时,可能有与 DIST2 或 DIST3 上的 PUB1 相关的孤立作业。 每当 DIST2 和 DIST3 发生故障转移时,Monitor and sync replication agent jobs 作业都将删除与 PUB1 的所有发布相关的孤立作业。

添加订阅

此示例介绍在分发服务器之间正确配置订阅服务器的信息。 此示例添加订阅服务器。 DIST1 是 AG 中分发数据库的主要副本,DIST2 和 DIST3 是 AG 中分发数据库的次要副本。 订阅服务器的名称为 SUB。

发布服务器工作流

在 PUB 上,类似于通常对订阅服务器 SUB 执行的操作一样,添加订阅。

分发服务器工作流

在 DIST2 和 DIST3 上,如果之前没有使用 DIST2 和 DIST3 注册“SUB”,则为其添加已链接的服务器。 以下是为链接服务器创建的示例 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=NULL,@rmtpassword=NULL

添加请求订阅

订阅服务器工作流

若要使用 AG 中的分发数据库添加请求订阅,请使用 sp_addpullsubscription_agent 的 @distributor 参数中的 AG 侦听器名称。

示例 T-SQL 创建 AG 中的分发数据库

以下脚本启用可用性组中的分发数据库。

--- 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
sp_adddistributor @distributor = @@ServerName, @password = 'Pass@word1'
Go 
:Connect SQLNode2
sp_adddistributor @distributor = @@ServerName, @password = 'Pass@word1'
Go

-- Step2 - Configure the Distribution Database
:Connect SQLNode1
USE master
EXEC 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 '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 SQLNode1 also as a Distributor
:CONNECT SQLNODE2
EXEC sp_adddistributiondb @database = 'DistributionDB', @security_mode = 1;
GO

--STEP 6 - On all Distributor Nodes Configure the Publisher Details 
:CONNECT SQLNODE1
EXEC sp_addDistPublisher @publisher = 'SQLNode4', @distribution_db = 'DistributionDB', 
    @working_directory = '\\sqlfileshare\Dist_Work_Directory\'
GO
:CONNECT SQLNODE2
EXEC sp_addDistPublisher @publisher = 'SQLNode4', @distribution_db = 'DistributionDB', 
    @working_directory = '\\sqlfileshare\Dist_Work_Directory\'
GO

-- SECTION 2 ---- CONFIGURE THE PUBLISHER SERVER
:CONNECT SQLNODE4
EXEC 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)

历史资料。此文对应 2018-11-30 的中文文档快照,适用产品版本和命令行为以原文为准。

来源与署名:Microsoft 和文档贡献者(原始作者元数据 MikeRayMSFT)。固定版本原文。文档采用 CC BY 4.0,示例代码采用 MIT 授权。本站将 Markdown 转为 HTML,规范化链接,移除外部媒体、发布器占位标签和导航标记。

资料来源与适用范围

资料来源
Microsoft / SQL Server 中文文档历史快照
适用范围
历史中文快照 2018-11-30;详见原文产品版本;文档日期 11/13/2018
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-16
授权说明
CC BY 4.0(文档)/ MIT(示例代码)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。