SQL Server Always On環境での自動フェールオーバーの問題のトラブルシューティング

[アーティクル]
03/24/2023

この記事は、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 、状態がレポートされ、アプリケーションはこれらのデータベースにアクセスできません。

たとえば、次の図では、自動フェールオーバープロセスでセカンダリレプリカをプライマリロールにRESOLVING移行できなかったため、セカンダリレプリカの状態が報告SQL Server Management Studio。

SQL Server Management Studioの可用性レプリカのスクリーンショット。

次のセクションでは、自動フェールオーバーが成功しない可能性があるいくつかの理由と、それぞれの原因を診断する方法について説明します。

ケース 1: "指定した期間内の最大エラー数" の値が使い果たされました

可用性グループには、Windows クラスターリソースのプロパティがあります。たとえば、 指定した期間の最大エラー プロパティです。このプロパティは、複数のノード障害が発生した場合のクラスター化リソースの不確定な移動を回避するために使用されます。

これがフェールオーバーの失敗の原因であるかどうかを調査して診断するには、Windows クラスターログ (Cluster.log) を確認し、プロパティをチェックします。

手順 1: Windows クラスターログのデータを確認する (Cluster.log)

Windows PowerShellを使用して、プライマリレプリカをホストしているクラスターノードで Windows クラスターログを生成します。これを行うには、プライマリレプリカをホストしているSQL Serverのインスタンスの管理者特権の PowerShell ウィンドウで次のコマンドレットを実行します。
```
Get-ClusterLog -Node <SQL Server node name> -TimeSpan 15
```
[!注]
- この手順のパラメーターは -TimeSpan 15 、診断中の問題が過去 15 分間に発生したことを前提としています。
- 既定では、ログファイルは %WINDIR%\cluster\reports に作成されます。
メモ帳で Cluster.log ファイルを開き、Windows クラスターログを確認します。
メモ帳で [検索の編集>] を選択し、ファイルの末尾にある "failoverCount" 文字列を検索します。結果には、次のようなメッセージが表示されます。

グループ <リソース名>、failoverCount 3、failoverThresholdSetting <Number>、computedFailoverThreshold 2 をフェールオーバーしない

手順 2: [指定した期間] プロパティで最大エラー数を確認する

フェールオーバークラスターマネージャーを起動します。
ナビゲーションウィンドウで、[ロール] を選択 します。
[ ロール ] ウィンドウで、クラスター化されたリソースを右クリックし、[プロパティ] を選択 します。
[ フェールオーバー ] タブを選択し、[ 指定された期間] の値で [最大エラー数] を 選択します。

注:

既定の動作では、クラスター化されたリソースが 6 時間以内に 3 回失敗した場合、失敗した状態のままであることを指定します。可用性グループの場合は、レプリカが状態のままであることを意味します RESOLVING 。

まとめ

ログを分析すると、 failoverCount 値 3 が computedFailoverThreshold 値 2 より大きいことがわかります。そのため、Windows クラスターは、フェールオーバーパートナーへの可用性グループリソースのフェールオーバー操作を完了できません。

Resolution

この問題を解決するには、[ 指定した期間] の [最大エラー数] の値を増やします。

注:

この値を増やすと、問題が解決しない可能性があります。可用性グループが短時間で何度も失敗する重大な問題が発生する可能性があります。既定では、この期間は 15 分です。この値を大きくすると、可用性グループが失敗する回数が増え、失敗した状態のままになる可能性があります。自動フェールオーバーが発生し続ける理由を判断するには、積極的なトラブルシューティングを使用することをお勧めします。

ケース 2: NT 機関\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 ウィンドウで次のコマンドレットを実行します。
```
Get-ClusterLog -Node <SQL Server node name> -TimeSpan 15
```
メモ帳で Cluster.log ファイルを開き、Windows クラスターログを確認します。
次のテキストのようなエラーエントリを見つけます。

コマンド診断実行できませんでした。ユーザーには、このアクションを実行するアクセス許可がありません。

まとめ

Cluster.log ファイルは、診断コマンドの実行時にアクセス許可の問題SQL Server報告します。この例では、自動フェールオーバーペアのセカンダリレプリカをホストしているSQL Serverのインスタンスで、ログインアカウントからNT AUTHORITY\SYSTEMサーバー状態の表示アクセス許可を削除することでエラーが発生しました。

