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

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

注意：这里只是针对可能成为主副本的角色进行配置，这里没有给db03配置只读路由列表，原因是不想将主副本切换到DB03上面来，配置越多的主副本意味着你后面要做越多的事情包括备份、作业等。

到此只读路由已配置完成，不要忘记在每个alwayson副本上创建登入用户。

登入方式

C#连接字符串server=侦听IP;database=;uid=;pwd=;ApplicationIntent=ReadOnly

ssms：其它连接参数

—仅意向读连接
ApplicationIntent=ReadOnly
—读写连接
ApplicationIntent=ReadWrite配置hosts

配置使用监听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

注意：这一步只是在没有加入域的客户端进行配置，如果非域的客户端没有配置hosts无法使用监听IP和hostname进行连接，数据库服务器端不需要配置此项！！！

连接测试

1.ReadOnly

可以看到使用ApplicationIntent=ReadOnly连接属性正确的连接到了只读副本DB02上。ApplicationIntent=ReadWrite同理。

20170714补充

SQLServer2016支持多个只读副本负载分担只读操作,只读路由列表修改如下：

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')));

当HD21DB01作为主节点时，HD21DB02,HD21DB03,HD21DB04平均分摊读的压力，当HD21DB02,HD21DB03,HD21DB04都无法访问时读连接访问HD21DB01；演示如下：

概述

从上面我们可以看到只读路由的读写分离是通过连接属性ApplicationIntent=ReadOnlyReadWrite使得连接是连向主副本还是辅助副本，这意味着需要在应用端配置多个连接串手动的配置代码是走写还是只读。这也就是为什么一开始我说这是半读写分离的原因。还有一个缺陷就是虽然配置了两个只读副本，但是每次只有优先级高的那个只读副本能提供只读连接，只有当优先级高的那个只读副本故障了才能路由到下一个只读副本。这也就意味着当前只有2个副本在提供读写操作，多个只读副本之间不能做到同时提供读操作的负载均衡。