SQL Server Alwayson读写分离配置

概述  

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

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 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副本上创建登入用户。

登入方式

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

2.ssms：其它连接参数
---仅意向读连接
ApplicationIntent=ReadOnly
---读写连接
ApplicationIntent=ReadWrite

配置hosts 

--配置使用监听ip进行连接
192.168.1.22    db01.ag.com 
192.168.1.23    db02.ag.com
192.168.1.24    db03.ag.com
--配置使用hostname进行连接
192.168.1.22    db01
192.168.1.23    db02
192.168.1.24    db03

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

连接测试

1.ReadOnly

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