Solución de problemas de conmutación automática por error en entornos de SQL Server Always On

Este artículo le ayuda a resolver los problemas que se producen durante la conmutación automática por error en Microsoft SQL Server.

Versión del producto original: SQL Server
Número de KB original: 2833707

Resumen

SQL Server Always On grupos de disponibilidad se pueden configurar para la conmutación automática por error. Si se detecta un problema de mantenimiento en la instancia de SQL Server que hospeda la réplica principal, el rol principal se puede pasar al asociado de conmutación por error automática (réplica secundaria). Sin embargo, la réplica secundaria no siempre se puede realizar la transición al rol principal. En algunos casos, solo se puede realizar la transición al RESOLVING rol. En esta situación, ninguna réplica tendrá el rol principal a menos que la réplica principal vuelva a un estado correcto. Además, no se podrá acceder a las bases de datos de disponibilidad.

En este artículo se enumeran algunas causas comunes de conmutación por error automática incorrecta y se describen los pasos que puede seguir para diagnosticar la causa de estos errores.

Síntomas si una conmutación automática por error se desencadena correctamente

Cuando se desencadena una conmutación por error automática en la instancia de SQL Server que hospeda la réplica principal, la réplica secundaria pasa al RESOLVING rol y, a continuación, al rol principal. Aunque el proceso se realiza correctamente, las entradas de error se registran en el informe de registro SQL Server similar al texto siguiente:

The state of the local availability replica in availability group '\<Group name>' has changed from 'RESOLVING_NORMAL' to 'PRIMARY_PENDING'  
The state of the local availability replica in availability group '\<Group name>' has changed from 'PRIMARY_PENDING' to 'PRIMARY_NORMAL'

Síntomas si una conmutación automática por error no se realiza correctamente

Si un evento de conmutación por error automática no se realiza correctamente, la réplica secundaria no realiza correctamente la transición al rol principal. Por lo tanto, la réplica de disponibilidad notificará que esta réplica está en un RESOLVING estado. Además, las bases de datos de disponibilidad informan de que están en un NOT SYNCHRONIZING estado y las aplicaciones no pueden acceder a estas bases de datos.

Por ejemplo, en la siguiente imagen, SQL Server Management Studio informa de que la réplica secundaria está en un RESOLVING estado porque el proceso de conmutación por error automática no pudo realizar la transición de la réplica secundaria al rol principal.

En las secciones siguientes se describen varias razones posibles por las que es posible que la conmutación por error automática no se realice correctamente y cómo diagnosticar cada causa.

Caso 1: Se agota el valor "Máximo de errores en el período especificado"

El grupo de disponibilidad tiene propiedades de recursos de clúster de Windows, como los errores máximos de la propiedad Período especificado . Esta propiedad se usa para evitar el movimiento indefinido de un recurso en clúster cuando se producen varios errores de nodo.

Para investigar y diagnosticar si esta es la causa de una conmutación por error incorrecta, revise el registro de clúster de Windows (Cluster.log) y, a continuación, compruebe la propiedad .

Paso 1: Revisar los datos del registro de clúster de Windows (Cluster.log)

1. Use Windows PowerShell para generar el registro de clúster de Windows en el nodo de clúster que hospeda la réplica principal. Para ello, ejecute el siguiente cmdlet en una ventana de PowerShell con privilegios elevados en la instancia de SQL Server que hospeda la réplica principal:

   Get-ClusterLog -Node <SQL Server node name> -TimeSpan 15
   

   

   [! NOTAS]

   • El -TimeSpan 15 parámetro de este paso supone que el problema que se está diagnosticando se produjo durante los 15 minutos anteriores.
   • De forma predeterminada, el archivo de registro se crea en %WINDIR%\cluster\reports.

2. Abra el archivo Cluster.log en el Bloc de notas para revisar el registro del clúster de Windows.

3. En el Bloc de notas, seleccione Editar>búsqueda y, a continuación, busque la cadena "failoverCount" al final del archivo. En los resultados, debe encontrar un mensaje similar al siguiente:

   No conmutar por error el nombre> de recurso del grupo<, failoverCount 3, failoverThresholdSetting <Number>, computedFailoverThreshold 2

   

Paso 2: Comprobar los errores máximos en la propiedad Período especificado

1. Inicie el Administrador de clústeres de conmutación por error.

2. En el panel de navegación, seleccione Roles.

3. En el panel Roles , haga clic con el botón derecho en el recurso clúster y, a continuación, seleccione Propiedades.

4. Seleccione la pestaña Conmutación por error y seleccione el valor Máximo de errores en el valor Período especificado .

   

   Nota:

   El comportamiento predeterminado especifica que si el recurso en clúster produce un error tres veces en un plazo de seis horas, debe permanecer en estado de error. Para un grupo de disponibilidad, esto significa que la réplica se deja en el RESOLVING estado .

