Solucionar problemas de conmutación por error de grupos de disponibilidad Always On

Resumen

En este artículo se proporcionan pasos para solucionar problemas que le ayudarán a determinar por qué el grupo de disponibilidad AlwaysOn conmutó por error en SQL Server. Explica cómo identificar eventos de estado en el registro del clúster de Windows, diagnosticar causas comunes, como eventos de estado del clúster, tiempos de espera de arrendamiento, tiempos de espera de comprobación de estado y problemas de estado de SQL Server, y aplicar soluciones para cada uno de ellos.

Cómo afectan los problemas de mantenimiento y la conmutación por error de AlwaysOn a las cargas de trabajo

AlwaysOn implementa una supervisión sólida del estado a través de diferentes mecanismos para garantizar el estado de la instancia de Microsoft SQL Server que hospeda la réplica principal, el clúster subyacente y el estado del sistema. La carga de trabajo de producción se interrumpe momentáneamente cuando se identifica un clúster de Windows o un problema de estado de Always On.

Cuando se detecta un estado de funcionamiento, suele producirse la siguiente secuencia de sucesos. A lo largo de este solucionador de problemas, los eventos de mantenimiento se mencionan en referencia a los siguientes eventos:

• Las réplicas y las bases de datos del grupo de disponibilidad pasan del rol principal al rol de resolución.

• Las bases de datos del grupo de disponibilidad pasan a estar sin conexión y dejan de ser accesibles.

• El clúster de Windows marca como erróneo el recurso en clúster del grupo de disponibilidad.

• El clúster de Windows intenta volver a poner en línea el rol del grupo de disponibilidad (en la réplica asociada de conmutación por error original o automática).

• El rol del grupo de disponibilidad se pone en línea correctamente si Always On y la supervisión del estado del clúster de Windows detectan que está en buen estado.

Si se realiza correctamente, las réplicas y las bases de datos del grupo de disponibilidad pasan a la función principal, y las bases de datos del grupo de disponibilidad se ponen en línea y quedan accesibles para la aplicación.

Las aplicaciones no pueden acceder a las bases de datos del grupo de disponibilidad

Cuando el sistema detecta una condición de estado, la réplica del grupo de disponibilidad y las bases de datos pasan al rol de resolución, y las bases de datos del grupo de disponibilidad quedan sin conexión. Una vez que la réplica entra en línea en el rol principal (en el servidor de réplica original o en el servidor de réplica del asociado de conmutación por error), la réplica y las bases de datos vuelven a estar en línea. Mientras la réplica y las bases de datos se están recuperando y están sin conexión, las aplicaciones que intentan acceder a esas bases de datos del grupo de disponibilidad fallan y generan un mensaje de “Error 983”: Unable to access availability database.... Si SQL Server está configurado para registrar intentos de inicio de sesión erróneos, también registra este error en el registro de errores de Microsoft SQL Server.

Logon Error: 983, Severity: 14, State: 1.

Logon Unable to access availability database '<databasename>' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later.

El período durante el cual el grupo de disponibilidad está en el estado de resolución antes de volver a estar en línea en el rol principal suele durar solo unos segundos o incluso menos de un segundo.

Identifique y diagnostique eventos de estado del grupo de disponibilidad Always On o de conmutación por error

Identificar las tendencias del estado de Always On

Puede investigar un único evento de estado de Always On, o puede haber una tendencia reciente o en curso de problemas de estado que interrumpen la producción de forma intermitente. Las siguientes preguntas pueden ayudarle a delimitar y establecer una correlación entre los cambios recientes en su entorno de producción que podrían estar relacionados con estos problemas de estado:

• ¿Cuándo comenzó la tendencia de eventos de mantenimiento de clúster o AlwaysOn?
• ¿Se producen los eventos de estado en un día determinado?
• ¿Se producen los eventos de estado a una hora determinada del día?
• ¿Se producen los eventos de salud en un determinado día o en una determinada semana del mes?

Si detecta una tendencia, compruebe el mantenimiento programado del sistema (el sistema anfitrión en un entorno virtual), los lotes de ETL y otros trabajos que puedan estar relacionados con estos eventos de estado. Si el sistema es una máquina virtual, investigue el sistema host para ver los cambios que posiblemente se introdujeron en el momento de las interrupciones.

Considere las cargas de trabajo de producción puntuales e intensas que podrían coincidir con el momento en que se producen los problemas de estado (por ejemplo, cuando los usuarios inician sesión por primera vez en el sistema o después de volver del almuerzo).