解決策

この問題を解決するには、SQL Server データベースエンジンリソース DLL の正常性検出に十分なアクセス許可NT AUTHORITY\SYSTEMをログインアカウントに付与します。

ケース 3: 可用性データベースが SYNCHRONIZED 状態ではない

自動的にフェールオーバーするには、可用性グループで定義されているすべての可用性データベースが、プライマリレプリカとセカンダリレプリカの間の状態である SYNCHRONIZED 必要があります。自動フェールオーバーが発生した場合は、データ損失が発生しないように、この同期条件を満たす必要があります。そのため、可用性グループ内の 1 つの可用性データベースが同期または NOT SYNCHRONIZED 状態にある場合、自動フェールオーバーはセカンダリレプリカをプライマリロールに正常に移行しません。

自動フェールオーバーに必要な条件の詳細については、「自動フェールオーバーに必要な条件」を参照してください。同期コミットレプリカでは、フェールオーバーモードとフェールオーバーモード (Always On可用性グループ) の 2 つの設定セクションがサポートされています。

これがフェールオーバーの失敗の原因であるかどうかを調査して診断するには、SQL Serverエラーログを確認します。次のテキストのようなエラーエントリが表示されます。

1 つ以上のデータベースが同期されていないか、可用性グループに参加していません。

ケース 3 のSQL Server エラーログのスクリーンショット。

可用性データベースが状態であるかどうかをチェックするには、次の手順にSYNCHRONIZED従います。

セカンダリレプリカに接続します。
次の SQL スクリプトを実行して、is_failover_readyフェールオーバーされなかった可用性グループ内のすべての可用性データベースの値をチェックします。

注:

可用性データベースの値が 0 の場合、自動フェールオーバーを防ぐことができます。この値は、可用性データベースがでなかった 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: レプリカが暗号化用に構成されていないのに、セカンダリレプリカ (ターゲットプライマリ) 上のクライアントプロトコルに対して "Force Protocol Encryption" 構成が選択されている

フェールオーバー中に、プライマリサーバーが正常性の問題を検出すると、フェールオーバーパートナー (セカンダリレプリカ) 上のクラスター DLL がローカルレプリカへの接続を試み、正常性の監視を開始します。これは、プライマリロールへの移行の一部です。セカンダリレプリカが暗号化用に構成されていないが、クライアント構成で [プロトコル暗号化の強制 ] 設定が誤って設定されている場合、接続は失敗し、フェールオーバーは発生しません。

この構成をチェックするには:

SQL Server 構成マネージャーを起動します。
左側のウィンドウで、[SQL Native Client 11.0 Configuration]\(SQL Native Client 11.0 構成\) を右クリックし、[プロパティ] を選択します。
ダイアログボックスで、[プロトコル暗号化の強制] 設定をチェックします。 [ はい] に設定されている場合は、値を [いいえ] に変更します。
フェールオーバーを再テストします。

SQL Server 構成マネージャーの SQL Native Client 11.0 Configuration プロパティのスクリーンショット。

まとめ

SQL Server Always On正常性の監視では、ローカル ODBC 接続を使用してSQL Server正常性を監視します。 SQL Server 構成マネージャーの [クライアント構成] セクションで [プロトコル暗号化の強制] を有効にする必要があるのは、[SQL Server ネットワーク構成] セクションのSQL Server 構成マネージャーでSQL Server自体が強制的に暗号化されるように構成されている場合のみです。詳細については、「データベースエンジンへの暗号化された接続を有効にする」を参照してください。

ケース 5: セカンダリレプリカまたはノードのパフォーマンスの問題により、Always On正常性チェックが失敗する

プライマリレプリカからセカンダリレプリカにフェールオーバーする前に、データベースエンジンリソース DLL SQL Serverセカンダリレプリカに接続して、レプリカの正常性を確認します。セカンダリレプリカのパフォーマンスの問題が原因でこの接続が失敗した場合、自動フェールオーバーは発生しません。

これが原因であるかどうかを調査して診断するには、次の手順に従います。

セカンダリレプリカのクラスターログを確認して、"サーバー接続の開き方が遅れ、ログインプロセスを完了できません" というエラーメッセージをチェックします。

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セカンダリレプリカへのフェールオーバーが行われる場合に発生する可能性があります。これにより、HADR 正常性接続要求の試行に対するSQL Serverの応答が遅延し、フェールオーバー試行の成功が妨げる可能性があります。

