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

--由于这里有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. 如果读写分离的分发规则复杂，则不适用

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

标签：ges   bar   读写分离配置   优先   更改   改进   关系   app   管理