SqlServer的复制订阅组建中,分发服务器是一个关键的隐患点,现在将分发distribution变成alwayson高可用模式。
发布:TEST125
distribution1 : TEST177
distribution2 : TEST178
订阅:TEST129
TEST177/178 建立分发,过程略。
添加发布服务器,过程略。
TEST125配置分发TEST177并建立发布test -- (过程)--TEST177 job TEST125-test-1 --logreader
TEST129推送订阅 --(过程)--TEST177 job TEST125-test-Repl_test-TEST129-3
暂停agent - job
TEST177 distribution属性完整并完备,copybak至TEST178,restore with nocovery 。
由于无法通过界面建议distribution的alwayson,所以只能依赖于脚本。
中间建立ENDPOINT和开启EVENT SESSION过程略。
CREATE AVAILABILITY GROUP [dis_awo] WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY, DB_FAILOVER = OFF, DTC_SUPPORT = NONE) FOR DATABASE [distribution] REPLICA ON N'TEST177' WITH (ENDPOINT_URL = N'TCP://TEST177.Test.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)), N'TEST178' WITH (ENDPOINT_URL = N'TCP://TEST178.Test.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
TEST178上联结可用组,监听器: dis_ip ,distribution alwayson完成。
TEST177上agentjob TEST125-test-1 生成脚本,在TEST178上执行。
修改点:
@command=N'-Publisher [TEST125] -PublisherDB [test] -Distributor [dis_ip] -DistributorSecurityMode 1 -Continuous',
TEST177上agentjob TEST125-test-Repl_test-TEST129-3 生成脚本,在TEST178上执行。
修改点:
@server=N'TEST178',
@command=N'-Subscriber [TEST129] -SubscriberDB [test] -Publisher [TEST125] -Distributor [dis_ip] -DistributorSecurityMode 1 -Publication [Repl_test] -PublisherDB [test] -Continuous',
TEST177/178建立主辅启动job。
declare @role_desc varchar(50) select @role_desc=a.role_desc from sys.dm_hadr_availability_replica_states a join sys.dm_hadr_availability_replica_cluster_states b on a.replica_id=b.replica_id where b.replica_server_name=@@SERVERNAME if @role_desc='PRIMARY' begin exec sp_start_job @job_name='TEST125-test-1' exec sp_start_job @job_name='TEST125-test-Repl_test-TEST129-3' end else begin exec sp_stop_job @job_name='TEST125-test-1' exec sp_stop_job @job_name='TEST125-test-Repl_test-TEST129-3' end
过程完成。
此方法,对自己没有信心就不要使用,之后复制监控器界面将不再可用,只能通过脚本进行监控复制订阅。
PS:
在整个搭建测试的过程中,本着哪里不通改哪里,最后测试成功,以上是简化的方法。
过程中其他用到的脚本:
exec sp_helpdistpublisher N'TEST125' sp_changedistpublisher @publisher = 'TEST125' , @property = 'active' , @value = 'true' select datasource, srvid from master..sysservers where upper(srvname) = upper(N'TEST125') select datasource, srvid from master..sysservers where upper(srvname) = upper(N'TEST129') select * from MSdistribution_agents