SQL Server Always On 读写分离配置方法

共计 3021 个字符，预计需要花费 8 分钟才能阅读完成。

使用了 SQL Server Always On 技术后，假如采用的配置是默认配置，会出现 Primary server CPU 很高的情况发生，比如默认配置如下：

需要自定义来解决这个问题。

我们先来看看上图中的这些选项的意义

主角色中的连接

• 允许所有连接
  • 如果当前 server 是 primary 角色时，primary instance 允许所有连接（如：读 / 写 / 管理）
• 允许读 / 写连接
  • 如果当前 server 是 primary 角色时，primary instance 只允许读 / 写连接（如果通过 ssms 连接，将报错、sqlcmd 也是报错）

可读辅助副本

• 是
  • 如果当前 server 是 primary 角色时，所有的 secondary servers 都是可以看的（通过 ssms 能看结构、数据，但不能更改）
• 仅读意向
  • 如果当前 server 是 primary 角色时，所有的 secondary servers 只允许读连接（需要在建立连接时加入 key 来标明为只读连接：ApplicationIntent=ReadOnly）
• 否
  • 如果当前 server 是 primary 角色时，所有的 secondary servers 都不可以看（通过 ssms 能连接，但是看不了，会报错，如下）
  • 

建立读写分离的方法：

第一种

1. 1. 设置某具体“可用性组”的属性为：可读副本为“是”
   2. 客户端通过直连副本方式实现将 select 的流量转发过去
   3. 暴露出去的 ip 地址至少 2 个：侦听器 ip 和副本 ip（如果副本多个，则可用 ip 哈希来进行更多的自定义）

第二种

1. 1. 设置某具体“可用性组”的属性为：可读辅助副本为“仅读意向”
2. 1. 
   2. 执行 sql 脚本，建立 read 指针
   3. 执行 sql 脚本，建立 primary, read db ur list 关系
   4. 暴露出去的 ip 地址只有 1 个：侦听器 IP

第一种方式能够进行更多地自定义，但是已经脱离 sqlserver always on 技术了，因此不讨论了

第二种方式对于客户端来讲更傻瓜点，但是自定义力度小，全依托于 ms 未来怎么改进这块了，而且这里有些坑。

下面来说说这些坑：

坑 1：UI 图形界面设置后，还需要执行脚本来建立读写分离支持

建立 read 指针 – 在当前的 primary 上为每个 sqlserver instance 建立 [instance name=>instance tcp url] Map

-- 由于这里有 2 个 instance（包括了 primary 角色的）, 因此在 primary 上分别为这 2 个 instance 建立关系

ALTER AVAILABILITY GROUP [alwayson]
MODIFY REPLICA ON
N'LAB-SQL1' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'tcp://LAB-SQL1.lab-sql.com:1433'))

ALTER AVAILABILITY GROUP [alwayson]
MODIFY REPLICA ON
N'LAB-SQL2' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'tcp://LAB-SQL2.lab-sql.com:1433'))

建立 primary, read db ur list 关系 – 在当前的 primary 上为各个 primary 建立对应的 read only url 列表（有优先级概念）

-- 为每个可能成为 primary 角色的 server，建立相应的只读列表，下面的代码由于互为 readonly server，因此优先级都是 1

ALTER AVAILABILITY GROUP [alwayson]
MODIFY REPLICA ON
N'LAB-SQL2' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('LAB-SQL1')));


ALTER AVAILABILITY GROUP [alwayson]
MODIFY REPLICA ON
N'LAB-SQL1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('LAB-SQL2')));


-- 假如又增加了一台 lab-sql3 的 secdonary，则 sql 可变为
ALTER AVAILABILITY GROUP [alwayson]
MODIFY REPLICA ON
N'LAB-SQL2' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('LAB-SQL1', 'LAB-SQL3')));


ALTER AVAILABILITY GROUP [alwayson]
MODIFY REPLICA ON
N'LAB-SQL1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('LAB-SQL2', 'LAB-SQL3')));

-- 上述语句中的列表是有优先级关系的，排在前面的具有更高的优先级 

可以通过如下语句查看这个关系，以及相应的优先级：

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

这里的 routing_priority 就是优先级

坑 2：客户端需要指定访问的数据库以及加入 ReadOnly 关键字

C# 连接字符串

• • server= 侦听器 IP;database=testDB3;uid=sa;pwd=111111;ApplicationIntent=ReadOnly

SSMS 方式

坑 3：Hosts 文件设置

由于 SQL Server Always On 依赖于 Windows 集群，而 Windows 集群依赖于活动目录，而客户端程序所在 server 很可能没有加入域，因此这里的解析存在问题

由于这种读写分离的方式，实际上是客户端先连接到侦听器 ip，然后通过协商后，让客户端再连接到具体的副本上（用 tcp url，使用了全名的，如：sql1.ad.com 这种格式，在 ad 外部默认无法解析），因此需要修改 hosts 文件，为每个可能成为 read 的全名增加记录，如下：

192.168.0.1        LAB-SQL1.lab-sql.com
192.168.0.2        LAB-SQL2.lab-sql.com 

总结

1. 简单情况下的读写分离比较适用
2. 只适用于粗粒度的读写分离，因为增加了一个额外的 ConnectionString，而不是建立在普通连接字符串上的
3. 如果读写分离的分发规则复杂，则不适用

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-03/141213.htm