Partager via

Instantanés de base de données (SQL Server)

  • Article

S'applique à :SQL Server

Un instantané de base de données est une vue statique en lecture seule d’une base de données SQL Server (la base de données source). Il est cohérent de façon transactionnelle avec la base de données source à partir de la création de l’instantané et réside toujours sur la même instance de serveur que sa base de données source. Bien que les captures instantanées de base de données fournissent une vue en lecture seule des données dans le même état que lors de la création de la capture instantanée, la taille du fichier de capture instantanée augmente à mesure que des changements sont apportés à la base de données source.

Bien que les captures instantanées de base de données puissent être utiles lors des mises à niveau majeures du schéma et permettent de revenir à un état précédent, il est essentiel de comprendre que les captures instantanées ne remplacent pas le besoin de sauvegardes régulières. Vous ne pouvez pas sauvegarder ou restaurer des instantanés de base de données, ce qui signifie qu’ils doivent être utilisés avec une stratégie de sauvegarde robuste pour garantir la protection et la récupération des données en cas de perte ou d’altération des données.

Les instantanés de base de données sont créés avec la syntaxe CREATE DATABASE T-SQL, à l’aide de la syntaxe AS SNAPSHOT OF.

Plusieurs instantanés peuvent exister sur une base de données source donnée. Chaque instantané de base de données persiste jusqu’à ce que le propriétaire de la base de données le supprime explicitement.

Remarque

Les instantanés de base de données n'ont aucun rapport avec les sauvegardes d'instantanés,les sauvegardes d'instantanés Transact-SQL, les instantanés liés à l'isolation des transactions ou la réplication d'instantanés.

Vue d’ensemble des fonctionnalités

Les instantanés de base de données fonctionnent au niveau de la page de données. Toute page de la base de données source qui n'a pas encore été modifiée est copiée de la base de données source vers l'instantané. L'instantané stocke la page d'origine, conservant ainsi les enregistrements de données tels qu'ils étaient lorsque l'instantané a été créé. Le même processus est répété pour chaque page en cours de modification pour la première fois. Pour l’utilisateur, un instantané de base de données n’apparaît jamais modifié, car les opérations de lecture sur un instantané de base de données accèdent toujours aux pages de données d’origine, quel que soit leur emplacement.

L’instantané stocke les pages copiées d’origine à l’aide d’un ou de plusieurs fichiers sparse . Initialement, un fichier sparse est un fichier vide qui ne contient aucune donnée utilisateur et n’a pas encore été alloué d’espace disque. La taille du fichier augmente à mesure que d’autres pages sont mises à jour dans la base de données source. La figure suivante illustre les effets de deux schémas de mise à jour distincts sur la taille d'un instantané. Le schéma de mise à jour A représente un environnement dans lequel seules 30% des pages d'origine sont mises à jour pendant la durée d'existence de l'instantané. Le schéma de mise à jour B correspond à un environnement dans lequel 80 % des pages d'origine sont mises à jour pendant la durée d'existence de l'instantané.

Diagramme montrant d’autres modèles de mise à jour et taille d’instantané.

