Gérer la récupération de base de données accélérée

  • Article

S’applique à : SQL Server 2019 (15.x)

Cet article contient des informations sur les meilleures pratiques pour la gestion et la configuration de la récupération de base de données accélérée (ADR) dans SQL Server 2019 (15.x) et versions ultérieures. Pour plus d’informations sur la récupération ADR sur Azure SQL, consultez Récupération de base de données accélérée dans Azure SQL.

Remarque

Dans la base de données Azure SQL et Azure SQL Managed Instance, la récupération de base de données accélérée (ADR) est activée sur toutes les bases de données et ne peut être désactivée. Si vous observez des problèmes liés à l’utilisation du stockage, à un nombre élevé de transactions interrompues ou à d’autres facteurs, consultez Résolution des problèmes de récupération de base de données accélérée ou contactez le support Azure.

Qui doit envisager d’utiliser la récupération de base de données accélérée ?

De nombreux clients trouvent que la récupération de base de données accélérée (ADR) est une technologie précieuse pour améliorer le temps de récupération. Une accumulation des facteurs ci-dessous doit être prise en compte avant de décider quelles bases de données doivent utiliser la récupération ADR. Évaluez les facteurs ci-dessous et déterminez si l’accumulation de facteurs pèse en faveur de l’utilisation de la récupération ADR ou non.

  • L’ADR est recommandé pour les charges de travail comportant des transactions de longue durée qui ne peuvent être évitées. Par exemple, dans les cas où les transactions à exécution longue risquent d’être restaurées, l’ADR peut vous aider.

  • ADR est recommandé pour les charges de travail qui ont rencontré des cas où des transactions actives provoquent une augmentation significative du journal des transactions.

  • La récupération ADR est recommandée pour les charges de travail qui ont connu de longues périodes d’indisponibilité d’une base de données en raison de la récupération longue de SQL Server (par exemple un redémarrage inattendu de SQL Server ou une restauration de transaction manuelle).

  • ADR n'est pas prise en charge pour les bases de données inscrites pour la mise en miroir de bases de données.

  • La récupération ADR n’est pas recommandée pour les bases de données dont la taille est supérieure à 100 To en raison du nettoyeur de version du magasin de versions persistantes (PVS) à thread unique.

  • Si votre application effectue de nombreuses mises à jour incrémentielles qui ne sont pas traitées par lot, comme la mise à jour d’un enregistrement à chaque fois qu’une ligne est consultée/insérée, votre charge de travail n’est peut-être pas optimale pour la récupération ADR. Envisagez de réécrire les requêtes d’application dans des mises à jour par lots quand cela est possible, jusqu’à la fin de la commande et réduisez un grand nombre de petites transactions de mise à jour.

Déterminez si votre charge de travail est adaptée à la récupération ADR

Une fois que vous avez activé la récupération ADR dans votre charge de travail, recherchez les signes indiquant que le magasin de versions persistantes (PVS) n’est pas en mesure de suivre. Il est recommandé de surveiller l’intégrité de la fonctionnalité ADR à l’aide des méthodes expliquées à la section Résolution des problèmes de récupération de base de données accélérée (ADR) sur SQL Server.

La récupération ADR n’est pas recommandée pour les environnements de base de données avec un nombre élevé de mises à jour/suppressions, comme le traitement OLTP à volume élevé, sans période de repos/récupération permettant au processus de nettoyage du magasin de versions persistantes (PVS) de récupérer de l’espace. En règle générale, les cycles opérationnels métier prévoient ce temps. Toutefois, dans certains scénarios, vous pouvez lancer le processus de nettoyage du magasin de versions persistantes (PVS) manuellement pour tirer profit des conditions d’activité de l’application.

  • Si le processus de nettoyage du magasin de versions persistantes (PVS) s’exécute pendant une période prolongée, vous pouvez constater une augmentation du nombre de transactions abandonnées, ce qui entraîne l’augmentation de la taille du PVS. Utilisez la vue DMV sys.dm_tran_aborted_transactions pour signaler le nombre de transactions abandonnées et utilisez sys.dm_tran_persistent_version_store_stats pour signaler les heures de début et de fin du nettoyage, ainsi que la taille du PVS. Pour plus d’informations, consultez sys.dm_tran_persistent_version_store_stats.

  • Pour activer manuellement le processus de nettoyage du PVS entre les charges de travail ou pendant les fenêtres de maintenance, utilisez sys.sp_persistent_version_cleanup. Pour plus d’informations, consultez sys.sp_persistent_version_cleanup.

  • Les charges de travail comportant des requêtes à exécution longue dans les modes d’isolation SNAPSHOT ou READ COMMITTED SNAPSHOT peuvent retarder le nettoyage du PVS de l’ADR dans d’autres bases de données, ce qui entraîne la croissance du fichier PVS. Pour plus d’informations, reportez-vous à la section sur les longues analyses d’instantanés actifs dans la rubrique Résolution des problèmes de récupération de base de données accélérée. Cette option s’applique aux instances de SQL Server et d’Azure SQL Managed Instance, ou dans un pool élastique de la base de données Azure SQL.

