Solución de problemas de tiempos de espera de conexión intermitentes entre réplicas de grupo de disponibilidad

Este artículo le ayuda a diagnosticar los tiempos de espera de conexión intermitentes que se notifican entre réplicas de grupo de disponibilidad.

Síntomas y efectos de tiempos de espera intermitentes de conexión de réplica de grupo de disponibilidad

La consulta de réplicas principales y secundarias devuelve resultados diferentes

Las cargas de trabajo de solo lectura que consultan réplicas secundarias pueden consultar datos obsoletos. Si se producen tiempos de espera intermitentes de conexión de réplica, los cambios en los datos de la base de datos de réplica principal aún no se reflejan en la base de datos secundaria al consultar los mismos datos. Para obtener más información, consulte la sección Latencia de datos en la réplica secundaria .

Grupo de disponibilidad de informes de diagnóstico no sincronizado

El panel de Always On de SQL Server Management Studio podría notificar un grupo de disponibilidad incorrecto que tenga réplicas en un estado No sincronizado. También puede observar que las réplicas de informe del panel de Always On están en el estado No sincronizando.

Al revisar los registros de error SQL Server de esas réplicas, es posible que observe mensajes como los siguientes que indican que se ha producido un tiempo de espera de conexión entre las réplicas del grupo de disponibilidad:

Registro de errores de la réplica principal

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.

Registro de errores de la réplica secundaria

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.

Los problemas de conexión intermitentes pueden afectar a la preparación de la conmutación por error de una réplica secundaria.

Si configura el grupo de disponibilidad para la conmutación por error automática y el asociado de conmutación por error de confirmación sincrónica está desconectado intermitentemente de la principal, la conmutación por error automática podría no funcionar correctamente.

Puede consultar sys.dm_hadr_database_replia_cluster_states para determinar si la base de datos del grupo de disponibilidad está lista para la conmutación por error en ese momento. Este es un ejemplo de los resultados si el punto de conexión de creación de reflejo se detuvo en la réplica secundaria:

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'

Es posible que la conmutación automática por error no ponga en línea el grupo de disponibilidad en el rol principal del equipo asociado de conmutación por error si la conmutación por error coincide con el tiempo de espera de una conexión de réplica.

¿Qué indican los errores de tiempo de espera de conexión?

El valor predeterminado es de 10 segundos para la configuración de réplica del grupo de disponibilidad, SESSION_TIMEOUT. Esta configuración está configurada para cada réplica. Determina cuánto tiempo espera la réplica para recibir una respuesta de su réplica de asociado antes de que notifique un tiempo de espera de conexión. Si una réplica no recibe ninguna respuesta de la réplica del asociado, notifica un tiempo de espera de conexión en el registro de errores de Microsoft SQL Server y en el registro de aplicaciones de Windows. La réplica que notifica el tiempo de espera intenta volver a conectarse inmediatamente y seguirá intentarlo cada cinco segundos.

Normalmente, solo una réplica detecta y notifica el tiempo de espera de conexión. Sin embargo, ambas réplicas podrían notificar el tiempo de espera de la conexión al mismo tiempo. Hay diferentes versiones de este mensaje, dependiendo de si el tiempo de espera de conexión se produjo mediante una conexión establecida anteriormente o una nueva conexión:

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.

Es posible que la réplica del asociado no detecte un tiempo de espera. Si lo hace, podría notificar el mensaje 35201 o 35206. Si no lo hace, notifica una pérdida de conexión a cada una de las bases de datos del grupo de disponibilidad:

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.

Este es un ejemplo de lo que SQL Server notifica al registro de errores: si detiene el punto de conexión de creación de reflejo en la réplica principal, la réplica secundaria detecta un tiempo de espera de conexión y los mensajes 35206 y 35267 se notifican en el registro de errores de réplica secundaria:

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.

