当前位置：Gxlcms > 数据库问题 > Sqlserver 2012 Always on技术

Sqlserver 2012 Always on技术

时间：2021-07-01 10:21:17 帮助过：12人阅读

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

第二种

    1. 设置某具体“可用性组”的属性为：可读辅助副本为“仅读意向”
    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

技术分享 
  1. <span style="color: #008080;">--<span style="color: #008080;">由于这里有2个instance（包括了primary角色的）, 因此在primary上分别为这2个instance建立关系
  2. <span style="color: #0000ff;">ALTER AVAILABILITY <span style="color: #0000ff;">GROUP <span style="color: #ff0000;">[<span style="color: #ff0000;">alwayson<span style="color: #ff0000;">]<span style="color: #000000;">
  3. MODIFY REPLICA <span style="color: #0000ff;">ON<span style="color: #000000;">
  4. N<span style="color: #ff0000;">‘<span style="color: #ff0000;">LAB-SQL1<span style="color: #ff0000;">‘ <span style="color: #0000ff;">WITH<span style="color: #000000;">
  5. (SECONDARY_ROLE (READ_ONLY_ROUTING_URL <span style="color: #808080;">= N<span style="color: #ff0000;">‘<span style="color: #ff0000;">tcp://LAB-SQL1.lab-sql.com:1433<span style="color: #ff0000;">‘<span style="color: #000000;">))
  6. <span style="color: #0000ff;">ALTER AVAILABILITY <span style="color: #0000ff;">GROUP <span style="color: #ff0000;">[<span style="color: #ff0000;">alwayson<span style="color: #ff0000;">]<span style="color: #000000;">
  7. MODIFY REPLICA <span style="color: #0000ff;">ON<span style="color: #000000;">
  8. N<span style="color: #ff0000;">‘<span style="color: #ff0000;">LAB-SQL2<span style="color: #ff0000;">‘ <span style="color: #0000ff;">WITH<span style="color: #000000;">
  9. (SECONDARY_ROLE (READ_ONLY_ROUTING_URL <span style="color: #808080;">= N<span style="color: #ff0000;">‘<span style="color: #ff0000;">tcp://LAB-SQL2.lab-sql.com:1433<span style="color: #ff0000;">‘))</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
技术分享

 

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

技术分享 
  1. <span style="color: #008080;">--<span style="color: #008080;">为每个可能成为primary角色的server，建立相应的只读列表，下面的代码由于互为readonly server，因此优先级都是1
  2. <span style="color: #0000ff;">ALTER AVAILABILITY <span style="color: #0000ff;">GROUP <span style="color: #ff0000;">[<span style="color: #ff0000;">alwayson<span style="color: #ff0000;">]<span style="color: #000000;">
  3. MODIFY REPLICA <span style="color: #0000ff;">ON<span style="color: #000000;">
  4. N<span style="color: #ff0000;">‘<span style="color: #ff0000;">LAB-SQL2<span style="color: #ff0000;">‘ <span style="color: #0000ff;">WITH<span style="color: #000000;">
  5. (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST<span style="color: #808080;">=(<span style="color: #ff0000;">‘<span style="color: #ff0000;">LAB-SQL1<span style="color: #ff0000;">‘<span style="color: #000000;">)));
  6. <span style="color: #0000ff;">ALTER AVAILABILITY <span style="color: #0000ff;">GROUP <span style="color: #ff0000;">[<span style="color: #ff0000;">alwayson<span style="color: #ff0000;">]<span style="color: #000000;">
  7. MODIFY REPLICA <span style="color: #0000ff;">ON<span style="color: #000000;">
  8. N<span style="color: #ff0000;">‘<span style="color: #ff0000;">LAB-SQL1<span style="color: #ff0000;">‘ <span style="color: #0000ff;">WITH<span style="color: #000000;">
  9. (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST<span style="color: #808080;">=(<span style="color: #ff0000;">‘<span style="color: #ff0000;">LAB-SQL2<span style="color: #ff0000;">‘<span style="color: #000000;">)));
  10. <span style="color: #008080;">--<span style="color: #008080;">假如又增加了一台lab-sql3的secdonary，则sql可变为
  11. <span style="color: #0000ff;">ALTER AVAILABILITY <span style="color: #0000ff;">GROUP <span style="color: #ff0000;">[<span style="color: #ff0000;">alwayson<span style="color: #ff0000;">]<span style="color: #000000;">
  12. MODIFY REPLICA <span style="color: #0000ff;">ON<span style="color: #000000;">
  13. N<span style="color: #ff0000;">‘<span style="color: #ff0000;">LAB-SQL2<span style="color: #ff0000;">‘ <span style="color: #0000ff;">WITH<span style="color: #000000;">
  14. (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST<span style="color: #808080;">=(<span style="color: #ff0000;">‘<span style="color: #ff0000;">LAB-SQL1<span style="color: #ff0000;">‘, <span style="color: #ff0000;">‘<span style="color: #ff0000;">LAB-SQL3<span style="color: #ff0000;">‘<span style="color: #000000;">)));
  15. <span style="color: #0000ff;">ALTER AVAILABILITY <span style="color: #0000ff;">GROUP <span style="color: #ff0000;">[<span style="color: #ff0000;">alwayson<span style="color: #ff0000;">]<span style="color: #000000;">
  16. MODIFY REPLICA <span style="color: #0000ff;">ON<span style="color: #000000;">
  17. N<span style="color: #ff0000;">‘<span style="color: #ff0000;">LAB-SQL1<span style="color: #ff0000;">‘ <span style="color: #0000ff;">WITH<span style="color: #000000;">
  18. (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST<span style="color: #808080;">=(<span style="color: #ff0000;">‘<span style="color: #ff0000;">LAB-SQL2<span style="color: #ff0000;">‘, <span style="color: #ff0000;">‘<span style="color: #ff0000;">LAB-SQL3<span style="color: #ff0000;">‘<span style="color: #000000;">)));
  19. <span style="color: #008080;">--<span style="color: #008080;">上述语句中的列表是有优先级关系的，排在前面的具有更高的优先级</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
