可用性グループレプリカ間の断続的な接続タイムアウトのトラブルシューティング

[アーティクル]
04/04/2023

この記事は、可用性グループレプリカ間で報告される断続的な接続タイムアウトを診断するのに役立ちます。

断続的な可用性グループレプリカ接続タイムアウトの症状と影響

プライマリレプリカとセカンダリレプリカに対してクエリを実行すると、さまざまな結果が返されます

セカンダリレプリカを照会する読み取り専用ワークロードは、古いデータに対してクエリを実行する可能性があります。間欠的なレプリカ接続タイムアウトが発生した場合、同じデータに対してクエリを実行しても、プライマリレプリカデータベース上のデータに対する変更はまだセカンダリデータベースに反映されません。詳細については、「セカンダリレプリカでのデータ待機時間」セクションを参照してください。

診断レポートの可用性グループが同期されない

SQL Server Management StudioのAlways On ダッシュボードでは、レプリカが同期されていない状態である異常な可用性グループが報告される場合があります。また、Always Onダッシュボードレポートのレプリカが [同期していない] 状態になっていることも確認できます。

これらのレプリカのSQL Serverエラーログを確認すると、可用性グループ内のレプリカ間に接続タイムアウトが発生したことを示す次のようなメッセージが表示されることがあります。

プライマリレプリカからのエラーログ

2023-02-15 07:10:55.500 spid43s Always On availability groups connection with secondary database terminated for primary database 'agdb' on the availability replica 'SQL19AGN2' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.

セカンダリレプリカからのエラーログ

2023-02-15 07:11:03.100 spid31s A connection time-out has occurred on a previously established connection to availability replica 'SQL19AGN1' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

2023-02-15 07:11:03.100 spid31s Always On Availability Groups connection with primary database terminated for secondary database 'agdb' on the availability replica 'SQL19AGN1' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.

断続的な接続の問題は、セカンダリレプリカのフェールオーバーの準備に影響する可能性があります

可用性グループを自動フェールオーバー用に構成し、同期コミットフェールオーバーパートナーがプライマリから断続的に切断された場合、自動フェールオーバーが失敗する可能性があります。

クエリを実行 sys.dm_hadr_database_replia_cluster_states して、可用性グループデータベースがその時点でフェールオーバーの準備ができているかどうかを判断できます。セカンダリレプリカでミラーリングエンドポイントが停止した場合の結果の例を次に示します。

SELECT drcs.database_name, drcs.is_failover_ready, ar.replica_server_name, ars.role_desc, ars.connected_state_desc,
ars.last_connect_error_description, ars.last_connect_error_number, ar.endpoint_url
FROM sys.dm_hadr_availability_replica_states ars JOIN sys.availability_replicas ar ON ars.replica_id=ar.replica_id
JOIN sys.dm_hadr_database_replica_cluster_states drcs ON ar.replica_id=drcs.replica_id
WHERE ars.role_desc='SECONDARY'

フェールオーバーがレプリカ接続のタイムアウトと一致する場合、フェールオーバーパートナーコンピューターのプライマリロールで可用性グループがオンラインにならない可能性があります。

接続タイムアウトエラーは何を示していますか?

可用性グループのレプリカ設定 SESSION_TIMEOUTの既定値は 10 秒です。この設定は、レプリカごとに構成されます。接続タイムアウトを報告する前に、レプリカがパートナーレプリカからの応答の受信を待機する時間を決定します。レプリカがパートナーレプリカから応答を受け取っていない場合、Microsoft SQL Server エラーログと Windows アプリケーションログに接続タイムアウトが報告されます。タイムアウトを報告するレプリカは直ちに再接続を試み、5 秒ごとに試行を続けます。

通常、接続のタイムアウトは検出され、1 つのレプリカによってのみ報告されます。ただし、接続のタイムアウトは、両方のレプリカによって同時に報告される場合があります。このメッセージのバージョンは、以前に確立した接続を使用して接続がタイムアウトしたか、新しい接続を使用して発生したかによって異なります。

Message 35206 A connection timeout has occurred on a previously established connection to availability replica '<replicaname>' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

Message 35201 A connection timeout has occurred while attempting to establish a connection to availability replica '<replicaname>' with id [<replicaid>]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.

パートナーレプリカでタイムアウトが検出されない場合があります。その場合は、メッセージ 35201 または 35206 が報告される可能性があります。そうでない場合は、各可用性グループデータベースに接続の損失が報告されます。

Message 35267 Always On Availability Groups connection with primary/secondary database terminated for primary/secondary database '<databasename>' on the availability replica '<replicaname>' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.

エラーログに報告SQL Server例を次に示します。プライマリレプリカでミラーリングエンドポイントを停止すると、セカンダリレプリカが接続タイムアウトを検出し、セカンダリレプリカエラーログにメッセージ 35206 と 35267 が報告されます。

2023-02-15 07:11:03.100 spid31s A connection timeout has occurred on a previously established connection to availability replica 'SQL19AGN1' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

2023-02-15 07:11:03.100 spid31s Always On Availability Groups connection with primary database terminated for secondary database 'agdb' on the availability replica 'SQL19AGN1' with Replica ID:[<replicaid>]. This is an informational message only. No user action is required.

この例では、プライマリレプリカはセカンダリと通信できるため、接続タイムアウトを検出せず、可用性グループデータベースごとにメッセージ 35267 を報告しました (この例では、"agdb" というデータベースは 1 つだけです)。

2023-02-15 07:10:55.500 spid43s Always On Availability Groups connection with secondary database terminated for primary database 'agdb' on the availability replica 'SQL19AGN2' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.

レプリカ接続のタイムアウトの原因

アプリケーションの問題

SQL Serverは、いくつかの理由でビジー状態になる可能性があり、可用性グループSESSION_TIMEOUT期間内にミラーリングエンドポイント接続をサービスしません。これにより、接続がタイムアウトします。これらの理由の一部を次に示します。

SQL Server CPU 使用率が 100% になります。これは、SQL Serverまたはその他のアプリケーションが一度に数秒間 CPU を駆動していることを意味します。
SQL Serverでは、生成されていないスケジューライベントが発生します。 SQL Serverスレッドは、スレッドがタイムリーに生成されない場合に、スケジューラ (CPU) を他のスレッドに提供して作業を完了する役割を担います。
SQL Serverでは、ワーカースレッドの枯渇、メモリ不足の問題、またはミラーリングエンドポイント接続のサービス機能に影響するアプリケーションの問題が発生します。

ネットワークの問題

これには、エラーがトリガーされたときに、プライマリレプリカとセカンダリレプリカのネットワークトレースログを収集する必要があります。これを行うには、ネットワーク待機時間とドロップされたパケットを調べることができます。

レプリカ接続のタイムアウトを診断する方法

SQL Serverがパートナーレプリカとの接続にサービスを提供できなくなるアプリケーションの問題について、このセクションでは、SQL Server ログを分析する方法について説明します。これらのヒントは、レプリカ接続タイムアウトの根本原因を特定するのに役立ちます。このセクションでは、ネットワーク状態をチェックできるように、接続タイムアウトが発生したときにネットワークトレースを収集する方法に関するより高度なガイダンスで終わります。

レプリカ接続のタイムアウトのタイミングと場所を評価する

接続タイムアウトの履歴、頻度、傾向を確認します。 SQL Serverエラーログで見つけたメッセージを使用すると、これを行うのに最適な方法です。接続タイムアウトはどこで報告されますか? プライマリレプリカまたはセカンダリレプリカで一貫して報告されますか? エラーはいつ発生しましたか? 月、曜日、または時刻の特定の週に発生しましたか? 他のスケジュールされたメンテナンスまたはバッチ処理は、接続タイムアウトが観察される時刻に対応していますか? この評価は、接続タイムアウトのスコープを設定して関連付け、根本原因を特定するのに役立ちます。

AlwaysOn_health拡張イベントセッションを確認する

拡張イベントセッションは AlwaysOn_health 、レプリカがパートナーレプリカとの接続を確立するときにトリガーされるイベントを含むように ucs_connection_setup 拡張されました。これは、接続タイムアウトの問題のトラブルシューティングに役立ちます。

注:

拡張イベントはucs_connection_setup、最新のSQL Server累積的な更新プログラムに追加されました。この拡張イベントを観察するには、最新の累積的な更新プログラムを実行している必要があります。

分散管理ビュー (DMV) Always Onクエリを実行する

レプリカの接続状態の詳細については、ALWAYS ON DMV に対してクエリを実行できます。このクエリは、接続状態と、問題が発生した時点で接続タイムアウトに関連付けられているエラーのみを報告します。接続の問題が断続的な場合、クエリで切断された状態が簡単にキャプチャされない可能性があります。

SELECT ar.replica_server_name, ars.role_desc, ars.connected_state_desc,
ars.last_connect_error_description, ars.last_connect_error_number, ar.endpoint_url
FROM sys.dm_hadr_availability_replica_states ars JOIN sys.availability_replicas ar ON ars.replica_id=ar.replica_id

次の例は、プライマリレプリカのミラーリングエンドポイントが停止したため、切断状態が維持されていることを示しています。プライマリレプリカに対してクエリを実行すると、ALWAYS ON DMV はプライマリレプリカとすべてのセカンダリレプリカについてレポートできます (プライマリレプリカではエンドポイントが無効になっています)。

セカンダリレプリカにクエリを実行すると、Always On DMV によってセカンダリレプリカのみが報告されます。

Always On拡張イベントセッションを確認する

SQL Server Management Studio (SSMS) オブジェクトエクスプローラーを使用して各レプリカに接続し、拡張イベントファイルをAlwaysOn_health開きます。
SSMS で、[ ファイルを>開く] に移動し、[ 拡張イベントファイルのマージ] を選択します。
[追加] ボタンを選択します。
[ファイルを開く] ダイアログボックスで、SQL Server \LOG ディレクトリ内のファイルに移動します。
Control キーを押し、名前が 'AlwaysOn_healthxxx.xel' で始まるファイルを選択します。
[ 開く] を選択し、[ OK] を選択します。

SSMS には、AlwaysOn イベントを示す新しいタブ付きウィンドウが表示されます。

次のスクリーンショットは、 AlwaysOn_health セカンダリレプリカからのデータを示しています。最初のアウトラインボックスには、プライマリレプリカ上のエンドポイントが停止した後の接続損失が表示されます。 2 つ目のボックスには、セカンダリレプリカが次にプライマリレプリカに接続しようとしたときに発生する接続エラーが表示されます。

非生成イベントによって接続のタイムアウトが発生しているかどうかを確認する

可用性レプリカがパートナーレプリカ接続にサービスを提供できない最も一般的な理由の 1 つは、非生成スケジューラです。非利回りスケジューラの詳細については、「スケジューリングと生成SQL Serverトラブルシューティング」を参照してください。

SQL Serverは、5 秒から 10 秒という短い非生成スケジューライベントを追跡します。これらのイベントは、コンポーネント出力の TrackingNonYieldingScheduler データポイントで報告されます sp_server_diagnostics query_processing 。

レプリカ接続のタイムアウトを引き起こす可能性がある非生成イベントをチェックするには、次の手順に従います。

5 秒ごとに記録 sp_server_diagnostics する SQL エージェントジョブを作成します。
接続のタイムアウトを報告しないサーバーで、このジョブをスケジュールします。つまり、サーバー A レプリカがエラーログでレプリカ接続タイムアウトを報告する場合は、パートナーレプリカの SQL エージェントジョブをサーバー B に設定します。または、両方のレプリカに接続タイムアウトが表示される場合は、両方のレプリカでジョブを作成します。

次のバッチファイルを実行して、5 秒ごとに実行 sp_server_diagnostics されるジョブを作成し、出力をテキストファイルに追加してから、ジョブを開始します。次の例のコマンドは、 sp_server_diagnostics 5 5 秒ごとに実行されます。そのため、このジョブを 5 秒ごとに実行するようにスケジュールする必要はなく、ジョブを開始するだけで、5 秒ごとに停止するまで実行されます。

