在 SQL SERVER 的標準版，AlwaysOn 基本上只有支援二個節點，只有 SQL SERVER 企業版才有與作業系統的節點數量有相同的支援，而且標準版的第二個節點，無法設定為唯讀複本，所以如果要設定唯讀複本時，只能升級到企業版。
Editions and supported features of SQL Server 2019
https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-ver16
許多公司由於在標準版與企業版在價格上的差異，許多企業版的功能上並沒有使用到，所以仍會先採用 SQL SERVER 標準版進行，但問題來了，如果希望增加一個節點來進行唯讀分攤負載時，除了升級到企業版之外，本篇就來提供一個不同的方式給大家參考。
SQL Server 2022 pricing and licensing
https://www.microsoft.com/en-us/sql-server/sql-server-2022-pricing
本篇我會透過 SQL SERVER AlwasyOn 標準版與交易式複寫(Transaction Replication)來達到上述的需求，在這個架構下，重點就是當 AlwaysOn 進行角色轉移 (Failover) 後，如何讓原本的交易式複寫仍可以正常的運作，就是本篇介紹的重點。
在介紹之前，我還是要先說一下這個架構的缺點，大家可以再考慮後是否合適，再進行考慮。
- 無法透過 AlwaysOn Listener 進行讀寫/唯讀的路由。
- 唯讀複本由於是透過交易式複寫進行，所以該節點不是真的唯讀，當用戶端不小心將資料寫入至該節點的話，造成資料衝突時，只能重新進行交易式複寫的設定(打掉重作)。
- 如果在後續資料傳輸過慢的問題需進行排除時，由於多重的架構，所以比較不容易查出問題瓶頸，但大神們可以忽略此問題。
- 管理時，需同時確認 AlwaysOn 與交易式複寫的情況，無法單純的透過 AlwaysOn 的 Dashboard 進行確認。
環境說明：
- SQLAGNode1: Windows 2019 + SQL Server 2019 Standard
- SQLAGNode2: Windows 2019 + SQL Server 2019 Standard
- SQLRep1: Windows 2019 + SQL Server 2019 Standard
複寫角色對應：
- 散發 (Distribution): SQLRep1
- 發行 (Publishing): SQLAGNode1 + SQLAGNode2
- 訂閱 (Subscription): SQLRep1
開始安裝設定：
1. 一開始請先依照先前的文件，將 SQLAGNode1 + SQLAGNode2 設定安裝 SQL SERVER，並啟用 AlwaysOn 的功能，最後也建議進行 Failover 測試確認可以正常的完成節點的切換。
SQL Server 2012 新功能 - AlwaysOn安裝與設定
https://caryhsu.blogspot.com/2012/04/sql-server-2012-alwayson.html
2. 設定散發主機 (SQLRep1)
散發的部份一樣可以設定在不同的主機上，此部份是與訂閱者設定在同一台的機器上。
2-1 請將第三台主機(SQLRep1)安裝好 SQL SERVER 單機環境，而且安裝時需增加勾選 SQL Server Replication 的功能。
2-2 安裝完成後，加入同網域，不用加入叢集，也不用加入 AlwaysOn 中(也無法加入，因為 SQL Server 標準版的限制)。
2-3 設定散發初使設定。
2-4 由於二台發行的資料，都要透過散發來進行，所以在此主機上，要手動設定散發並指定二台發行可以透過此台主機進行散發。
3. 設定第一台發行集
這個需確認 AlwaysOn 的主要節點在那一台上，目前假設在 SQLAGNode1，所以我們就以此台進行。
3-1 新增發行集，基本上動作都相同，只是在散發主機的部份，必須指定到 SQLRep1。
3-2 第二台的發行集不用特別設定，當進行 Failover 時，你就會發現該發行會切換到第二台上。
4. 設定訂閱者 (SQLRep1)
這部份只需注意先指定到主要節點的部份，目前假設在 SQLAGNode1上。
5. 這時候已完成初步的可讀複本的部份，請先嘗試新增一筆資料至主要節點中，目前假設在 SQLAGNode1上，然後確認可以正確的將資料複寫至 SQLRep1 的節點中。
6. 設定重新導向發行端於散發主機上 (SQLRep1)
在散發主機上，執行下列的語法，主要就是將目前指定到第一台的發行，重新導向，指定到 SQL Server Listener上。
USE distribution;
GO
EXEC sp_redirect_publisher
@original_publisher = 'SQLAGSTD1',
@publisher_db = 'carytestdb',
@redirected_publisher = 'sqlstdaglst';
7. 測試 Failover 後，確認是否可以正常的透過複寫將資料寫到訂閱端。
此時，我們第二台其實都還沒有設定發行端，但其實你在主要節點進行 Failover 切換後，您就會發現訂閱就會顯示在第二台的節點上，所以您可以嘗試在第二台也新增資料，確認資料也是有複寫到訂閱端，這樣就都完成了。
最後補充，許多文章提到可以透過 sys.sp_validate_replica_hosts_as_publishers 進行驗證，但因為驗證時，會同時到主節點與備援節點進行確認，而由於 SQL SERVER 標準版 上的第二個節點是無法設定為可讀取的，所以如果有遇到下列的錯誤，其實是可以忽略的。
USE distribution;
GO
EXEC sys.sp_validate_replica_hosts_as_publishers
@original_publisher = 'SQLAGSTD1',
@publisher_db = 'carytestdb',
@redirected_publisher = 'sqlstdaglst';
錯誤訊息：
OLE DB provider "MSOLEDBSQL" for linked server "[F9C2EDD8-1050-488F-97D3-BD64AFE91CCA]" returned message "Deferred prepare could not be completed.".
Msg 21899, Level 11, State 1, Procedure sys.sp_hadr_verify_subscribers_at_publisher, Line 109 [Batch Start Line 2]
The query at the redirected publisher 'SQLAGSTD1' to determine whether there were sysserver entries for the subscribers of the original publisher 'SQLAGSTD1' failed with error '976', error message 'Error 976, Level 14, State 1, Message: The target database, 'carytestdb', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.'.
One or more publisher validation errors were encountered for replica host 'SQLAGSTD1'.
沒有留言:
張貼留言