sqlserver读写分离

miss_puff

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;

结果输出：

登录/注册后可看大图