Avantages

  • Les instantanés peuvent être utilisés à des fins de création de rapports.

    • Les clients peuvent interroger un instantané de base de données, ce qui permet d’écrire des rapports en fonction des données lors de la création de l’instantané.

  • Gestion des données d'historique pour la création de rapports.

    • Un instantané peut étendre l'accès utilisateur aux données à partir d'un moment donné dans le temps. Par exemple, vous pouvez créer un instantané de base de données pour les rapports ultérieurs à la fin d’une période donnée (par exemple, un trimestre financier). Vous pouvez ensuite exécuter des rapports de fin de période sur l'instantané. Si l’espace disque le permet, vous pouvez également conserver des instantanés de fin de période indéfiniment, ce qui permet aux requêtes sur les résultats de ces périodes, par exemple, d’examiner les performances de l’organisation.

  • En utilisant une base de données miroir que vous maintenez pour des raisons de disponibilité afin de décharger les rapports.

    • L'utilisation d'instantanés de bases de données avec mise en miroir vous permet de rendre les données du serveur miroir accessibles à des fins de création de rapports. En outre, l’exécution de requêtes sur la base de données miroir peut libérer des ressources pour le principal. Pour plus d’informations, consultez Mise en miroir et instantanés de bases de données (SQL Server).

  • Protection des données contre les erreurs d'administration.

  • Si une erreur utilisateur se produit dans une base de données source, vous pouvez la rétablir à son état lorsqu’un instantané de base de données est créé. La perte de données est limitée aux mises à jour de la base de données depuis la création de l'instantané.

    • Par exemple, avant d’effectuer des mises à jour majeures, telles qu’une mise à jour en bloc ou une modification de schéma, créez un instantané de base de données sur la base de données pour protéger les données. Si vous commettez une erreur, vous pourrez utiliser l'instantané pour restaurer la base de données telle qu'elle était au moment de l'instantané. Revenir en arrière est plus rapide que de restaurer à partir d’une sauvegarde ; toutefois, vous ne pouvez pas passer à une version ultérieure par la suite.

      Important

      La restauration ne fonctionne pas sur une base de données hors connexion ou endommagée. Par conséquent, des sauvegardes et des tests réguliers de votre plan de restauration sont nécessaires pour protéger une base de données.

      Remarque

      Les instantanés de base de données dépendent de la base de données source. Par conséquent, l’utilisation de captures instantanées pour rétablir une base de données n’est pas un substitut à votre stratégie de sauvegarde et de restauration. L'exécution de toutes les sauvegardes planifiées reste primordiale. Si vous devez restaurer la base de données source au point où vous avez créé un instantané de base de données, implémentez une stratégie de sauvegarde qui vous permet de le faire.

  • Protection des données contre les erreurs des utilisateurs.

  • En créant régulièrement des instantanés de base de données, vous pouvez atténuer l’impact d’une erreur utilisateur majeure, telle qu’une table supprimée. Pour encore plus de protection, vous pouvez créer une série d'instantanés couvrant une période suffisamment importante pour identifier et réagir à la plupart des erreurs commises par les utilisateurs. Par exemple, selon vos ressources de disque, vous pouvez conserver 6 à 12 captures instantanées propagées couvrant un intervalle de 24 heures. Chaque fois qu'un instantané est créé, l'instantané le plus ancien est supprimé.

    • Pour récupérer après une erreur, vous pouvez rétablir la base de données telle qu'elle était juste avant l'erreur. Revenir en arrière est potentiellement beaucoup plus rapide que de restaurer à partir d’une sauvegarde ; toutefois, vous ne pouvez pas continuer par la suite.

    • Vous pouvez également reconstruire manuellement une table supprimée ou d’autres données perdues à partir des informations d’un instantané. Par exemple, vous pouvez copier en bloc les données de l’instantané dans la base de données et les fusionner manuellement.

      Remarque

      Le nombre d'instantanés maintenus à un moment donné, la fréquence de création des instantanés et la durée de conservation des instantanés dépendent de votre objectif final.

  • Gestion d'une base de données test.

    • Dans un environnement de test, il peut être utile que la base de données contienne des données identiques au début de chaque série de tests lors de l’exécution répétée d’un protocole de test. Avant d’exécuter la première série, un développeur d’applications ou un testeur peut créer un instantané de la base de données de test. À l'issue de chaque test, il est possible de revenir rapidement à l'état antérieur de la base de données en rétablissant l'instantané.

Termes et définitions

Les instantanés de base de données dans SQL Server impliquent plusieurs termes et définitions clés. Un instantané de base de données est une vue statique en lecture seule d’une base de données (appelée base de données source) à un moment donné. La base de données source est la base de données d’origine sur laquelle repose l’instantané, et elle doit rester en ligne et accessible pour que l’instantané soit utilisable. fichiers partiellement alloués stockent les pages d’origine de la base de données source qui ont été modifiées depuis la création de l’instantané. Ces fichiers sont initialement vides et se développent à mesure que des modifications se produisent dans la base de données source. Il est essentiel de comprendre ces termes pour gérer et utiliser efficacement les instantanés de base de données dans SQL Server.

Instantané de base de données

Vue statique, en lecture seule et cohérente sur le plan transactionnel d'une base de données (la base de données source).

Base de données source

Pour un instantané de base de données, la base de données sur laquelle l'instantané a été créé. Les instantanés de base de données dépendent de la base de données source. Les instantanés d'une base de données doivent résider sur la même instance de serveur que la base de données. En outre, si cette base de données devient indisponible pour une raison quelconque, ses instantanés deviennent indisponibles.

Fichier partiellement alloué

Le système de fichiers NTFS fournit un fichier qui nécessite beaucoup moins d’espace disque que ce qui serait nécessaire. Un fichier partiellement alloué est utilisé pour stocker les pages copiées dans un instantané de base de données. À sa création, un fichier partiellement alloué occupe très peu d'espace disque. Lorsque des données sont écrites dans un instantané de base de données, NTFS alloue de l'espace disque progressivement au fichier partiellement alloué correspondant.

Conditions préalables

