針對 SQL Server Always On 環境中的自動故障轉移問題進行疑難解答

發行項
03/20/2024

本文可協助您解決在 Microsoft SQL Server 中自動故障轉移期間發生的問題。

原始產品版本： SQL S
原始 KB 編號： 2833707

摘要

SQL Server Always On 可用性群組可以設定為自動故障轉移。如果在裝載主要複本的 SQL Server 實例上偵測到健康情況問題，則主要角色可以轉換為自動故障轉移夥伴， (次要複本) 。不過，次要復本不一定會轉換為主要角色。在某些情況下，它只能轉換為 RESOLVING 角色。在此情況下，除非主要複本回到狀況良好的狀態，否則沒有任何複本具有主要角色。此外，可用性資料庫將無法存取。

本文列出自動故障轉移失敗失敗的一些常見原因，並討論您可以採取的步驟來診斷這些失敗的原因。

成功觸發自動故障轉移時的徵兆

在裝載主要復本的 SQL Server 實例上觸發自動故障轉移時，次要復本會轉換為 RESOLVING 角色，然後轉換為主要角色。雖然程式成功，但錯誤項目會記錄在 SQL Server 記錄報表中，如下列文字所示：

The state of the local availability replica in availability group '\<Group name>' has changed from 'RESOLVING_NORMAL' to 'PRIMARY_PENDING'  
The state of the local availability replica in availability group '\<Group name>' has changed from 'PRIMARY_PENDING' to 'PRIMARY_NORMAL'

成功觸發自動故障轉移時的錯誤記錄檔螢幕快照。

注意事項

次要複本已成功從 RESOLVING_NORMAL 狀態轉換為 PRIMARY_NORMAL 狀態。

自動故障轉移失敗時的徵兆

如果自動故障轉移事件未成功，次要複本不會成功轉換為主要角色。因此，可用性複本會報告此複本處於 RESOLVING 狀態。此外，可用性資料庫會報告它們處於 NOT SYNCHRONIZING 狀態，而且應用程式無法存取這些資料庫。

例如，在下圖中，SQL Server Management Studio 報告次要複本處於RESOLVING狀態，因為自動故障轉移程式無法將次要複本轉換成主要角色。

SQL Server Management Studio 中可用性復本的螢幕快照。

下列各節討論自動故障轉移可能無法成功的幾個可能原因，以及如何診斷每個原因。

案例 1：「指定期間內的失敗數目上限」值已用盡

可用性群組具有 Windows 叢集資源屬性，例如 [ 指定期間] 屬性中的 [失敗次數上限] 。當發生多個節點失敗時，這個屬性可用來避免叢集資源的無限期移動。

若要調查並診斷這是否為故障轉移失敗的原因，請檢閱 Windows 叢集記錄檔 (Cluster.log) ，然後檢查屬性。

步驟 1：檢閱 Windows 叢集記錄檔中的數據 (Cluster.log)

使用 Windows PowerShell 在裝載主要複本的叢集節點上產生 Windows 叢集記錄。若要這樣做，請在裝載主要複本的 SQL Server 實例上，於提升許可權的 PowerShell 視窗中執行下列 Cmdlet：
```
Get-ClusterLog -Node <SQL Server node name> -TimeSpan 15
```
[!附注]
- -TimeSpan 15此步驟中的參數假設在前 15 分鐘內發生診斷的問題。
- 根據預設，記錄檔會建立在 %WINDIR%\cluster\reports 中。
在記事本中開 啟Cluster.log 檔案，以檢閱 Windows 叢集記錄檔。
在記事本中，選取 [編輯>尋找]，然後在檔案結尾搜尋 “failoverCount” 字串。在結果中，您應該會發現類似下列訊息的訊息：

Not fail over group <Resource name>， failoverCount 3， failoverThresholdSetting <Number>， computedFailoverThreshold 2

步驟 2：檢查指定期間屬性中的失敗次數上限

啟動故障轉移叢集管理員。
在瀏覽窗格中，選取 [ 角色]。
在 [ 角色] 窗格中，以滑鼠右鍵按兩下叢集資源，然後選取 [ 屬性]。
選取 [ 故障轉移] 索引 標籤，然後選取 [ 指定期間] 值中的 [失敗上限 ]。

注意事項

默認行為會指定如果叢集資源在六小時內失敗三次，它應該會維持在失敗狀態。針對可用性群組，這表示複本會保持在 RESOLVING 狀態。

總結

