排查可用性组副本之间的间歇性连接超时问题

本文可帮助你诊断可用性组副本之间报告的间歇性连接超时。

间歇性可用性组副本 (replica) 连接超时的症状和影响

查询主要副本和次要副本会返回不同的结果

查询次要副本的只读工作负荷可能会查询过时的数据。 如果发生间歇性副本 (replica) 连接超时，则查询相同数据时，主副本 (replica) 数据库上数据的更改不会反映在辅助数据库中。 有关详细信息，请参阅辅助副本 (replica) 上的数据延迟部分。

诊断报告可用性组未同步

SQL Server Management Studio 中的Always On 仪表板可能会报告副本处于“未同步”状态的不正常可用性组。 还可以观察到Always On 仪表板报表副本处于“未同步”状态。

查看这些副本SQL Server错误日志时，可能会看到如下消息，指示可用性组中的副本之间存在连接超时：

主副本 (replica) 的错误日志

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.

辅助副本 (replica) 中的错误日志

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.

间歇性连接问题可能会影响辅助副本 (replica) 的故障转移准备情况

如果将可用性组配置为自动故障转移，并且同步提交故障转移伙伴间歇性地与主要服务器断开连接，则自动故障转移可能会失败。

可以查询 sys.dm_hadr_database_replia_cluster_states 以确定可用性组数据库当时是否已准备好故障转移。 下面是在辅助副本 (replica) 上停止镜像终结点时的结果示例：

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'

如果故障转移与副本 (replica) 连接超时一致，则自动故障转移可能不会使故障转移伙伴计算机上的主要角色的可用性组联机。

连接超时错误指示什么？

可用性组的默认值为 10 秒，副本 (replica) 设置 SESSION_TIMEOUT。 为每个副本 (replica) 配置此设置。 它确定副本 (replica) 在报告连接超时之前等待从其合作伙伴副本 (replica) 收到响应的时间。如果副本 (replica) 未收到合作伙伴副本 (replica) 的响应，则会在 Microsoft SQL Server 错误日志和 Windows 应用程序日志中报告连接超时。 报告超时的副本 (replica) 会立即尝试重新连接，并将继续每五秒尝试一次。

通常，连接超时仅由一个副本 (replica) 检测到并报告。 但是，两个副本可能会同时报告连接超时。 此消息有不同的版本，具体取决于连接超时是使用以前建立的连接还是新连接发生的：

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.

合作伙伴副本 (replica) 可能无法检测到超时。如果存在，则可能会报告消息 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向错误日志报告的示例：如果在主副本 (replica) 上停止镜像终结点，辅助副本 (replica) 将检测到连接超时，辅助副本 (replica) 错误日志中报告消息 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.