Revisión del registro del clúster

El registro del clúster de Windows es el registro más completo para identificar tanto el tipo de evento de estado de Always On o del clúster como la condición de estado detectada que provocó el evento. Para generar y abrir el registro del clúster, siga estos pasos:

Use Windows PowerShell para generar el registro del clúster de Windows en el nodo de clúster que hospeda la réplica principal en el momento del evento de mantenimiento. Por ejemplo, ejecute el siguiente cmdlet en una ventana de PowerShell con privilegios elevados mediante sql19agn1 como nombre de servidor basado en SQL Server:

get-clusterlog -Node sql19agn1 -UseLocalTime     

Busque el evento de estado en el registro del clúster

Always On usa varios mecanismos de supervisión del estado para controlar el estado del grupo de disponibilidad. Además de un evento de mantenimiento del clúster de Windows (en el que el clúster de Windows detecta un problema de mantenimiento entre los nodos del clúster), AlwaysOn tiene cuatro tipos diferentes de comprobaciones de estado:

• El servicio SQL Server no se está ejecutando
• Agotamiento del tiempo de espera del arrendamiento de SQL Server
• Tiempo de espera de comprobación de estado de SQL Server
• Un problema de mantenimiento interno de SQL Server

Puede localizar cualquiera de estos eventos de estado específicos de Always On buscando la cadena [hadrag] Resource Alive result 0 en el registro del clúster. El registro del clúster guarda esta cadena cuando detecta cualquiera de estos eventos. Por ejemplo:

00001334.00002ef4::2019/06/24-18:24:36.153 ERR [RES] SQL Server Availability Group : [hadrag] Resource Alive result 0.

Puede usar una herramienta para buscar todos los eventos de mantenimiento en el registro de clúster para que pueda generar un informe de resumen de los problemas de mantenimiento de AlwaysOn. Este informe puede ayudarle a identificar tendencias cronológicas y determinar si una condición de mantenimiento AlwaysOn determinada es periódica. En la captura de pantalla siguiente se muestra cómo usar un editor de texto (NotePad++, en este caso) para buscar todas las líneas del registro del clúster que contienen la [hadrag] Resource Alive result 0 cadena:

Identificar y corregir el problema de mantenimiento que desencadenó la conmutación por error

Para identificar problemas de estado en el registro del clúster de la réplica principal, compárelos con los problemas descritos en las secciones siguientes. Las razones habituales de la conmutación por error de un AG son las siguientes:

• Evento de estado del clúster
• El servicio SQL Server está inactivo (un evento de mantenimiento AlwaysOn)
• Tiempo de espera del arrendamiento (un evento de estado de Always On)
• Tiempo de espera de comprobación de estado (un evento de mantenimiento AlwaysOn)
• Estado de SQL Server (un evento de mantenimiento AlwaysOn)

Eventos de estado del clúster

El clúster de Microsoft Windows supervisa el estado de los servidores miembros del clúster. Si detecta un problema de estado, quita del clúster a un servidor miembro de un clúster. Los recursos del clúster, incluido el rol del grupo de disponibilidad que está alojado en el servidor miembro del clúster que se ha quitado, se mueven a la réplica asociada de conmutación por error del grupo de disponibilidad si está configurada para la conmutación automática por error.

Síntomas

Este es un ejemplo de un evento de estado del clúster en el registro del clúster. Para encontrarlo, busque Lost quorum o Cluster service has terminated, porque puede estar presente uno u otro durante el cambio de rol del grupo de disponibilidad o la conmutación por error.

00000fe4.00001628::2022/12/15-14:26:02.654 WARN [QUORUM] Node 1: Lost quorum (1)
00000fe4.00001628::2022/12/15-14:26:02.654 WARN [QUORUM] Node 1: goingAway: 0, core.IsServiceShutdown: 0
00000fe4.00001628::2022/12/15-14:26:02.654 WARN lost quorum (status = 5925)
00000fe4.00001628::2022/12/15-14:26:02.654 INFO [NETFT] Cluster Service preterminate succeeded.
00000fe4.00001628::2022/12/15-14:26:02.654 WARN lost quorum (status = 5925), executing OnStop
00000fe4.00001628::2022/12/15-14:26:02.654 INFO [DM]: Shutting down, so unloading the cluster database.
00000fe4.00001628::2022/12/15-14:26:02.654 INFO [DM] Shutting down, so unloading the cluster database (waitForLock: false).
000019cc.000019d0::2022/12/15-14:26:02.654 WARN [RHS] Cluster service has terminated. Cluster.Service.Running.Event got signaled.

