SQL Server AlwaysOn架构下只读负载均衡 为 Always On 可用性组配置只读路由
摘要：
相对mysql router,haproxy等负载均衡的方案，sql server的侦听器功能相对略显单薄，不借助外部方案的情况下，可以通过添加虚拟IP资源的方式实现读写与只读的分离。SQL Server 2016版本之后提供了只读负载均衡的方案，提供了新的思路。
先决条件：
可用性组必须拥有可用性组侦听器。 必须在辅助角色中将一个或多个可用性副本配置为接受只读（Read-intent Only和yes都可以，推荐yes） 企业微信截图_20210511173938.png
您必须连接到承载当前主副本的服务器实例 (没什么好说的，在主节点上执行操作) 如果使用 SQL 登录名，请确保帐户配置正确无误(略) 配置只读路由列表：
--配置可用性副本以支持只读路由
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N'COMPUTER01' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)); -- ALLOW_CONNECTIONS官方文档使用的READ_ONLY，我这边用的ALL
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N'COMPUTER01' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N'COMPUTER02' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL));
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N'COMPUTER02' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N'COMPUTER03' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL));
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N'COMPUTER03' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER03.contoso.com:1433'));
--配置路由规则
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N'COMPUTER01' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('COMPUTER02','COMPUTER03'),'COMPUTER01')));
-- 查询优先级关系
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
复制代码 测试配置：
DataSource=xxx;InitialCatalog=test;IntegratedSecurity=False;UserID=xxx;Password=xxx;ApplicationIntent=ReadOnly;
这部分使用ssms配置ApplicationIntent=ReadOnly不生效，可以使用ado.net或者jdbc的驱动..图片中使用了jdbc,可以看到使用相同侦听器作为Data Source，最终主机名会在配置的路由规则中轮询；
image.png
image.png
其他测试：
辅助节点宕机 关闭COMPUTER02,COMPUTER03的SQL Server服务，根据之前的规则，在COMPUTER01为主节点时，若COMPUTER02，COMPUTER03宕机，访问请求由COMPUTER01承接
MODIFY REPLICA ON N'COMPUTER01' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('COMPUTER02','COMPUTER03'),'COMPUTER01'))); 复制代码 测试结果符合预期；
2.手动故障转移（更改主节点）
将COMPUTER02 手动切换为主节点，测试后发现readonly只能连接到COMPUTER02;
添加当将COMPUTER02为主时，新的规则
MODIFY REPLICA ON N'COMPUTER02' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('COMPUTER03','COMPUTER01'),'COMPUTER02'))); 复制代码 测试新规则生效；
3.手动移除节点 将COMPUTER01从列表中移除，测试发现readonly只能连接到COMPUTER03
MODIFY REPLICA ON N'COMPUTER02' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER03','COMPUTER02'))); 复制代码 总结：
基于以上测试，新的策略可以设置为： 在4节点的服务器情况下，我们可以设置为：
平时所有只读请求指向 同步只读节点1，异步只读节点2，请求在两者之间轮询；
当两台只读节点性能到达瓶颈时，将异步只读节点3，异步只读节点4加入轮询列表，使读压力在四台之间分摊；
非高峰期后，再将节点3和节点4从轮询列表中抽离；
所有新增节点与移除节点操作都是Online的，不会对业务产生影响。
