Résoudre les problèmes liés à la disponibilité des bases de données Always On dans un état de récupération en attente ou suspect dans SQL Server

Cet article décrit les erreurs et limitations d’une base de données de disponibilité dans Microsoft SQL Server qui est dans un état ou Suspect un Recovery Pending état et comment restaurer la base de données en fonctionnalités complètes dans un groupe de disponibilité.

Version du produit d’origine : SQL Server 2012
Numéro de base de connaissances d’origine : 2857849

Résumé

Supposons qu’une base de données de disponibilité définie dans un groupe de disponibilité Always On passe à un état ou Suspect à un Recovery Pending état dans SQL Server. Si cela se produit sur le réplica principal du groupe de disponibilité, la disponibilité de la base de données est affectée. Dans ce cas, vous ne pouvez pas accéder à la base de données via les applications clientes. En outre, vous ne pouvez pas supprimer ou supprimer la base de données du groupe de disponibilité.

Par exemple, supposons que SQL Server est en cours d’exécution et qu’une base de données de disponibilité est définie sur l’état ou Suspect l’étatRecovery Pending. Lorsque vous interrogez les vues de gestion dynamique (DMV) sur le réplica principal à l’aide du script SQL suivant, la base de données peut être signalée dans un NOT_HEALTHY état ou RECOVERY_PENDING dans un SUSPECT état comme suit :

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)

En outre, cette base de données peut être signalée comme étant dans l’état Not Synchronizing/ Recovery Pending ou Suspect dans SQL Server Management Studio.

Lorsque la base de données est définie dans un groupe de disponibilité, la base de données ne peut pas être supprimée ou restaurée. Par conséquent, vous devez prendre des mesures spécifiques pour récupérer la base de données et la remettre en production.

Plus d’informations

Le contenu suivant décrit les erreurs et les limitations d’une base de données de disponibilité qui se trouve dans un état de récupération en attente dans différentes situations.

• L’état de la base de données empêche la restauration de la base de données

  Vous essayez d’exécuter le script SQL suivant pour restaurer la base de données qui a le RECOVERY paramètre :

  RESTORE DATABASE <DatabaseName> WITH RECOVERY
  

  Lorsque vous exécutez ce script, vous recevez le message d’erreur suivant, car la base de données est définie dans un groupe de disponibilité :

  Msg 3104, Niveau 16, État 1, Ligne 1
  RESTORE ne peut pas fonctionner sur database <DatabaseName> , car il est configuré pour la mise en miroir de bases de données ou a rejoint un groupe de disponibilité. Si vous envisagez de restaurer la base de données, utilisez ALTER DATABASE pour supprimer la mise en miroir ou pour supprimer la base de données de son groupe de disponibilité.

  Message 3013, niveau 16, état 1, ligne 1
  RESTORE DATABASE se termine anormalement.

• L’état de la base de données empêche la suppression de la base de données

  Vous essayez d’exécuter le script SQL suivant pour supprimer la base de données :

  DROP DATABASE <DatabaseName>
  

  Lorsque vous exécutez ce script, vous recevez le message d’erreur suivant, car la base de données est définie dans un groupe de disponibilité :

  Msg 3752, Level 16, State 1, Line 1
  La base de données <DatabaseName> est actuellement jointe à un groupe de disponibilité. Avant de pouvoir supprimer la base de données, vous devez la supprimer du groupe de disponibilité.

• L’état de la base de données empêche la suppression de la base de données du groupe de disponibilité

  Vous essayez d’exécuter le script SQL suivant pour supprimer la base de données du groupe de disponibilité :

  ALTER DATABASE <DatabaseName> SET hadr OFF
  

  Lorsque vous essayez d’exécuter ce script, vous recevez le message d’erreur suivant, car la base de données de disponibilité appartient au réplica principal :

  Msg 35240, Niveau 16, État 14, Ligne 1
  DatabaseName <> ne peut pas être joint ou non joint à partir du groupe <de disponibilité AvailabilityGroupName>. Cette opération n’est pas prise en charge sur le réplica principal du groupe de disponibilité.

  En raison de ce message d’erreur, vous pouvez être obligé de basculer la base de données. Une fois la base de données basculée, le réplica propriétaire de la base de données en attente de récupération se trouve dans le rôle secondaire. Dans ce cas, vous essayez de réexécuter le script SQL suivant pour supprimer la base de données du groupe de disponibilité sur le réplica secondaire :

  ALTER DATABASE <DatabaseName> SET hadr OFF
  

  Toutefois, vous ne pouvez toujours pas supprimer la base de données du groupe de disponibilité et vous recevez le message d’erreur suivant, car la base de données est toujours dans l’état en attente de récupération :

  Msg 921, Level 16, State 112, Line 1
  DatabaseName <> n’a pas encore été récupéré. Attendez puis recommencez.