技术分享

 

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

技术分享 
  1. <span style="color: #0000ff;">select<span style="color: #000000;"> ar.replica_server_name, rl.routing_priority,
  2.  (<span style="color: #0000ff;">select<span style="color: #000000;"> ar2.replica_server_name 
  3.  <span style="color: #0000ff;">from<span style="color: #000000;"> sys.availability_read_only_routing_lists rl2 
  4.     <span style="color: #808080;">join sys.availability_replicas <span style="color: #0000ff;">AS ar2 <span style="color: #0000ff;">ON rl2.read_only_replica_id <span style="color: #808080;">=<span style="color: #000000;"> ar2.replica_id 
  5. <span style="color: #0000ff;">where rl.replica_id<span style="color: #808080;">=rl2.replica_id <span style="color: #808080;">and rl.routing_priority <span style="color: #808080;">=<span style="color: #000000;">rl2.routing_priority 
  6.     <span style="color: #808080;">and rl.read_only_replica_id<span style="color: #808080;">=rl2.read_only_replica_id) <span style="color: #0000ff;">as <span style="color: #ff0000;">‘<span style="color: #ff0000;">read_only_replica_server_name<span style="color: #ff0000;">‘ 
  7.     <span style="color: #0000ff;">from sys.availability_read_only_routing_lists rl <span style="color: #808080;">join sys.availability_replicas <span style="color: #0000ff;">AS ar <span style="color: #0000ff;">ON rl.replica_id <span style="color: #808080;">= ar.replica_id</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
技术分享

技术分享

这里的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的全名增加记录，如下：

  1. <span style="color: #000000;">192.168.0.1        LAB-SQL1.lab-sql.com
  2. 192.168.0.2        LAB-SQL2.lab-sql.com</span>

 

总结

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

Sqlserver 2012 Always on技术

标签：增加   hosts   read   流量   角色   建立连接时   转发   lists   lcm   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！