La base de données source, qui peut utiliser n'importe quel mode de récupération, doit respecter les conditions préalables suivantes :

  • L’instance de serveur doit s’exécuter sur une édition de SQL Server qui prend en charge les captures instantanées de base de données.

  • La base de données source doit être en ligne, sauf s’il s’agit d’une base de données miroir au sein d’une session de mise en miroir de bases de données.

  • Vous pouvez créer un instantané de base de données dans un groupe de disponibilité sur n’importe quelle base de données primaire ou secondaire. Le rôle de réplica doit être PRIMARY ou SECONDARY, et non dans l'état RESOLVING.

  • Nous vous recommandons que l'état de synchronisation de base de données soit SYNCHRONIZING ou SYNCHRONIZED lorsque vous créez un instantané de base de données. Toutefois, les instantanés de base de données peuvent être créés lorsque l’état n’est PAS EN SYNCHRONISATION.

  • Pour créer un instantané de base de données dans une base de données miroir, la base de données doit être dans un état de mise en miroir SYNCHRONIZED.

  • La base de données source ne peut pas être configurée en tant que base de données partagée évolutive.

  • Avant SQL Server 2019, la base de données source n’a pas pu contenir de groupe de fichiers MEMORY_OPTIMIZED_DATA. La prise en charge des instantanés de base de données en mémoire a été ajoutée dans SQL Server 2019.

  • Tous les modes de récupération prennent en charge les instantanés de base de données.

Limitations relatives à la base de données source

Dans la mesure où il existe un instantané de base de données, les limitations suivantes s'appliquent à la base de données source de l'instantané :

  • La base de données ne peut pas être supprimée, détachée ou restaurée.

  • La sauvegarde de la base de données source fonctionne généralement, mais elle n’est pas affectée par les instantanés de base de données.

  • Les performances sont réduites en raison d'une augmentation des E/S sur la base de données source résultant d'une opération de copie en écriture dans l'instantané à chaque mise à jour de page.

  • Les fichiers ne peuvent pas être supprimés de la base de données source ou des instantanés.

Limitations sur les instantanés de base de données

Les instantanés de base de données dépendent de la base de données source et ne protègent pas contre les erreurs de disque ou la corruption. Par conséquent, bien qu’elles puissent être utiles à des fins de création de rapports ou pendant les modifications de schéma, elles doivent compléter, non remplacer, les pratiques de sauvegarde régulières. Si vous devez restaurer la base de données source au point où vous avez créé un instantané de base de données, implémentez une stratégie de sauvegarde qui vous permet de le faire.

Les limitations suivantes s'appliquent aux instantanés de base de données :

  • Un instantané de base de données doit être créé et demeurer sur la même instance de serveur que la base de données source.

  • Les instantanés de base de données fonctionnent toujours sur une base de données complète.

  • Les instantanés de base de données dépendent de la base de données source et ne sont pas redondants. Ils ne protègent pas contre les erreurs de disque ou d’autres types d’altération. Par conséquent, l’utilisation de captures instantanées pour rétablir une base de données n’est pas un substitut à votre stratégie de sauvegarde et de restauration. L'exécution de toutes les sauvegardes planifiées reste primordiale. Si vous devez restaurer la base de données source au point où vous avez créé un instantané de base de données, implémentez une stratégie de sauvegarde qui vous permet de le faire.

Lorsqu’une page qui est mise à jour sur la base de données source est envoyée à un instantané, si l’instantané manque d’espace disque ou rencontre une autre erreur, il devient suspect et doit être supprimé.

  • Les instantanés sont en lecture seule. Comme ils sont en lecture seule, ils ne peuvent pas être mis à jour. Par conséquent, les instantanés de base de données ne sont pas censés être viables après une mise à niveau.

  • Les instantanés des bases de données model, master et tempdb sont interdits.

  • Vous ne pouvez pas modifier les spécifications des fichiers d’instantané de base de données.

  • Vous ne pouvez pas supprimer les fichiers d’un instantané de base de données.

  • Vous ne pouvez pas sauvegarder ou restaurer des instantanés de base de données.

  • Vous ne pouvez pas attacher ou détacher des instantanés de base de données.

  • Vous ne pouvez pas créer d’instantanés de base de données sur le système de fichiers FAT32 ou les partitions RAW. Le système de fichiers NTFS fournit les fichiers clairsemés utilisés par les instantanés de base de données.

  • L’indexation de texte intégral n’est pas prise en charge sur les instantanés de base de données. Les catalogues de texte intégral ne sont pas propagés à partir de la base de données source.

  • Un instantané hérite des contraintes de sécurité de sa base de données source au moment de la création de l'instantané. Étant donné que les instantanés sont en lecture seule, les autorisations héritées ne peuvent pas être modifiées et les modifications d’autorisation apportées à la source ne sont pas reflétées dans les instantanés existants.

  • Un instantané reflète toujours l'état de groupes de fichiers au moment de la création de l'instantané : les groupes de fichiers en ligne demeurent en ligne et les groupes de fichiers hors connexion demeurent hors connexion. Pour plus d’informations, consultez « Captures instantanées de base de données avec groupes de fichiers hors connexion » plus loin dans cet article.

  • Si une base de données source devient RECOVERY_PENDING, ses instantanés peuvent devenir inaccessibles. Toutefois, une fois le problème résolu sur la base de données source, ses instantanés doivent de nouveau être disponibles.

  • Le rétablissement n’est pas pris en charge pour les fichiers NTFS en lecture seule ou compressés dans la base de données. Les tentatives de restauration d’une base de données contenant l’un de ces types de groupes de fichiers échouent.

  • Dans une configuration de journalisation (ou expédition des journaux), les instantanés de base de données peuvent être créés uniquement sur la base de données primaire, et non pas sur une base de données secondaire. Supposons que vous changez de rôle entre les instances de serveur principal et secondaire. Dans ce cas, vous devez supprimer tous les instantanés de base de données avant de configurer la base de données primaire comme base de données secondaire.

  • Un instantané de base de données ne peut pas être configuré en tant que base de données partagée évolutive.