Conclusión

Después de analizar el registro, verá que el valor failoverCount de 3 es mayor que el valor computedFailoverThreshold de 2. Por lo tanto, el clúster de Windows no puede completar la operación de conmutación por error del recurso del grupo de disponibilidad al asociado de conmutación por error.

Resolución

Para resolver este problema, aumente el valor Máximo de errores en el período especificado .

Caso 2: Permisos insuficientes de la cuenta NT Authority\SYSTEM

El archivo DLL de recursos del motor de base de datos de SQL Server se conecta a la instancia de SQL Server que hospeda la réplica principal mediante ODBC para supervisar el estado. Las credenciales de inicio de sesión que se usan para esta conexión son la cuenta de inicio de sesión SQL Server NT AUTHORITY\SYSTEM local. De forma predeterminada, a esta cuenta de inicio de sesión local se le conceden los siguientes permisos:

• Modificar cualquier grupo de disponibilidad
• Conectar SQL
• Visualización del estado del servidor

Si la NT AUTHORITY\SYSTEM cuenta de inicio de sesión no tiene ninguno de estos permisos en el asociado de conmutación por error automática (la réplica secundaria), SQL Server no puede iniciar la detección de estado cuando se produce una conmutación por error automática. Por lo tanto, la réplica secundaria no puede realizar la transición al rol principal. Para investigar y diagnosticar si esta es la causa, revise el registro del clúster de Windows. Para ello, siga estos pasos:

1. Use Windows PowerShell para generar el registro de clúster de Windows en el nodo del clúster. Para ello, ejecute el siguiente cmdlet en una ventana de PowerShell con privilegios elevados en la instancia de SQL Server que hospeda la réplica secundaria que no ha pasado al rol principal:

   Get-ClusterLog -Node <SQL Server node name> -TimeSpan 15
   

   

2. Abra el archivo Cluster.log en el Bloc de notas para revisar el registro del clúster de Windows.

3. Busque una entrada de error similar al texto siguiente:

   Error al ejecutar el comando de diagnóstico. El usuario no tiene permiso para realizar esta acción.

   

Conclusión

El archivo Cluster.log informa de que existe un problema de permisos cuando SQL Server ejecuta el comando de diagnóstico. En este ejemplo, el error se produjo al quitar el permiso Ver estado del servidor de la NT AUTHORITY\SYSTEM cuenta de inicio de sesión en la instancia de SQL Server que hospeda la réplica secundaria de un par de conmutación por error automática.

Resolución

Para resolver este problema, conceda permisos suficientes a la NT AUTHORITY\SYSTEM cuenta de inicio de sesión para la detección de estado del archivo DLL de recursos del motor de base de datos SQL Server.

Caso 3: Las bases de datos de disponibilidad no están en estado SYNCHRONIZED

Para conmutar por error automáticamente, todas las bases de datos de disponibilidad definidas en el grupo de disponibilidad deben estar en un SYNCHRONIZED estado entre la réplica principal y la réplica secundaria. Cuando se produce una conmutación por error automática, se debe cumplir esta condición de sincronización para asegurarse de que no hay pérdida de datos. Por lo tanto, si una base de datos de disponibilidad del grupo de disponibilidad está en el estado o NOT SYNCHRONIZED sincronización, la conmutación por error automática no transición correctamente la réplica secundaria al rol principal.

Para obtener más información sobre las condiciones necesarias para una conmutación por error automática, vea las condiciones necesarias para una conmutación por error automática y las réplicas de confirmación sincrónica admiten dos secciones de configuración de Modos de conmutación por error y conmutación por error (Always On grupos de disponibilidad).

Para investigar y diagnosticar si esta es la causa de una conmutación por error incorrecta, revise el registro de errores de SQL Server. Debe encontrar una entrada de error similar al texto siguiente:

Una o varias bases de datos no se sincronizan o no se han unido al grupo de disponibilidad.

Para comprobar si las bases de datos de disponibilidad se encontraban en el SYNCHRONIZED estado , siga estos pasos:

1. Conéctese a la réplica secundaria.

2. Ejecute el siguiente script SQL para comprobar el is_failover_ready valor de todas las bases de datos de disponibilidad del grupo de disponibilidad que no conmutan por error.

   Nota:

   Un valor de cero para cualquiera de las bases de datos de disponibilidad puede impedir la conmutación automática por error. Este valor indica que la base de datos de disponibilidad no SYNCHRONIZEDera .

   SELECT database_name, is_failover_ready FROM sys.dm_hadr_database_replica_cluster_states WHERE replica_id IN (SELECT replica_id FROM sys.dm_hadr_availability_replica_states)
   

   

