在 上的SQL伺服器中的 Always On 可用性群組中設定唯讀路由 AWS

由 Subhani Shaik 建立 （AWS）

環境：PoC 或試行	技術：資料庫；基礎設施	工作負載：Microsoft
AWS 服務：AWSManaged Microsoft AD；Amazon EC2

Summary

此模式涵蓋如何在 SQL Server Always On 中使用待命次要複本，方法是將唯讀工作負載從主要複本卸載至次要複本。

資料庫鏡像具有 one-to-one映射。您無法直接讀取次要資料庫，因此您必須建立快照。已在 Microsoft SQL Server 2012 中推出 Always On 可用性群組功能。在更新版本中，已引入主要功能，包括唯讀路由。在 Always On 可用性群組中，您可以將複本模式變更為唯讀，以直接從次要複本讀取資料。

Always On 可用性群組解決方案支援高可用性 （HA）、災難復原 （DR），以及資料庫鏡像的替代方案。Always On 可用性群組可在資料庫層級運作，並將一組使用者資料庫的可用性最大化。

SQL 伺服器使用唯讀路由機制，將傳入的唯讀連線重新導向至次要僅供讀取複本。若要達成此目標，您應該在連線字串中新增下列參數和值：

• ApplicationIntent=ReadOnly

• Initial Catalog=<database name>

先決條件和限制

先決條件

• 具有虛擬私有雲端 （VPC）、兩個可用區域、私有子網路和安全群組的作用中AWS帳戶

• 兩台 Amazon Elastic Compute Cloud （Amazon EC2） 機器，其具有在執行個體層級設定的 SQL Server 2019 Enterprise Edition Amazon Machine Image with Windows Server 容錯移轉叢集 （WSFC），以及一個在伺服器SQL層級設定的 Always On 可用性群組，其位於主節點 （WSFCNODE1） 和次要節點 （WSFCNODE2） 之間，這是名為 Microsoft Active Directory 目錄的 AWS Directory Service for Microsoft Active Directory 目錄的一部分 tagechtalk.com

• 在次要複本read-only中設定為接受的一或多個節點

• SQLAG1 名為 Always On 可用性群組的接聽程式

• SQL 在兩個節點上執行相同服務帳戶的伺服器資料庫引擎

• SQL Server Management Studio （SSMS）

• 名為 的測試資料庫 test

產品版本

• SQL Server 2014 及更新版本

架構

目標技術堆疊

• Amazon EC2

• AWS Managed Microsoft AD

• Amazon FSx

目標架構

下圖顯示 Always On 可用性群組 （AG） 接聽程式如何將包含 ApplicationIntent 參數的查詢重新導向至適當的次要節點。

1. 請求會傳送至 Always On 可用性群組接聽程式。

2. 如果連線字串沒有 ApplicationIntent 參數，則請求會傳送至主要執行個體。

3. 如果連線字串包含 ApplicationIntent=ReadOnly，則請求會傳送至具有唯讀路由組態 的次要執行個體，該組態WSFC具有 Always On 可用性群組。

工具

AWS 服務

• AWS Directory Service for Microsoft Active Directory 可讓您的目錄感知工作負載AWS和資源在 AWS Cloud 中使用 Microsoft Active Directory。

• Amazon Elastic Compute Cloud （Amazon EC2） 在AWS雲端中提供可擴展的運算容量。您可以視需要啟動任意數量的虛擬伺服器，，並快速進行擴展或縮減。

• Amazon FSx 提供支援業界標準連線通訊協定的檔案系統，並提供跨AWS區域的高可用性和複寫功能。

其他服務

• SQL Server Management Studio （SSMS） 是用於連線、管理和管理SQL伺服器執行個體的工具。

• sqlcmd 是命令列公用程式。

最佳實務

如需 Always On 可用性群組的詳細資訊，請參閱SQL伺服器文件 。

史詩

設定唯讀路由

