積沙成塔

建立VM的DSR負載平衡端點

為了讓雲端服務以外的應用程式可以透過AlwaysOn可用性群組接聽程式連接到可用性複本，接下來必須透過Windows Azure PowerShell來建立DSR（Direct Server Return）負載平衡端點。假設您已經安裝Windows Azure PowerShell並下載及匯入發行設定檔（詳細步驟可參考透過PowerShell管理Microsoft Azure的準備工作一文），接下來執行下列PowerShell指令碼來建立DSR負載平衡端點，您可以自行決定端點所要使用的連接埠編號和端點的名稱，其中連接埠編號筆者以TCP 8083連接埠來做示範。

# 建立Direct Server Return負載平衡端點
$AGNodes = "sql1","sql2","sql3" # WSFC中的SQL Server VM節點
$ServiceName = "alwaysonag" # SQL Server AlwaysOn可用性群組雲端服務名稱
$EndpointName = "DSR" # Direct Server Return負載平衡端點名稱
$EndpointPort = "8083" # Direct Server Return負載平衡端點連接埠

# 針對每一個節點建立Direct Server Return負載平衡端點
ForEach ($node in $AGNodes)
{
    Get-AzureVM -ServiceName $ServiceName -Name $node | Add-AzureEndpoint -Name $EndpointName -Protocol "TCP" -PublicPort $EndpointPort -LocalPort $EndpointPort -LBSetName "$EndpointName-LB" -ProbePort 59999 -ProbeProtocol "TCP" -DirectServerReturn $true | Update-AzureVM
}

若一切順利，便可以看到三台SQL SERVER VM各會有一筆Succeeded的紀錄。

而在Microsoft Azure管理入口網站的SQL Server VM端點列表之中，也可以看到DSR負載平衡端點被建立。

建立AlwaysOn可用性群組接聽程式及探查連接埠防火牆規則

前一節我們透過Windows Azure PowerShell所建立DSR負載平衡端點，主要是給雲端服務以外的應用程式連接AlwaysOn可用性群組接聽程式所使用的端點，因此必須在SQL Server VM中建立相對應的Inbound Rule，允許外部存取VM的TCP 8083連接埠（如下圖的AlwaysOn Listener Port防火牆規則）。

此外，上一節使用Windows Azure PowerShell指令碼也設定了AlwaysOn可用性群組接聽程式探查連接埠為59999，所以必須在SQL Server VM中建立相對應的Inbound Rule，允許外部存取VM的TCP 59999連接埠（如下圖的AlwaysOn Listener Probe Port防火牆規則）。

建立AlwaysOn可用性群組接聽程式

和企業內部部署不一樣的是，在Microsoft Azure上建立AlwaysOn可用性群組接聽程式必須藉由Failover Cluster Manager來完成，請於Failover Cluster Manager中點選【叢集名稱>Roles>AlwaysOn可用性群組名稱>Add Resource>Client Access Point】。

在Network Namea and IP Address欄位中輸入AlwaysOn接聽程式的名稱，在此筆者輸入agl，接著按Next繼續。

確認名稱及相關組態正確無誤，按Next繼續。

Client Access Point建立完成後按Finish來關閉視窗。

回到Failover Cluster Manager視窗您將會看到Client Access Point（agl）已經被建立，但狀態為Offline。

因為AlwaysOn可用性群組接聽程式需要設為雲端服務公開虛擬IP，才可以對外提供服務，但是在Failover Cluster Manager沒辦法將IP位址設定和VM不同網段，因此必須再次透過PowerShell來完成。接著請開啟PowerShell命令提示字元，執行下列指令碼：

# 設定接聽程式
$ClusterNetworkName = "Cluster Network 1" # 叢集網路名稱
$IPResourceName = "IP Address 10.0.0.0" # 叢集IP位址名稱
$CloudServiceIP = "23.100.95.212" # 雲端服務虛擬IP位址
Import-Module FailoverClusters
Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$CloudServiceIP";"ProbePort"="59999";SubnetMask="255.255.255.255";"Network"="$ClusterNetworkName";"OverrideAddressMatch"=1;"EnableDhcp"=0}

