混合式IT環境中如何設定SQL Server AlwaysOn可用性群組的接聽程式

2014-07-14

混合雲環境中如何設定SQL Server AlwaysOn可用性群組的接聽程式設定

環境描述

在上一篇筆者介紹了如何利用SQL Server 2014在SSMS裡所新增的Add Azure Replica，來將AlwaysOn可用性群組的一個次要節點延伸到Microsoft Azure Virtual Machine，以實現混合式IT環境下的SQL Server高可用性解決方案。如何在這個情境中，建立可用性群組接聽程式的做法比較接近純粹在Microsoft Azure環境下實作AlwaysOn可用性群組，您沒辦法直接在SSMS利用GUI的方式來加入接聽程式，必須使用Failover Cluster Manager來建立Client Access Point，並將Client Access Point和叢集角色做關聯，才能順利建立接聽程式。

接下來筆者就以上圖的情境來說明，如何透過可用性群組的接聽程式來達到讀寫分流的應用，讓應用程式需要寫入資料時，由可用性群組接聽程式來導向到主要複本，僅需讀取資料的報表作業則自動導向次要複本。

建立Client Access Point

和純粹在Microsoft Azure環境下建置SQL Server AlwaysOn可用性群組時一樣，建立接聽程式不能在SSMS中進行，必須在Failover Cluster Manager的叢集角色上按滑鼠右鍵選擇【Add Resource>Client Access Point】。

在Client Access Point Name的部分，輸入將來您要當作AlwaysOn可用性群組接聽程式的名稱。

由於混合式架構下可用性複本會橫跨內部部署以及Microsoft Azure網段，因此您會看到多個IP Addrss。

接著按Finish來關閉New Resource Wizard視窗。

建立完成之後，您可以叢集資源中看到如下圖紅色框框處，已經建立兩個叢集IP Address，剛建立Client Access Point時，叢集IP Address會處於Offline的狀態，請跟著筆者繼續往下做。

於叢集IP Address: Address on Cluster Network1上按滑鼠右鍵選擇Properties，於IP Address屬性視窗將IP Address由預設的DHCP改為Static IP Address，輸入一個尚未使用的IP位址，然後按OK來關閉視窗。

另外，IP Address: Address on Cluster Network2是連接到Microsoft Azure Virtual Network的網段，一樣將IP Address由DHCP變更為Static IP Address，並指定一個尚未被使用的IP位址，然後按OK來關閉視窗。

將所有IP Address都設定好正確的IP位址後，就可以在Client Access Point項目上按滑鼠右鍵選擇Bring Online。

由於在Multi-Subnet Clusters環境下，同一時間只會有一個IP Address呈現於Online的狀態，以下圖為例，目前主要複本是內部部署的sql1en，因此內部網段的IP Address會是Online，若主要複本容錯移轉至Microsoft Azure上的sql3cloud，則另一個Azure網段的IP Address會由Offline變成Online。

最後，在叢集角色上按滑鼠右鍵選擇Properties來設定可用性群組（myagdemo）與Client Access Point（agl）的相依性。

於屬性視窗點選Dependencies，接著在Resource欄位中選擇前面所建立的Client Access Point，選擇完畢後按OK。

回到SSMS的Object Explorer視窗重新整理AlwaysOn High Availability節點，您就可以在Availability Group Listener節點中，看到您在Failover Cluster Manager所建立的Client Access Point，已經和可用性群組整合而成為可用性群組的接聽程式。

請於可用性群組接聽程式上按滑鼠右鍵選擇Properties，設定接聽程式所要接聽的連接埠，接著按OK來關閉屬性視窗。

方便起見，筆者使用SQLCMD公用程式來連接到接聽程式（agl），證明確實可以透過接聽程式連接到目前的主要複本（SQL1EN），並且能夠建立資料資料表及新增資料（如下圖）。

設定AlwaysOn可用性群組的唯讀路由

AlwaysOn可用性群組接聽程式建立完成後接下來您可以選擇性建立唯讀路由，來讓只需要讀取資料的應用程式或報表來自動導向次要複本，以分散工作負載到不同的可用性複本。作法如下：

一、設定可用性複本的Readable Secondary屬性為Read_Only

您可以以下列T-SQL來設定可用性複本的Readable Secondary屬性，將之設為Read_Only：


USE master
GO

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

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

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

或是在可用性群組的屬性視窗來變更Readable Secondary屬性。

二、設定唯讀路由網址

和純粹Microsoft Azure環境中建立唯讀路由不同的是，唯讀路由網址不是設定成接聽程式的FQDN，而是必須依照每一個可用性複本來個別做設定。您可以參考Calculating read_only_routing_url for AlwaysOn這篇文章，將文中的T-SQL指令碼分別在每一個可用性複本中執行，就可幫助您找到正確的唯讀路由網址，如下圖所示：


ALTER AVAILABILITY GROUP [MyAGDemo] 
MODIFY REPLICA ON N'SQL1EN' 
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'tcp://sql1en.網域名稱:1433'));
GO 

ALTER AVAILABILITY GROUP [MyAGDemo]
 MODIFY REPLICA ON N'SQL2EN' 
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'tcp://sql2en.網域名稱:1433'));
GO

ALTER AVAILABILITY GROUP [MyAGDemo] 
MODIFY REPLICA ON N'SQL3CLOUD' 
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'tcp://sql3cloud.網域名稱:1433'));
GO

三、設定唯讀路由清單

最後一樣是設定唯讀路由繞送的順序，例如下列的T-SQL指令碼：


ALTER AVAILABILITY GROUP [MyAGDemo] 
MODIFY REPLICA ON N'SQL1EN' 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQL3CLOUD','SQL2EN')));
GO

ALTER AVAILABILITY GROUP [MyAGDemo]
MODIFY REPLICA ON N'SQL2EN' 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQL3CLOUD','SQL1EN')));
GO 

ALTER AVAILABILITY GROUP [MyAGDemo]
MODIFY REPLICA ON N'SQL3CLOUD' 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQL1EN','SQL2EN')));
GO

完成上述設定唯讀路由的三個步驟之後，您就可以利用SQLCMD的-K Readonly參數來測試唯讀路由是否依照您所指定的內容來繞送僅限讀取意圖的連線。在步驟三筆者將唯讀路由清單設定為，當主要複本是SQL1EN時，唯讀路由順序第一個是SQL3CLOUD再來是SQL2EN，如下圖所示，證明確實接聽程式已經依照我們的設定來進行繞送。