--查看可用性组消息 --select * from sys.availability_replicas --01 设置 --配置CLUSTER1副本的只读路由属性(ReadOnly代表‘只读意向’) ALTER AVAILABILITY GROUP [TestAlwaysGroup] MODIFY REPLICA ON N‘CLUSTER1‘ WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)); ALTER AVAILABILITY GROUP [TestAlwaysGroup] MODIFY REPLICA ON N‘CLUSTER1‘ WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N‘TCP://cluster1.zszc.com:1433‘)); --配置CLUSTER2副本的只读路由属性(ReadOnly代表‘只读意向’) ALTER AVAILABILITY GROUP [TestAlwaysGroup] MODIFY REPLICA ON N‘CLUSTER2‘ WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP [TestAlwaysGroup] MODIFY REPLICA ON N‘CLUSTER2‘ WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N‘TCP://cluster2.zszc.com:1433‘)); --配置CLUSTER3副本的只读路由属性(ReadOnly代表‘只读意向’) ALTER AVAILABILITY GROUP [TestAlwaysGroup] MODIFY REPLICA ON N‘CLUSTER3\CLUSTER3‘ WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP [TestAlwaysGroup] MODIFY REPLICA ON N‘CLUSTER3\CLUSTER3‘ WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N‘TCP://cluster3.zszc.com:1433‘)); --配置CLUSTER1副本作为主副本时候的只读路由表 ALTER AVAILABILITY GROUP [TestAlwaysGroup] MODIFY REPLICA ON N‘CLUSTER1‘ WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘CLUSTER2‘,‘CLUSTER3\CLUSTER3‘,‘CLUSTER1‘))); --配置CLUSTER2副本作为主副本时候的只读路由表 ALTER AVAILABILITY GROUP [TestAlwaysGroup] MODIFY REPLICA ON N‘CLUSTER2‘ WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘CLUSTER1‘,‘CLUSTER3\CLUSTER3‘,‘CLUSTER2‘))); --配置CLUSTER3副本作为主副本时候的只读路由表 ALTER AVAILABILITY GROUP [TestAlwaysGroup] MODIFY REPLICA ON N‘CLUSTER3\CLUSTER3‘ WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘CLUSTER1‘,‘CLUSTER2‘,‘CLUSTER3\CLUSTER3‘))); GO --02. 查看只读路由表 --SELECT * FROM sys.availability_read_only_routing_lists select rp.replica_server_name, rp2.replica_server_name as readonly_replica_server_name, rl.routing_priority from sys.availability_read_only_routing_lists rl join sys.availability_replicas rp on rl.replica_id = rp.replica_id join sys.availability_replicas rp2 on rl.read_only_replica_id = rp2.replica_id
通过上述SQL设置路由,查询执行到的设置如下: