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

[アーティクル]
12/14/2024

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

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

プライマリレプリカとセカンダリレプリカのクエリを実行すると、異なる結果が返される

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

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

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

$[Not Synchronizing]$同期していない$ 状態の 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 イベントを含むように拡張されました。これは、接続タイムアウトの問題のトラブルシューティングを行うときに役立ちます。

Note

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

Always On 分散管理ビュー (DMV) のクエリ

レプリカの接続状態の詳細については、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 で、 File>Open に移動し、 Merge 拡張イベントファイルを選択します。
[追加] ボタンを選びます。
[ ファイルを開く ] ダイアログボックスで、 SQL Server \LOG ディレクトリ内のファイルに移動します。
Control キーを押し、名前が 'AlwaysOn_healthxxx.xel' で始まるファイルを選択します。
Open を選択し、OK を選択します。

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

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

非生成イベントが原因で接続タイムアウトが発生しているかどうかを確認する

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

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

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

sp_server_diagnosticsを 5 秒ごとに記録する 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'

Note

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

結果を分析する

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

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

Note

次の sp_server_diagnostics 出力は連結され、 create_time (タイムスタンプ) と query_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 ネットワークトレースを開始します。 Windows のスケジュールされたイベントタスクは、SQL Server 接続エラーのいずれかがアプリケーションログに記録されたときにトリガーされます。スケジュールされたタスクは、主要なネットワークトレースデータが上書きされないように、 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_TIMEOUT) は 10 秒間構成されます。可用性グループレプリカの SESSION_TIMEOUT プロパティを調整することで、接続のタイムアウトを軽減できる場合があります。この設定はレプリカごとに行われます。プライマリレプリカと影響を受ける各セカンダリレプリカに合わせて調整します。構文の例を次に示します。既定の SESSION_TIMEOUT 値は 10 です。したがって、次の値として 15 を使用できます。

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

次の方法で共有

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

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

プライマリレプリカとセカンダリレプリカのクエリを実行すると、異なる結果が返される

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

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

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

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

アプリケーションの問題

ネットワークの問題

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

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

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

Always On 分散管理ビュー (DMV) のクエリ

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

非生成イベントが原因で接続タイムアウトが発生しているかどうかを確認する

結果を分析する

非生成スケジューライベントを調査する

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

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

フィードバック

その他のリソース

次の方法で共有

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

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

プライマリ レプリカとセカンダリ レプリカのクエリを実行すると、異なる結果が返される

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

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

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

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

アプリケーションの問題

ネットワークの問題

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

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

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

Always On 分散管理ビュー (DMV) のクエリ

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

非生成イベントが原因で接続タイムアウトが発生しているかどうかを確認する

結果を分析する

非生成スケジューラ イベントを調査する

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

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

フィードバック

その他のリソース

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

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

プライマリレプリカとセカンダリレプリカのクエリを実行すると、異なる結果が返される

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

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

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

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

非生成スケジューライベントを調査する

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