分析記錄之後，您會發現 failoverCount 值 3 大於 2 的 computedFailoverThreshold 值。因此，Windows 叢集無法將可用性群組資源的故障轉移作業完成至故障轉移夥伴。

解決方案

若要解決此問題，請在 [指定的期間] 值中增加 [失敗次數上限 ]。

注意事項

增加此值可能無法解決問題。可能有更嚴重的問題，導致可用性群組在短時間內失敗多次。根據預設，此期間為15分鐘。增加此值可能只會導致可用性群組失敗更多次，並維持在失敗狀態。建議您使用積極式疑難解答來判斷自動故障轉移持續發生的原因。

案例 2：NT 授權單位\SYSTEM 帳戶許可權不足

SQL Server Database Engine 資源 DLL 會使用 ODBC 監視健康情況，連線到裝載主要複本的 SQL Server 實例。用於此連線的登入認證是本機 SQL Server NT AUTHORITY\SYSTEM 登入帳戶。根據預設，此本機登入帳戶會被授與下列許可權：

改變任何可用性群組
連線 SQL
檢視伺服器狀態

NT AUTHORITY\SYSTEM如果登入帳戶在次要複本) (自動故障轉移夥伴上缺少任何這些許可權，則 SQL Server 無法在發生自動故障轉移時啟動健康情況偵測。因此，次要複本無法轉換為主要角色。若要調查並診斷這是否為原因，請檢閱 Windows 叢集記錄檔。如果要執行這項操作，請依照下列步驟執行：

使用 Windows PowerShell 在叢集節點上產生 Windows 叢集記錄檔。若要這樣做，請在裝載未轉換為主要角色之次要複本的 SQL Server 實例上，於提升許可權的 PowerShell 視窗中執行下列 Cmdlet：
```
Get-ClusterLog -Node <SQL Server node name> -TimeSpan 15
```
在記事本中開 啟Cluster.log 檔案，以檢閱 Windows 叢集記錄檔。
尋找類似下列文字的錯誤專案：

無法執行診斷命令。用戶沒有執行此動作的許可權。

總結

Cluster.log檔案會報告當 SQL Server 執行診斷命令時，許可權問題存在。在此範例中，失敗是因為從NT AUTHORITY\SYSTEM裝載自動故障轉移配對次要複本之 SQL Server 實例上的登入帳戶移除檢視伺服器狀態許可權所造成。

解決方案

若要解決此問題，請將足夠的許可權NT AUTHORITY\SYSTEM授與登入帳戶，以偵測 SQL Server Database Engine 資源 DLL 的健康情況。

案例 3：可用性資料庫未處於 SYNCHRONIZED 狀態

若要自動故障轉移，可用性群組中定義的所有可用性資料庫都必須處於 SYNCHRONIZED 主要複本與次要複本之間的狀態。發生自動故障轉移時，必須符合此同步處理條件，以確保不會遺失任何數據。因此，如果可用性群組中的一個可用性資料庫處於同步或 NOT SYNCHRONIZED 狀態，自動故障轉移將無法成功將次要複本轉換成主要角色。

如需自動故障轉移所需條件的詳細資訊，請參閱自動故障轉移所需的條件，以及支援故障轉移和故障轉移模式的兩個設定區段的同步認可複本 (Always On 可用性群組) 。

若要調查並診斷這是否為故障轉移失敗的原因，請檢閱 SQL Server 錯誤記錄檔。您應該發現類似下列文字的錯誤項目：

一或多個資料庫未同步處理，或尚未加入可用性群組。

案例 3 中 SQL Server 錯誤記錄檔的螢幕快照。

若要檢查可用性資料庫是否處於 SYNCHRONIZED 狀態，請遵循下列步驟：

聯機到次要複本。
執行下列 SQL 腳本來檢查 is_failover_ready 可用性群組中所有未故障轉移之可用性資料庫的值。

注意事項

任何可用性資料庫的值為零可防止自動故障轉移。這個值表示可用性資料庫不是 SYNCHRONIZED。
```
SELECT database_name, is_failover_ready FROM sys.dm_hadr_database_replica_cluster_states WHERE replica_id IN (SELECT replica_id FROM sys.dm_hadr_availability_replica_states)
```

總結

可用性群組的成功自動故障轉移需要所有可用性資料庫都處於 SYNCHRONIZED 狀態。如需可用性模式的詳細資訊，請參閱 Always On 可用性群組中的可用性模式。

案例 4：已針對次要複本上的用戶端通訊協定選取 [強制通訊協定加密] 設定， (目標主要) 但未設定複本進行加密

在故障轉移期間，當主伺服器偵測到故障轉移夥伴上的叢集 DLL 健康情況問題時， (次要複本) 嘗試連線到本機複本以起始健康情況監視。這是轉換為主要角色的一部分。如果未設定次要複本進行加密，但用戶端組態中不小心設定了 [強制通訊協定加密 ] 設定，則連線將會失敗，而且無法進行故障轉移。

若要檢查此設定：

啟動 SQL Server 組態管理員。
在左窗格中，以滑鼠右鍵按兩下 [SQL Native Client 11.0 組態]，然後選取 [ 屬性]。
在對話框中，核取 [強制通訊協定加密 ] 設定。如果設定為 [ 是]，請將值變更為 [否]。
重新測試故障轉移。

SQL Server 組態管理員中 SQL Native Client 11.0 組態屬性的螢幕快照。

總結

SQL Server Always On 健康情況監視會使用本機 ODBC 連線來監視 SQL Server 健康情況。只有在 [SQL Server 網络組態] 區段中 SQL Server 本身設定為 [在 SQL Server 網络組態] 區段 SQL Server 組態管理員中強制加密時，才應在SQL Server 組態管理員 的 [客戶端設定] 區段中啟用強制通訊協定加密。如需詳細資訊，請參閱啟用 Database Engine 的加密連線。

案例 5：次要複本或節點的效能問題導致 Always On 健康情況檢查失敗

從主要復本故障轉移至次要復本之前，SQL Server Database Engine 資源 DLL 會連線到次要複本，以確定複本的健康情況。如果此連線因為次要複本上的效能問題而失敗，則不會發生自動故障轉移。

若要調查並診斷這是否為原因，請遵循下列步驟：

檢閱次要複本上的叢集記錄，以檢查是否有錯誤訊息「因為開啟伺服器連線延遲而無法完成登入程式」。

0000110c.00002bcc::2020/08/06-01:17:54.943 INFO  [RCM] move of group AOCProd01AG from CO2ICMV3SQL09(1) to CO2ICMV3SQL10(2) of type MoveType::Manual is about to succeed, failoverCount=3, lastFailoverTime=2020/08/05-02:08:54.524 targeted=true 
00002a54.0000610c::2020/08/06-01:18:44.929 ERR   [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]Unable to complete login process due to delay in opening server connection (0) 
00002a54.0000610c::2020/08/06-01:18:44.929 INFO  [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] Could not connect to SQL Server (rc -1) 
00002a54.0000610c::2020/08/06-01:18:44.929 INFO  [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] SQLDisconnect returns following information 
00002a54.0000610c::2020/08/06-01:18:44.929 ERR   [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0) 
00002a54.0000610c::2020/08/06-01:18:44.931 ERR   [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] Failed to connect to SQL Server 
00002a54.0000610c::2020/08/06-01:18:44.931 ERR   [RHS] Online for resource AOCProd01AG failed.

如果故障轉移至具有忙碌現有工作負載的 SQL Server 次要複本，就會發生這種情況。這可能會延遲 SQL Server 對 HADR 健康情況連線要求嘗試的回應，並防止故障轉移嘗試成功。

若要判斷系統排程器是否有壓力，請使用 SQL Server Management Studio 在次要複本上執行下列腳本：

USE MASTER 
GO  
WHILE 1=1 
BEGIN 
PRINT convert(varchar(20), getdate(),120) 
DECLARE @max INT; 
SELECT @max = max_workers_count 
FROM sys.dm_os_sys_info; 
SELECT GETDATE() AS 'CurrentDate',  
       @max AS 'TotalThreads',  
       SUM(active_Workers_count) AS 'CurrentThreads',  
       @max - SUM(active_Workers_count) AS 'AvailableThreads',  
       SUM(runnable_tasks_count) AS 'WorkersWaitingForCpu',  
       SUM(work_queue_count) AS 'RequestWaitingForThreads' 
       --SUM(current_workers_count) AS 'AssociatedWorkers' 
FROM sys.dm_os_Schedulers 
WHERE STATUS = 'VISIBLE ONLINE'; 
wait for delay '0:0:15' 
END

以下是上述查詢的範例輸出：

CurrentDate	TotalThreads	CurrentThreads	AvailableThreads	WorkersWaitingForCpu	RequestWaitingForThreads
2020-10-06 01:27:01.337	1216	361	855	33	0
2020-10-06 01:27:08.340	1216	1412	-196	22	76
2020-10-06 01:27:15.340	1216	1304	-88	2	161
2020-10-06 01:27:22.340	1216	1242	-26	21	185
2020-10-06 01:27:29.343	1216	1346	-130	19	476
2020-10-06 01:27:36.350	1216	1350	-134	9	630
2020-10-06 01:27:43.353	1216	1346	-130	13	539
2020-10-06 01:27:50.360	1216	1378	-162	5	328
2020-10-06 01:27:57.360	1216	197	1019	0	0

針對回報的高WorkersWaitingForCpuRequestWaitingForThreads值，表示發生排程爭用，且 SQL Server 無法及時為目前的工作負載提供服務。

解決方案

如果您遇到這個問題，請重新平衡次要復本上的工作負載，或考慮增加處理能力 (在執行這些工作負載的計算機上新增處理器) 。

針對其他故障轉移事件進行疑難解答

若要在故障轉移期間監視新主要復本的健康情況，您必須在本機將 AlwaysOn 健全狀況監視連線到轉換為主要角色的 SQL Server 實例。

除了本文中討論的較常見原因之外，還有許多其他原因表示此連線嘗試可能會失敗。若要進一步調查失敗的故障轉移嘗試，請檢閱故障轉移夥伴上的叢集記錄 (您無法故障轉移至) 的複本：

使用 Windows PowerShell 在叢集節點上產生 Windows 叢集記錄。若要這樣做，請在裝載未轉換為主要角色之次要複本的 SQL Server 實例上，於提升許可權的 PowerShell 視窗中執行下列 Cmdlet。系統會在活動的最後 60 分鐘產生叢集記錄。
```
Get-ClusterLog -Node <SQLServerNodeName> -TimeSpan 60
```
若要檢閱 Windows 叢集記錄檔，請在記事本中開 啟Cluster.log 檔案。
搜尋失敗故障轉移事件期間所落在的「連線到 SQL Server」字串。
使用線程標識子檢閱後續的登入訊息 (請參閱下列螢幕快照) 將與登入事件相關的事件相互關聯。下列範例顯示「連線到 SQL Server」搜尋。它也會顯示使用線程標識碼 (左側) 來找出描述連線嘗試失敗原因的其他診斷。

下列範例顯示新主要複本的連線失敗。

範例集 1

[hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]SQL Server Network
Interfaces: No client protocols are enabled and no protocol was specified in the connection
string [xFFFFFFFF]. (268435455)

解決方案

啟動 SQL Server 組態管理員，然後確認已在 SQL Native Client 組態的用戶端通訊協定下啟用共用記憶體或 TCP/IP。

範例集 2

[hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]SQL Server Network
Interfaces: Server doesn't support requested protocol [xFFFFFFFF]. (268435455)

解決方案

啟動 SQL Server 組態管理員，然後確認已在 SQL Native Client 組態的用戶端通訊協定下啟用共用記憶體或 TCP/IP。

範例集 3

000010b8.00001764::2020/12/02-16:52:49.808 ERR [RES] SQL Server Availability Group : [hadrag]
ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot alter the availability
group 'ag', because it does not exist or you do not have permission. (15151)
000010b8.00000fd0::2020/12/02-17:01:14.821 ERR [RES] SQL Server Availability Group: [hadrag]
ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The user does not have permission to perform this action. (297)
000010b8.00001838::2020/12/02-17:10:04.427 ERR [RES] SQL Server Availability Group : [hadrag]
ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user
'SQLREPRO\NODE2$'. Reason: The account is disabled. (18470)

解決方案

檢閱案例 2：NT 授權單位\SYSTEM 帳戶許可權不足。

針對 SQL Server Always On 環境中的自動故障轉移問題進行疑難解答

摘要

成功觸發自動故障轉移時的徵兆

自動故障轉移失敗時的徵兆

案例 1：「指定期間內的失敗數目上限」值已用盡

步驟 1：檢閱 Windows 叢集記錄檔中的數據 (Cluster.log)

步驟 2：檢查指定期間屬性中的失敗次數上限

案例 2：NT 授權單位\SYSTEM 帳戶許可權不足

案例 3：可用性資料庫未處於 SYNCHRONIZED 狀態

案例 4：已針對次要複本上的用戶端通訊協定選取 [強制通訊協定加密] 設定， (目標主要) 但未設定複本進行加密

案例 5：次要複本或節點的效能問題導致 Always On 健康情況檢查失敗

針對其他故障轉移事件進行疑難解答

範例集 1

範例集 2

範例集 3

意見反應

意見反應

其他資源