Sql server Always On 讀寫分離配置方法

使用了Sqlserver 2012 Always on技術(shù)后，假如采用的配置是默認配置，會出現(xiàn)PRimary server CPU很高的情況發(fā)生，比如默認配置如下：

需要自定義來解決這個問題。

我們先來看看上圖中的這些選項的意義

主角色中的連接

• 允許所有連接
  • 如果當前server是primary角色時，primary instance允許所有連接（如：讀/寫/管理）
• 允許讀/寫連接
  • 如果當前server是primary角色時，primary instance只允許讀/寫連接（如果通過ssms連接，將報錯、sqlcmd也是報錯）

可讀輔助副本

• 是
  • 如果當前server是primary角色時，所有的secondary servers都是可以看的（通過ssms能看結(jié)構(gòu)、數(shù)據(jù)，但不能更改）
• 僅讀意向
  • 如果當前server是primary角色時，所有的secondary servers只允許讀連接（需要在建立連接時加入key來標明為只讀連接：applicationIntent=ReadOnly）
• 否
  • 如果當前server是primary角色時，所有的secondary servers都不可以看（通過ssms能連接，但是看不了，會報錯，如下）
  • 

建立讀寫分離的方法：

第一種

1. 1. 設(shè)置某具體“可用性組”的屬性為：可讀副本為“是”
   2. 客戶端通過直連副本方式實現(xiàn)將select的流量轉(zhuǎn)發(fā)過去
   3. 暴露出去的ip地址至少2個：偵聽器ip和副本ip（如果副本多個，則可用ip哈希來進行更多的自定義）

第二種

1. 1. 設(shè)置某具體“可用性組”的屬性為：可讀輔助副本為“僅讀意向”
2. 1. 
   2. 執(zhí)行sql腳本，建立read指針
   3. 執(zhí)行sql腳本，建立primary, read db ur list關(guān)系
   4. 暴露出去的ip地址只有1個：偵聽器IP

第一種方式能夠進行更多地自定義，但是已經(jīng)脫離sqlserver always on技術(shù)了，因此不討論了

第二種方式對于客戶端來講更傻瓜點，但是自定義力度小，全依托于ms未來怎么改進這塊了，而且這里有些坑。。。

下面來說說這些坑：

坑1：UI圖形界面設(shè)置后，還需要執(zhí)行腳本來建立讀寫分離支持

建立read指針 - 在當前的primary上為每個sqlserver instance建立[instance name=>instance tcp url] Map

--由于這里有2個instance（包括了primary角色的）, 因此在primary上分別為這2個instance建立關(guān)系A(chǔ)LTER 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關(guān)系 - 在當前的primary上為各個primary建立對應(yīng)的read only url 列表（有優(yōu)先級概念）

--為每個可能成為primary角色的server，建立相應(yīng)的只讀列表，下面的代碼由于互為readonly server，因此優(yōu)先級都是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可變?yōu)锳LTER 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')));--上述語句中的列表是有優(yōu)先級關(guān)系的，排在前面的具有更高的優(yōu)先級

可以通過如下語句查看這個關(guān)系，以及相應(yīng)的優(yōu)先級：

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就是優(yōu)先級

坑2：客戶端需要指定訪問的數(shù)據(jù)庫以及加入ReadOnly關(guān)鍵字

C#連接字符串

• • server=偵聽器IP;database=testDB3;uid=sa;pwd=111111;ApplicationIntent=ReadOnly

SSMS方式

坑3：Hosts文件設(shè)置

由于sql server always on依賴于windows集群，而windows集群依賴于活動目錄，而客戶端程序所在server很可能沒有加入域，因此這里的解析存在問題

由于這種讀寫分離的方式，實際上是客戶端先連接到偵聽器ip，然后通過協(xié)商后，讓客戶端再連接到具體的副本上（用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

總結(jié)

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