Résolution lorsque la base de données se trouve dans le rôle secondaire

Pour résoudre ce problème, effectuez les actions générales suivantes :

• Supprimez du groupe de disponibilité le réplica qui héberge la base de données endommagée lorsque la base de données se trouve dans le rôle secondaire.
• Résolvez les problèmes qui affectent le système et qui peuvent avoir contribué à l’échec de la base de données.
• Restaurez le réplica dans le groupe de disponibilité.

Pour effectuer ces actions, connectez-vous au nouveau réplica principal, puis exécutez le ALTER AVAILABILITY GROUP script SQL pour supprimer le réplica qui héberge la base de données de disponibilité ayant échoué. Pour ce faire, procédez comme suit.

Ces étapes supposent que le réplica principal héberge d’abord la base de données endommagée. Par conséquent, un basculement doit d’abord se produire pour faire passer le réplica qui héberge la base de données endommagée dans un rôle secondaire.

1. Connectez-vous au serveur exécutant SQL Server et hébergeant le réplica secondaire.

2. Exécutez le script SQL suivant :

   ALTER AVAILABILITY GROUP <AvailabilityGroupName> FAILOVER
   

3. Exécutez le script SQL suivant pour supprimer le réplica qui héberge la base de données endommagée du groupe de disponibilité :

   ALTER AVAILABILITY GROUP <AvailabilityGroupName> REMOVE REPLICA ON '<SQLServerNodeName>'
   

4. Résolvez les problèmes sur le serveur qui exécute SQL Server et qui peuvent contribuer à l’échec de la base de données.

5. Ajoutez le réplica dans le groupe de disponibilité.

Résolution lorsque le réplica principal est le seul réplica dans le groupe de disponibilité

Si le réplica principal héberge la base de données endommagée et est le seul réplica opérationnel dans le groupe de disponibilité, le groupe de disponibilité doit être supprimé. Une fois le groupe de disponibilité supprimé, votre base de données peut être récupérée à partir d’une sauvegarde, ou d’autres efforts de récupération d’urgence peuvent être appliqués pour restaurer les bases de données et reprendre la production.

Pour supprimer le groupe de disponibilité, utilisez le script SQL suivant :

DROP AVAILABILITY GROUP <AvailabilityGroupName>

À ce stade, vous pouvez essayer de récupérer la base de données problématique. Vous pouvez également restaurer la base de données à partir de la dernière copie de sauvegarde correcte connue.

Résolution lorsque vous supprimez le groupe de disponibilité

Lorsque vous supprimez un groupe de disponibilité, la ressource de l’écouteur est également supprimée et interrompt la connectivité des applications aux bases de données de disponibilité.

Pour réduire le temps d’arrêt de l’application, utilisez l’une des méthodes suivantes pour maintenir la connectivité des applications via l’écouteur et supprimer le groupe de disponibilité :

Méthode 1 : Associer l’écouteur à un nouveau groupe de disponibilité (rôle) dans le Gestionnaire du cluster de basculement

Cette méthode vous permet de gérer l’écouteur lors de la suppression et de la recréation du groupe de disponibilité.

1. Sur l’instance de SQL Server vers laquelle l’écouteur de groupe de disponibilité existant dirige les connexions, créez un groupe de disponibilité vide. Pour simplifier ce processus, utilisez la commande Transact-SQL pour créer un groupe de disponibilité qui n’a pas de réplica ou de base de données secondaire :

   USE master
   GO
   CREATE AVAILABILITY GROUP ag FOR REPLICA ON 'sqlnode1' WITH (
       ENDPOINT_URL = 'tcp://sqlnode1:5022',
       AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
       FAILOVER_MODE = MANUAL
   )
   

2. Démarrez le Gestionnaire du cluster de basculement, puis sélectionnez Rôles dans le volet gauche. Dans le volet qui répertorie les rôles, sélectionnez le groupe de disponibilité d’origine.

