Solución de problemas de Always On bases de datos de disponibilidad en estado de recuperación pendiente o sospechoso en SQL Server

Artículo
06/27/2023

En este artículo se describen los errores y limitaciones de una base de datos de disponibilidad en Microsoft SQL Server que se encuentra en un Recovery Pending estado o Suspect y cómo restaurar la base de datos a la funcionalidad completa de un grupo de disponibilidad.

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

Resumen

Supongamos que una base de datos de disponibilidad definida en un grupo de disponibilidad de Always On pasa a un Recovery Pending estado o Suspect en SQL Server. Si esto ocurre en la réplica principal del grupo de disponibilidad, la disponibilidad de la base de datos se ve afectada. En esta situación, no puede acceder a la base de datos a través de las aplicaciones cliente. Además, no puede quitar ni quitar la base de datos del grupo de disponibilidad.

Por ejemplo, suponga que SQL Server se está ejecutando y que una base de datos de disponibilidad está establecida en el Recovery Pending estado o Suspect . Al consultar las vistas de administración dinámica (DMV) en la réplica principal mediante el siguiente script SQL, es posible que la base de datos se notifique en un NOT_HEALTHY estado y RECOVERY_PENDING o en un SUSPECT estado como se indica a continuación:

SELECT
    dc.database_name,
    d.synchronization_health_desc,
    d.synchronization_state_desc,
    d.database_state_desc
FROM
    sys.dm_hadr_database_replica_states d
    JOIN sys.availability_databases_cluster dc ON d.group_database_id = dc.group_database_id
    AND d.is_local = 1

database_name          synchronization_health_desc     synchronization_state_desc   database_state_desc
-------------------- ------------------------------ ------------------------------ ---------------------
<DatabaseName>                         NOT_HEALTHY              NOT SYNCHRONIZING      RECOVERY_PENDING
(1 row(s) affected)

Captura de pantalla del resultado de ejecución del script para comprobar el estado de sincronización y el estado de la base de datos.

Además, esta base de datos se puede notificar como en el estado No sincronizando/Recuperación pendiente o Sospechoso en SQL Server Management Studio.

Captura de pantalla de la base de datos que se encuentra en estado No sincronizado/Pendiente de recuperación.

Cuando la base de datos se define en un grupo de disponibilidad, la base de datos no se puede quitar ni restaurar. Por lo tanto, debe realizar pasos específicos para recuperar la base de datos y devolverla al uso en producción.

Más información

En el siguiente contenido se describen los errores y limitaciones de una base de datos de disponibilidad que se encuentra en un estado Pendiente de recuperación en varias situaciones.

El estado de la base de datos impide la restauración de la base de datos

Intente ejecutar el siguiente script SQL para restaurar la base de datos que tiene el RECOVERY parámetro :
```
RESTORE DATABASE <DatabaseName> WITH RECOVERY
```
Al ejecutar este script, recibirá el siguiente mensaje de error porque la base de datos se define en un grupo de disponibilidad:

Msg 3104, Level 16, State 1, Line 1
RESTORE no puede funcionar en database <DatabaseName> porque está configurado para la creación de reflejo de la base de datos o se ha unido a un grupo de disponibilidad. Si tiene previsto restaurar la base de datos, use ALTER DATABASE para quitar la creación de reflejo o para quitar la base de datos de su grupo de disponibilidad.

Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE termina de forma anómala.
El estado de la base de datos impide quitar la base de datos

Intente ejecutar el siguiente script SQL para quitar la base de datos:
```
DROP DATABASE <DatabaseName>
```
Al ejecutar este script, recibirá el siguiente mensaje de error porque la base de datos se define en un grupo de disponibilidad:

Msg 3752, Level 16, State 1, Line 1
La base de datos <DatabaseName> está actualmente unida a un grupo de disponibilidad. Para poder quitar la base de datos, debe quitarla del grupo de disponibilidad.
El estado de la base de datos impide quitar la base de datos del grupo de disponibilidad

Intente ejecutar el siguiente script SQL para quitar la base de datos del grupo de disponibilidad:
```
ALTER DATABASE <DatabaseName> SET hadr OFF
```
Al intentar ejecutar este script, recibe el siguiente mensaje de error porque la base de datos de disponibilidad pertenece a la réplica principal:

Msg 35240, Level 16, State 14, Line 1
Database <DatabaseName> no se puede unir ni separar del grupo <de disponibilidad AvailabilityGroupName>. Esta operación no se admite en la réplica principal del grupo de disponibilidad.

