使用了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能連接，但是看不了，會報錯，如下）

?

建立讀寫分離的方法：

第一種

設置某具體“可用性組”的屬性為：可讀副本為“是”

客戶端通過直連副本方式實現將select的流量轉發過去

暴露出去的ip地址至少2個：偵聽器ip和副本ip（如果副本多個，則可用ip哈希來進行更多的自定義）

第二種

設置某具體“可用性組”的屬性為：可讀輔助副本為“僅讀意向”

執行sql腳本，建立read指針

執行sql腳本，建立primary, read db ur list關系

暴露出去的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，因此優先級都是1ALTER 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

?

總結

簡單情況下的讀寫分離比較適用

只適用于粗粒度的讀寫分離，因為增加了一個額外的ConnectionString，而不是建立在普通連接字符串上的

如果讀寫分離的分發規則復雜，則不適用

?

?

轉載于:https://www.cnblogs.com/aarond/p/3679915.html

總結

以上是生活随笔為你收集整理的Sql server Always On 读写分离配置方法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。