[SQL Server] Plan de maintenance: un peu de discussion…

Ce billet aborde brièvement l’importance d’un plan de maintenance et d’une bonne planification de ses jobs.

Prérequis essentiels

Pour mener à bien le travail de création de plans de maintenance sous SQL Server (avec SQL Server Management Studio (SSMS) ou Enterprise Manager (EM)), il est important de s’assurer que :

  • L’édition de SQL Server utilisée est une édition « payante » (donc, hormis la version d’évaluation, exit : Express, Compact, Personal et Desktop Engine, notamment), les éditions « gratuites » ne, pouvant pas utiliser de plans de maintenance au niveau de l’instance SQL Server à cause du non-support du SQL Agent).
  • Le SQL Agent soit bien démarré.
  • Un client SQL Server bien installé (SQL Server Management Studio pour toute version supérieure ou égale à SQL Server 2005 ; Enterprise Manager pour SQL Server 2000).
Notez qu’il est néanmoins possible de créer un plan de maintenance pour les éditions « gratuites » de SQL Server en emploi, par exemple, des jobs Windows chargés de lancer des scripts de maintenance customisés. Nous aborderons cela dans un billet indépendant.

De la nécessité de choisir la bonne approche de planification des jobs de maintenance

Plan de maintenance idéal

Il n’existe aucune approche idéale concernant la planification de jobs constituant un ou plusieurs plans de maintenance. Voici toutefois un exemple de planification (arbitraire) d’un plan de maintenance unique pouvant être adapté à un grand nombre de besoins (on suppose que l’on dispose d’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.
  • Tous les jours (sauf le dimanche), à 1h du matin: réorganisation des indexes des bases de données, puis mise-à-jour de leurs statistiques (recommandé).
  • Tous les jours, à 1h du matin : reconstruction des indexes de toutes les bases de données (ce qui va automatiquement mettre à jour les statistiques de celles-ci).
  • Tous les jours, à 2h du matin : sauvegarde complète de toutes les bases de données. Et si possible, avec compression (activation de l’option native à partir de SQL Server 2008). Avec suppression, au préalable, des sauvegardes complètes (en garder 2 au minimum, par précaution) et transactionnelles datant de plus de X jours/semaines/mois.
  • Tous les jours, toutes les heures (ou toutes les 15 ou 30 minutes) : sauvegarde transactionnelle des bases dites « critiques ».
  • Chaque dimanche, à 6h du matin: nettoyage des historiques des jobs de maintenance datant de plus d’1 mois.
Sachez qu’une fréquence élevée de sauvegardes transactionnelles sur des bases de données critiques offre un double-avantage : troncature fréquente de leur fichier de journal des transactions (fichier LDF) et amélioration (très) significative de la stratégie de restauration. De plus, il est important d’effectuer une mise-à-jour des statistiques des indexes après toute opération de réorganisation d’indexes.

Quelques axes d’amélioration

Il est important de ne pas mettre au second plan la sauvegarde et l’optimisation de bases de données systèmes. En effet, celles-ci sont régulièrement utilisées par l’instance SQL Server pour délivrer des informations vitales (i.e., informations systèmes, informations sur les bases de données, points de contrôles, informations d’authentification,…).

Par ailleurs, pour des raisons de performances, il est important de bannir toute utilisation d’un job (ou sous-plan) de maintenance destiné à faire un shrink (purge) de base(s) de données. C’est un worst-practice absolu.
        Au sujet de la vérification de l’intégrité des bases de données

S’il y a au moins une base de données très volumineuse (VLDB) et que la vérification de l’intégrité de toutes ses pages prend trop de temps et/ou de ressources, il est possible la répartir sur plusieurs jours via l’utilisation de commandes plus avancées telles que CHECKCATALOG, CHECKTABLE,… Référez-vous à la section Mise-en-œuvre de différentes stratégies de vérification de l’intégrité de données selon divers scénarii du billet [SQL Server] Gestion de fichiers de données: vérification de l’intégrité des données pour plus de détails intéressants.

   Au sujet des sauvegardes

S’il existe au moins une base de données trop volumineuse (VLDB), il est possible d’utiliser des sauvegardes différentielles. Surtout si le(s) job(s) de sauvegarde complète prennent trop de temps (avec ou sans compression).

Dans ce cas de figure, le plan de maintenance idéal pourrait être modifié de sorte que les sauvegardes complètes aient lieu une fois par semaine, idéalement le dimanche soir ou nuit (si possible, avec une durée de vie d’au moins 2 semaines histoire d’avoir au moins 2 fichiers BAK complets), et les sauvegardes différentielles (si possible, avec durée de vie d’au moins 1 à 2 semaines, sachant qu’une durée de vie de 2 jours peut faire l’affaire si problème de volumétrie), les autres soirs. La durée de rétention des fichiers de sauvegardes est bien sûr, ici, arbitraire et doit être adaptée en fonction des besoins et des contraintes. Et en outre, il est important de retenir les points suivants (voir également ici) :

  • Toute sauvegarde transactionnelle est liée à la dernière sauvegarde transactionnelle qui la précède ou à une sauvegarde complète pouvant initier le jeu de sauvegardes.
  • Toute sauvegarde différentielle est liée à la dernière sauvegarde complète qui la précède. Par conséquent, toute sauvegarde différentielle n’est pas possible s’il n’existe pas de sauvegarde complète.
  • Si vous souhaitez effectuer une sauvegarde exceptionnelle (pour une migration ou un rafraîchissement d’une base de développement, par exemple) sans altérer le jeu de sauvegardes différentielles existant, ni passer par un job de sauvegarde de votre plan de maintenance, lancez une sauvegarde de copie (possible depuis SQL Server 2005 en employant l’option WITH COPY_ONLY de BACKUP DATABASE). L’utilisation d’une sauvegarde de copie ou non n’a aucune incidence sur les sauvegardes transactionnelles.
A noter que les sauvegardes différentielles sous SQL Server 2000 ne sont possibles qu’avec l’Enterprise Edition. De plus, les sauvegardes dites de compression ne sont disponibles qu’à partir de SQL Server 2008 Enterprise Edition.

Vous pouvez jeter un coup d’oeil ici  pour en savoir plus sues sauvegardes avec SQL Server.

Au sujet des jobs d’optimisation

Pour finir, afin de maximiser l’efficacité des jobs d’optimisation d’indexes, il serait louable d’employer des scripts d’optimisation d’indexes sélective[1], surtout s’il faut traiter une base de données très volumineuse et critique n’offrant aucun moment idéal pour lancer les jobs d’optimisation d’indexes standards du fait d’une forte activité sur le serveur traité.

L’incorporation de scripts T-SQL de réindexation sélective peut se faire en modifiant tout simplement les sous-plans relatifs à la réorganisation des indexes et à leur reconstruction, et en utilisant à la place des tâches de réorganisation d’indexes ou de reconstruction d’indexes, la tâche Execute T-SQL Statement Task (trouvable dans la boîte d’outils Maintenance Plan Tasks, de SSMS, par exemple)
au sein de laquelle pourrait être placé notre code.

A noter que du fait de la gourmandise des opérations de reconstruction d’indexes en termes de ressources volumétriques, il peut être utile de lancer automatiquement 2 scripts T-SQL dont l’un permettrait, avant tout lancement du job d’optimisation, de passer les bases de données qui sont en FULL
recovery mode en BULK-LOGGED (de sorte que les opérations de reconstruction d’indexes soient peu journalisées, afin d’économiser des ressources) et l’autre de repasser les bases concernées en FULL (quelques minutes après ou) à la fin du travail du job de réindexation. En revanche, cela n’aura aucun effet sur les opérations de réorganisation d’indexes.

Pour aller plus loin…

Dans un prochain billet, nous aborderons la mise-en-place d’un plan de maintenance alliant jobs d’optimisation et jobs de sauvegardes.


[1]Ola Hallengren, DBA très connu au sein du microcosme de SQL Server, possède des scripts particulièrement performants, et téléchargeables (ainsi que la documentation) sur http://ola.hallengren.com, dont Brad M. McGehee, MVP SQL Server, explique bien leur utilisation sur http://www.simple-talk.com/sql/database-administration/automate-and-improve-your-database-maintenance-using-ola-hallengrens-free-script/. Dans notre cas, après déploiement du script MaintenanceSolution.sql, il suffira de faire un appel de la procédure IndexOptimize à placer au sein d’une tâche Execute T-SQL Statement Task dans un job de réindexation, et en adaptant bien les paramètres en fonction des besoins (réorganisation sélective et/ou MAJ sélective des statistiques ? Reconstruction sélective des indexes ?).
Il est possible de ne pas ou peu journaliser les opérations de reconstruction d’indexes (via le mode de récupération BULK-LOGGED), par contre, il est impossible d’éviter la journalisation de celles de réorganisation d’indexes.
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