SQL Server AlwaysOn读写分离配置图文教程

---查询可用性副本信息
SELECT * FROM master.sys.availability_replicas
---建立read指针 - 在当前的primary上为每个副本建立副本对于的tcp连接
ALTER AVAILABILITY GROUP [Alwayson22]
MODIFY REPLICA ON
N'db01' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://db01.ag.com:1433'))
ALTER AVAILABILITY GROUP [Alwayson22]
MODIFY REPLICA ON
N'db02' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://db02.ag.com:1433'))
ALTER AVAILABILITY GROUP [Alwayson22]
MODIFY REPLICA ON
N'db03' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://db03.ag.com:1433'))
----为每个可能的primary role配置对应的只读路由副本
--list列表有优先级关系，排在前面的具有更高的优先级,当db02正常时只读路由只能到db02，如果db02故障了只读路由才能路由到DB03
ALTER AVAILABILITY GROUP [Alwayson22]
MODIFY REPLICA ON
N'db01' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('db02','db03')));
ALTER AVAILABILITY GROUP [Alwayson22]
MODIFY REPLICA ON
N'db02' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('db01','db03')));
--查询优先级关系
SELECT ar.replica_server_name ,
    rl.routing_priority ,
    ( SELECT  ar2.replica_server_name
     FROM   sys.availability_read_only_routing_lists rl2
          JOIN sys.availability_replicas AS 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 ON rl.replica_id = ar.replica_id

配置使用监听ip进行连接192.168.1.22 db01.ag.com 192.168.1.23 db02.ag.com192.168.1.24 db03.ag.com--配置使用hostname进行连接192.168.1.22 db01192.168.1.23 db02192.168.1.24 db03

ALTER AVAILABILITY GROUP [Alwayson21]
MODIFY REPLICA ON
N'HD21DB01' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('HD21DB02','HD21DB03','HD21DB04'),'HD21DB01')));
ALTER AVAILABILITY GROUP [Alwayson21]
MODIFY REPLICA ON
N'HD21DB02' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('HD21DB01','HD21DB03','HD21DB04'),'HD21DB02')));