Publisher数据的来源Distributor相当于搬运工Subscriber复制数据的目地首先配置Distributor其次Publisher最后Subscriber一、配置Distributorhttps://learn.microsoft.com/en-us/sql/relational-databases/replication/configure-distribution-availability-group?viewsql-server-ver17--1.在每个distributor实例上运行--这一步会创建1个repl_distributor的linked server,且会在instance上创建一个distributor_admin的sql账号use masterexec sp_adddistributor distributor servernameGO--2.只在第1个distributor运行exec sp_adddistributiondb database NdistributionGO--上面语句会创建1个叫distribution的数据库将它的Recovery mode从simple改为full并马上做一下备份--将该数据库添加到AG,注意图形界面看不到distributor只能用命令USE [master];GOALTER AVAILABILITY GROUP [agccc] ADD DATABASE [distribution];GO--3.只在从节点运行EXEC sys.sp_adddistributiondb database distribution;--4.在所有节点上运行distributor上面添加publisher如果publisher是AG则把AG中的每个instance都加进来。第一次添加publisher会要求设置Administrative link password的密码不管之前有没有设置都要再设一次。同时它会自动把publisher添加到linked server:重要distributor主节点上的sql server agent jobs在没发生主从切换时你在从节点是看不到。只有当切换时监视和同步复制代理作业自动运行后才会创建出来。二、配置publishersubscriberhttps://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server?viewsql-server-ver17将distribution对应的 AG listener 当做distributor, 添加到每个publisher创建publication,subscription在创建subscription时对应的linked server会自动在主节点上创建publisher和distributor的从节点都需要手动添加subscriber作为linked server。subscriber如果是AG则直接用subscriber的listener以下这步很重要涉及到主从切换AG时让复制也能工作Redirect the original publisher to the AG listener nameUSE distribution;GOEXECUTE sys.sp_redirect_publisheroriginal_publisher MyPublisher,publisher_db MyPublishedDB,redirected_publisher MyPublisherAGListenerName;验证一下DECLARE redirected_publisher AS sysname;EXEC sys.sp_validate_replica_hosts_as_publishersoriginal_publisher XMTESTAG1\ERRFIX,publisher_db ccc,redirected_publisher redirected_publisher OUTPUT;SELECT redirected_publisher AS [重定向生效的侦听器];GO
