SQL Server AlwaysOn讀寫分離配置

pursuer.chen發表於2017-06-13
SQLServer

標籤：MSSQL/只讀路由

概述  

Alwayson相對於資料庫映象最大的優勢就是可讀副本，帶來可讀副本的同時還新增了一個新的功能就是配置只讀路由實現讀寫分離；當然這裡的讀寫分離稍微誇張了一點，只能稱之為半讀寫分離吧！看接下來的文章就知道為什麼稱之為半讀寫分離。

 
資料庫：SQLServer2014

db01:192.168.1.22

db02:192.168.1.23

db03:192.168.1.24

監聽ip:192.168.1.25

 

配置可用性組

可用性副本概念

輔助角色支援的連線訪問型別

1.無連線
不允許任何使用者連線。 輔助資料庫不可用於讀訪問。 這是輔助角色中的預設行為。

2.僅讀意向連線
輔助資料庫僅接受ApplicationIntent=ReadOnly 的連線,其它的連線方式無法連線。

3.允許任何只讀連線
輔助資料庫全部可用於讀訪問連線。 此選項允許較低版本的客戶端進行連線。

主角色支援的連線訪問型別

1.允許所有連線
主資料庫同時允許讀寫連線和只讀連線。 這是主角色的預設行為。

2.僅允許讀/寫連線
允許ApplicationIntent=ReadWrite或未設定連線條件的連線。 不允許 ApplicationIntent=ReadOnly的連線。 僅允許讀寫連線可幫助防止客戶錯誤地將讀意向工作負荷連線到主副本。

配置語句

---查詢可用性副本資訊
SELECT * FROM master.sys.availability_replicas

---建立read指標 - 在當前的primary上為每個副本建立副本對於的tcp連線
ALTER AVAILABILITY GROUP [Alwayson22]
MODIFY REPLICA ON
N'db01' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://db01.ag.com:1433'))

ALTER AVAILABILITY GROUP [Alwayson22]
MODIFY REPLICA ON
N'db02' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://db02.ag.com:1433'))

ALTER AVAILABILITY GROUP [Alwayson22]
MODIFY REPLICA ON
N'db03' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://db03.ag.com:1433'))


----為每個可能的primary role配置對應的只讀路由副本
--list列表有優先順序關係，排在前面的具有更高的優先順序,當db02正常時只讀路由只能到db02，如果db02故障了只讀路由才能路由到DB03
ALTER AVAILABILITY GROUP [Alwayson22]
MODIFY REPLICA ON
N'db01' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('db02','db03')));

ALTER AVAILABILITY GROUP [Alwayson22]
MODIFY REPLICA ON
N'db02' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('db01','db03')));

--查詢優先順序關係
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

注意：這裡只是針對可能成為主副本的角色進行配置，這裡沒有給db03配置只讀路由列表，原因是不想將主副本切換到DB03上面來，配置越多的主副本意味著你後面要做越多的事情包括備份、作業等。

到此只讀路由已配置完成，不要忘記在每個alwayson副本上建立登入使用者。

登入方式

C#連線字串

server=偵聽IP;database=;uid=;pwd=;ApplicationIntent=ReadOnly

ssms：其它連線引數

---僅意向讀連線
ApplicationIntent=ReadOnly
---讀寫連線
ApplicationIntent=ReadWrite

配置hosts 

--配置使用監聽ip進行連線
192.168.1.22    db01.ag.com 
192.168.1.23    db02.ag.com
192.168.1.24    db03.ag.com
--配置使用hostname進行連線
192.168.1.22    db01
192.168.1.23    db02
192.168.1.24    db03

注意：這一步只是在沒有加入域的客戶端進行配置，如果非域的客戶端沒有配置hosts無法使用監聽IP和hostname進行連線，資料庫伺服器端不需要配置此項！！！

連線測試

1.ReadOnly

可以看到使用ApplicationIntent=ReadOnly連線屬性正確的連線到了只讀副本DB02上。ApplicationIntent=ReadWrite同理。

20170714補充

SQLServer2016支援多個只讀副本負載分擔只讀操作,只讀路由列表修改如下：

ALTER AVAILABILITY GROUP [Alwayson21]
MODIFY REPLICA ON
N'HD21DB01' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('HD21DB02','HD21DB03','HD21DB04'),'HD21DB01')));

ALTER AVAILABILITY GROUP [Alwayson21]
MODIFY REPLICA ON
N'HD21DB02' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('HD21DB01','HD21DB03','HD21DB04'),'HD21DB02')));

當HD21DB01作為主節點時，HD21DB02,HD21DB03,HD21DB04平均分攤讀的壓力，當HD21DB02,HD21DB03,HD21DB04都無法訪問時讀連線訪問HD21DB01；演示如下：

概述  

從上面我們可以看到只讀路由的讀寫分離是通過連線屬性ApplicationIntent=ReadOnly\ReadWrite使得連線是連向主副本還是輔助副本，這意味著需要在應用端配置多個連線串手動的配置程式碼是走寫還是隻讀。這也就是為什麼一開始我說這是半讀寫分離的原因。還有一個缺陷就是雖然配置了兩個只讀副本，但是每次只有優先順序高的那個只讀副本能提供只讀連線，只有當優先順序高的那個只讀副本故障了才能路由到下一個只讀副本。這也就意味著當前只有2個副本在提供讀寫操作，多個只讀副本之間不能做到同時提供讀操作的負載均衡。

 

注意： 域伺服器當機了也不影響使用SQLServer身份驗證連線副本或者監聽器，Windows身份驗證會受域伺服器的影響。所以只要不故障切換AD當機了也不影響AlwaysOn群集的連線。這個功能減少了AlwaysON對AD的依賴，同時也減少建雙域控的成本。

 

 

 

 

搭建和加入域參考：http://www.cnblogs.com/chenmh/p/4444168.html

搭建故障轉移群集參考：http://www.cnblogs.com/chenmh/p/4479304.html

Alwayson搭建參考：http://www.cnblogs.com/chenmh/p/4484176.html

Alwayson配置兩個節點加共享資料夾仲裁見證：http://www.cnblogs.com/chenmh/p/7156719.html

Alwayson概念總結參考：http://www.cnblogs.com/chenmh/p/6972007.html

 

備註：

    作者：pursuer.chen

    部落格：http://www.cnblogs.com/chenmh

本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結。

《歡迎交流討論》

相關文章