集群配置：SQL Server AlwaysOn读写分离配置详细教程

概述

Alwayson相对于数据库镜像最大的优势就是可读副本，带来可读副本的同时还添加了一个新的功能就是配置只读路由实现读写分离；当然这里的读写分离稍微夸张了一点，只能称之为半读写分离吧！看接下来的文章就知道为什么称之为半读写分离。

数据库：SQLServer2014

db01:192.168.1.22

db02:192.168.1.23

db03:192.168.1.24

监听ip:192.168.1.25

配置可用性组

可用性副本概念辅助角色支持的连接访问类型

1.无连接

不允许任何用户连接。 辅助数据库不可用于读访问。 这是辅助角色中的默认行为。

2.仅读意向连接

辅助数据库仅接受ApplicationIntent=ReadOnly的连接,其它的连接方式无法连接。

3.允许任何只读连接

辅助数据库全部可用于读访问连接。 此选项允许较低版本的客户端进行连接。

主角色支持的连接访问类型

1.允许所有连接

主数据库同时允许读写连接和只读连接。 这是主角色的默认行为。

2.仅允许读/写连接

允许ApplicationIntent=ReadWrite或未设置连接条件的连接。 不允许ApplicationIntent=ReadOnly的连接。 仅允许读写连接可帮助防止客户错误地将读意向工作负荷连接到主副本。

配置语句

—查询可用性副本信息
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