Conclusión

Una conmutación por error automática correcta del grupo de disponibilidad requiere que todas las bases de datos de disponibilidad estén en el SYNCHRONIZED estado . Para obtener más información sobre los modos de disponibilidad, consulte Modos de disponibilidad en Always On grupos de disponibilidad.

Caso 4: La configuración "Forzar cifrado de protocolo" está seleccionada para los protocolos de cliente en la réplica secundaria (principal de destino), aunque la réplica no está configurada para el cifrado.

Durante la conmutación por error, cuando el servidor principal detecta un problema de mantenimiento, el archivo DLL del clúster en el asociado de conmutación por error (réplica secundaria) intenta conectarse a la réplica local para iniciar la supervisión del estado. Esto forma parte de la transición al rol principal. Si la réplica secundaria no está configurada para el cifrado, pero la opción Forzar cifrado de protocolo se establece involuntariamente en la configuración del cliente, se producirá un error en la conexión y no se puede producir la conmutación por error.

Para comprobar esta configuración:

1. Inicie el administrador de configuración de SQL Server.
2. En el panel izquierdo , haga clic con el botón derecho en la configuración de SQL Native Client 11.0 y, a continuación, seleccione Propiedades.
3. En el cuadro de diálogo, active la opción Forzar cifrado de protocolo . Si se establece en Sí, cambie el valor a No.
4. Vuelva a probar la conmutación por error.

Conclusión

SQL Server Always On supervisión de estado usa una conexión ODBC local para supervisar el estado de SQL Server. El cifrado de protocolo forzado debe habilitarse en la sección Configuración de cliente de Administrador de configuración de SQL Server solo si SQL Server se configuró para forzar cifrados en Administrador de configuración de SQL Server en la sección Configuración de red de SQL Server. Para obtener más información, vea Habilitar conexiones cifradas con el motor de base de datos.

Caso 5: Los problemas de rendimiento en la réplica secundaria o el nodo provocan un error en las comprobaciones de estado de Always On

Antes de realizar la conmutación por error de la réplica principal a la réplica secundaria, SQL Server dll de recursos del motor de base de datos se conecta a la réplica secundaria para determinar el estado de la réplica. Si se produce un error en esta conexión debido a problemas de rendimiento en la réplica secundaria, no se produce la conmutación por error automática.

Para investigar y diagnosticar si esta es la causa, siga estos pasos:

1. Revise el registro de clúster en la réplica secundaria para comprobar el mensaje de error "No se puede completar el proceso de inicio de sesión debido al retraso en la apertura de la conexión del servidor".

   0000110c.00002bcc::2020/08/06-01:17:54.943 INFO  [RCM] move of group AOCProd01AG from CO2ICMV3SQL09(1) to CO2ICMV3SQL10(2) of type MoveType::Manual is about to succeed, failoverCount=3, lastFailoverTime=2020/08/05-02:08:54.524 targeted=true 
   00002a54.0000610c::2020/08/06-01:18:44.929 ERR   [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]Unable to complete login process due to delay in opening server connection (0) 
   00002a54.0000610c::2020/08/06-01:18:44.929 INFO  [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] Could not connect to SQL Server (rc -1) 
   00002a54.0000610c::2020/08/06-01:18:44.929 INFO  [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] SQLDisconnect returns following information 
   00002a54.0000610c::2020/08/06-01:18:44.929 ERR   [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0) 
   00002a54.0000610c::2020/08/06-01:18:44.931 ERR   [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] Failed to connect to SQL Server 
   00002a54.0000610c::2020/08/06-01:18:44.931 ERR   [RHS] Online for resource AOCProd01AG failed. 
   

   Esta situación puede producirse si la conmutación por error se realiza en una réplica secundaria SQL Server que tiene una carga de trabajo existente ocupada. Esto podría retrasar la respuesta de SQL Server al intento de solicitud de conexión de mantenimiento hadr y evitar un intento de conmutación por error correcto.