USE [msdb]
GO
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Run sp_server_diagnostics',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
/****** Object: Step [Run SP_SERVER_DIAGNOSTICS] Script Date: 2/15/2023 4:20:41 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run SP_SERVER_DIAGNOSTICS',
@subsystem=N'TSQL',
@command=N'sp_server_diagnostics 5',
@database_name=N'master',
@output_file_name=N'D:\cases\2423\sp_server_diagnostics_output.out',
@flags=2
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
EXEC sp_start_job 'Run sp_server_diagnostics'

注:

これらのコマンドで、有効なパスに変更 @output_file_name し、ファイル名を指定します。

結果を分析する

接続のタイムアウトが報告されたら、SQL Serverエラーログに表示されるタイムアウトイベントのタイムスタンプをメモします。次の例のレプリカでは、 SQL19AGN1 レプリカ接続のタイムアウトを報告していました。そのため、SQL エージェントジョブは、パートナーレプリカ上に SQL19AGN2作成されました。その後、07:24:31 にエラーログに SQL19AGN1 接続タイムアウトが報告されました。

次に、sp_server_diagnosticsを実行する SQL エージェントジョブからの出力は、報告された時間の前後にチェックされ、特にコンポーネント出力のデータポイントがquery_processing確認TrackingNonYieldingSchedulerされます。出力は、レプリカ接続タイムアウトがSQL19AGN1 (07:24:31) に報告された前後に、サーバー SQL19AGN2 (07:24:33) で非生成スケジューラが追跡されたことを報告します (07:24:31)。

注:

次sp_server_diagnosticsの出力が連結され、(タイムスタンプ) と結果の両方がcreate_timequery_processing TrackingNonYieldingScheduler表示されます。

生成されていないスケジューライベントを調査する

前の診断手順から、生成しないイベントによってレプリカ接続がタイムアウトしたことを確認した場合:

非生成イベントが実行された時点でSQL Serverで実行されているワークロードを特定します。
レプリカ接続のタイムアウトと同様に、発生した月、日、または週の間にこれらのイベントの傾向を調べます。
非生成イベントが検出されたシステムのパフォーマンスモニタートレースを収集します。
Processor::% Processor Time、Memory::Available MBytes、Logical Disk::Avg Disk Queue Length、Logical Disk::Avg Disk sec/Transfer など、システムリソースの主要なパフォーマンスカウンターを収集します。
必要な場合は、SQL Server サポートインシデントを開き、これらの非生成イベントの根本原因を見つけるためのさらに支援を行います。さらに分析するために収集したログを共有します。

高度なデータ収集: 接続タイムアウト中にネットワークトレースを収集する

SQL Server アプリケーションの以前の診断で根本原因が得られなかった場合は、ネットワークをチェックする必要があります。ネットワークの分析に成功するには、接続タイムアウトの時間をカバーするネットワークトレースを収集する必要があります。

次の手順では、SQL Server エラーログで接続タイムアウトが報告されるレプリカに対して Windows netsh ネットワークトレースを開始します。 SQL Server接続エラーのいずれかがアプリケーションログに記録されると、Windows スケジュールされたイベントタスクがトリガーされます。スケジュールされたタスクは、キーネットワークトレースデータが netsh 上書きされないように、ネットワークトレースを停止するコマンドを実行します。これらの手順では、バッチログとトレースログの *F:* のパスも想定しています。このパスを環境に合わせて調整します。

次のコードスニペットに示すように、接続タイムアウトが発生する 2 つのレプリカで、ネットワークトレースを開始します。
```
netsh trace start capture=yes persistent=yes overwrite=yes maxsize=500 tracefile=f:\trace.etl
```

イベント 35206 または 35267 でトレースを停止 netsh する Windows スケジュールタスクを作成します。これらのタスクは、管理コマンドラインで作成できます。

schtasks /Create /tn Event35206Task /tr F:\stoptrace.bat /SC ONEVENT /EC Application /MO *[System/EventID=35206] /f /RL HIGHEST

schtasks /Create /tn Event35267Task /tr F:\stoptrace.bat /SC ONEVENT /EC Application /MO *[System/EventID=35267] /f /RL HIGHEST

イベントが発生し、ネットワークトレースが停止してキャプチャされたら、タスクを ONEVENT 削除できます。
```
PS C:\Users\sqladmin> Schtasks /Delete /tn Event35206Task /F
PS C:\Users\sqladmin> Schtasks /Delete /tn Event35267Task /F
```

ネットワークトレースの分析は、このトラブルシューティングツールの範囲外です。ネットワークトレースを解釈できない場合は、Microsoft SQL Server サポートチームに問い合わせて、根本原因分析のために他の要求されたログファイルと共にトレースを提供してください。

接続のタイムアウトを軽減するには、他に何ができますか?

既定の可用性グループは、 SESSION_TIMEOUT10 秒間構成されます。可用性グループのレプリカ SESSION_TIMEOUT プロパティを調整することで、接続のタイムアウトを軽減できる場合があります。この設定はレプリカごとです。プライマリレプリカと影響を受ける各セカンダリレプリカに合わせて調整します。構文の例を次に示します。既定値 SESSION_TIMEOUT は 10 です。したがって、次の値として 15 を使用できます。

ALTER AVAILABILITY GROUP ag
MODIFY REPLICA ON 'SQL19AGN1' WITH (SESSION_TIMEOUT = 15);

次の方法で共有

可用性グループレプリカ間の断続的な接続タイムアウトのトラブルシューティング

断続的な可用性グループレプリカ接続タイムアウトの症状と影響

プライマリレプリカとセカンダリレプリカに対してクエリを実行すると、さまざまな結果が返されます

診断レポートの可用性グループが同期されない

断続的な接続の問題は、セカンダリレプリカのフェールオーバーの準備に影響する可能性があります

接続タイムアウトエラーは何を示していますか?

レプリカ接続のタイムアウトの原因

アプリケーションの問題

ネットワークの問題

レプリカ接続のタイムアウトを診断する方法

レプリカ接続のタイムアウトのタイミングと場所を評価する

AlwaysOn_health拡張イベントセッションを確認する

分散管理ビュー (DMV) Always Onクエリを実行する

Always On拡張イベントセッションを確認する

非生成イベントによって接続のタイムアウトが発生しているかどうかを確認する

結果を分析する

生成されていないスケジューライベントを調査する

高度なデータ収集: 接続タイムアウト中にネットワークトレースを収集する

接続のタイムアウトを軽減するには、他に何ができますか?

フィードバック

その他のリソース

次の方法で共有

可用性グループ レプリカ間の断続的な接続タイムアウトのトラブルシューティング

断続的な可用性グループ レプリカ接続タイムアウトの症状と影響

プライマリ レプリカとセカンダリ レプリカに対してクエリを実行すると、さまざまな結果が返されます

診断レポートの可用性グループが同期されない

断続的な接続の問題は、セカンダリ レプリカのフェールオーバーの準備に影響する可能性があります

接続タイムアウト エラーは何を示していますか?

レプリカ接続のタイムアウトの原因

アプリケーションの問題

ネットワークの問題

レプリカ接続のタイムアウトを診断する方法

レプリカ接続のタイムアウトのタイミングと場所を評価する

AlwaysOn_health拡張イベント セッションを確認する

分散管理ビュー (DMV) Always Onクエリを実行する

Always On拡張イベント セッションを確認する

非生成イベントによって接続のタイムアウトが発生しているかどうかを確認する

結果を分析する

生成されていないスケジューラ イベントを調査する

高度なデータ収集: 接続タイムアウト中にネットワーク トレースを収集する

接続のタイムアウトを軽減するには、他に何ができますか?

フィードバック

その他のリソース

可用性グループレプリカ間の断続的な接続タイムアウトのトラブルシューティング

断続的な可用性グループレプリカ接続タイムアウトの症状と影響

プライマリレプリカとセカンダリレプリカに対してクエリを実行すると、さまざまな結果が返されます

断続的な接続の問題は、セカンダリレプリカのフェールオーバーの準備に影響する可能性があります

接続タイムアウトエラーは何を示していますか?

AlwaysOn_health拡張イベントセッションを確認する

Always On拡張イベントセッションを確認する

生成されていないスケジューライベントを調査する

高度なデータ収集: 接続タイムアウト中にネットワークトレースを収集する