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

この記事は、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 状態であることを報告します。 さらに、可用性データベースでは NOT SYNCHRONIZING 、状態がレポートされ、アプリケーションはこれらのデータベースにアクセスできません。

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

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

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

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

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

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

1. Windows PowerShellを使用して、プライマリ レプリカをホストしているクラスター ノードで Windows クラスター ログを生成します。 これを行うには、プライマリ レプリカをホストしているSQL Serverのインスタンスの管理者特権の PowerShell ウィンドウで次のコマンドレットを実行します。

   Get-ClusterLog -Node <SQL Server node name> -TimeSpan 15
   

   

   [!注]

   • この手順のパラメーターは -TimeSpan 15 、診断中の問題が過去 15 分間に発生したことを前提としています。
   • 既定では、ログ ファイルは %WINDIR%\cluster\reports に作成されます。

2. メモ帳で Cluster.log ファイルを開き、Windows クラスター ログを確認します。

3. メモ帳で [検索の編集>] を選択し、ファイルの末尾にある "failoverCount" 文字列を検索します。 結果には、次のようなメッセージが表示されます。

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

   

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

1. フェールオーバー クラスター マネージャーを起動します。

2. ナビゲーション ウィンドウで、[ロール] を選択 します。

3. [ ロール ] ウィンドウで、クラスター化されたリソースを右クリックし、[プロパティ] を選択 します。

4. [ フェールオーバー ] タブを選択し、[ 指定された期間] の値で [最大エラー数] を 選択します。

   

   注:

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

まとめ

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

Resolution

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

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

SQL Server データベース エンジン リソース DLL は、ODBC を使用して正常性を監視することで、プライマリ レプリカをホストしているSQL Serverのインスタンスに接続します。 この接続に使用されるログオン資格情報は、ローカル SQL Server NT AUTHORITY\SYSTEM ログイン アカウントです。 既定では、このローカル ログイン アカウントには次のアクセス許可が付与されます。

• 任意の可用性グループを変更する
• SQL の接続
• サーバーの状態を表示する

ログイン アカウントにNT AUTHORITY\SYSTEM自動フェールオーバー パートナー (セカンダリ レプリカ) に対するこれらのアクセス許可がない場合、自動フェールオーバーが発生したときに正常性検出を開始SQL Serverできません。 そのため、セカンダリ レプリカはプライマリ ロールに移行できません。 これが原因であるかどうかを調査して診断するには、Windows クラスター ログを確認します。 これを行うには、次の手順を実行します。

1. Windows PowerShellを使用して、クラスター ノードで Windows クラスター ログを生成します。 これを行うには、プライマリ ロールに移行しなかったセカンダリ レプリカをホストしているSQL Serverのインスタンスで、管理者特権の PowerShell ウィンドウで次のコマンドレットを実行します。

   Get-ClusterLog -Node <SQL Server node name> -TimeSpan 15
   

   

2. メモ帳で Cluster.log ファイルを開き、Windows クラスター ログを確認します。

3. 次のテキストのようなエラー エントリを見つけます。

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

   

まとめ

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 つ以上のデータベースが同期されていないか、可用性グループに参加していません。

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

1. セカンダリ レプリカに接続します。

2. 次の 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 がローカル レプリカへの接続を試み、正常性の監視を開始します。 これは、プライマリ ロールへの移行の一部です。 セカンダリ レプリカが暗号化用に構成されていないが、クライアント構成で [プロトコル暗号化の強制 ] 設定が誤って設定されている場合、接続は失敗し、フェールオーバーは発生しません。

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

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

まとめ

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

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

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

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

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

   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の応答が遅延し、フェールオーバー試行の成功が妨げる可能性があります。

2. システム スケジューラに負荷がかかっているかどうかを判断するには、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 正常性監視をローカルに接続する必要があります。

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

1. Windows PowerShellを使用して、クラスター ノードで Windows クラスター ログを生成します。 これを行うには、プライマリ ロールに移行しなかったセカンダリ レプリカをホストしているSQL Serverのインスタンスで、管理者特権の PowerShell ウィンドウで次のコマンドレットを実行します。 クラスター ログは、過去 60 分間のアクティビティで生成されます。

   Get-ClusterLog -Node <SQLServerNodeName> -TimeSpan 60
   

2. Windows クラスター ログを確認するには、メモ帳で Cluster.log ファイルを開きます。

3. 失敗したフェールオーバー イベント中に該当する "connect to SQL Server" 文字列を検索します。

4. スレッド 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)

解決策

SQL Server 構成マネージャーを開始し、[SQL ネイティブ クライアント構成のクライアント プロトコル] で共有メモリまたは 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 アカウントのアクセス許可が不十分です。