Sqlserver 2012 Always on技術

標籤：增加   hosts   read   流量   角色   建立串連時   轉寄   lists   lcm   

使用了Sqlserver 2012 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. 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 ONN‘LAB-SQL1‘ WITH(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N‘tcp://LAB-SQL1.lab-sql.com:1433‘))ALTER AVAILABILITY GROUP [alwayson]MODIFY REPLICA ONN‘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，因此優先順序都是1ALTER AVAILABILITY GROUP [alwayson]MODIFY REPLICA ONN‘LAB-SQL2‘ WITH(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘LAB-SQL1‘)));ALTER AVAILABILITY GROUP [alwayson]MODIFY REPLICA ONN‘LAB-SQL1‘ WITH(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘LAB-SQL2‘)));--假如又增加了一台lab-sql3的secdonary，則sql可變為ALTER AVAILABILITY GROUP [alwayson]MODIFY REPLICA ONN‘LAB-SQL2‘ WITH(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘LAB-SQL1‘, ‘LAB-SQL3‘)));ALTER AVAILABILITY GROUP [alwayson]MODIFY REPLICA ONN‘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.com192.168.0.2        LAB-SQL2.lab-sql.com

 

總結

1. 簡單情況下的讀寫分離比較適用
2. 只適用於粗粒度的讀寫分離，因為增加了一個額外的ConnectionString，而不是建立在普通連接字串上的
3. 如果讀寫分離的分發規則複雜，則不適用

Sqlserver 2012 Always on技術