Option de basculement pour la détection de l’intégrité au niveau de la base de données du groupe de disponibilité

S'applique à :SQL Server

À compter de SQL Server 2016, l’option de détection de l’intégrité au niveau base de données (DB_FAILOVER) est disponible lors de la configuration d’un groupe de disponibilité Always On. La détection de l’état d’intégrité au niveau base de données indique quand une base de données n’est plus en ligne ou quand un problème se produit, puis déclenche le basculement automatique du groupe de disponibilité. Les exemples susceptibles de déclencher la détection de l’état de santé incluent une base de données en mode suspect, une base de données hors ligne et une base de données en cours de récupération (échec de la récupération). Pour plus d’informations, consultez Colonne State dans sys.databases.

La détection de l’intégrité au niveau base de données est activée pour le groupe de disponibilité dans son ensemble : par conséquent, la détection d’intégrité au niveau base de données surveille chaque base de données du groupe de disponibilité. Elle ne peut pas être activée sélectivement pour des bases de données spécifiques du groupe de disponibilité.

Avantages de l’option de détection de l’état de santé de la base de données

L’option de détection de l’état de santé au niveau de la base de données du groupe de disponibilité est largement recommandée pour aider à garantir la haute disponibilité de vos bases de données. Envisagez de l’activer pour tous les groupes de disponibilité. Si votre application dépend de la haute disponibilité de plusieurs bases de données, regroupez-les dans un groupe de disponibilité avec l’option de détection de l’intégrité des bases de données activée.

Par exemple, lorsque l’option de détection d’intégrité au niveau de la base de données est activée, si SQL Server ne pouvait pas écrire dans le fichier journal de transactions de l’une des bases de données, l’état de cette base de données changerait pour indiquer une défaillance, le groupe de disponibilité basculerait peu après, et votre application pourrait se reconnecter et continuer à fonctionner avec une interruption minimale une fois que les bases de données seraient de nouveau en ligne.

Activation de la détection de l’état de santé au niveau de la base de données

Bien qu’elle soit généralement recommandée, l’option d’intégrité des bases de données est désactivée par défaut afin de conserver la compatibilité descendante avec les paramètres par défaut des versions antérieures.

Il existe plusieurs méthodes simples pour activer le paramètre de détection de l’état de santé au niveau de la base de données :

1. Dans SQL Server Management Studio, connectez-vous au moteur de base de données de votre serveur SQL Server. Dans la fenêtre de l’Explorateur d’objets, cliquez avec le bouton droit sur le nœud Haute disponibilité Always On, puis démarrez l’Assistant Nouveau groupe de disponibilité. Cochez la case Détection de l’état d’intégrité au niveau de la base de données sur la page « Spécifier le nom ». Ensuite, complétez le reste des pages pour terminer l’Assistant.

   

2. Affichez les Propriétés d’un groupe de disponibilité existant dans SQL Server Management Studio. Connectez-vous à votre serveur SQL Server. Dans la fenêtre de l’Explorateur d’objets, développez le nœud Haute disponibilité Always On. Développer les groupes de disponibilité. Cliquez avec le bouton droit sur le groupe de disponibilité et choisissez Propriétés. Activez l’option Détection de l’état d’intégrité au niveau base de données, puis cliquez sur OK ou générez un script avec la modification.

   