En este ejemplo, la réplica principal no detectó ningún tiempo de espera de conexión porque todavía podría comunicarse con la secundaria y notificó el mensaje 35267 para cada base de datos de grupo de disponibilidad (en este ejemplo, solo hay una base de datos, "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.

Causas de los tiempos de espera de conexión de réplica

Problema de la aplicación

SQL Server puede estar ocupado por alguna de las diversas razones y no presta servicio a la conexión de punto de conexión de creación de reflejo dentro del período del grupo SESSION_TIMEOUT de disponibilidad. Esto hace que se agote el tiempo de espera de la conexión. Algunas de estas razones son:

• SQL Server experimenta un uso de CPU del 100 %. Esto significa que SQL Server o alguna otra aplicación está impulsando la CPU durante segundos a la vez.

• SQL Server experimenta eventos de programador que no producen resultados. SQL Server subprocesos son responsables de producir el programador (CPU) a otros subprocesos para completar su trabajo si un subproceso no se produce de forma oportuna.

• SQL Server experimenta agotamiento de subprocesos de trabajo, problemas de memoria insuficiente o problemas de aplicación que afectan a su capacidad para atender la conexión del punto de conexión de creación de reflejo.

Problema de red

Esto requiere que recopile registros de seguimiento de red en las réplicas principal y secundaria cuando se desencadene el error. Para ello, puede examinar la latencia de red y los paquetes eliminados.

Diagnóstico de tiempos de espera de conexión de réplica

Para el problema de los problemas de la aplicación que impiden que SQL Server de mantenimiento de la conexión con la réplica del asociado, en esta sección se explica cómo analizar los registros de SQL Server. Estas sugerencias pueden ayudarle a identificar la causa principal de los tiempos de espera de conexión de réplica. Esta sección termina con instrucciones más avanzadas sobre cómo recopilar seguimientos de red cuando se producen los tiempos de espera de conexión para que pueda comprobar el estado de la red.

Evaluación del tiempo de espera y la ubicación de los tiempos de espera de conexión de réplica

Revise el historial, la frecuencia y las tendencias de los tiempos de espera de conexión. El uso de los mensajes que se encuentran en el registro de errores de SQL Server es una excelente manera de hacerlo. ¿Dónde se notifican los tiempos de espera de conexión? ¿Se notifican de forma coherente en la réplica principal o secundaria? ¿Cuándo se produjeron los errores? ¿Ocurrieron en una semana determinada del mes, día de la semana o hora del día? ¿Otro mantenimiento programado o procesamiento por lotes corresponde a las horas en las que se observan los tiempos de espera de conexión? Esta evaluación puede ayudarle a limitar y correlacionar los tiempos de espera de conexión para identificar la causa principal.

Revisar la AlwaysOn_health sesión de eventos extendida

La AlwaysOn_health sesión de eventos extendida se ha mejorado para incluir el ucs_connection_setup evento, que se desencadena cuando una réplica establece una conexión con su réplica de asociado. Esto puede ser útil al solucionar problemas de tiempo de espera de conexión.

Nota:

El ucs_connection_setup evento extendido se agregó a las últimas actualizaciones acumulativas SQL Server. Debe ejecutar las actualizaciones acumulativas más recientes para observar este evento extendido.

Consultas Always On vistas de administración distribuida (DMV)

Puede consultar Always On DMV para obtener más información sobre el estado conectado de la réplica. Esta consulta notifica solo el estado conectado y los errores asociados al tiempo de espera de conexión en el momento en que se producen los problemas. Si los problemas de conexión son intermitentes, es posible que la consulta no capture fácilmente el estado desconectado.

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

En el ejemplo siguiente se muestra un estado desconectado sostenido porque se detuvo el punto de conexión de creación de reflejo en la réplica principal. Al consultar la réplica principal, el Always On DMV puede informar sobre la réplica principal y todas las réplicas secundarias (el punto de conexión está deshabilitado en la réplica principal).

Al consultar la réplica secundaria, el Always On DMV informa solo de la réplica secundaria.

Revisión de la Always On sesión de eventos extendida

1. Conéctese a cada réplica mediante SQL Server Management Studio (SSMS) Explorador de objetos y abra los archivos de AlwaysOn_health eventos extendidos.

2. En SSMS, vaya a Archivo>abierto y, a continuación, seleccione Combinar archivos de eventos extendidos.

3. Seleccione el botón Agregar.

4. En el cuadro de diálogo Abrir archivo, vaya a los archivos del directorio SQL Server \LOG.

5. Presione Control y, a continuación, seleccione los archivos cuyo nombre comienza por "AlwaysOn_healthxxx.xel".

6. Seleccione Abrir y, después, Aceptar.

   Debería ver una nueva ventana con pestañas en SSMS que muestra los eventos AlwaysOn.

   En la captura de pantalla siguiente se muestran los AlwaysOn_health datos de la réplica secundaria. El primer cuadro descrito muestra la pérdida de conexión después de detener el punto de conexión en la réplica principal. El segundo cuadro descrito muestra el error de conexión que se produce la próxima vez que la réplica secundaria intenta conectarse a la réplica principal.

   

Compruebe si los eventos que no producen provocan tiempos de espera de conexión

Una de las razones más comunes por las que una réplica de disponibilidad no puede dar servicio a la conexión de réplica del asociado es un programador que no produce resultados. Para obtener más información sobre los programadores que no producen rendimiento, consulte Solución de problemas SQL Server programación y rendimiento.

SQL Server realiza un seguimiento de los eventos del programador que no producen resultados que son tan cortos como 5 a 10 segundos. Notifica estos eventos en el punto de TrackingNonYieldingScheduler datos de la salida del sp_server_diagnostics query_processing componente.

Para comprobar si hay eventos que no producen resultados que puedan provocar tiempos de espera de conexión de réplica, siga estos pasos:

1. Cree un trabajo del Agente SQL que registre sp_server_diagnostics cada cinco segundos.

2. Programe este trabajo en el servidor que no notifica el tiempo de espera de conexión. Es decir, si la réplica del servidor A notifica el tiempo de espera de conexión de la réplica en su registro de errores, configure el trabajo del Agente SQL en la réplica del asociado, servidor B. Como alternativa, si ve tiempos de espera de conexión en ambas réplicas, cree el trabajo en ambas réplicas.

3. Ejecute el siguiente archivo por lotes para crear un trabajo que se ejecute sp_server_diagnostics cada cinco segundos, anexe la salida a un archivo de texto y, a continuación, inicie el trabajo. El comando del ejemplo siguiente se sp_server_diagnostics 5 ejecuta cada cinco segundos. Por lo tanto, no es necesario programar este trabajo para que se ejecute cada cinco segundos, simplemente inicie el trabajo y se ejecutará hasta que se detenga, cada cinco segundos:

   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'
   

   Nota:

   En estos comandos, cambie @output_file_name a una ruta de acceso válida y proporcione un nombre de archivo.

Análisis de los resultados

Cuando se notifica un tiempo de espera de conexión, anote la marca de tiempo del evento de tiempo de espera que se muestra en el registro de errores de SQL Server. En el caso de las réplicas del ejemplo siguiente, SQL19AGN1 se notificaba el tiempo de espera de conexión de la réplica. Por lo tanto, se creó un trabajo del Agente SQL en SQL19AGN2, la réplica del asociado. A continuación, se notificó un tiempo de espera de conexión en el SQL19AGN1 registro de errores a las 07:24:31.

A continuación, la salida del trabajo del Agente SQL que se ejecuta sp_server_diagnostics se comprueba aproximadamente en la hora notificada, revisando específicamente el TrackingNonYieldingScheduler punto de datos en la salida del query_processing componente. La salida informa de que se ha realizado un seguimiento de un programador que no produce resultados (como un valor hexadecimal distinto de cero) en el servidor SQL19AGN2 (a las 07:24:33) alrededor del momento en que el tiempo de espera de conexión de la réplica se notificó en SQL19AGN1 (a las 07:24:31).

Nota:

La salida siguiente sp_server_diagnostics se concatena para mostrar los create_time resultados (marca de tiempo) y query_processing TrackingNonYieldingScheduler .

Investigación de un evento de programador que no produce resultados

Si ha comprobado desde los pasos de diagnóstico anteriores que un evento que no produce resultados ha provocado el tiempo de espera de conexión de la réplica:

1. Identifique las cargas de trabajo que se ejecutan en SQL Server en el momento en que se ejecutan los eventos que no producen.

2. De forma similar a los tiempos de espera de conexión de réplica, busque tendencias en estos eventos durante el mes, el día o la semana en que se producen.

3. Recopile el seguimiento del monitor de rendimiento en el sistema en el que se detectó el evento que no produce.

4. Recopile contadores clave de rendimiento para los recursos del sistema, incluidos Processor::% Processor Time, Memory::Available MBytes, Logical Disk::Avg Disk Queue Length y Logical Disk::Avg Disk sec/Transfer.

5. Si es necesario, abra un incidente de soporte técnico de SQL Server para obtener más ayuda en la búsqueda de la causa principal de estos eventos que no producen. Comparta los registros que ha recopilado para su análisis posterior.

Recopilación avanzada de datos: recopilación de seguimiento de red durante el tiempo de espera de conexión

Si el diagnóstico anterior de la aplicación SQL Server no produjo una causa principal, debe comprobar la red. El análisis correcto de la red requiere que recopile un seguimiento de red que cubra el tiempo de espera de la conexión.

El siguiente procedimiento inicia un seguimiento de red de Windows netsh en las réplicas en las que se notifican los tiempos de espera de conexión en los registros de error de SQL Server. Una tarea de eventos programados de Windows se desencadena cuando se registra uno de los errores de conexión de SQL Server en el registro de aplicaciones. La tarea programada ejecuta un comando para detener el netsh seguimiento de red para que los datos de seguimiento de red clave no se sobrescriban. Estos pasos también asumen una ruta de acceso de *F:* para los registros por lotes y de seguimiento. Ajuste esta ruta de acceso al entorno.

1. Inicie un seguimiento de red, como se muestra en el siguiente fragmento de código, en las dos réplicas en las que se producen los tiempos de espera de conexión:

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

2. Cree tareas programadas de Windows que detengan el netsh seguimiento en los eventos 35206 o 35267. Puede crear estas tareas en una línea de comandos administrativa:

   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. Una vez que se produce el evento y se detienen y capturan los seguimientos de red, puede eliminar las ONEVENT tareas:

   PS C:\Users\sqladmin> Schtasks /Delete /tn Event35206Task /F
   PS C:\Users\sqladmin> Schtasks /Delete /tn Event35267Task /F
   

El análisis del seguimiento de red está fuera del ámbito de este solucionador de problemas. Si no puede interpretar el seguimiento de red, póngase en contacto con el equipo de soporte técnico de Microsoft SQL Server y proporcione el seguimiento junto con otros archivos de registro solicitados para el análisis de la causa raíz.

¿Qué más puedo hacer para mitigar los tiempos de espera de conexión?

El grupo de disponibilidad predeterminado, SESSION_TIMEOUT, está configurado durante 10 segundos. Es posible que pueda mitigar los tiempos de espera de conexión ajustando la propiedad de réplica SESSION_TIMEOUT del grupo de disponibilidad. Esta configuración es por réplica. Ajústelo para la réplica principal y cada réplica secundaria afectada. Este es un ejemplo de la sintaxis. El valor predeterminado SESSION_TIMEOUT es 10. Por lo tanto, podría usar 15 como el siguiente valor.

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