有關叢集網路名稱及叢集IP資源名稱，可以使用Get-ClusterNetwork以及Get-ClusterResource指令來查詢。

而雲端服務外部IP則可以在Microsoft Azure管理入口網站，雲端服務的儀表板頁面看的到（即下圖的公開虛擬IP（VIP）位址）。

執行完上述PowerShell指令碼，您會發現Client Access Point的IP Address已經被改為雲端服務的公開虛擬IP位址，接著在Client Access Point（agl）項目上按滑鼠右鍵選擇Bring Online來讓Client Access Point上線。

於AlwaysOn可用性群組（MyAG）上按滑鼠右鍵選擇Properties。

點選Dependencies頁籤，於Resource欄位下拉式選單中選擇前面所建立的Client Access Point（agl），接著按OK來關閉屬性視窗。

回到SSMS在【Object Explorer>執行個體>AlwaysOn High Availability>Availability Groups>可用性群組名稱>Availability Group Listeners】，就可以看到您在Failiver Cluster Manager所建立的Client Access Point（agl）已經成為AlwaysOn可用性群組接聽程式，請在agl項目上按滑鼠右鍵選擇Properties。

最後在Port欄位中輸入DSR負載平衡端點的連接埠編號，輸入完畢後按OK。

測試AlwaysOn可用性群組接聽程式

為了支援不在網域內的應用程式存取AlwaysOn可用性群組，比較簡單的作法是將所有SQL Server執行個體都設定為混合式驗證，請在執行個體上按滑鼠右鍵選擇Properties，點選Security>SQL Server and Windows Authertication mode，然後按OK來關閉視窗。

或執行下列T-SQL指令碼來變更驗證模式：

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

設定完畢記得重新啟動執行個體來讓設定生效。

另外當您容錯移轉到其他可用性複本時，可能發生無法登入可用性資料庫的問題，您可以參考James Fu的[SQL]AlwaysON 帳號設定這篇文章，在每一台SQL Server執行個體中建立具有相同SID和密碼的登入（Login），或是將可用性資料庫設定為自主資料庫（請參考在AlwaysOn可用性群組中啟用自主資料庫的注意事項），您可以依照實際需求來決定較適合的方式來解決容錯移轉後的登入問題。

測試AlwaysOn可用性群組接聽程式時，必須在雲端服務外部來進行。因此筆者在本機開啟SQLCMD公用程式，輸入下列T-SQL指令碼，其中-S參數後面接的【雲端服務公開虛擬IP,DSR負載平衡連接埠】：

sqlcmd -S alwaysonag.cloudapp.net,8083 -d AGDB -U AGDBUser -P P@ssw0rd

連接之後查詢@@servername組態函數，得到和目前主要複本相同的伺服器名稱-SQL1，代表已經透過AlwaysOn可用性群組接聽程式連接成功，並且新增資料也是沒問題，因為主要複本支援讀寫動作。為了測試容錯移轉功能及接聽程式是否會跟著容錯移轉發生後，連接到新的主要複本，於可用性群組儀表板上按右上方的Start Failover Wizard來執行手動容錯移轉。

將主要複本移轉到SQL2。

手動容錯移轉成功後重新連到AlwaysOn可用性群組接聽程式，再次查詢@@servername系統函數得到SQL2，和目前儀表板上顯示的主要複本相同，證明已經接聽程式已經將連線需求導向新的主要複本SQL2。

使用AlwaysOn可用性群組唯讀路由實作讀寫分流

當您的可用性群組包含超過兩個可用性複本時，可以規劃將非自動容錯移轉的可用性複本（sql3）當作是報表或是唯讀查詢的用途，而設定為自動容錯移轉的可用性複本（sql1、sql2）則專門用來提供寫入作業及高可用性。

請依照下列步驟來建立唯讀路由：

一、修改Readable Secodary屬性

由下圖可以看出目前Readable Secodary都設定為Yes，代表您可以連接到任一次要複本進行查詢動作。

