SQL Server Alwayson讀寫分離配置

標籤：sql

概述  

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

 

db01:192.168.1.22

db02:192.168.1.23

db03:192.168.1.24

監聽ip:192.168.1.25

 

配置可用性群組

650) this.width=650;" src="http://images2015.cnblogs.com/blog/135426/201706/135426-20170613112651696-1072170469.png" style="border:0px;" />

可用性複本概念

背景工作角色支援的串連訪問類型

1.無串連
不允許任何使用者串連。 次要資料庫不可用於讀訪問。 這是背景工作角色中的預設行為。

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

3.允許任何唯讀串連
次要資料庫全部可用於讀訪問串連。 此選項允許較低版本的用戶端進行串連。

主角色支援的串連訪問類型

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

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

配置語句

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="border:none;" />

---查詢可用性複本資訊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

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="border:none;" />

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

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

登入方式

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

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

配置hosts 

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="border:none;" />

--配置使用監聽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

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="border:none;" />

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

串連測試

1.ReadOnly

650) this.width=650;" src="http://images2015.cnblogs.com/blog/135426/201706/135426-20170613223549915-656934900.png" style="border:0px;" />

650) this.width=650;" src="http://images2015.cnblogs.com/blog/135426/201706/135426-20170613223729087-968451592.png" style="border:0px;" />

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

SQL Server Alwayson讀寫分離配置