3. Dans le volet inférieur du milieu sous l’onglet Ressources, cliquez avec le bouton droit sur la ressource du groupe de disponibilité, puis sélectionnez Propriétés. Sélectionnez l’onglet Dépendances , supprimez la dépendance à l’écouteur, puis sélectionnez OK.

   

4. Sous les ressources, cliquez avec le bouton droit sur l’écouteur, sélectionnez Autres actions, puis sélectionnez Affecter à un autre rôle.

5. Dans la boîte de dialogue Attribuer une source au rôle , sélectionnez le nouveau groupe de disponibilité, puis sélectionnez OK.

   

6. Dans le volet Rôles , sélectionnez le nouveau groupe de disponibilité. Dans le volet inférieur, sous l’onglet Ressources , vous devez maintenant voir le nouveau groupe de disponibilité et la ressource de l’écouteur. Cliquez avec le bouton droit sur la nouvelle ressource de groupe de disponibilité, puis sélectionnez Propriétés.

7. Cliquez sur l’onglet Dépendances , sélectionnez la ressource de l’écouteur dans la zone de liste déroulante, puis sélectionnez OK.

   

8. Dans SQL Server Management Studio, utilisez l’Explorateur d’objets pour vous connecter à l’instance de SQL Server qui héberge le réplica principal du nouveau groupe de disponibilité. Sélectionnez Haute disponibilité Always On, cliquez sur le nouveau groupe de disponibilité, puis sélectionnez Écouteurs de groupe de disponibilité. Vous devriez trouver l’écouteur.

9. Cliquez avec le bouton droit sur l’écouteur, sélectionnez Propriétés, tapez le numéro de port approprié pour l’écouteur, puis sélectionnez OK.

   

Cela garantit que les applications qui utilisent l’écouteur peuvent toujours l’utiliser pour se connecter à l’instance de SQL Server qui héberge les bases de données de production sans interruption. Le groupe de disponibilité d’origine peut désormais être complètement supprimé et recréé. Ou les bases de données et les réplicas peuvent être ajoutés au nouveau groupe de disponibilité.

Si vous recréez le groupe de disponibilité d’origine, vous devez réaffecter l’écouteur au rôle de groupe de disponibilité, configurer la dépendance entre la nouvelle ressource de groupe de disponibilité et l’écouteur, puis réaffecter le port à l’écouteur. Pour ce faire, procédez comme suit :

1. Démarrez le Gestionnaire du cluster de basculement, puis sélectionnez Rôles dans le volet gauche. Dans le volet qui répertorie les rôles, cliquez sur le nouveau groupe de disponibilité qui héberge l’écouteur.
2. Dans le volet central inférieur sous l’onglet Ressources, cliquez avec le bouton droit sur l’écouteur, sélectionnez Autres actions, puis sélectionnez Affecter à un autre rôle. Dans la boîte de dialogue, choisissez le groupe de disponibilité recréé, puis sélectionnez OK.
3. Dans le volet Rôles , cliquez sur le groupe de disponibilité recréé. Dans le volet central inférieur, sous l’onglet Ressources , vous devez maintenant voir le groupe de disponibilité recréé et la ressource de l’écouteur. Cliquez avec le bouton droit sur la ressource de groupe de disponibilité recréé, puis sélectionnez Propriétés.
4. Sélectionnez l’onglet Dépendances , sélectionnez la ressource de l’écouteur dans la zone de liste déroulante, puis sélectionnez OK.
5. Dans SQL Server Management Studio, utilisez l’Explorateur d’objets pour vous connecter à l’instance de SQL Server qui héberge le réplica principal du groupe de disponibilité recréé. Sélectionnez Haute disponibilité Always On, cliquez sur le nouveau groupe de disponibilité, puis sélectionnez Écouteurs de groupe de disponibilité. Vous devriez trouver l’écouteur.
6. Cliquez avec le bouton droit sur l’écouteur, sélectionnez Propriétés, tapez le numéro de port approprié pour l’écouteur, puis sélectionnez OK.

Méthode 2 : Associer l’écouteur à une instance de cluster de basculement SQL Server existante (SQLFCI)

Si vous hébergez votre groupe de disponibilité sur une instance de cluster de basculement SQL Server (SQLFCI), vous pouvez associer la ressource cluster de l’écouteur au groupe de ressources cluster SQLFCI lors de la suppression, puis recréer le groupe de disponibilité.

