Résoudre les problèmes Always On bases de données de disponibilité dans l’état En attente de récupération ou Suspect dans SQL Server

Cet article décrit les erreurs et les limitations d’une base de données de disponibilité dans Microsoft SQL Server qui se trouve dans un Recovery Pending état ou Suspect et explique comment restaurer la base de données avec toutes ses fonctionnalités dans un groupe de disponibilité.

Version d’origine du produit : SQL Server 2012
Numéro de la 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 Recovery Pending état ou Suspect 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 Recovery Pending ou Suspect . Lorsque vous interrogez les vues de gestion dynamique (DMV) au niveau du réplica principal à l’aide du script SQL suivant, la base de données peut être signalée dans un NOT_HEALTHY état et RECOVERY_PENDING ou 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 Non synchronisation/récupération en attente 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.

• La status de 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é :

  Message 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 supprimer la base de données de son groupe de disponibilité.

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

• La status de 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é :

  Message 3752, niveau 16, état 1, ligne 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é.

• La status de 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 :

  Message 35240, niveau 16, état 14, ligne 1
  DatabaseName <> ne peut pas être joint ou disjoint à 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 qui possède la base de données en attente de récupération est dans le rôle secondaire. Dans ce cas, vous essayez d’exécuter à nouveau le script SQL suivant pour supprimer la base de données du groupe de disponibilité au 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 Récupération en attente :

  Msg 921, Niveau 16, État 112, Ligne 1
  La base de données <DatabaseName> n’a pas encore été récupérée. Attendez et réessayez.

Résolution lorsque la base de données est 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 est 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 à la nouvelle réplica principale, 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 cela, 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 vers un rôle secondaire.

1. Connectez-vous au serveur qui exécute SQL Server et qui héberge 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. Rajoutez 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 d’écouteur est également supprimée et interrompt la connectivité de l’application aux bases de données de disponibilité.

Pour réduire le temps d’arrêt des applications, 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 conserver l’écouteur lors de la suppression et de la recréation du groupe de disponibilité.

1. Sur la 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 base de données ou de réplica 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 central 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 de 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 Attribuer à un autre rôle.

5. Dans la boîte de dialogue Attribuer une source à un 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-central, sous l’onglet Ressources , vous devez maintenant voir le nouveau groupe de disponibilité et la ressource d’é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 d’écouteur dans la zone de liste déroulante, puis sélectionnez OK.

   

8. Dans SQL Server Management Studio, utilisez Explorateur d'objets pour vous connecter au instance de SQL Server qui héberge le réplica principal du nouveau groupe de disponibilité. Sélectionnez Always On Haute disponibilité, cliquez sur le nouveau groupe de disponibilité, puis sélectionnez Écouteurs de groupe de disponibilité. Vous devez 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 permet de s’assurer que les applications qui utilisent l’écouteur peuvent toujours l’utiliser pour se connecter au 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 complètement supprimé et recréé. Vous pouvez également ajouter les bases de données et les réplicas 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 cela, 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 inférieur central sous l’onglet Ressources , cliquez avec le bouton droit sur l’écouteur, sélectionnez Autres actions, puis sélectionnez Attribuer à 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 d’é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 d’écouteur dans la zone de liste déroulante, puis sélectionnez OK.
5. Dans SQL Server Management Studio, utilisez Explorateur d'objets pour vous connecter au instance de SQL Server qui héberge le réplica principal du groupe de disponibilité recréé. Sélectionnez Always On Haute disponibilité, cliquez sur le nouveau groupe de disponibilité, puis sélectionnez Écouteurs de groupe de disponibilité. Vous devez 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 en cluster de l’écouteur au groupe de ressources en cluster SQLFCI pendant que vous supprimez puis recréez 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 de l’écouteur, puis sélectionnez OK.

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

6. Dans la boîte de dialogue Attribuer une ressource à un rôle, cliquez sur le instance SQL Server FCI, 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 permet de s’assurer que les applications qui utilisent l’écouteur peuvent toujours l’utiliser pour se connecter au 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éé. Vous pouvez également ajouter les bases de données et les réplicas 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 Attribuer à 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 d’é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 d’écouteur dans la zone de liste déroulante, puis sélectionnez OK.
8. Dans SQL Server Management Studio, utilisez Explorateur d'objets pour vous connecter au instance de SQL Server qui héberge le réplica principal du nouveau groupe de disponibilité.
9. Sélectionnez Always On Haute disponibilité, cliquez sur le nouveau groupe de disponibilité, puis sélectionnez Écouteurs de groupe de disponibilité. Vous devez 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é.

   Remarque

   Cela supprimera également l’écouteur.

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

   Par exemple, supposons que votre écouteur de 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 elle 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é.