請將每一個執行個體的Readable Seconary屬性都設定為Read-intent Only，設定完畢後按OK。

或是執行下列T-SQL指令碼來修改Readable Seconary屬性：

--設定可用性複本的Readable Secondary屬性為Read_Only
USE master
GO

ALTER AVAILABILITY GROUP [MyAG]
MODIFY REPLICA ON N'SQL1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
GO

ALTER AVAILABILITY GROUP [MyAG]
MODIFY REPLICA ON N'SQL2' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
GO

ALTER AVAILABILITY GROUP [MyAG]
MODIFY REPLICA ON N'SQL3' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
GO

二、設定唯讀路由URL

請在主要複本上執行下列T-SQL指令碼來建立唯讀路由URL：

--設定唯讀路由網址 
ALTER AVAILABILITY GROUP [MyAG] 
MODIFY REPLICA ON N'SQL1' 
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://alwaysonag.cloudapp.net:5000'));
GO 

ALTER AVAILABILITY GROUP [MyAG]
 MODIFY REPLICA ON N'SQL2' 
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://alwaysonag.cloudapp.net:6000'));
GO

ALTER AVAILABILITY GROUP [MyAG] 
MODIFY REPLICA ON N'SQL3' 
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://alwaysonag.cloudapp.net:7000'));
GO

其中SQL1使用的是TCP 5000作為唯讀路由的連接埠，SQL2使用的是TCP 6000，SQL3則是使用TCP 7000。

三、根據唯讀路由URL建立SQL Server VM端點

開啟Microsoft Azure分別在SQL1、SQL2和SQL3建立相對應的TCP端點，建立端點時選擇名稱為MSSQL，修改公用連接埠為前一步驟所指定的連接埠編號，例如SQL1公用連接埠設定為 5000。（特醒：請勿勾選建立負載平衡集）

完成之後您應該會每一台SQL Server VM的中看到唯讀路由所要使用的端點。

四、設定唯讀路由清單

最後一個步驟就是利用下列T-SQL來設定唯讀路由清單，當SQL1為主要複本時，若用戶端應用程式進行讀取意圖連線，會優先導向SQL3，其次是SQL2；當SQL2為主要複本時，用戶端應用程式進行讀取意圖連線，會優先導向SQL3，其次是SQL1；以此類推。

--設定唯讀路由清單
ALTER AVAILABILITY GROUP [MyAG] 
MODIFY REPLICA ON N'SQL1' 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQL3','SQL2')));
GO

ALTER AVAILABILITY GROUP [MyAG]
MODIFY REPLICA ON N'SQL2' 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQL3','SQL1')));
GO 

ALTER AVAILABILITY GROUP [MyAG]
MODIFY REPLICA ON N'SQL3' 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQL1','SQL2')));
GO 

五、測試唯讀路由

由下圖可知，目前主要複本為SQL1，依照前一步驟所設定的唯讀路由清單，當用戶端透過SQLCMD公用程式的-K Readonly參數進行唯讀意圖連線，則會被導向SQL3。

sqlcmd -S alwaysonag.cloudapp.net,8083 -d AGDB -U AGDBUser -P P@ssw0rd -K readonly

手動容錯移轉到SQL3，讓SQL3成為主要複本，重新進行唯讀意圖連線，由下圖可知，的確被導向原先設定好的SQL1。

若您的應用程式不支援讀取意圖連線，只要在雲端服務DNS名稱後面加上MSSQL端點的公用連接埠，也可以接連接到特定次要複本。

參考資料

Azure 虛擬機器中的 SQL Server 高可用性和災害復原

教學課程：Windows Azure 中的 AlwaysOn 可用性群組 (GUI)

教學課程：AlwaysOn 可用性群組的接聽程式設定

Use ReadIntent Routing with Azure AlwaysOn Availability Group Listener

建立或設定可用性群組接聽程式 (SQL Server)

透過PowerShell管理Microsoft Azure的準備工作

建立或設定可用性群組接聽程式

[SQL]AlwaysON 帳號設定

在AlwaysOn可用性群組中啟用自主資料庫的注意事項