五餅二魚工作室

之前有關 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))