Meilleures pratiques pour la récupération de base de données accélérée

Cette section contient une aide et des recommandations pour la récupération ADR.

  • Pour SQL Server, isolez le magasin de versions persistantes dans un groupe de fichiers sur un stockage de niveau supérieur, comme un disque SSD haut de gamme, un disque SSD avancé ou une mémoire persistante (PMEM), parfois appelée mémoire de classe de stockage (SCM). Pour plus d’informations, consultez Changer l’emplacement du magasin de versions persistantes pour un autre groupe de fichiers. Cette option n’est pas disponible pour la base de données Azure SQL et Azure SQL Managed Instance.

  • Assurez-vous que la base de données dispose de suffisamment d’espace pour l’utilisation du magasin de versions persistantes (PVS). Si la base de données ne dispose pas de suffisamment d’espace pour que la taille du Magasin de versions persistantes augmente, la génération d’une version par la récupération ADR échoue. La récupération ADR permet d’économiser de l’espace dans le magasin de versions par rapport au magasin de versions tempdb.

  • Évitez les transactions multiples et de longue durée dans la base de données. Bien qu’un objectif de la fonctionnalité ADR consiste à accélérer la récupération de la base de données en raison de la restauration par progression, plusieurs transactions de longue durée peuvent retarder le nettoyage de la version et augmenter la taille du PVS.

  • Évitez les transactions volumineuses avec des modifications de la définition de données ou des opérations DDL. La fonctionnalité ADR utilise un mécanisme de flux de journal système (SLOG, system log stream) pour effectuer le suivi des opérations DDL utilisées dans la récupération. Le SLOG n’est utilisé que pendant que la transaction est active. Le SLOG fait l’objet d’un point de contrôle, c’est pourquoi le fait d’éviter les transactions volumineuses qui utilisent SLOG peut améliorer les performances globales. Ces scénarios peuvent amener le SLOG à occuper davantage d’espace :

    • De nombreux DDL sont exécutés dans une seule transaction. Par exemple, dans une seule transaction, la création et la suppression rapides de tables temporaires.

    • Une table comporte un grand nombre de partitions/index qui sont modifiés. Par exemple, une opération DROP TABLE sur une telle table nécessiterait une grande réservation de mémoire SLOG, ce qui retarderait la troncation du journal des transactions, ainsi que les opérations d’annulation/de rétablissement. La solution de contournement peut consister à supprimer les index individuellement et progressivement, puis à supprimer la table. Pour plus d’informations sur SLOG, consultez Composants de la récupération de base de données accélérée.

  • Empêchez ou réduisez les situations abandonnées inutiles. Un taux d’abandon élevé entraîne une pression sur le nettoyeur du PVS et une baisse des performances de la fonctionnalité ADR. Les abandons peuvent être dus à un taux élevé de blocages, des clés dupliquées ou d’autres violations de contrainte.

    • La vue DMV sys.dm_tran_aborted_transactions affiche toutes les transactions abandonnées sur l’instance SQL Server. La colonne nested_abort indique que la transaction est validée, mais qu’il existe des parties abandonnées (points de sauvegarde ou transactions imbriquées) qui peuvent bloquer le processus de nettoyage du PVS. Pour plus d’informations, consultez sys.dm_tran_aborted_transactions (Transact-SQL).

Activation et contrôle de la récupération de base de données accélérée

Remarque

Dans la base de données Azure SQL et Azure SQL Managed Instance, la récupération de base de données accélérée (ADR) est activée sur toutes les bases de données. Elle ne peut être ni désactivée ni déplacée dans un autre groupe de fichiers.

La récupération de base de données accélérée est désactivée par défaut dans SQL Server 2019 (15.x) et peut être contrôlée avec la syntaxe DDL :

ALTER DATABASE [DB] SET ACCELERATED_DATABASE_RECOVERY = {ON | OFF};

Utilisez cette syntaxe pour contrôler si la fonctionnalité est activée ou désactivée, et désignez un groupe de fichiers spécifique pour les données du magasin de versions persistantes. Si aucun groupe de fichiers n’est spécifié, le magasin de versions persistantes est stocké dans le groupe de fichiers PRIMARY.