在此示例中，主副本 (replica) 未检测到任何连接超时，因为它仍可以与辅助数据库通信，在此示例中，它报告了每个可用性组数据库 (的消息 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.

副本 (replica) 连接超时的原因

应用程序问题

SQL Server可能由于多种原因而繁忙，并且不会在可用性组SESSION_TIMEOUT期间内为镜像终结点连接提供服务。 这会导致连接超时。其中一些原因包括：

• SQL Server体验到 100% 的 CPU 利用率。 这意味着SQL Server或其他某个应用程序一次会驱动 CPU 数秒。

• SQL Server遇到无收益计划程序事件。 如果线程不及时生成，SQL Server线程负责将计划程序 (CPU) 生成给其他线程来完成其工作。

• SQL Server遇到工作线程耗尽、内存不足问题或应用程序问题，这些问题会影响其为镜像终结点连接提供服务的能力。

网络问题

这要求在触发错误时收集主要副本和辅助副本上的网络跟踪日志。 为此，可以检查网络延迟和丢弃的数据包。

如何诊断副本 (replica) 连接超时

对于阻止SQL Server维护与合作伙伴副本 (replica) 连接的应用程序问题，本部分介绍如何分析SQL Server日志。 这些提示可帮助你确定副本 (replica) 连接超时的根本原因。 本部分最后提供了有关如何在连接超时时收集网络跟踪的更高级指南，以便检查网络状态。

评估副本 (replica) 连接超时的计时和位置

查看连接超时的历史记录、频率和趋势。 使用在SQL Server错误日志中找到的消息是执行此操作的好方法。 哪里报告了连接超时？ 它们是在主副本 (replica) 还是辅助副本 (replica) 上一致报告？ 错误何时发生？ 它们是否发生在每月的某个星期、星期几或一天中的某个时间？ 其他计划维护或批处理是否对应于观察到连接超时的时间？ 此评估可帮助你确定连接超时的范围并关联，以确定根本原因。

查看AlwaysOn_health扩展事件会话

扩展AlwaysOn_health事件会话已得到增强，以包括 ucs_connection_setup 事件，当副本 (replica) 与其合作伙伴副本 (replica) 建立连接时触发该事件。 排查连接超时问题时，这非常有用。

注意

扩展ucs_connection_setup事件已添加到最新的SQL Server累积更新。 必须运行最新的累积更新才能观察此扩展事件。

(DMV) 查询Always On分布式管理视图

可以查询Always On DMV，了解有关副本 (replica) 的连接状态的详细信息。 此查询仅报告连接状态，以及问题发生时与连接超时关联的任何错误。 如果连接问题是间歇性的，查询可能无法轻松捕获断开连接状态。

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

以下示例显示持续断开连接状态，因为主副本 (replica) 上的镜像终结点已停止。 通过查询主副本 (replica) ，ALWAYS ON DMV 可以报告主副本和所有辅助副本， (在主副本 (replica) ) 上禁用终结点。

通过查询辅助副本 (replica) ，Always On DMV 仅报告辅助副本 (replica) 。

查看Always On扩展事件会话

1. 使用 SQL Server Management Studio (SSMS) 对象资源管理器 连接到每个副本 (replica) ，并打开AlwaysOn_health扩展事件文件。

2. 在 SSMS 中，转到 “文件>打开”，然后选择“ 合并扩展事件文件”。

3. 选择“添加”按钮。

4. 在“文件打开”对话框中，导航到 SQL Server \LOG 目录中的文件。

5. 按 Control，然后选择名称以 “AlwaysOn_healthxxx.xel”开头的文件。

6. 选择“ 打开”，然后选择“ 确定”。

   应该会在 SSMS 中看到一个新的选项卡式窗口，其中显示了 AlwaysOn 事件。

   以下屏幕截图显示了AlwaysOn_health辅助副本 (replica) 中的数据。 第一个大纲框显示主副本 (replica) 上的终结点停止后的连接丢失。 第二个大纲框显示辅助副本 (replica) 下次尝试连接到主副本 (replica) 时发生的连接失败。

   

检查非生成事件是否导致连接超时

可用性副本 (replica) 无法为合作伙伴副本 (replica) 连接提供服务的最常见原因之一是计划程序不合格。 有关无收益计划程序的详细信息，请参阅故障排除SQL Server计划和收益。

SQL Server跟踪短于 5 到 10 秒的非生成计划程序事件。 它会在组件输出的数据 TrackingNonYieldingScheduler 点中 sp_server_diagnostics query_processing 报告这些事件。

若要检查可能导致副本 (replica) 连接超时的非生成事件，请执行以下步骤：

1. 创建每五秒记录 sp_server_diagnostics 一次的 SQL 代理作业。

2. 在未报告连接超时的服务器上计划此作业。也就是说，如果服务器 A 副本 (replica) 在其错误日志中报告副本 (replica) 连接超时，请在合作伙伴副本 (replica) 服务器 B 上设置 SQL 代理作业。或者，如果两个副本上都显示连接超时，请在这两个副本上创建作业。

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 报告副本 (replica) 连接超时。 因此，已在 上SQL19AGN2创建了一个 SQL 代理作业，合作伙伴副本 (replica) 。 然后，错误日志 07：24：31 报告 SQL19AGN1 了连接超时。

接下来，在报告时间左右检查运行sp_server_diagnostics的 SQL 代理作业的输出，特别是检查 TrackingNonYieldingScheduler 组件输出中的数据 query_processing 点。 输出报告，在服务器SQL19AGN2 (07：24：33) ，SQL19AGN1 (07：24：31 SQL19AGN1 (07：24：31 报告副本 (replica) 连接超时时， (跟踪为非零十六进制值) ) 。

注意

以下 sp_server_diagnostics 输出连接在一起，以显示 create_time (时间戳) 和 query_processing TrackingNonYieldingScheduler 结果。

调查无收益计划程序事件

如果从前面的诊断步骤中验证了非生成事件是否导致副本 (replica) 连接超时：

1. 确定运行非生成事件时在 SQL Server 中运行的工作负荷。

2. 与副本 (replica) 连接超时类似，请在这些事件发生的月份、日期或星期内查找这些事件的趋势。

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. 在发生连接超时的两个副本上启动网络跟踪，如以下代码片段所示：

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

2. 创建停止 netsh 跟踪事件 35206 或 35267 的 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
   

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 秒。 可以通过调整可用性组副本 (replica) SESSION_TIMEOUT属性来缓解连接超时。 此设置按副本 (replica) 。 针对主数据库和每个受影响的辅助副本 (replica) 调整它。 下面是语法示例。 默认值为 SESSION_TIMEOUT 10。 因此，可以使用 15 作为下一个值。

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