針對可用性群組復本之間的間歇性連線逾時進行疑難解答

本文可協助您診斷可用性群組複本之間所報告的間歇性連線逾時。

間歇性可用性群組復本連線逾時的徵兆和效果

查詢主要和次要複本會傳回不同的結果

查詢次要複本的唯讀工作負載可能會查詢過時的數據。 如果發生間歇性復本連線逾時，當您查詢相同的數據時，主要複本資料庫上的數據變更尚未反映在輔助資料庫中。 如需詳細資訊，請參閱 次要複本上的數據延遲一 節。

診斷報告可用性群組未同步處理

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'

如果故障轉移與複本連線逾時一致，自動故障轉移可能不會讓故障轉移夥伴計算機上的主要角色中的可用性群組上線。

線上逾時錯誤表示什麼？

可用性群組複本設定的預設值為 10 秒。 SESSION_TIMEOUT 此設定會針對每個複本進行設定。 它會決定複本在回報連線逾時之前，要等候多久才能收到來自其夥伴復本的回應。如果復本未收到夥伴複本的回應，則會在 Microsoft SQL Server 錯誤記錄檔和 Windows 應用程式記錄檔中報告連線逾時。 報告逾時的複本會立即嘗試重新連線，並會每隔五秒繼續試用一次。

一般而言，只有一個複本會偵測到連線逾時並回報。 不過，這兩個複本可能會同時報告連線逾時。 根據使用先前建立的連線或新連線發生連線逾時，此訊息有不同的版本：

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') ：

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 體驗 100% 的 CPU 使用率。 這表示 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 累積更新。 您必須執行最新的累積更新，才能觀察此擴充事件。

查詢 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. 使用 SQL Server Management Studio (SSMS) 物件總管 連線到每個複本，然後開啟AlwaysOn_health擴充事件檔案。

2. 在 SSMS 中，移至 [檔案>開啟]，然後選取 [ 合併擴充事件檔案]。

3. 選取 [新增] 按鈕。

4. 在 [檔案開啟] 對話框中，流覽至 SQL Server \LOG 目錄中的檔案。

5. 按 下 [控制]，然後選取名稱開頭為 'AlwaysOn_healthxxx.xel' 的檔案。

6. 選取 [開啟]，然後選取 [ 確定]。

   您應該會在 SSMS 中看到顯示 AlwaysOn 事件的新索引標籤視窗。

   下列螢幕快照顯示 AlwaysOn_health 次要複本的數據。 第一個外框方塊會顯示主要複本上的端點停止之後的聯機遺失。 第二個外框方塊會顯示次要複本下次嘗試連線到主要複本時發生的連線失敗。

   

檢查非產生事件是否造成連線逾時

可用性復本無法為夥伴複本連線提供服務的最常見原因之一，是無法產生排程器。 如需非產生排程器的詳細資訊，請參閱排程和產生 SQL Server 疑難解答。

SQL Server 追蹤最短 5 到 10 秒的非產生排程器事件。 它會在元件輸出的數據 TrackingNonYieldingScheduler 點中 sp_server_diagnostics query_processing 報告這些事件。

若要檢查可能導致複本連線逾時的非產生事件，請遵循下列步驟：

1. 建立每五秒記錄 sp_server_diagnostics 一次的 SQL Agent 作業。

2. 在未報告連線逾時的伺服器上排程此作業。也就是說，如果伺服器 A 複本在其錯誤記錄檔中報告複本連線逾時，請在夥伴復本伺服器 B 上設定 SQL Agent 作業。或者，如果您在這兩個複本上看到連線逾時，請在這兩個複本上建立作業。

3. 執行下列批處理檔，以建立每隔五秒執行一次的作業 sp_server_diagnostics 、將輸出附加至文本檔，然後啟動作業。 下列範例中的 命令會 sp_server_diagnostics 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 Agent 作業。 然後，錯誤記錄檔於 SQL19AGN1 07：24：31 回報連線逾時。

接下來，會在報告的時間檢查執行sp_server_diagnostics之 SQL Agent 作業的輸出，特別是檢閱 TrackingNonYieldingScheduler 元件輸出中 query_processing 的數據點。 輸出會報告未產生的排程器 (在伺服器SQL19AGN2 (上追踪为非零十六进制值) ，SQL19AGN2 (在 07：24：33 時) 大約在SQL19AGN1 (于 07：24：31) 回報復本聯機逾時的時間。

注意事項

下列 sp_server_diagnostics 輸出會串連，以顯示 create_time (時間戳) 和 query_processing TrackingNonYieldingScheduler 結果。

調查未產生的排程器事件

如果您從先前的診斷步驟中確認未產生的事件導致複本連線逾時：

1. 識別在執行未產生事件時，在 SQL Server 中執行的工作負載。

2. 類似於複本聯機逾時，請在發生這些事件的月份、日或周中尋找這些事件的趨勢。

3. 在偵測到非產生事件的系統上收集性能監視器追蹤。

4. 收集系統資源的關鍵性能計數器，包括 處理器：：% 處理器時間、記憶體：：可用的 MBytes、 邏輯磁碟：：Avg 磁碟佇列長度，以及 邏輯磁碟：：Avg 磁碟秒/傳輸。

5. 如有必要，請開啟 SQL Server 支援事件，以進一步協助找出這些未產生事件的根本原因。 共用您已收集的記錄以供進一步分析。

進階數據收集：在連線逾時期間收集網路追蹤

如果先前診斷的 SQL Server 應用程式未產生根本原因，您應該檢查網路。 若要成功分析網路，您必須收集涵蓋連線逾時時間的網路追蹤。

下列程式會在 SQL Server 錯誤記錄中報告連線逾時的複本上啟動 Windows netsh 網路追蹤。 當應用程式記錄檔中記錄其中一個 SQL Server 連線錯誤時，就會觸發 Windows 排程事件工作。 排程的工作會執行命令來停止 netsh 網路追蹤，以免覆寫密鑰網路追蹤數據。 這些步驟也假設批次和追蹤記錄的路徑為 *F：*。 調整此環境路徑。

1. 在發生聯機逾時的兩個複本上啟動網路追蹤，如下列代碼段所示：

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

2. 建立停止事件 35206 或 35267 追蹤的 Windows 排程 netsh 工作。 您可以在系統管理指令列建立這些工作：

   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
   

3. 發生事件且停止並擷取網路追蹤之後，您可以刪除工作 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);