3. Syntaxe Transact-SQL pour CREATE AVAILABILITY GROUP. Le paramètre DB_FAILOVER accepte les valeurs ON ou OFF.

   CREATE AVAILABILITY GROUP [Contoso-ag]
   WITH (DB_FAILOVER=ON)
   FOR DATABASE [AutoHa-Sample]
   REPLICA ON
       N'SQLSERVER-0' WITH (ENDPOINT_URL = N'TCP://SQLSERVER-0.DOMAIN.COM:5022',
         FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT),
       N'SQLSERVER-1' WITH (ENDPOINT_URL = N'TCP://SQLSERVER-1.DOMAIN.COM:5022',
        FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
   

4. Syntaxe Transact-SQL vers ALTER AVAILABILITY GROUP. Le paramètre DB_FAILOVER accepte les valeurs ON ou OFF.

   ALTER AVAILABILITY GROUP [Contoso-ag] SET (DB_FAILOVER = ON);
   
   ALTER AVAILABILITY GROUP [Contoso-ag] SET (DB_FAILOVER = OFF);
   

Mises en garde

Il est important de noter qu’actuellement l’option Détection de l’état d’intégrité au niveau base de données n’implique pas que SQL Server surveille le bon fonctionnement des disques. SQL Server ne surveille pas non plus directement la disponibilité des fichiers de base de données. Si un lecteur de disque est en échec ou devient non disponible, ce seul événement ne déclenche pas nécessairement le basculement automatique du groupe de disponibilité.

Par exemple, quand une base de données est inactive, sans aucune transaction active et sans qu’aucune écriture physique ne se produise, si certains fichiers de base de données deviennent inaccessibles, SQL Server peut n’effectuer aucune E/S en lecture ou en écriture sur les fichiers, ni changer l’état de cette base de données immédiatement : un basculement n’est donc pas déclenché. Par la suite, quand un point de contrôle de base de données est effectué, ou qu’il se produit une lecture ou une écriture physique pour répondre à une requête, SQL Server peut alors découvrir le problème relatif au fichier et réagir en changeant l’état de la base de données. Ensuite, le groupe de disponibilité pour lequel la détection d’intégrité au niveau base de données a été activée bascule en raison du changement de l’intégrité de la base de données.

Voici un autre exemple : quand le moteur de base de données SQL Server doit lire une page de données pour répondre à une requête, si la page de données est mise en cache dans le pool de mémoires tampons, un accès physique au disque peut ne pas être nécessaire pour répondre à la demande de la requête. Ainsi, un fichier de données manquant ou non disponible peut ne pas déclencher immédiatement un basculement automatique, même quand l’option d’intégrité de base de données est activée, car l’état de la base de données n’est pas mis à jour immédiatement.

Le basculement des bases de données est distinct de la politique de basculement flexible

La détection de l’état d’intégrité au niveau base de données implémente une stratégie de basculement flexible qui configure les seuils d’intégrité du processus SQL Server pour la stratégie de basculement. La détection de l’état d’intégrité au niveau base de données est configurée à l’aide du paramètre DB_FAILOVER, tandis que l’option FAILURE_CONDITION_LEVEL du groupe de disponibilité est distincte pour la configuration de la détection de l’état d’intégrité du processus SQL Server. Les deux options sont indépendantes.

Gestion et surveillance de la détection de l’état de santé au niveau de la base de données

Vues de gestion dynamiques

La vue de gestion dynamique système sys.availability_groups contient une colonne db_failover, qui indique si l’option de détection de l’état d’intégrité au niveau base de données est désactivée (0) ou activée (1).

select name, db_failover from sys.availability_groups

Exemple de sortie de la vue de gestion dynamique :

Journal des erreurs

Le journal d’erreurs de SQL Server (ou le texte retourné par sp_readerrorlog) affiche le message d’erreur 41653 lorsqu’un groupe de disponibilité a effectué un basculement en raison des vérifications de détection de l’intégrité au niveau de la base de données.

Par exemple, cet extrait de journal des erreurs montre qu’une écriture dans le journal des transactions a échoué en raison d’un problème de disque, et que par la suite, la base de données nommée AutoHa-Sample a été arrêtée, ce qui a déclenché le basculement du groupe de disponibilité par la détection de l’état d’intégrité au niveau base de données.

25-04-2016 12:20:21.08 spid1s Erreur : 17053, Gravité : 16, État : 1.

25-04-2016 12:20:21.08 spid1s SQLServerLogMgr::LogWriter: erreur du système d’exploitation 21(Le périphérique n’est pas prêt.). 25/04/2016 12:20:21.08 spid1s Erreur d’écriture lors du vidage du journal.

25-04-2016 12:20:21.08 spid79 Erreur : 9001, Gravité : 21, État : 4.

25-04-2016 12:20:21.08 spid79 Le journal de la base de données « AutoHa-Sample » n’est pas disponible. Consultez le journal des événements pour voir s'il contient des messages d'erreur liés à ce problème. Résolvez toutes les erreurs et redémarrez la base de données.

25-04-2016 12:20:21.15 spid79 Erreur : 41653, Gravité : 21, État : 1.

25-04-2016 12:20:21.15 spid79 La base de données « AutoHa-Sample » a rencontré une erreur (type d’erreur : 2 ’DB_SHUTDOWN’) entraînant une défaillance du groupe de disponibilité « Contoso-ag ». Reportez-vous au journal des erreurs de SQL Server pour en apprendre davantage sur les erreurs rencontrées. Si cette situation persiste, contactez l’administrateur système.

2016-04-25 12:20:21.17 spid79 Informations d’état de la base de données 'AutoHa-Sample' - LSN renforcé : '(34:664:1)' LSN de validation : '(34:656:1)' Heure de validation : 'Apr 25 2016 12:19PM'

2016-04-25 12:20:21.19 spid15s La connexion au groupe de disponibilité Always On avec la base de données secondaire a été interrompue pour la base de données principale « AutoHa-Sample » sur le réplica de disponibilité « SQLServer-0 » avec l’ID de réplica : {c4ad5ea4-8a99-41fa-893e-189154c24b49}. Ce message est fourni uniquement à titre d'information. Aucune action de l'utilisateur n'est requise.

2016-04-25 12:20:21.21 spid75 Always On : le réplica local du groupe de disponibilité « Contoso-ag » se prépare à passer au rôle de résolution en réponse à une demande du cluster de basculement Windows Server (WSFC). Ce message est fourni uniquement à titre d'information. Aucune action de l'utilisateur n'est requise.

25-04-2016 12:20:21.21 spid75 L’état du réplica de disponibilité local du groupe de disponibilité « ag » est passé de « PRIMARY_NORMAL » à « RESOLVING_NORMAL ». L’état a changé, car le groupe de disponibilité est en train de se mettre hors connexion. Le réplica passe hors connexion, car le groupe de disponibilité associé a été supprimé, ou l’utilisateur a passé hors connexion le groupe de disponibilité associé dans la console de gestion WSFC, ou le groupe de disponibilité bascule sur une autre instance de SQL Server. Pour plus d’informations, consultez le journal des erreurs SQL Server, la console de gestion WSFC ou le journal WSFC.

Événement étendu sqlserver.availability_replica_database_fault_reporting

Il s’agit d’un nouvel événement étendu défini à compter de SQL Server 2016, qui est déclenché par la détection de l’état d’intégrité au niveau base de données. Le nom de l’événement est sqlserver.availability_replica_database_fault_reporting

Ce XEvent est déclenché uniquement sur la réplique principale. Ce XEvent est déclenché lorsqu’un problème d’intégrité au niveau de la base de données est détecté pour une base de données hébergée au sein d’un groupe de disponibilité.

Voici un exemple de création d’une session XEvent qui capture cet événement. Comme aucun chemin n’est spécifié, le fichier de sortie XEvent doit se trouver dans le chemin par défaut du journal des erreurs SQL Server. Exécutez ceci sur le réplica principal de votre groupe de disponibilité :

Exemple de script de session d’événements étendus

CREATE EVENT SESSION [AlwaysOn_dbfault] ON SERVER
ADD EVENT sqlserver.availability_replica_database_fault_reporting
ADD TARGET package0.event_file(SET filename=N'dbfault.xel',max_file_size=(5),max_rollover_files=(4))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION AlwaysOn_dbfault ON SERVER STATE=START
GO

Sortie des événements étendus

Dans SQL Server Management Studio, connectez-vous au serveur SQL Server principal, développez le nœud Gestion puis développez Événements étendus. Recherchez la session (AlwaysOn_dbfault étant le nom utilisé dans l’exemple ci-dessus) et développez-la pour afficher les fichiers de sortie. Sélectionnez le fichier de sortie : le fichier d’événements s’ouvre alors dans un nouvel onglet.

Explication des champs :

Dans cet exemple de sortie, le champ fault_type indique qu’un événement critique s’est produit dans le groupe de disponibilité Contoso-ag, sur le réplica nommé SQLSERVER-1, en raison de la base de données AutoHa-Sample2, avec le type d’erreur 2 - Shutdown (arrêt).

Références connexes

• CREATE AVAILABILITY GROUP

• ALTER AVAILABILITY GROUP

• Stratégie de basculement flexible pour le basculement automatique d’un groupe de disponibilité (SQL Server)

• Améliorer la stratégie de basculement Always On pour tester les données et les lecteurs de journal de base de données SQL Server

• Événements étendus

• Guide pratique pour configurer un groupe de disponibilité Always On pour effectuer un basculement lorsque le fichier de données de base de données n’est pas disponible