También puede identificar este evento buscando en el registro de eventos del sistema Windows.

Critical SQL19AGN1.CSSSQL 1135 Microsoft-Windows-FailoverClusterin Node Mgr NT AUTHORITY\SYSTEM Cluster node 'SQL19AGN2' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapters on this node. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.
Critical SQL19AGN1.CSSSQL 1177 Microsoft-Windows-FailoverClusterin Quorum Manager NT AUTHORITY\SYSTEM The Cluster service is shutting down because quorum was lost. This could be due to the loss of network connectivity between some or all nodes in the cluster, or a failover of the witness disk. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapter. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.

Diagnosticar un evento de estado del clúster

Los errores del registro de eventos de Windows (eventos 1135 y 1177) sugieren que la conectividad de red es una causa del evento. Este estado es la razón más común por la que se detecta un problema de estado del clúster. En el ejemplo siguiente se muestra que otros servidores miembros del clúster no se pudieron comunicar con este servidor que hospeda la réplica principal del grupo de disponibilidad y que este problema desencadenó la eliminación del nodo de clúster del clúster:

00000fe4.00001edc::2022/12/14-22:44:36.870 INFO [NODE] Node 1: New join with n3: stage: 'Attempt Initial Connection' status (10060) reason: 'Failed to connect to remote endpoint <endpoint address>'
00000fe4.00001620::2022/12/15-14:26:02.050 INFO [IM] got event: Remote endpoint <endpoint address> unreachable from <endpoint address>
00000fe4.00001620::2022/12/15-14:26:02.050 WARN [NDP] All routes for route (virtual) local <local address> to remote <remote address> are down
00000fe4.0000179c::2022/12/15-14:26:02.053 WARN [NODE] Node 1: Connection to Node 2 is broken. Reason GracefulClose(1226)' because of 'channel to remote endpoint <endpoint address> is closed'

Puede buscar en el registro del clúster evidencias de un error de conexión en el nodo. Desde la ubicación del registro de clúster donde encontró Lost quorum, busque hacia atrás cadenas como Failed to connect to remote endpoint, unreachabley is broken.

Solución

Asegúrese de que la supervisión del estado del clúster sea apropiada para el entorno del host. Para más información sobre los grupos de disponibilidad AlwaysOn de SQL Server hospedados en Microsoft Azure, consulte Introducción al clúster de conmutación por error de Windows Server: SQL Server en máquinas virtuales de Azure.

Si es necesario, considere la posibilidad de ponerse en contacto con Microsoft Windows soporte técnico de alta disponibilidad para abrir un incidente de soporte técnico.

El servicio SQL Server está inactivo: un evento de mantenimiento AlwaysOn

La supervisión de estado de AlwaysOn puede detectar si el servicio SQL Server que hospeda la réplica principal del grupo de disponibilidad ya no se está ejecutando.

Síntomas

Este es un ejemplo del informe del registro del clúster para el rol del grupo de disponibilidad "ag" que indica un fallo porque QueryServiceStatusEx devolvió un identificador de proceso 0:

00001898.0000185c::2023/02/27-13:27:41.121 ERR [RES] SQL Server Availability Group <ag>: [hadrag] QueryServiceStatusEx returned a process id 0
00001898.0000185c::2023/02/27-13:27:41.121 ERR [RES] SQL Server Availability Group <ag>: [hadrag] SQL server service is not alive
00001898.0000185c::2023/02/27-13:27:41.121 ERR [RES] SQL Server Availability Group <ag>: [hadrag] Resource Alive result 0.
00001898.0000185c::2023/02/27-13:27:41.121 WARN [RHS] Resource ag IsAlive has indicated failure.

Diagnóstico de eventos de apagado del servicio SQL

Compruebe el registro de eventos del sistema de Windows y el registro de errores de SQL Server para comprobar si se produjo un cierre inesperado de SQL Server.

Si SQL Server se cerró mediante un apagado del sistema o un apagado administrativo, verá la siguiente entrada en el registro de errores de SQL Server:

2023-03-10 09:38:46.73 spid9s SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.

El registro de eventos del sistema Windows muestra la siguiente entrada de error:

Information 3/10/2023 9:41:06 AM Service Control Manager 7036 None The SQL Server (MSSQLSERVER) service entered the stopped state.

El registro de eventos del sistema De Windows muestra la siguiente entrada de error si SQL Server se cierra inesperadamente:

Error 3/10/2023 8:37:46 AM Service Control Manager 7034 None The SQL Server (MSSQLSERVER) service terminated unexpectedly. It has done this 1 time(s).

Compruebe el final del registro de errores de SQL Server para obtener pistas. Si el registro de errores finaliza abruptamente, esta condición significa que se produjo un cierre forzado. Por ejemplo, si SQL Server se terminó mediante el Administrador de tareas, el informe de errores de SQL Server no revela ninguna información sobre los problemas internos que podrían haber provocado el cierre del proceso.

Solución

Asegúrese de que los administradores de sistema y base de datos autorizados tengan acceso al sistema para minimizar las finalizaciones inesperadas del servicio SQL Server. Después de examinar los registros de eventos, investigue por qué un servicio tuvo que finalizarse inesperadamente.

Si un problema interno de estado de SQL Server provocó que SQL Server finalizara inesperadamente, podría haber indicios de una posible excepción grave (incluida la generación de un archivo de diagnóstico de volcado de memoria) al final del registro de errores de SQL Server. Revise las pistas y tome las medidas necesarias. Si encuentra un archivo de volcado de memoria, considere la posibilidad de ponerse en contacto con el soporte técnico de Microsoft SQL Server y proporcione el contenido del archivo de volcado de memoria y el registro de errores de SQL Server para una investigación más detallada.

Tiempo de espera del arrendamiento: un evento de estado de Always On

AlwaysOn usa un mecanismo de "concesión" para supervisar el estado del equipo donde se instala SQL Server. El tiempo de espera predeterminado de la concesión es de 20 segundos.

Síntomas

Esta es una salida de ejemplo de un tiempo de espera de concesión AlwaysOn del registro del clúster. Puede buscar estas cadenas para localizar un tiempo de espera de una concesión en el registro del clúster.

00001a0c.00001c5c::2023/01/04-15:36:54.762 ERR [RES] SQL Server Availability Group : [hadrag] Availability Group lease is no longer valid 
00001a0c.00001c5c::2023/01/04-15:36:54.762 ERR [RES] SQL Server Availability Group : [hadrag] Resource Alive result 0. 
00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] Lease timeout detected, logging perf counter data collected so far
00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] Date/Time, Processor time(%), Available memory(bytes), Avg disk read(secs), Avg disk write(secs)
00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:35:57.0, 98.068572, 509227008.000000, 0.000395, 0.000350 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:7.0, 12.314941, 451817472.000000, 0.000278, 0.000266 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:17.0, 17.270742, 416096256.000000, 0.000376, 0.000292 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:27.0, 38.399895, 416301056.000000, 0.000446, 0.000304 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:37.0, 100.000000, 417517568.000000, 0.001292, 0.000666

Para obtener más información sobre el tiempo de espera del arrendamiento, consulte la sección Mecanismo de arrendamiento en Mecanismos y directrices sobre los tiempos de espera del arrendamiento, del clúster y de la comprobación de estado para los grupos de disponibilidad de Always On.

Diagnosticar y corregir eventos de tiempo de espera de concesión de Always On

Dos problemas principales pueden desencadenar un tiempo de espera de un arrendamiento:

• Volcado de memoria de SQL Server: cuando SQL Server detecta determinados eventos internos relacionados con el estado, como una violación de acceso, una aserción o un interbloqueo del programador de tareas, genera un archivo de volcado de diagnóstico (.mdmp) en la carpeta \LOG de SQL Server. El proceso de generación de un volcado de memoria suspende la ejecución de SQL Server durante un breve período. Durante ese período, el mecanismo de lease puede detectar la falta de respuesta del servicio y desencadenar una acción. Para obtener más información, consulte Impacto de la generación de volcados de memoria.

• Un problema de rendimiento en todo el sistema: la expiración de un arrendamiento no indica necesariamente un problema en el estado de SQL Server. En su lugar, podría indicar un problema de mantenimiento de todo el sistema que también afecta al estado del servidor basado en SQL Server.

  • Uso elevado de CPU en el sistema (cerca del 100 %).
  • Condiciones de memoria insuficiente: poca memoria virtual y/o uno de los procesos se está paginando.
  • WSFC se queda sin conexión debido a la pérdida de cuórum.
  • Limitación de recursos de las máquinas virtuales que afecta al rendimiento y provoca la expiración del lease.

