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'
Nota:
La réplica secundaria pasa correctamente de un RESOLVING_NORMAL
estado a un PRIMARY_NORMAL
estado.
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)
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.
- El
Abra el archivo Cluster.log en el Bloc de notas para revisar el registro del clúster de Windows.
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
Inicie el Administrador de clústeres de conmutación por error.
En el panel de navegación, seleccione Roles.
En el panel Roles , haga clic con el botón derecho en el recurso clúster y, a continuación, seleccione Propiedades.
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 .
Nota:
Es posible que el aumento de este valor no resuelva el problema. Puede haber un problema más crítico que haga que el grupo de disponibilidad produzca un error muchas veces en un período corto. De forma predeterminada, este período es de 15 minutos. Aumentar este valor podría simplemente hacer que el grupo de disponibilidad produzca un error más veces y permanezca en un estado de error. Se recomienda usar la solución de problemas agresiva para determinar por qué se sigue produciendo la conmutación por error automática.
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:
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
Abra el archivo Cluster.log en el Bloc de notas para revisar el registro del clúster de Windows.
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:
Conéctese a la réplica secundaria.
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
SYNCHRONIZED
era .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:
- Inicie el administrador de configuración de SQL Server.
- 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.
- En el cuadro de diálogo, active la opción Forzar cifrado de protocolo . Si se establece en Sí, cambie el valor a No.
- 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:
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.
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
eRequestWaitingForThreads
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):
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
Para revisar el registro del clúster de Windows, abra el archivo Cluster.log en el Bloc de notas.
Busque la cadena "Conectarse a SQL Server" que cae durante el evento de conmutación por error incorrecto.
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.