[SQL Server] Plan de maintenance : modus operandi de la mise-en-œuvre d’un plan de maintenance efficace avec SSMS

Ce billet présente comment implémenter un bon plan de maintenance alliant sauvegardes et optimisations avec SQL Server Management Studio (SSMS).

Pour toute information complémentaire liée aux prérequis essentiels ou pour avoir des conseils d’implémentation d’un plan de maintenance, vous pouvez aller ici.

Avant de commencer…

Depuis la version 2005 de MSSQL, Microsoft offre la possibilité de créer une série de sous-plans au sein d’un plan de maintenance via SSMS. Cette possibilité est telle qu’elle permet de gagner en temps, en productivité et en cohérence concernant la mise-en-place d’un plan de maintenance optimal.

Dans notre exemple, nous appellerons notre plan de maintenance Full_MaintenancePlan, qui respectera la planification (arbitraire) suivante (pour une fenêtre de maintenance comprise entre minuit et 6h du matin inclus) :

  • Tous les jours, à minuit : vérification de l’intégrité de toutes les bases de données (systèmes comme applicatives). Cette étape peut se faire quelques minutes juste avant la sauvegarde complète ou différentielle.
  • Chaque jour (sauf le dimanche), à 1h du matin : réorganisation des indexes de toutes les bases de données suivie d’une mise-à-jour des statistiques.
  • Chaque dimanche, à minuit : reconstruction des indexes de toutes les bases de données (ce qui va automatiquement mettre à jour les statistiques de celles-ci).
  • Chaque jour, à 2h du matin : sauvegarde complète de toutes les bases de données. Avec suppression, au préalable, des sauvegardes complètes et transactionnelles datant respectivement de plus d’1 mois et de plus d’1 semaine.
  • Toutes les heures, entre minuit et 23h59 : sauvegarde transactionnelle des bases dites « critiques ».
  • Chaque dimanche, à 6h du matin: nettoyage de l’historique des jobs de maintenance datant de plus d’1 mois.
Par ailleurs, il est à noter que les différents modes opératoires expliqués dans ce billet seront effectués sous SSMS 2012. Toutefois, ils restent également valables et identiques pour toute version supérieure ou égale à 2005.

Chaque tâche (ou sous-plan) aura un nom spécifique :

  • Check_databases, pour la vérification de l’intégrité des bases de données.
  • Reorganize_indexes_Update_stats, pour la réorganisation des indexes et mise-à-jour des statistiques des bases de données.
  • Rebuild_indexes, pour la reconstruction des indexes des bases de données.
  • Full_backups, pour la sauvegarde complète des bases de données (qui contiendra également des tâches de nettoyage des sauvegardes).
  • Transactional_backups, pour la sauvegarde transactionnelle des bases de données « critiques ».
  • Cleanup_history, pour le nettoyage de l’historique des jobs de maintenance.

Mise-en-place d’un plan de maintenance générique

    Etapes préliminaires

  • Lancement de l’assistant de création d’un plan de maintenance.

SSMS offre deux approches de création d’un plan de maintenance :

    • New Maintenance Plan… : permet de créer un plan de maintenance de façon manuelle.
    • Maintenance Plan Wizard : permet de créer un plan de maintenance via l’assistant.

Ces deux options offrent les mêmes résultats, mais nous allons opter pour Maintenance Plan Wizard qui permet de gagner du temps. En effet, en plus de créer une série de sous-plans, il permet d’enchaîner rapidement avec leur planification :


Cela va conduire à l’ouverture d’une boîte de dialogue de bienvenu avec un résumé des possibilités offertes par l’assistant :


Cliquez sur Next.

  • Spécification des propriétés du plan de maintenance.


Comme ci-dessus, nous avons opté pour Full_MaintenancePlan comme nom arbitraire pour le futur plan de maintenance, car il alliera tâches de sauvegardes et tâches d’optimisation.

Côté planification, choisissez Separate schedules for each task est, ici, recommandé. En effet, cela permet de créer un ensemble de sous-plans indépendants et pouvant être planifiés de sorte qu’il n’y ait pas de chevauchements.