Debido a este mensaje de error, es posible que se vea obligado a conmutar por error la base de datos. Una vez conmutada por error la base de datos, la réplica propietaria de la base de datos pendiente de recuperación se encuentra en el rol secundario. En esta situación, intenta ejecutar de nuevo el siguiente script SQL para quitar la base de datos del grupo de disponibilidad en la réplica secundaria:
```
ALTER DATABASE <DatabaseName> SET hadr OFF
```
Sin embargo, todavía no puede quitar la base de datos del grupo de disponibilidad y recibirá el siguiente mensaje de error porque la base de datos sigue en estado Pendiente de recuperación:

Msg 921, Level 16, State 112, Line 1
Database <DatabaseName> aún no se ha recuperado. Espere e inténtelo de nuevo.

Resolución cuando la base de datos está en el rol secundario

Para resolver este problema, realice las siguientes acciones generales:

Quite del grupo de disponibilidad la réplica que hospeda la base de datos dañada cuando la base de datos está en el rol secundario.
Resuelva los problemas que afectan al sistema y que podrían haber contribuido al error de la base de datos.
Restaure la réplica en el grupo de disponibilidad.

Para realizar estas acciones, conéctese a la nueva réplica principal y, a continuación, ejecute el ALTER AVAILABILITY GROUP script SQL para quitar la réplica que hospeda la base de datos de disponibilidad con errores. Para ello, siga estos pasos.

En estos pasos se supone que la réplica principal hospeda primero la base de datos dañada. Por lo tanto, primero debe producirse una conmutación por error para realizar la transición de la réplica que hospeda la base de datos dañada a un rol secundario.

Conéctese al servidor que ejecuta SQL Server y que hospeda la réplica secundaria.

Ejecute el siguiente script SQL:

ALTER AVAILABILITY GROUP <AvailabilityGroupName> FAILOVER

Ejecute el siguiente script SQL para quitar la réplica que hospeda la base de datos dañada del grupo de disponibilidad:
```
ALTER AVAILABILITY GROUP <AvailabilityGroupName> REMOVE REPLICA ON '<SQLServerNodeName>'
```
Resuelva los problemas en el servidor que ejecutan SQL Server y que puedan contribuir al error de la base de datos.
Vuelva a agregar la réplica al grupo de disponibilidad.

Resolución cuando la réplica principal es la única réplica del grupo de disponibilidad

Si la réplica principal hospeda la base de datos dañada y es la única réplica que funciona en el grupo de disponibilidad, se debe quitar el grupo de disponibilidad. Después de quitar el grupo de disponibilidad, la base de datos se puede recuperar de una copia de seguridad, u otros esfuerzos de recuperación de emergencia se pueden aplicar para restaurar las bases de datos y reanudar la producción.

Para quitar el grupo de disponibilidad, use el siguiente script SQL:

DROP AVAILABILITY GROUP <AvailabilityGroupName>

En este momento, puede intentar recuperar la base de datos problemática. O bien, puede restaurar la base de datos a partir de la última copia de seguridad correcta conocida.

Resolución al quitar el grupo de disponibilidad

Al quitar un grupo de disponibilidad, el recurso de agente de escucha también se quita e interrumpe la conectividad de la aplicación con las bases de datos de disponibilidad.

Para minimizar el tiempo de inactividad de la aplicación, use uno de los métodos siguientes para mantener la conectividad de la aplicación a través del agente de escucha y quitar el grupo de disponibilidad:

Método 1: Asociar el agente de escucha con un nuevo grupo de disponibilidad (rol) en el Administrador de clústeres de conmutación por error

Este método le permite mantener el agente de escucha mientras quita y vuelve a crear el grupo de disponibilidad.