Un verrou exclusif est nécessaire sur la base de données pour modifier cet état. Cela signifie que la commande ALTER DATABASE est bloquée jusqu’à ce que toutes les sessions actives aient disparu et que toutes les nouvelles sessions attendent derrière la commande ALTER DATABASE. S’il est important d’effectuer l’opération et de supprimer le verrou, vous pouvez utiliser la clause de fin WITH ROLLBACK [IMMEDIATE | AFTER {number} SECONDS | NO_WAIT] pour abandonner toutes les sessions actives dans la base de données. Pour plus d’informations, consultez les Options définies ALTER DATABASE.

Gestion du groupe de fichiers du magasin de versions persistantes

La fonctionnalité Récupération de base de données accélérée est basée sur les changements versionnés, les différentes versions d’un élément de données étant conservées dans le magasin de versions persistantes. Vous devez prendre en compte certaines considérations quant à l’emplacement du magasin de versions persistantes et à la gestion de la taille des données qui y sont stockées.

Pour activer la récupération de base de données accélérée sans spécifier de groupe de fichiers

Cette opération demande un accès exclusif à la base de données.

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON;
GO

Dans ce cas, quand le groupe de fichiers du magasin de versions persistantes n’est pas spécifié, c’est le groupe de fichiers PRIMARY qui contient ses données.

Pour activer la récupération de base de données accélérée et spécifier que le magasin de versions persistantes doit être stocké dans un groupe de fichiers

Vous pouvez configurer l’ADR pour qu’il utilise un autre groupe de fichiers que le groupe de fichiers PRIMARY par défaut pour contenir les données PVS.

Avant d’activer l’ADR dans un groupe de fichiers autre que PRIMARY, vous devez créer le groupe de fichiers et le fichier de données.

Créez le groupe de fichiers VersionStoreFG et créez un nouveau fichier de données dans le groupe de fichiers. Par exemple :

ALTER DATABASE [MyDatabase] ADD FILEGROUP [VersionStoreFG];
GO
ALTER DATABASE [MyDatabase] ADD FILE ( NAME = N'VersionStoreFG'
, FILENAME = N'E:\DATA\VersionStore.ndf'
, SIZE = 8192KB , FILEGROWTH = 65536KB )
TO FILEGROUP [VersionStoreFG];
GO

Ce n’est qu’après la création du groupe de fichiers et d’un fichier de données secondaire que vous pouvez activer l’ADR et spécifier que le PVS doit être stocké dans un groupe de fichiers spécifique. Cette opération demande un accès exclusif à la base de données.

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON
(PERSISTENT_VERSION_STORE_FILEGROUP = [VersionStoreFG]);

Pour désactiver la fonctionnalité Récupération de base de données accélérée

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = OFF;
GO

Même après la désactivation de la fonctionnalité Récupération de base de données accélérée, il restera des versions stockées dans le magasin de versions persistantes, qui sont toujours nécessaires au système pour la restauration logique.

Changer l’emplacement du magasin de versions persistantes pour un autre groupe de fichiers

Dans SQL Server, il peut être nécessaire de déplacer l’emplacement du magasin de versions persistantes vers un autre groupe de fichiers pour différentes raisons. Par exemple, le magasin de versions persistantes peut nécessiter plus d’espace ou un stockage plus rapide.

Le changement d’emplacement du magasin de versions persistantes est un processus en trois étapes.

  1. Désactivez la fonctionnalité ADR.

    ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = OFF;
GO

  2. Attendez que toutes les versions stockées dans le magasin de versions persistantes puissent être libérées.

    Pour être en mesure d’activer la récupération de base de données accélérée avec un nouvel emplacement pour le magasin de versions persistantes, vous devez d’abord vérifier que toutes les informations des versions ont été éliminées de l’emplacement précédent du magasin de versions persistantes. Pour forcer le nettoyage, exécutez cette commande :

    EXEC sys.sp_persistent_version_cleanup [database name];

    La procédure stockée sys.sp_persistent_version_cleanup est synchrone, ce qui signifie qu’elle ne se termine pas tant que toutes les informations des versions ne sont pas nettoyées dans le magasin de versions persistantes actuel. Une fois l’opération terminée, vous pouvez vérifier que les informations des versions sont effectivement supprimées en interrogeant la vue de gestion dynamique sys.dm_tran_persistent_version_store_stats et en examinant la valeur de persistent_version_store_size_kb.

    SELECT DB_Name(database_id), persistent_version_store_size_kb 
FROM sys.dm_tran_persistent_version_store_stats where database_id = [MyDatabaseID];

    Quand la valeur de persistent_version_store_size_kb est 0, vous pouvez réactiver la fonctionnalité ADR, en configurant le magasin de versions persistantes pour qu’il se trouve dans le nouveau groupe de fichiers.

  3. Activer la récupération ADR en spécifiant le nouvel emplacement du magasin de versions persistantes :

    ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON
(PERSISTENT_VERSION_STORE_FILEGROUP = [VersionStoreFG]);

Étapes suivantes