基于AG Listener的专用服务器事务复制故障自动切换方案咨询

前提条件 #

• 已完成Availability Group（AG）配置，包含目标3张表所在的数据库，且AG启用自动故障转移（同步提交模式、具备仲裁机制）。
• 专用分发服务器可正常访问AG所有副本节点。

核心实现步骤 #

1. 用AG监听器作为发布服务器标识 #

创建事务复制发布时，不要使用单个AG节点的主机名，而是指定AG监听器名称作为发布服务器：

• SSMS图形化创建：在"发布服务器"输入框中填写AG监听器名。
• T-SQL创建：执行sp_addpublication时，@publisher参数设为AG监听器名。
  这样复制代理（日志读取器、分发代理）会通过监听器自动指向当前AG主副本，故障转移后无需修改代理配置。

2. 配置复制代理的故障恢复能力 #

• 日志读取器代理：确保其连接字符串使用AG监听器，且默认启用-Continuous参数（持续运行）。通过sp_addlogreader_agent配置代理账号时，使用在所有AG节点都具备权限的账号（Windows集成或SQL身份验证，需同步权限）。
• 分发代理：同样使用AG监听器作为发布端连接标识，配置作业自动重试：在SQL Server代理中找到对应分发作业，进入「步骤→高级」，勾选「失败时重试」，设置重试次数（如10次）和间隔（如1分钟）。

3. 统一AG副本的复制权限 #

在所有AG副本节点上，为复制代理账号授予以下权限：

• 发布数据库：ALTER ANY PUBLICATION、VIEW SERVER STATE、VIEW DATABASE STATE权限（或直接加入db_owner角色）。
• 分发数据库：若使用专用分发服务器，确保代理账号在分发服务器上拥有分发数据库的db_owner权限。

4. 启用AG的事务复制支持（SQL Server 2016+） #

若使用SQL Server 2016及以上版本，可在AG属性中开启「允许事务复制」选项：

• 打开SSMS，右键AG→属性→事务复制，勾选允许事务复制。
  该功能会在AG故障转移后自动更新复制元数据，确保代理无缝切换到新主副本。

验证方法 #

手动触发AG自动故障转移，检查以下项确认复制正常运行：

• AG监听器成功指向新主副本。
• 日志读取器代理自动重新连接并读取新主的事务日志。
• 分发代理持续推送事务到订阅端，3张表数据与新主副本一致。

额外优化 #

• 仅复制目标3张表：创建发布时选择项目级发布，仅添加需要的表，降低复制负载。
• 监控复制状态：配置SQL Server代理警报，当复制代理失败时触发通知。

内容的提问来源于stack exchange，提问作者zul nasir