重庆思庄Oracle、Redhat认证学习论坛
标题: sqlserver读写分离 [打印本页]
作者: miss_puff 时间: 2020-11-19 13:52
标题: sqlserver读写分离
sqlserver搭建好always on后，可配置读写分离，作用是只读用户直接访问只读库，而避免给写库带来压力，步骤：
1、建立read指针，在当前的primary上为每个副本建立副本对应的TCP连接
ALTERAVAILABILITY GROUP [MergeADG]
MODIFYREPLICA ON
N'FOCDB01'WITH
(SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://172.31.86.63:1433'))
ALTERAVAILABILITY GROUP [MergeADG]
MODIFYREPLICA ON
N'FOCDB02'WITH
(SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://172.31.86.64:1433'))
ALTERAVAILABILITY GROUP [MergeADG]
MODIFYREPLICA ON
N'FOCDB03'WITH
(SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://172.31.86.118:1433'))
2、为每个可能的primaryrole配置对应的只读路由副本
list列表有优先级关系，排在前面的具有更高的优先级，当db02正常时只读路由只能到db02,如果db02故障了只读路由才能到db03
ALTERAVAILABILITY GROUP [MergeADG]
MODIFYREPLICA ON
N'FOCDB01'WITH
(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=('FOCDB02','FOCDB03')));
ALTERAVAILABILITY GROUP [MergeADG]
MODIFYREPLICA ON
N'FOCDB02'WITH
(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=('FOCDB01','FOCDB03')));
ALTER AVAILABILITY GROUP [MergeADG]
MODIFY REPLICA ON
N'FOCDB03' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('FOCDB01','FOCDB02')));
3、查询优先级关系
SELECT ar.replica_server_name ,
rl.routing_priority ,
( SELECT ar2.replica_server_name
FROM sys.availability_read_only_routing_listsrl2
JOIN sys.availability_replicasAS ar2 ON rl2.read_only_replica_id = ar2.replica_id
WHERE rl.replica_id = rl2.replica_id
AND rl.routing_priority =rl2.routing_priority
AND rl.read_only_replica_id= rl2.read_only_replica_id
) AS 'read_only_replica_server_name'
FROM sys.availability_read_only_routing_lists rl
JOIN sys.availability_replicas AS ar ONrl.replica_id = ar.replica_id;
结果输出：