Solución

Para ver los pasos de solución de problemas detallados, consulte MSSQLSERVER_19407. Estos son los dos problemas más comunes:

1. Diagnóstico del archivo de volcado de SQL Server

SQL Server podría detectar un problema interno de estado, como, por ejemplo, una violación de acceso, un error de aserción o planificadores interbloqueados. En esta situación, el programa genera un archivo de minivolcado (.mdmp) en la carpeta \LOG del proceso de SQL Server para su diagnóstico. El proceso de SQL Server queda bloqueado durante varios segundos mientras el archivo de minivolcado se escribe en disco. Durante este tiempo, todos los subprocesos del proceso de SQL Server están en un estado de suspensión, incluido el subproceso de arrendamiento supervisado por la supervisión de estado de Always On. Por lo tanto, Always On podría detectar un tiempo de espera de arrendamiento.

**Dump thread - spid = 0, EC = 0x0000000000000000
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\LOG\SQLDump0001.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
*   11/02/14 21:21:10 spid 1920
*
* Deadlocked Schedulers
*
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
Stack Signature for the dump is 0x00000000000002BA
Error: 19407, Severity: 16, State: 1.
The lease between availability group 'ag' and the Windows Server Failover Cluster has expired. A connectivity issue occurred between the instance of SQL Server and the Windows Server Failover Cluster. To determine whether the availability group is failing over correctly, check the corresponding availability group resource in the Windows Server Failover Cluster.

Para corregir este problema, examine el diagnóstico del archivo de volcado de memoria para determinar la causa raíz. Considere la posibilidad de ponerse en contacto con el soporte técnico de Microsoft SQL Server para proporcionar el contenido del archivo de volcado y el registro de errores de SQL Server para una investigación más detallada.

2. Uso elevado de CPU u otro problema de rendimiento del sistema

Un tiempo de espera de un arrendamiento indica un problema de rendimiento que afecta a todo el sistema, incluido SQL Server. Para diagnosticar el problema del sistema, los diagnósticos de mantenimiento de Always On registran los datos del Monitor de rendimiento en el registro del clúster e incluyen el evento de expiración del tiempo de espera de concesión. Los datos de rendimiento abarcan aproximadamente los 50 segundos previos al evento de expiración del arrendamiento e incluyen información sobre el uso de CPU, la memoria libre y la latencia del disco.

Este es un ejemplo de los datos de rendimiento registrados en los que se muestra una expiración del arrendamiento en el registro del clúster. En esta salida de ejemplo, la utilización general elevada de la CPU podría estar relacionada con el tiempo de espera de la concesión.

00000f90.000015c0::2020/08/07-14:16:41.378 WARN [RES] SQL Server Availability Group: [hadrag] Lease timeout detected, logging perf counter data collected so far
00000f90.000015c0::2020/08/07-14:16:41.382 WARN [RES] SQL Server Availability Group: [hadrag] Date/Time, Processor time(%), Available memory(bytes), Avg disk read(secs), Avg disk write(secs)
00000f90.000015c0::2020/08/07-14:16:41.431 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:15:20.0, 83.266073, 31700828160.000000, 0.018094, 0.015752
00000f90.000015c0::2020/08/07-14:16:41.431 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:15:30.0, 93.653224, 31697063936.000000, 0.038590, 0.026897
00000f90.000015c0::2020/08/07-14:16:41.431 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:15:40.0, 94.270691, 31696265216.000000, 0.166000, 0.038962
00000f90.000015c0::2020/08/07-14:16:41.434 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:15:50.0, 90.272016, 31695409152.000000, 0.215141, 0.106084
00000f90.000015c0::2020/08/07-14:16:41.434 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:16:1.0, 99.991336, 31695892480.000000, 0.046983, 0.035440

Si los datos de rendimiento muestran un uso elevado de la CPU, falta de memoria o una latencia de disco alta en el momento en que se produce un tiempo de espera de concesión, comience a recopilar datos del Monitor de rendimiento durante todo el día en la réplica principal para investigar estos síntomas. Al capturar datos del Monitor de rendimiento durante un período más largo, podrá identificar mejor los valores de referencia y los valores máximos de estos recursos, así como supervisar sus cambios cuando se agota el tiempo de espera de una concesión. A medida que recopila estos datos, considere si hay determinadas cargas de trabajo programadas o ad hoc en SQL Server que se correlacionan con el momento de estos problemas de recursos y eventos de mantenimiento.

