SQLServerAlwaysOn读写分离配置图文教程

概述

Alwayson相对于数据库镜像最⼤的优势就是可读副本，带来可读副本的同时还添加了⼀个新的功能就是配置只读路由实现读写分离；当然这⾥的读写分离稍微夸张了⼀点，只能称之为半读写分离吧！看接下来的⽂章就知道为什么称之为半读写分离。数据库：SQLServer2014db01:192.168.1.22db02:192.168.1.23db03: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 ONN'db01' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://db01.ag.com:1433'))ALTER AVAILABILITY GROUP [Alwayson22]MODIFY REPLICA ONN'db02' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://db02.ag.com:1433'))ALTER AVAILABILITY GROUP [Alwayson22]MODIFY REPLICA ONN'db03' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://db03.ag.com:1433'))----为每个可能的primary role配置对应的只读路由副本

--list列表有优先级关系，排在前⾯的具有更⾼的优先级,当db02正常时只读路由只能到db02，如果db02故障了只读路由才能路由到DB03ALTER AVAILABILITY GROUP [Alwayson22]MODIFY REPLICA ONN'db01' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('db02','db03')));ALTER AVAILABILITY GROUP [Alwayson22]MODIFY REPLICA ONN'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

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

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

C#连接字符串server=侦听IP;database=;uid=;pwd=;ApplicationIntent=ReadOnlyssms：其它连接参数

---仅意向读连接

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 ONN'HD21DB01' WITH

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

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('HD21DB01','HD21DB03','HD21DB04'),'HD21DB02')));

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

概述

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

总结

以上所述是⼩编给⼤家介绍的SQL Server AlwaysOn读写分离配置，希望对⼤家有所帮助，如果⼤家有任何疑问请给我留⾔，⼩编会及时回复⼤家的。在此也⾮常感谢⼤家对⽹站的⽀持！