Les instantanés de base de données ne prennent pas en charge les groupes de fichiers FILESTREAM. Si des groupes de fichiers FILESTREAM existent dans une base de données source, ils sont marqués comme hors connexion dans ses instantanés de base de données et les instantanés ne peuvent pas être utilisés pour rétablir la base de données.

Remarque

Une instruction SELECT exécutée sur un instantané de base de données ne doit pas spécifier de colonne FILESTREAM ; autrement, le message d'erreur suivant est retourné : Could not continue scan with NOLOCK due to data movement.

  • Quand les statistiques sur un instantané en lecture seule sont absentes ou obsolètes, le moteur de base de données crée et gère les statistiques temporaires dans tempdb. Consultez Statistiques pour plus d’informations.

Espace disque

Les instantanés de base de données consomment une grande quantité d'espace disque. Si un instantané de base de données manque d’espace disque, il est marqué comme suspect et doit être supprimé. (Toutefois, la base de données source n’est pas affectée ; les actions sur celle-ci continuent normalement.)

Toutefois, les instantanés sont très économes en espace comparés à une copie complète d'une base de données. Un instantané nécessite uniquement un espace de stockage adapté aux pages qui changent au cours de sa durée de vie. En règle générale, les instantanés sont conservés pendant une durée limitée, de sorte que leur taille n’est pas une préoccupation importante.

Toutefois, plus vous conservez un instantané, plus il est probable qu’il utilise de l’espace disponible. La taille maximale à laquelle un fichier sparse peut croître est la taille de fichier de la base de données source correspondante au moment de la création du snapshot. Un instantané de base de données doit être supprimé (supprimé) s’il manque de l’espace disque.

Remarque

À l'exception de l'espace de fichier, un instantané de base de données consomme grossièrement autant de ressources qu'une base de données.

Groupes de fichiers hors connexion

Les groupes de fichiers hors ligne dans la base de données source ont une incidence sur les instantanés de base de données lorsque vous tentez d'effectuer les opérations suivantes :

  • Créez un instantané.

    • Lorsqu'une base de données source possède un ou plusieurs groupes de fichiers hors ligne, la création d'instantanés réussit avec les groupes de fichiers hors ligne. Les fichiers épars ne sont pas créés pour les groupes de fichiers hors connexion.

  • Mettre hors connexion un groupe de fichiers

    • Vous pouvez mettre un fichier hors ligne dans la base de données source. Cependant, le groupe de fichiers reste en ligne dans les instantanés de la base de données s'il était en ligne lorsque l'instantané a été créé. Si les données interrogées ont changé depuis la création de l’instantané, la page de données d’origine est accessible dans l’instantané. Cependant, les requêtes qui utilisent l'instantané pour accéder aux données non modifiées dans le groupe de fichiers échoueront probablement en raison d'erreurs d'entrées/sorties (E/S).

  • Mettre en ligne un groupe de fichiers

    • Vous ne pouvez pas mettre en ligne un groupe de fichiers dans une base de données contenant des instantanés de base de données. Si un groupe de fichiers est hors connexion au moment de la création d’instantanés ou s’il est mis hors connexion pendant qu’un instantané de base de données existe, il reste hors connexion. Cela est dû au fait que le retour en ligne d’un fichier implique de le restaurer, ce qui n’est pas possible si un instantané de base de données existe sur la base de données.

  • Restaurer la base de données source de l'instantané

    • La restauration d’une base de données source vers un instantané de base de données nécessite que tous les groupes de fichiers soient en ligne, à l’exception de ceux qui étaient hors connexion lors de la création de l’instantané.

Contenu connexe