Forum aux questions sur SQL
Astuces sur la récupération suite à un endommagement, conseils sur la réduction de base de données et bien plus encore
Paul S. Randal
Question : Ma stratégie de sauvegarde consiste en une sauvegarde quotidienne complète à 1 heure du matin et en une sauvegarde de journal toutes les heures. Un DBCC CHECKDB est également exécuté tous les jours à 4 heures du matin. Si, en arrivant au bureau à 8 heures, je découvre que les vérifications de cohérence exécutées pendant la nuit ont détecté un endommagement massif, comment procéder à une récupération sans perdre trop de données ?
Réponse : Tout dépend du moment de l'endommagement, de ce qui est altéré et des sauvegardes dont vous disposez. Dans l'idéal, vous disposez d'un plan de récupération d'urgence préparé à l'avance qui vous guidera au cours des prochaines étapes. Je vais cependant supposer que votre question est hypothétique.
Dans le scénario que vous décrivez, l'endommagement est détecté par le DBCC CHECKDB qui s'exécute après la sauvegarde complète de la base de données. Il n'y a cependant aucun moyen de savoir si l'endommagement s'est produit avant ou après la sauvegarde de la base de données. S'il s'est produit avant la sauvegarde de la base de données, alors la sauvegarde contient une version altérée de la base de données et la récupération sera plus difficile.
Ma première action serait de restaurer ailleurs la sauvegarde de base de données la plus récente et d'y exécuter un DBCC CHECKDB. Si aucun endommagement n'est détecté, vous devriez pouvoir restaurer sans perdre de données en procédant comme suit :
- Prenez une sauvegarde de la fin du journal de la base de données altérée (pour capturer les transactions les plus récentes).
- Restaurez la sauvegarde de base de données complète la plus récente et précisez WITH NORECOVERY.
- Restaurez toutes les sauvegardes de journaux des transactions depuis la sauvegarde de base de données complète et la sauvegarde de la fin du journal, l'une après l'autre et en précisant WITH NORECOVERY.
- Terminez la restauration en utilisant la commande RESTORE nom de la base de données WITH RECOVERY.
Enfin, exécutez un autre DBCC CHECKDB pour vérifier l'absence d'endommagement, procédez à une analyse de la cause initiale pour savoir ce qui a provoqué l'endommagement au départ, puis prenez les mesures nécessaires pour corriger le problème.
En cas d'endommagement persistant à la fin de la procédure de restauration ci-dessus, il se peut que l'endommagement ait été introduit dans le journal des transactions par le biais d'une des sauvegardes de journaux des transactions ou encore de la mémoire. Le cas échéant, vous devrez peut-être procéder à une restauration vers un point dans le temps pour rechercher l'heure à laquelle l'endommagement s'est produit et arrêter la restauration juste avant. La procédure n'entre pas dans le cadre de cet article, mais est détaillée dans la Documentation en ligne.
Si la sauvegarde de base de données la plus récente contient l'endommagement, vous devrez peut-être suivre la procédure ci-dessus en commençant par la prochaine sauvegarde de base de données complète la plus récente. À supposer qu'elle soit disponible, de même que toutes les sauvegardes de journaux qui ont été faites.
Une autre stratégie (à laquelle vous devrez peut-être vous plier à cause d'une durée maximale d'indisponibilité admissible imposée, également appelée RTO, Recovery time Objective) consiste à supprimer les données altérées, manuellement ou via la fonctionnalité de réparation de DBCC CHECKDB, puis à essayer d'en récupérer certaines à partir d'un jeu de sauvegardes plus ancien.
La restauration suite à un endommagement est parfois très simple, d'autres fois très compliquée, selon la cause et les options disponibles. Je traiterai ce sujet dans plusieurs articles les mois prochains.
Question : Notre équipe de développement va créer une solution qui implique la fonctionnalité de suivi des modifications de SQL Server 2008. Si l'on s'en réfère à la documentation, nous devrions activer l'isolement de capture instantanée sur la base de données impliquée, mais je me demande quel en sera l'impact sur les performances. Avez-vous des commentaires sur le sujet ?
Réponse : J'ai abordé le sujet du suivi des modifications dans l'édition de novembre 2008 (« Suivi des modifications de la base de données entreprise »). Vous devez effectivement activer la gestion des versions des lignes. Vous le devez, car le mécanisme d'extraction des données modifiées est généralement le suivant :
- Interrogation du système de suivi des changements pour savoir quelles lignes de table ont changé.
- Interrogation de la table elle-même pour extraire les lignes modifiées.
Sans mécanisme de gestion des versions des lignes, la première requête peut renvoyer des résultats non valides si la tâche de nettoyage de suivi des modifications s'exécute pendant l'exécution de la requête. La deuxième requête peut également échouer si certaines lignes de table référencées dans les résultats de la première requête sont supprimées avant l'exécution de la deuxième requête.
La stabilisation peut être obtenue notamment en verrouillant les données de suivi des modifications et les tables utilisateur nécessaires, mais cette méthode aboutira à une faible simultanéité (par le blocage) et à un débit réduit de la charge de travail. L'autre moyen d'obtenir la stabilisation est d'utiliser l'isolement de capture instantanée.
Il existe deux types d'isolements de capture instantanée, le premier garantit la cohérence au niveau des transactions (option de base de données : allow_snapshot_isolation), le deuxième la garantit au niveau instruction T-SQL (option de base de données : read_committed_snapshot). L'option au niveau de la transaction est nécessaire pour utiliser correctement le suivi des modifications. Elle est appelée isolement de capture instantanée.
L'isolement de capture instantanée conserve les versions des enregistrements de table de sorte que si, par exemple, une transaction explicite démarre, la transaction aura une vue ponctuelle cohérente de la base de données, à compter du point de départ de la transaction. Pour utiliser le suivi des modifications, les deux requêtes ci-dessus doivent être enveloppées dans une transaction explicite unique et le niveau d'isolement doit être défini sur le niveau capture. Cette combinaison garantit la cohérence.
L'isolement de capture instantanée est expliqué en détail dans le livre blanc de Kimberly, ma femme, « SQL Server 2005 – Isolement de capture instantanée ».
L'isolement de capture instantanée peut éventuellement poser deux problèmes de performances. Le premier est que toutes les mises à jour de toutes les tables de la base de données doivent générer des versions des enregistrements au fur et à mesure qu'elles les modifient, même si la version n'est jamais utilisée. La version avant modification de l'enregistrement doit être copiée dans le magasin de version et le nouvel enregistrement a un lien vers l'ancien, au cas où une autre transaction commencerait avant la fin de celle-ci et demanderait la version correcte de l'enregistrement. Cela représente une charge de traitement supplémentaire pour toutes les opérations de mise à jour.
Le magasin de version se trouve dans la base de données tempdb. C'est la cause du deuxième problème éventuel de performances. Tempdb peut être la base de données la plus occupée sur certaines instances SQL Server, car elle est partagée par toutes les connexions et bases de données. En général, tempdb peut se transformer en goulot d'étranglement de performances, même sans gestion des versions des lignes. Ajouter la gestion des versions des lignes augmente la pression qui pèse sur tempdb, en termes d'espace utilisé et d'opérations d'E/S, et risque d'aboutir à la dégradation générale du débit de la charge de travail.
Vous trouverez plus de détails sur le sujet dans le livre blanc « Utilisation de Tempdb dans SQL Server 2005 ». Les deux livres blancs mentionnés ici ont été rédigés pour SQL Server 2005, mais s'appliquent également à SQL Server 2008.
Question : Peut-on estimer qu'un DBCC CHECKDB vérifie exhaustivement la base de données ? J'ai entendu dire le contraire. Par ailleurs, une réparation peut-elle tout réparer ? Là encore, j'ai entendu dire que c'était impossible. Si DBCC CHECKDB n'est pas exhaustif, que puis-je faire d'autre ?
Réponse : La réponse est oui et non ! DBCC CHECKDB assure un ensemble de vérifications de cohérence extrêmement complet, optimisé à chaque nouvelle version. Vous avez raison, cependant, car il ne valide pas un certain nombre d'éléments.
Voici, en bref, ce qu'il fait :
- Vérification de la cohérence des catalogues système
- Vérification de la cohérence des métadonnées d'allocation
- Vérification de la cohérence de toutes les tables utilisateur
La description détaillée des vérifications exécutées n'entre pas dans le cadre de cette réponse (plus d'informations sont disponibles sur mon blog ou dans les derniers ouvrages SQL Server 2008 Internals), mais chaque page de base de données utilisée est au moins lue en mémoire et validée. Cela permet de détecter les endommagements courants provoqués par des défauts dans le sous-système d'E/S (à l'origine d'environ 99,99 % de l'ensemble des endommagements).
Les deux éléments connus pour n'être vérifiés dans aucune version de SQL Server sont le contenu des statistiques par colonne et clé d'index stocké dans la base de données, même si l'ajout est envisageable dans une prochaine version, et la validité des contraintes (par exemple, contraintes de clé étrangère entre tables). La validité des contraintes peut être vérifiée en utilisant la commande DBCC CHECKCONSTRAINTS séparément de DBCC CHECKDB. En fait, si vous devez lancer une opération de réparation sur une base de données qui contient des contraintes, il peut être intéressant de valider les contraintes par la suite, car les réparations ne tiennent pas compte des contraintes et peuvent les invalider par inadvertance. Ce sujet est documenté dans la Documentation en ligne.
Le système de réparation ne peut pas tout réparer. Certains endommagements sont impossibles à réparer avec exactitude dans un délai raisonnable. La liste de ces endommagements est très courte. Elle est documentée sur mon blog dans le message « CHECKDB sous tous les angles : CHECKDB peut-il tout réparer ? » Dans le cas d'une page altérée dans un catalogue système, la seule réparation possible serait de supprimer la page, par exemple. Mais que se passerait-il si la page stockait les métadonnées pour des tables utilisateur dans la base de données ? Supprimer cette page supprimerait également ces tables utilisateur, et la réparation serait impossible.
La plupart des réparations s'accompagnent d'une perte de données (car c'est une garantie d'exactitude dans un délai raisonnable). La réparation doit donc être considérée uniquement en dernier ressort lors de la récupération après incident. L'utilisation de sauvegardes dans le cadre d'une stratégie de sauvegarde complète est le seul moyen d'éviter la perte de données (sauf si une copie synchrone de la base de données est assurée d'une façon ou d'une autre).
DBCC CHECKDB est suffisamment complet pour détecter les endommagements graves et, pour être sûr de détecter les endommagements le plus tôt possible, il devrait être exécuté régulièrement dans le cadre d'une stratégie de maintenance de base de données (voir sur mon blog le message « De l'importance des vérifications de cohérence régulières »). Aucune autre solution n'est plus efficace, mais vous pouvez optimiser DBCC CHECKDB en veillant à activer les sommes de contrôle de page sur toutes les bases de données. Cela permet à SQL Server de détecter une modification sur une page de base de données en dehors de la mémoire SQL Server.
Question : Je ne sais plus quoi penser sur la réduction. J'ai lu plusieurs articles sur la réduction des fichiers de données, l'un expliquant que c'était une bonne idée et d'autres disant le contraire. J'ai le même problème lorsque je cherche des informations sur la réduction des fichiers journaux. Quelle est la réponse ?
Réponse : La réduction est une opération largement incomprise. Par ailleurs, la différence entre la réduction des fichiers de données et la réduction des fichiers journaux est une source courante de confusion.
Une opération de réduction sur un fichier de données tente de déplacer la page de base de données la plus proche de la fin du fichier vers le début du fichier. Cette opération crée un « vide » à la fin du fichier de données qui peut être renvoyé au système d'exploitation. Autrement dit, le fichier de données est physiquement réduit.
Une opération de réduction sur un fichier de journal de transactions, quant à elle, ne déplace rien. Elle se contente de supprimer la partie vide du journal des transactions à la fin du fichier, tant que les enregistrements du journal des transactions ne sont pas conservés pour une raison ou une autre. Si l'opération aboutit, le fichier journal est physiquement réduit.
La confusion est due aux effets secondaires des deux opérations et au moment où elles doivent être exécutées.
La réduction des fichiers de données est conseillée (ou décidée) pour récupérer de l'espace. Il se peut que la tâche de maintenance des index provoque le grossissement des fichiers de données ou que l'unité soit saturée... D'où la réaction naturelle de vouloir récupérer une partie de cet espace « perdu ». Il est cependant probable que cet espace soit de nouveau nécessaire et, en général, il vaut mieux le conserver pour le fichier de données plutôt que de sans cesse réduire et faire croître automatiquement un fichier.
La réduction d'un fichier de données doit être exceptionnelle en raison d'un effet secondaire pénible. Réduire un fichier de données provoque la fragmentation massive des index, ce qui peut compromettre la performance des requêtes. Sur mon blog, le message « Pourquoi il ne faut pas réduire les fichiers de données » inclut un script simple qui met cet effet en évidence.
Ce message décrit également dans quels cas il est acceptable de réduire un fichier de données (presque jamais) et indique une autre méthode qui permet d'éviter la fragmentation. Je connais malheureusement un grand nombre de cas dans lesquels la réduction de fichiers de données est recommandée sans aucune mention des effets secondaires.
La réduction d'un fichier journal doit être une opération encore plus rare que celle d'un fichier de données. J'entends couramment parler de réduire un fichier journal qui a augmenté de façon disproportionnée par rapport aux fichiers de données suite à une gestion incorrecte des tailles ou encore par volonté de le maintenir aussi peu volumineux que possible. Le fichier journal d'une base de données active n'a pas besoin d'être d'une taille raisonnable, mais le journal doit être géré de sorte qu'il soit inutile de le réduire et de le faire croître pour tenir compte de l'activité de la base de données.
Vous pouvez en savoir plus sur le journal des transactions dans l'article « Comprendre la journalisation et la récupération » que j'ai rédigé pour l'édition de février 2009 du magazine. Sur mon blog, un message traite également de la gestion de la taille du journal des transactions. Voir « De l'importance d'une gestion appropriée de la taille du journal des transactions ».
Au final, la réduction doit être une opération rarissime et décidée uniquement lorsque ses conséquences sont entièrement assimilées.
Paul S. Randal est directeur général de SQLskills.com, directeur régional Microsoft et MVP SQL Server. Il a travaillé sur le moteur de stockage de données de SQL Server chez Microsoft de 1999 à 2007. Paul a écrit DBCC CHECKDB/repair pour SQL Server 2005 et était responsable de Core Storage Engine pendant le développement de SQL Server 2008. Expert de la récupération après incident, de la haute disponibilité et de la maintenance de base de données, il présente fréquemment des conférences. Vous trouverez son blog à l'adresse SQLskills.com/blogs/paul et sa page Twitter à l'adresse Twitter.com/PaulRandal.