1. Démarrez le Gestionnaire du cluster de basculement, puis sélectionnez Rôles dans le volet gauche.

2. Dans le volet qui répertorie les rôles, sélectionnez le groupe de disponibilité d’origine.

3. Dans le volet central inférieur sous l’onglet Ressources , cliquez avec le bouton droit sur la ressource du groupe de disponibilité, puis sélectionnez Propriétés.

4. Sélectionnez l’onglet Dépendances , supprimez la dépendance à l’écouteur, puis sélectionnez OK.

5. Dans le volet central inférieur sous l’onglet Ressources, cliquez avec le bouton droit sur l’écouteur, sélectionnez Autres actions, puis sélectionnez Affecter à un autre rôle.

6. Dans la boîte de dialogue Attribuer une ressource au rôle , cliquez sur l’instance FCI SQL Server, puis sélectionnez OK.

   

7. Dans le volet Rôles , sélectionnez le groupe SQLFCI. Dans le volet central inférieur, sous l’onglet Ressources , vous devez maintenant voir la nouvelle ressource d’écouteur.

Cela garantit que les applications qui utilisent l’écouteur peuvent toujours l’utiliser pour se connecter à l’instance de SQL Server qui héberge les bases de données de production sans interruption. Le groupe de disponibilité d’origine peut maintenant être supprimé et recréé. Ou les bases de données et les réplicas peuvent être ajoutés au nouveau groupe de disponibilité.

Une fois le groupe de disponibilité recréé, réaffectez l’écouteur au rôle de groupe de disponibilité. Ensuite, configurez la dépendance entre la nouvelle ressource de groupe de disponibilité et l’écouteur, puis réaffectez le port à l’écouteur :

1. Démarrez le Gestionnaire du cluster de basculement, puis sélectionnez Rôles dans le volet gauche.
2. Dans le volet qui répertorie les rôles, cliquez sur le rôle SQLFCI d’origine.
3. Dans le volet central inférieur, sous l’onglet Ressources, cliquez avec le bouton droit sur l’écouteur, sélectionnez Autres actions, puis sélectionnez Affecter à un autre rôle.
4. Dans la boîte de dialogue, cliquez sur le groupe de disponibilité recréé, puis sélectionnez OK.
5. Dans le volet Rôles , sélectionnez le nouveau groupe de disponibilité.
6. Sous l’onglet Ressources , vous devez voir le nouveau groupe de disponibilité et la ressource de l’écouteur. Cliquez avec le bouton droit sur la nouvelle ressource de groupe de disponibilité, puis sélectionnez Propriétés.
7. Sélectionnez l’onglet Dépendances , sélectionnez la ressource de l’écouteur dans la zone de liste déroulante, puis sélectionnez OK.
8. Dans SQL Server Management Studio, utilisez l’Explorateur d’objets pour vous connecter à l’instance de SQL Server qui héberge le réplica principal du nouveau groupe de disponibilité.
9. Sélectionnez Haute disponibilité Always On, cliquez sur le nouveau groupe de disponibilité, puis sélectionnez Écouteurs de groupe de disponibilité. Vous devriez trouver l’écouteur.
10. Cliquez avec le bouton droit sur l’écouteur, sélectionnez Propriétés, tapez le numéro de port approprié pour l’écouteur, puis sélectionnez OK.

Méthode 3 : Supprimer le groupe de disponibilité, puis recréer le groupe de disponibilité et l’écouteur avec le même nom d’écouteur

Cette méthode entraîne une petite panne pour les applications actuellement connectées, car le groupe de disponibilité et l’écouteur sont supprimés, puis recréés :

1. Supprimez le groupe de disponibilité.

   Note

   Cela supprime également l’écouteur.

2. Créez immédiatement un groupe de disponibilité vide qui inclut la définition de l’écouteur sur le même serveur qui héberge les bases de données de production.

   Par exemple, supposons que l’écouteur de votre groupe de disponibilité est aglisten. L’instruction Transact-SQL suivante crée un groupe de disponibilité sans base de données primaire ou secondaire, mais il crée également un écouteur nommé aglisten. Les applications peuvent utiliser cet écouteur pour se connecter.

   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
   

3. Récupérez la base de données endommagée. Ensuite, ajoutez-le et le réplica secondaire au groupe de disponibilité.