SQL Server Alwayson讀寫分離配置

標籤：mod   data   標籤   mil   read   存在   priority   out   pwd   

標籤：MSSQL/唯讀路由

概述  

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

 db01:192.168.1.22db02:192.168.1.23db03: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 ONN‘db01‘ WITH(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N‘TCP://db01.ag.com:1433‘))ALTER AVAILABILITY GROUP [Alwayson22]MODIFY REPLICA ONN‘db02‘ WITH(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N‘TCP://db02.ag.com:1433‘))ALTER AVAILABILITY GROUP [Alwayson22]MODIFY REPLICA ONN‘db03‘ WITH(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N‘TCP://db03.ag.com:1433‘))----為每個可能的primary role配置對應的唯讀路由副本--list列表有優先順序關係，排在前面的具有更高的優先順序,當db02正常時唯讀路由只能到db02，如果db02故障了唯讀路由才能路由到DB03ALTER AVAILABILITY GROUP [Alwayson22]MODIFY REPLICA ONN‘db01‘ WITH(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘db02‘,‘db03‘)));ALTER AVAILABILITY GROUP [Alwayson22]MODIFY REPLICA ONN‘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副本上建立登入使用者。

登入方式

1.C#連接字串
server=偵聽IP;database=;uid=;pwd=;ApplicationIntent=ReadOnly

2.ssms：其它串連參數
---僅意向讀串連
ApplicationIntent=ReadOnly
---讀寫串連
ApplicationIntent=ReadWrite

配置hosts 

--配置使用監聽ip進行串連192.168.1.22    db01.ag.com 192.168.1.23    db02.ag.com192.168.1.24    db03.ag.com--配置使用hostname進行串連192.168.1.22    db01192.168.1.23    db02192.168.1.24    db03

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

串連測試

1.ReadOnly

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

概述  

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

 

SQL Server Alwayson讀寫分離配置