También debe capturar contadores que notifiquen el mismo uso de recursos del sistema, incluidos los siguientes:

• Processor Information::% Processor Time
• Memory::Available MBytes
• Logical Disk::Avg. Disk sec/Read
• Logical Disk::Avg. Disk sec/Write
• Logical Disk::Avg. Disk Read Queue Length
• Logical Disk::Avg. Disk Write Queue Length
• MSSQLServer:SQL Statistics::Batch Requests/sec

Tiempo de espera de comprobación de estado: un evento de mantenimiento AlwaysOn

AlwaysOn usa un mecanismo de comprobación de estado para supervisar el estado de SQL Server y la capacidad de que las aplicaciones cliente se conecten.

Síntomas

Cuando una réplica de un grupo de disponibilidad pasa a desempeñar el rol principal, la supervisión del estado de Always On establece una conexión ODBC local con la instancia de SQL Server. Aunque AlwaysOn está conectado y supervisando, si SQL Server no responde a través de la conexión ODBC dentro del período establecido para el tiempo de espera de comprobación de estado del grupo de disponibilidad (el valor predeterminado es 30 segundos), AlwaysOn desencadena un evento de tiempo de espera de comprobación de estado. En esta situación, el grupo de disponibilidad pasa del rol principal al rol de Resolución e inicia la conmutación por error, si está configurado para ello.

Para obtener más información sobre los tiempos de espera de la comprobación de estado, consulte la sección "Funcionamiento del tiempo de espera de la comprobación de estado" en Aspectos técnicos y directrices sobre los tiempos de espera de arrendamiento, clúster y comprobación de estado para los grupos de disponibilidad Always On.

A continuación se muestra un tiempo de espera de la comprobación de estado de Always On, tal como se informa en el registro del clúster:

0000211c.00002d70::2021/02/24-02:50:01.890 WARN [RES] SQL Server Availability Group: [hadrag] Failed to retrieve data column. Return code -1
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group <AG>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group <AG>: [hadrag] Resource Alive result 0.
0000211c.00002594::2021/02/24-02:50:02.453 WARN [RHS] Resource AG IsAlive has indicated failure.
00001278.00002ed8::2021/02/24-02:50:02.453 INFO [RCM] HandleMonitorReply: FAILURENOTIFICATION for 'AG', gen(0) result 1/0.

Diagnosticar y corregir eventos de tiempo de espera en las comprobaciones de estado de Always On

La siguiente sección le ayuda a revisar los registros de SQL Server en busca de eventos "breadcrumb" que pueda encontrar y que estén relacionados con los tiempos de espera de las comprobaciones de estado de Always On que se detectan y notifican. Los registros que revise aquí incluyen el registro del clúster (donde se confirma el tiempo de espera de comprobación de estado), los system_health registros de eventos extendidos y los registros de errores de SQL Server (ambos se encuentran en la carpeta SQL Server \LOG) y el registro de eventos del sistema de Windows. Use estos y otros registros para buscar eventos de correlación que puedan ayudarle a determinar la causa del tiempo de espera de la comprobación de estado.

1. Comprobar si hay eventos de programador que no producen ningún rendimiento

El tiempo de espera de la comprobación de estado de Always On suele deberse a eventos de “no cesión” en SQL Server. Cuando SQL Server detecta que un subproceso no se ha producido en un programador, informa de que se produjo un evento de programador que no produce ningún rendimiento. Si ve otras tareas en el mismo programador que no reciben tiempo de CPU, esta condición es el signo principal de un programador que no produce rendimiento. Este comportamiento puede provocar un retraso en la ejecución de esas tareas y privar de tiempo de CPU a las cargas de trabajo asignadas a un determinado planificador.

Para comprobar si hay eventos del programador que no responden, siga estos pasos:

1. Compruebe los registros de eventos extendidos de SQL Server system_health para determinar si se notificó un evento de programador sin cesión de algún tipo en torno al momento en que se produjo el evento de tiempo de espera de la comprobación de estado de Always On. Entre los eventos no cedidos que podría encontrar se incluyen los siguientes:

   • scheduler_monitor_non_yielding_ring_buffer_recorded
   • scheduler_monitor_non_yielding_iocp_ring_buffer_recorded
   • scheduler_monitor_stalled_dispatcher_ring_buffer_recorded
   • scheduler_monitor_non_yielding_rm_ring_buffer_recorded