En outre, vous pouvez laisser le choix du compte d’exécution par défaut tel quel : SQL Server Agent service account.

Cliquez sur Next pour passer à l’étape suivante.

  • Sélection des tâches de maintenance.

Cette étape porte sur la sélection des tâches destinées à constituer les futurs sous-plans (les éléments cochés correspondent à ceux qu’il faut sélectionner) :


Evitez à tout prix l’utilisation de la tâche Shrink Database (voir ici).

Nous utiliserons la tâche Update Statistics au sein de Reorganize Index plus loin. En attendant,
cliquez sur Next pour passer à l’étape suivante.

  • Ordonnancement des tâches de maintenance.

L’ordonnancement par défaut étant déjà très bien (dans notre cas), nous pouvons directement cliquer sur Next pour passer à l’étape suivante :


Définition des tâches de maintenance

  • Check_databases : vérification de l’intégrité de toutes les bases de données.

    • Sélection de toutes les bases de données avec inclusion de leurs indexes :


    • Planification (après clic sur Change dans la boîte modale principale) de la tâche de sorte qu’elle s’exécute tous les jours, à minuit :


    • Validation de la configuration :


  • Reorganize_indexes_Update_stats : réorganisation des indexes de toutes les bases de données.
    • Sélection de toutes les bases de données avec compactage des objets LOB :


    • Planification de la tâche de sorte qu’elle s’exécute tous les soirs (sauf les dimanches), à 01h00 :


    • Validation de la configuration :


Sachez que si vous sélectionnez des bases de données spécifiques, vous pouvez choisir sur quel type d’objet vous souhaitez travailler (tables ou vues, voire les deux) :


  • Rebuild_indexes : reconstruction des indexes de toutes les bases de données.
    • Sélection de toutes les bases de données avec réorganisation des pages :


Ne sélectionnez :

  • Sort results in tempDB que si vous souhaitez que le job se déroule plus vite, et que vous êtes prêts à accepter de voir tempDB grossir plus vite (dans ce cas, avoir un disque dédié à tempDB est recommandé).
  • Keep index online while reindexing que si vous souhaitez pouvoir éviter que le job empêche de verrouiller un index en cours d’utilisation. Opération efficace, mais nécessite, tout de même, l’Enterprise (ou Developer) Edition et d’accepter de voir tempDB grossir plus vite (mêmes recommandations que le point précédent).
    • Planification de la tâche de sorte qu’elle s’exécute tous les dimanches, à 01h00 :


    • Validation de la configuration :


Par ailleurs, vous pouvez laisser l’option Default free space per page cochée si le fill factor (facteur de remplissage, correspondant à l’option Change free space per page)
par défaut de l’instance est à un niveau acceptable (par exemple, à 85%). Sauf si vous souhaitez changer le fill factor ce qui, dans ce cas, nécessitera d’appliquer une valeur comprise entre 50% et 75% (ou entre 35% et 50%) si la majorité de vos indexes sont utilisés par des requêtes de mise-à-jour, entre 75% et 100% (ou entre 0% et 35%), dans le cas échéant. Voir ici.

  • Cleanup_history : nettoyage de l’historique du plan de maintenance.
    • Sélection de tout type d’historique datant d’au moins 4 semaines (ou 1 mois) :


    • Planification de la tâche de sorte qu’elle s’exécute tous les dimanches, à 05h50 :


    • Validation de la configuration :


  • Full_backups : sauvegarde complète de toutes les bases de données.
    • Sélection de toutes les bases de données :


    • Planification de la tâche de sorte qu’elle s’exécute toutes les nuits, à 02h00 :

    • Validation de la configuration :


Outre le fait d’indiquer (par défaut) que notre jeu de sauvegardes n’expire jamais, la spécification du chemin d’accès de dépôt des fichiers de sauvegardes BAK (avec création automatique d’un sous-dossier pour chaque base concernée pour des raisons d’organisation) et l’activation de la vérification de l’intégrité de chaque sauvegarde ont été réalisées.