En la instancia de SQL Server a la que el agente de escucha del grupo de disponibilidad existente dirige las conexiones, cree un nuevo grupo de disponibilidad vacío. Para simplificar este proceso, use el comando Transact-SQL para crear un grupo de disponibilidad que no tenga ninguna réplica o base de datos secundaria:
```
USE master
GO
CREATE AVAILABILITY GROUP ag FOR REPLICA ON 'sqlnode1' WITH (
    ENDPOINT_URL = 'tcp://sqlnode1:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL
)
```
Inicie el Administrador de clústeres de conmutación por error y, a continuación, seleccione Roles en el panel izquierdo. En el panel que enumera los roles, seleccione el grupo de disponibilidad original.
En el panel inferior central de la pestaña Recursos , haga clic con el botón derecho en el recurso del grupo de disponibilidad y, a continuación, seleccione Propiedades. Seleccione la pestaña Dependencias , elimine la dependencia del agente de escucha y, a continuación, seleccione Aceptar.
En los recursos, haga clic con el botón derecho en el agente de escucha, seleccione Más acciones y, a continuación, seleccione Asignar a otro rol.
En el cuadro de diálogo Asignar origen a rol , seleccione el nuevo grupo de disponibilidad y, a continuación, seleccione Aceptar.
En el panel Roles , seleccione el nuevo grupo de disponibilidad. En el panel inferior central, en la pestaña Recursos , ahora debería ver el nuevo grupo de disponibilidad y el recurso de agente de escucha. Haga clic con el botón derecho en el nuevo recurso del grupo de disponibilidad y, a continuación, seleccione Propiedades.
Haga clic en la pestaña Dependencias , seleccione el recurso de agente de escucha en el cuadro desplegable y, a continuación, seleccione Aceptar.
En SQL Server Management Studio, use Explorador de objetos para conectarse a la instancia de SQL Server que hospeda la réplica principal del nuevo grupo de disponibilidad. Seleccione Always On alta disponibilidad, haga clic en el nuevo grupo de disponibilidad y, a continuación, seleccione Agentes de escucha del grupo de disponibilidad. Debe encontrar el agente de escucha.
Haga clic con el botón derecho en el agente de escucha, seleccione Propiedades, escriba el número de puerto adecuado para el agente de escucha y, a continuación, seleccione Aceptar.

Esto garantiza que las aplicaciones que usan el agente de escucha puedan seguir usándolo para conectarse a la instancia de SQL Server que hospeda las bases de datos de producción sin interrupción. El grupo de disponibilidad original ahora se puede quitar por completo y volver a crear. O bien, las bases de datos y las réplicas se pueden agregar al nuevo grupo de disponibilidad.

Si vuelve a crear el grupo de disponibilidad original, debe volver a reasignar el agente de escucha al rol de grupo de disponibilidad, configurar la dependencia entre el nuevo recurso del grupo de disponibilidad y el agente de escucha y, a continuación, reasignar el puerto al agente de escucha. Para ello, siga estos pasos:

Inicie el Administrador de clústeres de conmutación por error y, a continuación, seleccione Roles en el panel izquierdo. En el panel que enumera los roles, haga clic en el nuevo grupo de disponibilidad que hospeda el agente de escucha.
En el panel central inferior de la pestaña Recursos , haga clic con el botón derecho en el agente de escucha, seleccione Más acciones y, a continuación, seleccione Asignar a otro rol. En el cuadro de diálogo, elija el grupo de disponibilidad que se ha vuelto a crear y, a continuación, seleccione Aceptar.
En el panel Roles , haga clic en el grupo de disponibilidad que se ha vuelto a crear. En el panel central inferior, en la pestaña Recursos , ahora debería ver el grupo de disponibilidad que se ha vuelto a crear y el recurso de agente de escucha. Haga clic con el botón derecho en el recurso del grupo de disponibilidad que se ha vuelto a crear y, a continuación, seleccione Propiedades.
Seleccione la pestaña Dependencias , seleccione el recurso de agente de escucha en el cuadro desplegable y, a continuación, seleccione Aceptar.
En SQL Server Management Studio, use Explorador de objetos para conectarse a la instancia de SQL Server que hospeda la réplica principal del grupo de disponibilidad que se ha vuelto a crear. Seleccione Always On alta disponibilidad, haga clic en el nuevo grupo de disponibilidad y, a continuación, seleccione Agentes de escucha del grupo de disponibilidad. Debe encontrar el agente de escucha.
Haga clic con el botón derecho en el agente de escucha, seleccione Propiedades, escriba el número de puerto adecuado para el agente de escucha y, a continuación, seleccione Aceptar.

Método 2: Asociar el agente de escucha a una instancia en clúster de conmutación por error de SQL Server existente (SQLFCI)

Si hospeda el grupo de disponibilidad en una instancia en clúster de conmutación por error de SQL Server (SQLFCI), puede asociar el recurso en clúster del agente de escucha al grupo de recursos en clúster de SQLFCI mientras quita y, a continuación, vuelve a crear el grupo de disponibilidad.

