[SQL Serveur] Créer un plan de maintenance.
Nous allons créer un plan de maintenance pour une base de données SQL. Nous allons le faire grâce à l’assistant plan de maintenance. Nous prendrons une base de données qui est en mode de récupération complet afin d’avoir la possibilité de faire différents types de sauvegarde.
Il est bon de rappeler que l’agent SQL serveur doit être en marche et en mode de démarrage automatique puisque c’est lui qui va exécuter, aux heures indiquées, les travaux que nous allons lui confier.
Donc une fois connecté au serveur nous allons dans gestion puis plan de maintenance. Nous faisons un clic droit créer un plan de maintenance avec l’assistant.
La fenêtre suivante s’ouvre :
L’assistant nous propose de vérifier l’intégrité des bases de données, de faire la maintenance des index, de mettre à jours les statistiques et de faire la sauvegarde des bases de données. On clique sur suivant :
Ici nous devons donner un nom à notre plan de maintenance et il est de bon ton de renseigner la description.
Ensuite nous avons un choix à propos de la planification de notre plan. Tel que présenté sur l’image, il ne sera exécuté que manuellement (on demand) ce qui peut être intéressant si ce plan nécessite un certain état de la base ou si ce plan ne doit pas être lancé de manière régulière.
Le choix d’une seule planification pour tout le plan permet de tout faire d’un coup et les étapes s’enchainerons les unes après les autres. Néanmoins si on veut faire une sauvegarde complète à minuit et une sauvegarde différentielle à 10 heures ce ne sera pas possible avec ce modèle ou alors il faudra faire plusieurs plans de maintenance (ce qui est possible mais pas optimisé). Donc ici nous allons opter pour une planification séparée pour chaque tâche.
Lorsqu’on clique sur suivant la fenêtre suivante s’ouvre :
Ici on retrouve toute les actions possibles au travers de l’assistant. Noter qu’un descriptif succinct de chaque action est présenté dans la zone de texte jaune à titre informatif.
Donc on peu vérifier l’intégrité, réduire la base de données, Réorganiser les index, reconstruire les index, mettre à jour les statistiques, nettoyer l’historique, exécuter des jobs, faire toutes les sorte de sauvegarde enfin vider les buffers.
Ce qui est intéressant ici c’est que l’ordre proposé pour effectuer des actions correspond bien à la réalité, il vaut mieux vérifier l’intégrité de la base avant de la sauvegarder, de même qu’il vaut mieux réorganiser ou reconstruire les index avant de mettre à jour les statistiques.
Donc pour cet exemple nous allons simuler la chose suivante :
Le dimanche, le mercredi et le vendredi à 20heures faire une vérification de l’intégrité des données, à 20h30 une réorganisation des index, à 21h une mise à jour des statistiques et à 21h30 une sauvegarde complète.
Les autre jours de la semaine faire pareil mais avec une sauvegarde différentielle à la même heure.
Tout les jours ouvré faire une sauvegarde du journal de transaction à 10h, 12h, 14h, 16h, 18h.
On y va donc je vais cocher tout ce dont j’ai besoin :
Je fais suivant et j’arrive sur une fenêtre qui me permet de modifier l’ordre des actions demandées.
Je fais encore suivant et j’arrive sur la fenêtre de vérification de l’intégrité des données ou on me demande quelle est la ou les bases que je souhaite vérifier.
Ici je ne sélectionne qu’une base et je fais OK. Remarquez la case à cocher qui signale que les index seront inclus dans la vérification.
Maintenant je vais planifier ce travail en cliquant sur modifier sur la partie planification :
Donc tous les jours à 20h je dois faire une vérification de l’intégrité des données.
Je clique sur ok et suivant pour arriver sur la réorganisation des index.
Je vais avoir des choix similaire c'est-à-dire choisir la ou les bases de données choisir la planification et en plus j’aurais le choix de réorganiser les index dans les tables et/ ou les vues. 
Si je choisis le « ou » je pourrais choisir les tables ou les vues que je désire.
Je planifie le travail et je fais suivant :
Mise à jour des statistiques ou j’ai sélectionné toute les tables et les vues de la ou des bases sélectionnées. Toutes les statistiques seront évaluées avec un full scan et cela tout les jours à 21 heures.
Je fais suivant et voila la sauvegarde complète qui arrive :
Ici j’ai spécifié deux bases de données, j’ai signalé que les sauvegardes devaient être détruite après 7 jours, que les sauvegardes devraient avoir une extension .BAK et quelles devaient se placer dans e:\mes sauvegardes\ en créant un sous dossier pour chaque base sélectionnées. Il est sous entendu que le nom du fichier de sauvegarde portera le nom de la base, la date et l’heure de la sauvegarde (ceci est automatique quand on choisit cette option et c’est pratique de savoir quelle est la dernière sauvegarde).
Je fais suivant et je passe aux sauvegardes différentielles qui doivent êtres planifiées le lundi, mardi, jeudi et samedi à 21h30. Vous remarquerez que l’interface est sensiblement la même que précédemment.
J’arrive à la sauvegarde du journal de transaction qui doit s’effectuer tout les jours, toutes les 2 heures entre 10 et 18 heures donc la difficulté ici c’est la planification :
Pour ce qui est de la fenêtre principale il est à noter que l’extension du fichier de sauvegarde est ici « .TRN ».
En cliquent sur suivant me voici sur la fenêtre qui me demande si je veux enregistrer le rapport dans un fichier texte et si je veux envoyer le dit rapport à un opérateur.
J’arrive enfin sur la dernière page de mon assistant qui me récapitule les choix effectué c’est le moment de valider chaque étape.
En cliquant sur « Finish » l’assistant va créer tout les travaux relatifs aux actions demandées.
On peut aller dans l’agent SQL serveur et voir les différents travaux que l’assistant vient de créer.
Voilà maintenant Il ne reste plus qu’à voir si ça marche. Petit point important on a demandé des sauvegardes différentielles et de journaux des transactions, or pour que celle-ci soient validées il faut qu’il y ait eut une sauvegarde complète de faite, donc en fonction de votre planification et du jour où vous mettez en place votre plan de maintenance tenez en compte !!!
Lorsque une nuit est passée on va voir le job activity monitor pour voir si notre plan de maintenance a marché ou pas :
Voici ce que le mien affiche :
Voila on voit que les tout les plans ont marché avec succès sauf un (le sous plan 4) qui n’a pas été lancé : en fait c’est normal car c’est celui qui contient la sauvegarde complète qui lui ne sera lancé que ce soir. Donc si je vais voir dans mon répertoire « mes sauvegardes » j’ai un répertoire adventure works qui correspond à la base avec laquelle j’ai travaillé et dedans j’ai les fichiers suivants :
Si maintenant je veux restaurer ma base SQL me propose la chose suivante :
Soit la restauration complète, suivie de la restauration différentielle du 07/07/2009 à 21h30, suivie de la restauration du journal de transaction du 0/07/2009 à 10h or on est le 08/07/2009 et il est 11h15 donc c’est cool !