N’acceptez les sauvegardes compressées (très efficaces, soit dit en passant) que si vous disposez de l’Enterprise Edition (de la version 2008 minimum de SQL Server), et que vous êtes prêts à accepter une possible surconsommation des ressources systèmes.

  • Transactional_backups : sauvegardes transactionnelles de bases de données critiques :
    • Sélection de toutes les bases de données concernées (ici, une seule):


Notons qu’à partir du SP2 de MSSQL 2005, seules bases de données en mode de récupération FULL sont listées parmi les bases susceptibles d’être concernées par des sauvegardes transactionnelles, comme sur la figure ci-dessus. De plus, à partir de MSSQL 2008, il est possible d’exclure les bases de données non-disponibles (et idem lors de la définition du sous-plan dédié à la vérification de l’intégrité des bases de données).

    • Planification de la tâche de sorte qu’elle s’exécute tous les jours, toutes les heures, entre minuit et 23h59:


    • Validation de la configuration :


N’oubliez bien de bien spécifier le chemin d’accès des fichiers de sauvegarde transactionnelle TRN (idéalement différente que celle pour les BAK, pour des raisons organisationnelles), ainsi que l’option d’auto-création de répertoires pour chaque base de données concernée.

  • Après réalisation des différents sous-plans essentiels :
    • Sélection du chemin d’accès des fichiers relatifs aux historiques de travail du plan de maintenance :


Vous pouvez éventuellement spécifier une adresse mail vers laquelle seront envoyés les rapports relatifs à la réussite ou à l’échec des tâches de maintenance.

    • Résumé des travaux de création effectués (un retour en arrière est possible pour toute correction, sinon cliquez sur Finish):


Vous pouvez dépiler chaque tâche pour avoir le détail des configurations effectuées.

    • Confirmation de succès de l’opération :

Vous pouvez, si vous le souhaitez, lire le rapport de configuration du plan de maintenance, le sauvegarder, le copier dans le presse-papier ou l’envoyer par mail :


Finalisation de l’implémentation du plan de maintenance

Le plan de maintenance générique créé, il est retrouvable au sein de l’explorateur d’objets :


De plus, la création automatiquement de jobs d’exécution pour chaque tâche (ou sous-plan) de maintenance peut être remarquée au sein du sous-nœud Jobs du nœud SQL Server Agent de l’explorateur d’objets :


Pour accéder au contenu des sous-plans, il vous suffira simplement de double-cliquer sur Full_MaintenancePlan, dans la section Management, toujours au sein de l’explorateur d’objets, ou de sélectionner Modify après clic-droit :


… ce qui conduira à une interface qui va sans rappeler celle de Business Intelligence Development Studio (BIDS) ou de Visual Studio :


Notez bien, dans la partie gauche de la figure, la liste des tâches utilisables peut être affichées en cliquant (ou positionnant la souris sur) Toolbox :


  • Renommage des sous-plans (ou tâches) de maintenance.

Pour des raisons de convention et de lisibilité, un renommage des sous-plans s’impose. Et pour ce faire, il suffit simplement de double-cliquer sur l’un des sous-plans concernés, au hasard Subplan_1 pour accéder à ses propriétés, ce qui aura pour effet de lancer une boîte modale :


Subplan_1 faisant référence au sous-plan relatif à la vérification de l’intégrité des bases de données, le renommer suivant la convention de nommage choisie dans la section relative au contexte des besoins (en début de billet) serait plus adéquat :


En procédant de la sorte pour les autres sous-plans, vous finirez par obtenir le résultat suivant :


Voilà, le plan de maintenance est prêt et « propre », mais il reste encore 2 détails :

  • Créer un workflow permettant de réaliser une mise-à-jour des statistiques après chaque réorganisation d’indexes, pour la tâche Reorganize_indexes_Update_stats.
  • Créer un workflow permettant de supprimer les fichiers de sauvegardes vieux de + de X jours/semaines/mois/années, suivant les besoins (ici, 1 mois pour les sauvegardes complètes, 1 semaine pour les sauvegardes transactionnelles), au sein de la tâche Full_backups.
  • Inclusion d’une tâche de mise-à-jour des statistiques au sein de Reorganize_indexes_Update_stats.
    • Sélection de la tâche Update Statistics Task dans la toolbox :


    • Création d’un connecteur vert (pour le succès ; rouge pour l’échec ; bleu pour la complétion) allant de Reorganize Index Task vers Update Statistics Task :

    • Choix des bases dont l’on souhaite mettre les statistiques à jour (faites un double-clic sur la tâche Update Statistics Task) :