2. Abra los registros de eventos extendidos de estado del sistema de SQL Server en la réplica principal a la hora del tiempo de espera de comprobación de estado sospechoso.

3. En SQL Server Management Studio (SSMS), vaya a Abrir archivo> y seleccione Combinar archivos de eventos extendidos.

4. Seleccione el botón Agregar.

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

6. Mantenga presionada la tecla Control y, a continuación, seleccione los archivos cuyos nombres comienzan por system_health_xxx.xel.

7. Seleccione Abrir>aceptar.

8. Filtre los resultados. Haga clic con el botón derecho en un evento en la columna de nombre y seleccione Filtrar por este valor.

   

9. Defina un filtro para ordenar las filas en las que los valores de la columna name contienen yield, como se muestra en la captura de pantalla siguiente. Este filtro devuelve todo tipo de eventos no productivos que podrían haberse registrado en los registros de system_health.

   

10. Compare las marcas de tiempo para ver si hubo eventos sin cesión en el momento en que se agotó el tiempo de espera de la comprobación de estado. Este es el tiempo de espera de comprobación de estado que se indica en el registro del clúster:

    0000211c.00002594::2021/02/24-21:50:02.452 ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost 0000211c.00002594::2021/02/24-21:50:02.452 ERR [RES] SQL Server Availability Group < SQL19AGN1>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel 0000211c.00002594::2021/02/24-21:50:02.452 ERR [RES] SQL Server Availability Group < SQL19AGN1: [hadrag] Resource Alive result 0.
    

    Puede ver que se produjeron eventos sin respuesta cuando se agotó el tiempo de espera de la comprobación de estado.

    

Si se detectan eventos no receptivos, compruebe la causa del evento no receptivo. Considere ponerse en contacto con el equipo de soporte técnico de SQL Server para investigar los eventos sin cesión.

2. Compruebe el registro de errores de SQL Server.

Compruebe el registro de errores de SQL Server para ver si hay eventos relacionados en el momento en que se agotó el tiempo de espera de la comprobación de estado. Estos eventos podrían proporcionar indicios que apunten a pasos adicionales para acotar la causa raíz de los tiempos de espera de las comprobaciones de estado.

Por ejemplo, la siguiente entrada del registro muestra que se produjo un tiempo de espera en una comprobación de estado en el registro del clúster:

0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost 
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group <SQL19AGN1>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel 
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group <SQL19AGN1>: [hadrag] Resource Alive result 0.

En el registro de errores de SQL Server, a los pocos segundos de que se agotara el tiempo de espera de la comprobación de estado, SQL Server informa de que detectó una latencia grave de E/S:

2021-02-23 20:49:54.64 spid12s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\agdb_log.ldf] in database id 12. The OS file handle is 0x0000000000001594. The offset of the latest long I/O is: 0x000030435b0000. The duration of the long I/O is: 26728 ms.

Revise el registro de eventos del sistema para ver posibles pistas del sistema que podrían estar relacionadas con el evento de tiempo de espera de comprobación de estado. Al revisar el registro de eventos del sistema de Windows, es posible que encuentres un problema de E/S del que se informa al mismo tiempo, coincidiendo con el mismo tiempo de espera de la comprobación de estado:

02/23/2021,08:50:16 PM,Warning,SQL19AGN1.CSSSQL.local.local,<...>,"Reset to device, \Device\<device ID>, was issued."
02/23/2021,08:50:16 PM,Warning,SQL19AGN1.CSSSQL.local.local,<...>,"The IO operation at logical block address <block address> for Disk 6 (PDO name: \Device\<device ID>) was retried."

Estado de SQL Server: un evento de mantenimiento AlwaysOn

AlwaysOn supervisa diferentes tipos de eventos de mantenimiento de SQL Server. Mientras hospeda una réplica principal de un grupo de disponibilidad, SQL Server ejecuta continuamente sp_server_diagnostics, que informa sobre el estado de SQL Server mediante distintos componentes. Cuando se detectan problemas de estado, sp_server_diagnostics informa de un error para ese componente concreto y, a continuación, envía los resultados de vuelta al proceso de detección de estado de Always On. Cuando se notifica un error, el rol del Grupo de disponibilidad muestra el estado de error y la posible conmutación por error si el grupo de disponibilidad está configurado para ello.

