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

まとめ

SQL Server Always On 可用性グループでは、SESSION_TIMEOUT期間内にレプリカがそのパートナー レプリカから応答を受信しない場合、接続タイムアウトが発生します。 SQL Serverエラー ログは、これらの断続的なタイムアウトをエラー 35201、35206、および 35267 として報告します。 これらのタイムアウトにより、可用性グループが Not Synchronizing 状態のままになることがあります。 一般的な原因としては、CPU 使用率が高い、スケジューラが生成されていないなどのアプリケーションの問題、または待機時間や破棄されたパケットなどのネットワークの問題などがあります。 この記事では、SQL Server エラー ログ、Always On 動的管理ビュー (DMV)、拡張イベント、およびネットワーク トレースを使用して、接続タイムアウト エラーを解釈し、根本原因を診断するのに役立ちます。 また、可用性グループ レプリカの SESSION_TIMEOUT 設定を調整する方法など、タイムアウトを軽減する方法についても説明します。

断続的な接続タイムアウトの症状と影響

プライマリ レプリカとセカンダリ レプリカが異なる結果を返す

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

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

SQL Server Management Studio (SSMS) の 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_replica_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 2019 CU15 以降で使用できます。 詳細については、「 可用性グループの拡張イベントの構成」を参照してください。

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 拡張イベント セッションを確認する

  1. SSMS オブジェクト エクスプローラーを使用して各レプリカに接続し、AlwaysOn_health拡張イベント ファイルを開きます。

  2. SSMS で、 File>Open に移動し、 Merge 拡張イベント ファイルを選択します。

  3. [追加] ボタンを選びます。

  4. [ファイルを開く] ダイアログ ボックスで、SQL Server \LOG ディレクトリ内のファイルに移動します。

  5. Ctrl キーを押しながら、名前が AlwaysOn_healthxxx.xel で始まるファイルを選択します。

  6. Open を選択し、OK を選択します。

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

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

    セカンダリ レプリカからのAlwaysOn_health データのスクリーンショット。

制御を明け渡さないイベントが接続タイムアウトを引き起こすかどうかを確認する

可用性レプリカがパートナー レプリカとの接続を処理できない最も一般的な理由の 1 つは、CPU を明け渡さないスケジューラです。 yield しないスケジューラの詳細については、「SQL Server のスケジューリングと Yielding のトラブルシューティング」を参照してください。

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

レプリカ接続のタイムアウトの原因となる可能性がある応答を返さないイベントを確認するには、次の手順に従ってください。

  1. sp_server_diagnosticsを 5 秒ごとに記録する SQL エージェント ジョブを作成します。

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

  3. 次の T-SQL スクリプトを実行して、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 はレプリカ接続のタイムアウトを報告します。 そのため、パートナー レプリカである SQL19AGN2 上に SQL Server エージェント ジョブを作成します。 その後、 SQL19AGN1 エラー ログの 07:24:31 に接続タイムアウトが報告されます。

SQL19AGN1 エラー ログで報告された接続タイムアウトのスクリーンショット。

次に、報告された時間の前後に sp_server_diagnostics 実行される SQL エージェント ジョブからの出力を確認します。 具体的には、TrackingNonYieldingScheduler コンポーネント出力のquery_processing データ ポイントを確認します。 出力では、レプリカ接続タイムアウトが SQL19AGN1 で 07:24:31 に報告された時刻の前後に、サーバー SQL19AGN2 で非応答スケジューラが 0 以外の 16 進数値として追跡されたことが 07:24:33 に報告されています。

注

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

create_timeとquery_processingの結果を示す連結されたsp_server_diagnostics出力のスクリーンショット。

応答しないスケジューラ イベントを調査する

前述の診断手順で、レプリカ接続のタイムアウトの原因が non-yielding イベントであることを確認した場合は、次の手順に従います。

  1. 非生成イベントが発生した時点でSQL Serverで実行されているワークロードを特定します。

  2. レプリカ接続のタイムアウトと同様に、発生する月、日、または週の間にこれらのイベントの傾向を探します。

  3. 応答停止イベントが検出されたシステムで、パフォーマンス モニターのトレースを収集します。

  4. Processor::% Processor Time、Memory::Available MBytes、Logical Disk::Avg Disk Queue Length、Logical Disk::Avg Disk sec/Transfer など、システム リソースの主要なパフォーマンス カウンターを収集します。

  5. 必要に応じて、これらの非降伏イベントの根本原因を特定するための追加支援を受けるために、SQL Server サポート インシデントを開いてください。 さらに分析するために収集したログを共有します。

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

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

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

  1. 次のコード スニペットに示すように、接続タイムアウトが発生した 2 つのレプリカでネットワーク トレースを開始します。

    netsh trace start capture=yes persistent=yes overwrite=yes maxsize=500 tracefile=f:\trace.etl

  2. stoptrace.batを作成 します 。 このファイルは、管理コマンド プロンプトで作成できます。

    echo netsh trace stop > F:\stoptrace.bat

  3. イベント 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

  4. イベントが発生し、ネットワーク トレースが停止してキャプチャされたら、 ONEVENT タスクを削除します。

    schtasks /Delete /tn Event35206Task /F
schtasks /Delete /tn Event35267Task /F

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

接続タイムアウトを軽減する

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

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

関連するコンテンツ

  • Last updated on