システムスケジューラに負荷がかかっているかどうかを判断するには、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

に対してWorkersWaitingForCpu報告されたRequestWaitingForThreads値が高く、スケジュールの競合が発生しており、SQL Serverが現在のワークロードにタイムリーにサービスを提供できないことを示します。

解決策

この問題が発生した場合は、セカンダリレプリカのワークロードを再調整するか、これらのワークロードを実行しているコンピューターで処理能力を増やす (プロセッサを追加する) 検討してください。

その他の失敗したフェールオーバーイベントのトラブルシューティング

フェールオーバー中に新しいプライマリレプリカの正常性を監視するには、プライマリロールに移行しているSQL Server インスタンスに AlwaysOn 正常性監視をローカルに接続する必要があります。

この記事で説明するより一般的な理由に加えて、この接続試行が失敗する理由は他にも多数あります。フェールオーバー試行の失敗をさらに調査するには、フェールオーバーパートナー (フェールオーバーできなかったレプリカ) のクラスターログを確認します。

Windows PowerShellを使用して、クラスターノードで Windows クラスターログを生成します。これを行うには、プライマリロールに移行しなかったセカンダリレプリカをホストしているSQL Serverのインスタンスで、管理者特権の PowerShell ウィンドウで次のコマンドレットを実行します。クラスターログは、過去 60 分間のアクティビティで生成されます。
```
Get-ClusterLog -Node <SQLServerNodeName> -TimeSpan 60
```
Windows クラスターログを確認するには、メモ帳で Cluster.log ファイルを開きます。
失敗したフェールオーバーイベント中に該当する "connect to SQL Server" 文字列を検索します。
スレッド ID を使用して後続のログインメッセージを確認し (次のスクリーンショットを参照)、ログインイベントに関連するイベントを関連付けます。次の例は、"SQL Server に接続" の検索を示しています。また、スレッド ID (左側) を使用して、接続試行が失敗した理由を説明する他の診断を見つけることもできます。

次の例は、新しいプライマリレプリカへの接続エラーを示しています。

例セット 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 ネイティブクライアント構成のクライアントプロトコル] で共有メモリまたは 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)

解決策

例セット 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: レプリカが暗号化用に構成されていないのに、セカンダリレプリカ (ターゲットプライマリ) 上のクライアントプロトコルに対して "Force Protocol Encryption" 構成が選択されている

ケース 5: セカンダリレプリカまたはノードのパフォーマンスの問題により、Always On正常性チェックが失敗する

その他の失敗したフェールオーバーイベントのトラブルシューティング

例セット 1

例セット 2

例セット 3

フィードバック

その他のリソース

次の方法で共有

SQL Server Always On環境での自動フェールオーバーの問題のトラブルシューティング

概要

自動フェールオーバーが正常にトリガーされた場合の現象

自動フェールオーバーが失敗した場合の現象

ケース 1: "指定した期間内の最大エラー数" の値が使い果たされました

手順 1: Windows クラスター ログのデータを確認する (Cluster.log)

手順 2: [指定した期間] プロパティで最大エラー数を確認する

ケース 2: NT 機関\SYSTEM アカウントのアクセス許可が不十分

ケース 3: 可用性データベースが SYNCHRONIZED 状態ではない

ケース 4: レプリカが暗号化用に構成されていないのに、セカンダリ レプリカ (ターゲット プライマリ) 上のクライアント プロトコルに対して "Force Protocol Encryption" 構成が選択されている

ケース 5: セカンダリ レプリカまたはノードのパフォーマンスの問題により、Always On正常性チェックが失敗する

その他の失敗したフェールオーバー イベントのトラブルシューティング

例セット 1

例セット 2

例セット 3

フィードバック

その他のリソース

手順 1: Windows クラスターログのデータを確認する (Cluster.log)

ケース 4: レプリカが暗号化用に構成されていないのに、セカンダリレプリカ (ターゲットプライマリ) 上のクライアントプロトコルに対して "Force Protocol Encryption" 構成が選択されている

ケース 5: セカンダリレプリカまたはノードのパフォーマンスの問題により、Always On正常性チェックが失敗する

その他の失敗したフェールオーバーイベントのトラブルシューティング