SQLServer2016 AlwaysOn搭建实践含过程截图(2/2) -清零世界

bubuko 2022/1/25 19:50:11 sqlserver 字数 9923 阅读 1020 1 来源 http://www.bubuko.com/infolist-5-1.html

--查看可用性组消息
--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设置路由，查询执行到的设置如下：

技术分享图片

SQLServer2016 AlwaysOn搭建实践含过程截图(2/2)

原文：https://www.cnblogs.com/shuzehui/p/12887229.html

如果您也喜欢它,动动您的小指点个赞吧

SQLServer2016 AlwaysOn搭建实践含过程截图(2/2)

分类汇总

您的鼓励是对我最大的支持