Ci-dessus, toutes les bases ont été sélectionnées.

    • Validation de la configuration de la tâche de mise-à-jour des statistiques :

Comme on peut le souligner, les options All existing statistics et Full scan ont été choisies, ce qui donnera, à chaque lancement de la tâche, un scan complet de toutes les pages de toutes les statistiques existantes de chaque base à traiter.

    • Appréciation du résultat final :



Chaque fois que la tâche de réorganisation des indexes se sera terminée avec succès, une mise-à-jour des statistiques sera réalisée.

  • Inclusion de tâches de suppression de fichiers de sauvegardes complètes et transactionnelles au sein de Full_backups.
    • Sélection de la tâche Maintenance Cleanup Task deux fois dans la toolbox :


    • Renommage des tâches de nettoyage en Cleanup BAK et Cleanup TRN…


… pour arriver au résultat suivant :


    • Création d’un connecteur vert (pour le succès ; rouge pour l’échec ; bleu pour la complétion) allant de Cleanup TRN vers Cleanup BAK, puis de Cleanup BAK vers Full backups :


    • Configuration de :
      • La tâche Cleanup TRN (chemin d’accès, type de fichier TRN, prise en compte de sous-dossiers, ciblage des fichiers âgés de plus d’1 semaine) :

      • La tâche Cleanup BAK (chemin d’accès, type de fichier BAK, prise en compte de sous-dossiers, ciblage des fichiers âgés de plus d’1 mois ou 4 semaines) :

    • Validation de la configuration du sous-plan Full_backups :


La sauvegarde complète de toutes les bases de données aura lieu chaque nuit, à 2h du matin, à condition que les tâches de nettoyage de sauvegardes se soient bien déroulées. Le fait de faire débuter ce sous-plan par les tâches de nettoyage de sauvegardes permet de libérer de l’espace, de façon proactive (et dans la mesure du possible).

Sitôt tout le travail finit et enregistré (via CTRL+S ou l’icône Disquette d’enregistrement dans la barre de tâches de SSMS), on peut apprécier les changements significatifs dans le sous-nœud Jobs de SQL Agent:


Il est recommandé de donner comme nom de propriétaire (owner), à chaque job de maintenance, un login SQL (avec mot-de-passe n’expirant jamais), et non Windows. Vous pouvez effectuer les modifications en accédant aux propriétés de chaque job. Ce login SQL ne doit idéalement pas être le compte sa, mais un login SQL dédié que vous pourrez appeler maintenance_user, par exemple.

Pour aller plus loin…

Ce billet a permis de mesurer à quel point il n’était pas difficile d’implémenter un plan de maintenance alliant optimisations et sauvegardes via SSMS. Différents axes d’amélioration peuvent être explorés pour notre exemple, comme par exemple :

  • Incorporer un sous-plan de sauvegardes Differential backups si les temps de sauvegarde complète s’avèrent trop longs pour au moins une base de données de l’instance (cas des VLDBs). Pour comprendre l’utilité d’une sauvegarde différentielle, voir ici.
  • Ne planifier l’optimisation des bases systèmes que pour le week-end, au lieu de toutes les nuits.
  • Adopter une stratégie de réindexation sélective ne tenant compte que les indexes malades. Et même chose pour les statistiques, pour ne prendre en compte que les statistiques obsolètes. Voir section Au sujet des jobs d’optimisation du billet [SQL Server] Plan de maintenance : un peu de discussion….
  • Répartir la vérification de l’intégrité des bases de données (toutes ou spécifiques) si la tâche Check integrity s’avère trop longues (car au moins une base est trop volumineuse, par exemple). Voir ici.
  • Ajouter une tâche Operator Task dans chaque sous-plan pour prévenir par mail du succès ou non d’une ou plusieurs tâches spécifiques.

Vous pouvez garder un œil ici si vous souhaitez découvrir d’autres sujets sur les plans de maintenance.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s