Inicie el Administrador de clústeres de conmutación por error y, a continuación, seleccione Roles en el panel izquierdo.
En el panel que enumera los roles, seleccione el grupo de disponibilidad original.
En el panel central inferior, en la pestaña Recursos , haga clic con el botón derecho en el recurso del grupo de disponibilidad y, a continuación, seleccione Propiedades.
Seleccione la pestaña Dependencias , elimine la dependencia del agente de escucha y, a continuación, seleccione Aceptar.
En el panel central inferior de la pestaña Recursos , haga clic con el botón derecho en el agente de escucha, seleccione Más acciones y, a continuación, seleccione Asignar a otro rol.
En el cuadro de diálogo Asignar recurso a rol, haga clic en la instancia de FCI de SQL Server y, a continuación, seleccione Aceptar.
En el panel Roles , seleccione el grupo SQLFCI. En el panel central inferior, en la pestaña Recursos , ahora debería ver el nuevo recurso de agente de escucha.

Esto garantiza que las aplicaciones que usan el agente de escucha puedan seguir usándolo para conectarse a la instancia de SQL Server que hospeda las bases de datos de producción sin interrupción. El grupo de disponibilidad original ahora se puede quitar y volver a crear. O bien, las bases de datos y las réplicas se pueden agregar al nuevo grupo de disponibilidad.

Después de volver a crear el grupo de disponibilidad, vuelva a reasignar el agente de escucha al rol de grupo de disponibilidad. A continuación, configure la dependencia entre el nuevo recurso del grupo de disponibilidad y el agente de escucha y reasigne el puerto al agente de escucha:

Inicie el Administrador de clústeres de conmutación por error y, a continuación, seleccione Roles en el panel izquierdo.
En el panel que enumera los roles, haga clic en el rol SQLFCI original.
En el panel central inferior, en la pestaña Recursos , haga clic con el botón derecho en el agente de escucha, seleccione Más acciones y, a continuación, seleccione Asignar a otro rol.
En el cuadro de diálogo, haga clic en el grupo de disponibilidad que se ha vuelto a crear y, a continuación, seleccione Aceptar.
En el panel Roles , seleccione el nuevo grupo de disponibilidad.
En la pestaña Recursos , debería ver el nuevo grupo de disponibilidad y el recurso de agente de escucha. Haga clic con el botón derecho en el nuevo recurso del grupo de disponibilidad y, a continuación, seleccione Propiedades.
Seleccione la pestaña Dependencias , seleccione el recurso de agente de escucha en el cuadro desplegable y, a continuación, seleccione Aceptar.
En SQL Server Management Studio, use Explorador de objetos para conectarse a la instancia de SQL Server que hospeda la réplica principal del nuevo grupo de disponibilidad.
Seleccione Always On alta disponibilidad, haga clic en el nuevo grupo de disponibilidad y, a continuación, seleccione Agentes de escucha del grupo de disponibilidad. Debe encontrar el agente de escucha.
Haga clic con el botón derecho en el agente de escucha, seleccione Propiedades, escriba el número de puerto adecuado para el agente de escucha y, a continuación, seleccione Aceptar.

Método 3: Quitar el grupo de disponibilidad y, a continuación, volver a crear el grupo de disponibilidad y el agente de escucha con el mismo nombre de agente de escucha

Este método provocará una pequeña interrupción para las aplicaciones que están conectadas actualmente porque el grupo de disponibilidad y el agente de escucha se quitan y, a continuación, se vuelven a crear:

Quite el grupo de disponibilidad.

Nota:

Esto también quitará el agente de escucha.
Cree inmediatamente un nuevo grupo de disponibilidad vacío que incluya la definición del agente de escucha en el mismo servidor que hospeda las bases de datos de producción.

Por ejemplo, suponga que el agente de escucha del grupo de disponibilidad es aglisten. La siguiente instrucción Transact-SQL crea un grupo de disponibilidad sin base de datos principal o secundaria, pero también crea un agente de escucha denominado aglisten. Las aplicaciones pueden usar este agente de escucha para conectarse.
```
USE master
GO
    CREATE AVAILABILITY GROUP ag FOR REPLICA ON 'sqlnode1' WITH (
        ENDPOINT_URL = 'tcp://sqlnode1:5022',
        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
        FAILOVER_MODE = MANUAL
    ) LISTENER 'aglisten' (
        WITH IP ((N'11.0.0.25', N'255.0.0.0')),
        PORT = 1433
    )
GO
```
Recupere la base de datos dañada. A continuación, agréguela y vuelva a agregar la réplica secundaria al grupo de disponibilidad.