[SQL]AlwaysON 的唯讀路由設定

SQL Server 的 AlwaysON 不僅可以做到 HA/DR,也可以讓 Secondary Replica 支援資料讀取,讓系統發揮更大的效能。

之前有關 AlwaysON 的設定,本來是自己做個筆記,把相關的指令給記下來免得忘記。整理完之後,剛好跟一些朋友聊到,似乎大家都只有把 AlwaysON 當成備援使用,感覺起來似乎有點浪費,因此整理一下相關路由設定。

一般在設定好 AlwaysON 之後,大家都會要記得設定 Listen,但有些時候會忘記設定可用性群組的「唯讀路由」,雖然這個部分可以透過 SSMS 來做設定,但有些時候會發生錯誤,因此我就改用 Script 的語法來做處理

在我們的架構中,雖然有三個 SQL Server,但我們主要還是設定以 PROD Site 為主

因此我們就可以透過以下語法,先設定好每一台 SQL 的路由 URL,接著再設定路由的優先順序,因此我規劃當 SQL1 是 Primary Replica 的時候,那麼就可以去讀取 SQL2 的資料當分流;反之如果是 SQL2 是 Primary Replica 的時候,則就去讀取 SQL1 。

USE [master]
GO
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON N'SQL1' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://SQL1.5l2f.com:1433'))
GO
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON N'SQL2' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://SQL2.5l2f.com:1433'))
GO
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON N'SQL3' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://SQL3.5l2f.com:1433'))
GO

USE [master]
GO
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON N'SQL2' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'SQL1',N'SQL2')))
GO
ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON N'SQL1' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'SQL2',N'SQL1')))
GO

當設定好之後,我們可以來測試是否唯讀路由是否有設定正確,因此這裡我先搭配 sqlcmd 的工具程式來做測試,如下圖所示。您可以看到在 1 的部分,我們再連接可用性群組的 Listen 時候,特別加上一個 -K (要注意參數有分大小寫,這裡一定要用大寫)的參數,並且指定為「readonly」,您可以看到執行的結果,他會去連接到 SQL2;而在下面 2 的部分,這裡我就沒有指定讀取意圖,因此可以看到這個時候,就會連接到 SQL1 上面去了。

所以透過上述的測試,可以看到我們的唯讀路由設定是成功的。那接下來我們要來看那如果在我們的應用程式內,要如何去指定呢 ? 因為懶得寫程式編譯去測試,我就直接土法煉鋼,用 VB Script 來測試,這樣寫好之後就直接雙擊兩下就可以直接執行了。

在下面的範例中,我採用的是 SQL Native Client Driver 來進行連接,而在連線字串最後面,加上 Application Intent=ReadOnly 的設定,存檔之後點擊兩下,就會顯示出連到哪一台機器上了。這裡要特別注意一下,如果使用 Ole DB Provider 的話,那麼他沒有支援唯讀路由,要改用 SQL Native Client 才可以,且 Application Intent 這個中間是有個空白,我一開始也在這裡卡關卡一陣子。

Dim Conn  
Dim Rs1  
  
Connect = "Provider=SQLNCLI11.1;Data Source=SQLListen;Initial Catalog=DEMO;Integrated Security=SSPI;Application Intent=ReadOnly"  

Source = "SELECT @@servername"  

Set Conn = CreateObject("ADODB.Connection")
Set Rs1 = CreateObject( "ADODB.Recordset" )  
Rs1.Open Source, Connect 
msgbox(Rs1(0))