2. Para determinar si hay presión sobre los programadores del sistema, use SQL Server Management Studio para ejecutar el siguiente script en la réplica secundaria:

   USE MASTER 
   GO  
   WHILE 1=1 
   BEGIN 
   PRINT convert(varchar(20), getdate(),120) 
   DECLARE @max INT; 
   SELECT @max = max_workers_count 
   FROM sys.dm_os_sys_info; 
   SELECT GETDATE() AS 'CurrentDate',  
          @max AS 'TotalThreads',  
          SUM(active_Workers_count) AS 'CurrentThreads',  
          @max - SUM(active_Workers_count) AS 'AvailableThreads',  
          SUM(runnable_tasks_count) AS 'WorkersWaitingForCpu',  
          SUM(work_queue_count) AS 'RequestWaitingForThreads' 
          --SUM(current_workers_count) AS 'AssociatedWorkers' 
   FROM sys.dm_os_Schedulers 
   WHERE STATUS = 'VISIBLE ONLINE'; 
   wait for delay '0:0:15' 
   END
   

   A continuación se muestra la salida de ejemplo de la consulta anterior:

   CurrentDate	TotalThreads	CurrentThreads	AvailableThreads	WorkersWaitingForCpu	RequestWaitingForThreads
   2020-10-06 01:27:01.337	1216	361	855	33	0
   2020-10-06 01:27:08.340	1216	1412	-196	22	76
   2020-10-06 01:27:15.340	1216	1304	-88	2	161
   2020-10-06 01:27:22.340	1216	1242	-26	21	185
   2020-10-06 01:27:29.343	1216	1346	-130	19	476
   2020-10-06 01:27:36.350	1216	1350	-134	9	630
   2020-10-06 01:27:43.353	1216	1346	-130	13	539
   2020-10-06 01:27:50.360	1216	1378	-162	5	328
   2020-10-06 01:27:57.360	1216	197	1019	0	0

   Los valores altos notificados para WorkersWaitingForCpu e RequestWaitingForThreads indican que se está produciendo una contención de programación y que SQL Server no pueden atender la carga de trabajo actual de forma oportuna.

Resolución

Si experimenta este problema, vuelva a equilibrar la carga de trabajo en la réplica secundaria o considere la posibilidad de aumentar la potencia de procesamiento (agregar procesadores) en los equipos que ejecutan estas cargas de trabajo.

Solución de problemas de otros eventos de conmutación por error

Para supervisar el estado de la nueva réplica principal durante la conmutación por error, debe conectar localmente la supervisión de estado de AlwaysOn a la instancia de SQL Server que está realizando la transición al rol principal.

Además de las razones más comunes que se describen en este artículo, hay muchas otras razones por las que este intento de conexión podría producir un error. Para investigar aún más un intento de conmutación por error erróneo, revise el registro de clúster en el asociado de conmutación por error (la réplica a la que no pudo conmutar por error):

1. Use Windows PowerShell para generar el registro del clúster de Windows en el nodo del clúster. Para ello, ejecute el siguiente cmdlet en una ventana de PowerShell con privilegios elevados en la instancia de SQL Server que hospeda la réplica secundaria que no ha pasado al rol principal. Se generará un registro de clúster durante los últimos 60 minutos de actividad.

   Get-ClusterLog -Node <SQLServerNodeName> -TimeSpan 60
   

2. Para revisar el registro del clúster de Windows, abra el archivo Cluster.log en el Bloc de notas.

3. Busque la cadena "Conectarse a SQL Server" que cae durante el evento de conmutación por error incorrecto.

4. Revise los mensajes de inicio de sesión posteriores mediante el identificador de subproceso (vea la captura de pantalla siguiente) para correlacionar los eventos relacionados con el evento de inicio de sesión. En el ejemplo siguiente se muestra una búsqueda de "Conectarse a SQL Server". También se muestra el uso del identificador de subproceso (lado izquierdo) para buscar los otros diagnósticos que describen por qué se produjo un error en el intento de conexión.

   

En los ejemplos siguientes se muestran errores de conexión a la nueva réplica principal.

Conjunto de ejemplo 1

[hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]SQL Server Network
Interfaces: No client protocols are enabled and no protocol was specified in the connection
string [xFFFFFFFF]. (268435455)

Resolución

Inicie Administrador de configuración de SQL Server y compruebe que memoria compartida o TCP/IP está habilitado en Protocolos de cliente para la configuración de SQL Native Client.

Conjunto de ejemplo 2

[hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]SQL Server Network
Interfaces: Server doesn't support requested protocol [xFFFFFFFF]. (268435455)

Resolución

Inicie Administrador de configuración de SQL Server y compruebe que memoria compartida o TCP/IP está habilitado en Protocolos de cliente para la configuración de SQL Native Client.

Conjunto de ejemplo 3

000010b8.00001764::2020/12/02-16:52:49.808 ERR [RES] SQL Server Availability Group : [hadrag]
ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot alter the availability
group 'ag', because it does not exist or you do not have permission. (15151)
000010b8.00000fd0::2020/12/02-17:01:14.821 ERR [RES] SQL Server Availability Group: [hadrag]
ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The user does not have permission to perform this action. (297)
000010b8.00001838::2020/12/02-17:10:04.427 ERR [RES] SQL Server Availability Group : [hadrag]
ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user
'SQLREPRO\NODE2$'. Reason: The account is disabled. (18470)

Resolución

Caso de revisión 2: permisos insuficientes de la cuenta de NT Authority\SYSTEM.