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

發行項
12/14/2024

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

原始產品版本：SQL Server
原始 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
```
[!NOTES]
- -TimeSpan 15此步驟中的參數假設在前 15 分鐘內發生診斷出的問題。
- 根據預設，記錄檔會在 %WINDIR%\cluster\reports 中建立。
在 記事本中開啟Cluster.log 檔案，以檢閱 Windows 叢集記錄。
在 [記事本] 中，選取 [編輯>尋找]，然後在檔案結尾搜尋 “failoverCount” 字串。在結果中，您應該會找到類似下列訊息的訊息：

未故障轉移群組 <資源組名>、failoverCount 3、failoverThresholdSetting <Number>、computedFailoverThreshold 2

步驟 2：檢查指定期間屬性中的 [最大失敗數] 屬性

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

注意

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

結論

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

解決方法

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

注意

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

案例 2：NT Authority\SYSTEM 帳戶許可權不足

SQL Server 資料庫引擎資源 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 資料庫引擎資源 DLL 的健康情況。

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

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

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

若要調查並診斷這是失敗故障轉移的原因，請檢閱 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 狀態。如需可用性模式的詳細資訊，請參閱 AlwaysOn 可用性群組中的可用性模式。

案例 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 組態管理員的 [用戶端設定] 區段中啟用強制通訊協定加密。如需詳細資訊，請參閱啟用資料庫引擎的加密連線。

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

從主要復本故障轉移至次要復本之前，SQL Server 資料庫引擎資源 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	WorkerWaitingForCpu	RequestWaitingForThreads
2020-10-06 01:27:01.337	1,612	361	855	33	0
2020-10-06 01:27:08.340	1,612	1412	-196	22	76
2020-10-06 01:27:15.340	1,612	1,304	-88	2	161
2020-10-06 01:27:22.340	1,612	1242	26-	21	185
2020-10-06 01:27:29.343	1,612	13:46	-130	19	476
2020-10-06 01:27:36.350	1,612	1350	-134	9	630
2020-10-06 01:27:43.353	1,612	13:46	-130	13	5:39
2020-10-06 01:27:50.360	1,612	1378	-162	5	328
2020-10-06 01:27:57.360	1,612	197	1019	0	0

回報 WorkersWaitingForCpu 並 RequestWaitingForThreads 指出發生排程爭用的高值，且 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 Authority\SYSTEM 帳戶許可權不足。

共用方式為

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

摘要

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

如果自動故障轉移失敗，則徵兆

案例 1：「指定期間的最大失敗」值已用盡

步驟 1：檢閱 Windows 叢集記錄中的數據（Cluster.log）

步驟 2：檢查指定期間屬性中的 [最大失敗數] 屬性

案例 2：NT Authority\SYSTEM 帳戶許可權不足

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

案例 4：針對次要複本（目標主要複本）上的用戶端通訊協定選取 [強制通訊協定加密] 組態，但未針對加密設定複本

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

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

範例集 1

範例集 2

範例集 3

意見反應

其他資源

共用方式為

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

摘要

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

如果自動故障轉移失敗，則徵兆

案例 1：「指定期間的最大失敗」值已用盡

步驟 1：檢閱 Windows 叢集記錄中的數據 （Cluster.log）

步驟 2：檢查指定期間屬性中的 [最大失敗數] 屬性

案例 2：NT Authority\SYSTEM 帳戶許可權不足

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

案例 4：針對次要複本（目標主要複本）上的用戶端通訊協定選取 [強制通訊協定加密] 組態，但未針對加密設定複本

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

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

範例集 1

範例集 2

範例集 3

意見反應

其他資源

步驟 1：檢閱 Windows 叢集記錄中的數據（Cluster.log）