概述

Alwayson相對(duì)于數(shù)據(jù)庫(kù)鏡像最大的優(yōu)勢(shì)就是可讀副本，帶來(lái)可讀副本的同時(shí)還添加了一個(gè)新的功能就是配置只讀路由實(shí)現(xiàn)讀寫(xiě)分離；當(dāng)然這里的讀寫(xiě)分離稍微夸張了一點(diǎn)，只能稱之為半讀寫(xiě)分離吧！看接下來(lái)的文章就知道為什么稱之為半讀寫(xiě)分離。

數(shù)據(jù)庫(kù)：SQLServer2014

db01:192.168.1.22

db02:192.168.1.23

db03:192.168.1.24

監(jiān)聽(tīng)ip:192.168.1.25

配置可用性組

可用性副本概念輔助角色支持的連接訪問(wèn)類型

1.無(wú)連接

不允許任何用戶連接。 輔助數(shù)據(jù)庫(kù)不可用于讀訪問(wèn)。 這是輔助角色中的默認(rèn)行為。

2.僅讀意向連接

輔助數(shù)據(jù)庫(kù)僅接受ApplicationIntent=ReadOnly的連接,其它的連接方式無(wú)法連接。

3.允許任何只讀連接

輔助數(shù)據(jù)庫(kù)全部可用于讀訪問(wèn)連接。 此選項(xiàng)允許較低版本的客戶端進(jìn)行連接。

主角色支持的連接訪問(wèn)類型

1.允許所有連接

主數(shù)據(jù)庫(kù)同時(shí)允許讀寫(xiě)連接和只讀連接。 這是主角色的默認(rèn)行為。

2.僅允許讀/寫(xiě)連接

允許ApplicationIntent=ReadWrite或未設(shè)置連接條件的連接。 不允許ApplicationIntent=ReadOnly的連接。 僅允許讀寫(xiě)連接可幫助防止客戶錯(cuò)誤地將讀意向工作負(fù)荷連接到主副本。

配置語(yǔ)句

---查詢可用性副本信息
SELECT * FROM master.sys.availability_replicas
---建立read指針 - 在當(dāng)前的primary上為每個(gè)副本建立副本對(duì)于的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'))
----為每個(gè)可能的primary role配置對(duì)應(yīng)的只讀路由副本
--list列表有優(yōu)先級(jí)關(guān)系，排在前面的具有更高的優(yōu)先級(jí),當(dāng)db02正常時(shí)只讀路由只能到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')));
--查詢優(yōu)先級(jí)關(guān)系
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

注意：這里只是針對(duì)可能成為主副本的角色進(jìn)行配置，這里沒(méi)有給db03配置只讀路由列表，原因是不想將主副本切換到DB03上面來(lái)，配置越多的主副本意味著你后面要做越多的事情包括備份、作業(yè)等。

到此只讀路由已配置完成，不要忘記在每個(gè)alwayson副本上創(chuàng)建登入用戶。

登入方式

C#連接字符串server=偵聽(tīng)I(yíng)P;database=;uid=;pwd=;ApplicationIntent=ReadOnly

ssms：其它連接參數(shù)

---僅意向讀連接
ApplicationIntent=ReadOnly
---讀寫(xiě)連接
ApplicationIntent=ReadWrite配置hosts

配置使用監(jiān)聽(tīng)ip進(jìn)行連接192.168.1.22 db01.ag.com 192.168.1.23 db02.ag.com192.168.1.24 db03.ag.com--配置使用hostname進(jìn)行連接192.168.1.22 db01192.168.1.23 db02192.168.1.24 db03

注意：這一步只是在沒(méi)有加入域的客戶端進(jìn)行配置，如果非域的客戶端沒(méi)有配置hosts無(wú)法使用監(jiān)聽(tīng)I(yíng)P和hostname進(jìn)行連接，數(shù)據(jù)庫(kù)服務(wù)器端不需要配置此項(xiàng)！！！

連接測(cè)試

1.ReadOnly

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

20170714補(bǔ)充

SQLServer2016支持多個(gè)只讀副本負(fù)載分擔(dān)只讀操作,只讀路由列表修改如下：

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')));

當(dāng)HD21DB01作為主節(jié)點(diǎn)時(shí)，HD21DB02,HD21DB03,HD21DB04平均分?jǐn)傋x的壓力，當(dāng)HD21DB02,HD21DB03,HD21DB04都無(wú)法訪問(wèn)時(shí)讀連接訪問(wèn)HD21DB01；演示如下：

概述

從上面我們可以看到只讀路由的讀寫(xiě)分離是通過(guò)連接屬性ApplicationIntent=ReadOnly\ReadWrite使得連接是連向主副本還是輔助副本，這意味著需要在應(yīng)用端配置多個(gè)連接串手動(dòng)的配置代碼是走寫(xiě)還是只讀。這也就是為什么一開(kāi)始我說(shuō)這是半讀寫(xiě)分離的原因。還有一個(gè)缺陷就是雖然配置了兩個(gè)只讀副本，但是每次只有優(yōu)先級(jí)高的那個(gè)只讀副本能提供只讀連接，只有當(dāng)優(yōu)先級(jí)高的那個(gè)只讀副本故障了才能路由到下一個(gè)只讀副本。這也就意味著當(dāng)前只有2個(gè)副本在提供讀寫(xiě)操作，多個(gè)只讀副本之間不能做到同時(shí)提供讀操作的負(fù)載均衡。

總結(jié)

以上所述是小編給大家介紹的SQL Server AlwaysOn讀寫(xiě)分離配置，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！