Síntomas

Este es un ejemplo de un problema de estado de SQL Server, tal como lo informa sp_server_diagnostics en el registro del clúster. SQL Server informa a la supervisión de estado de Always On de un estado de "error" en el componente del sistema, y el grupo de disponibilidad "contoso-ag" pasa a un estado de error.

INFO [RES] SQL Server Availability Group: [hadrag] SQL Server component 'system' health state has been changed from 'warning' to 'error' at 2019-06-20 15:05:52.330
ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, the state of system component is error
ERR [RES] SQL Server Availability Group <contoso-ag>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel
ERR [RES] SQL Server Availability Group <contoso-ag>: [hadrag] Resource Alive result 0.
ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, the state of system component is error
WARN [RHS] Resource contoso-ag IsAlive has indicated failure.
INFO [RCM] HandleMonitorReply: FAILURENOTIFICATION for 'contoso-ag', gen(0) result 1/0.

Diagnosticar eventos de estado de SQL Server

El tipo de problema de estado detectado por la comprobación de estado de SQL Server debe orientar el análisis de la causa raíz.

De forma predeterminada, al implementar un grupo de disponibilidad, FAILURE_CONDITION_LEVEL se establece en tres. Esto activa la supervisión de algunos perfiles de estado de SQL Server, pero no de todos. En el nivel predeterminado, AlwaysOn desencadena un evento de mantenimiento cuando SQL Server genera demasiados archivos de volcado, una infracción de acceso de escritura o un bloqueo por subproceso huérfano. Establecer el grupo de disponibilidad en el nivel cuatro o cinco amplía los tipos de problemas de estado de SQL Server que se supervisan. Para obtener más información sobre los monitores AlwaysOn de mantenimiento de SQL Server, consulte Configuración de una directiva de conmutación automática automática flexible para un grupo de disponibilidad: SQL Server AlwaysOn.

Para identificar el problema específico de estado de Always On, siga estos pasos:

1. Abra los registros de eventos extendidos de diagnóstico del clúster de SQL Server en la réplica principal correspondientes al momento en que se produjo el presunto evento de estado de SQL Server.

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

3. Seleccione Agregar.

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

5. Presione Control, seleccione los archivos cuyos nombres coincidan con <servername>_<instance>_SQLDIAG_xxx.xel y, a continuación, seleccione Abrir>Aceptar.

   

   Verá una nueva ventana con pestañas en SSMS que incluye los eventos extendidos, como se muestra en la captura de pantalla siguiente.

6. Para investigar un problema de mantenimiento de SQL Server, busque el component_health_result cuyo state_desc valor es error. Este es un ejemplo de un evento de componente del sistema que informó de un error a la supervisión de estado AlwaysOn:

   

7. Haga doble clic en la columna de datos en el panel inferior. Esta acción abre los datos detallados del componente en un nuevo panel de ventana de SSMS para su revisión. Así son los datos del componente del sistema:

   

   Los totalDumprequests=186 datos indican que hay demasiados eventos de diagnóstico de archivos de volcado generados en este SQL Server. Esta condición hace que el componente del sistema notifique un estado de error. Cuando la supervisión del estado de Always On recibe este estado de error, genera un evento de estado del grupo de disponibilidad. También puede comprobar, a partir de los datos proporcionados en los datos del componente del sistema, que no se detectan violaciones de acceso de escritura ni spinlocks huérfanos.

Solución

En función del tipo de problema que detecte, solucione el problema en consecuencia. Como se describe en el artículo Configuración de una directiva de conmutación por error automática flexible para un grupo de disponibilidad: SQL Server AlwaysOn, los distintos problemas pueden dar lugar a esta condición. Algunos ejemplos son:

• El servicio de SQL Server está inactivo.
• Tiempo de espera de concesión.
• La réplica de disponibilidad está en estado de error.
• Volcados de memoria generados por bloqueos por subprocesos huérfanos, infracciones de acceso o demasiados volcados de memoria generados en un breve período de tiempo.
• Estado persistente de falta de memoria en el grupo interno de recursos de SQL Server.
• Detección de un interbloqueo del programador.
• Detección de un interbloqueo irresoluble.

Si es necesario, póngase en contacto con el soporte técnico de SQL Server para abrir una incidencia de soporte y obtener asistencia adicional a fin de encontrar la causa principal de estos problemas internos relacionados con el estado de SQL Server.