任務	描述	所需的技能
將複本更新為唯讀。	若要同時將主要複本和次要複本更新為唯讀，請從 連線至主要複本SSMS，然後從其他資訊區段執行步驟 1 程式碼。	DBA
建立路由 URL。	若要URL為兩個複本建立路由，請從其他資訊區段執行步驟 2 程式碼。在此程式碼中， tagechtalk.com是 AWS Managed Microsoft AD 目錄的名稱。	DBA
建立路由清單。	若要建立兩個複本的路由清單，請從其他資訊區段執行步驟 3 程式碼。	DBA
驗證路由清單。	從 SQL Server Management Studio 連線至主要執行個體，然後從其他資訊區段執行步驟 4 程式碼，以驗證路由清單。	DBA

測試唯讀路由

任務	描述	所需的技能
使用 ApplicationIntent 參數連線。	從 SSMS，使用 連線至 Always On 可用性群組接聽程式名稱ApplicationIntent=ReadOnly;Initial Catalog=test。 使用次要複本建立連線。若要測試此項目，請執行下列命令以顯示連線的伺服器名稱。 SELECT SERVERPROPERTY('ComputernamePhysicalNetBios') 輸出將顯示目前的次要複本名稱 （WSFCNODE2）。	DBA
執行容錯移轉。	從 SSMS連線至 Always On 可用性群組接聽程式名稱。 確認主要和次要資料庫同步，沒有資料遺失。 執行容錯移轉，讓目前的主要複本成為次要複本，而次要複本則成為主要複本。 從 SSMS，使用 連線至 Always On 可用性群組接聽程式名稱ApplicationIntent=ReadOnly;Initial Catalog=test。 使用次要複本建立連線。若要測試此項目，請執行下列命令來顯示連線的伺服器名稱。 SELECT SERVERPROPERTY('ComputernamePhysicalNetBios') 它會顯示目前的次要複本名稱 （WSFCNODE1）。	DBA

使用 sqlcmd 命令列公用程式連線

任務	描述	所需的技能
使用 sqlcmd 連線。	若要從 sqlcmd 連線，請從命令提示字元的其他資訊區段執行步驟 5 程式碼。連線後，請執行下列命令以顯示連線的伺服器名稱。 SELECT SERVERPROPERTY('ComputernamePhysicalNetBios') . 輸出會顯示目前的次要複本名稱 （WSFCNODE1）。	DBA

故障診斷

問題	解決方案
建立接聽程式失敗，訊息為「WSFC叢集無法讓 Network Name 資源上線」。	如需詳細資訊，請參閱 Microsoft 部落格文章 Create Listener Fails with Message 'WSFC叢集無法讓 Network Name 資源上線'。
潛在問題，包括其他接聽程式問題或網路存取問題。	請參閱 Microsoft 文件中的 Always On 可用群組組態 （SQL 伺服器） 疑難排解。

相關資源

• 設定 Always On 可用性群組的唯讀路由

• 對 Always On 可用群組組態 （SQL 伺服器） 進行故障診斷

其他資訊

步驟 1. 將複本更新為唯讀

ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))
GO
ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))
GO

步驟 2. 建立路由 URL

ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://WSFCNode1.tagechtalk.com:1433'))
GO
ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://WSFCNode2.tagechtalk.com:1433'))
GO

步驟 3. 建立路由清單

ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE1' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=('WSFCNODE2','WSFCNODE1')));
GO
ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('WSFCNODE1','WSFCNODE2')));
GO

步驟 4. 驗證路由清單

SELECT AGSrc.replica_server_name AS PrimaryReplica, AGRepl.replica_server_name AS ReadOnlyReplica, AGRepl.read_only_routing_url AS RoutingURL , AGRL.routing_priority AS RoutingPriority FROM sys.availability_read_only_routing_lists AGRL INNER JOIN sys.availability_replicas AGSrc ON AGRL.replica_id = AGSrc.replica_id INNER JOIN sys.availability_replicas AGRepl ON AGRL.read_only_replica_id = AGRepl.replica_id INNER JOIN sys.availability_groups AV ON AV.group_id = AGSrc.group_id ORDER BY PrimaryReplica

步驟 5. SQL 命令公用程式

sqlcmd -S SQLAG1,1433 -E -d test -K ReadOnly