Plan de maintenance – a quoi ça sert ?

Le plan de maintenance d’une instance SQL Server est l’ensemble des jobs qui permettent de garantir le bon fonctionnement des bases de données dans le temps.

Est-ce indispensable ?

Imaginez une instance de bases de données non maintenue. Sans action sur vos indexs, les performances deviendraient catastrophiques. Sans sauvegarde, vous risqueriez de perdre toutes vos données, les journaux de logs satureraient l’espace de vos disques. Et ce n’est qu’un avant-gout des problèmes qui pourraient se multiplier. Je suis donc obligé de dire que la mise en place d’un plan de maintenance est obligatoire. C’est même une des premières choses à définir lors d’une installation.

Les différentes étapes

La sauvegarde

C’est surement à la sauvegarde de nos bases de données que l’on pense en premier quand on parle de plan de maintenance. Vous devez adapter votre stratégie de sauvegarde en fonction de vos besoins. Par exemple, pour un petit site web relativement statique avec un recovery model simple, une sauvegarde FULL chaque nuit lors des heures creuses d’activité pourrait suffire. Par contre, pour un gros site e-commerce, une sauvegarde FULL quotidienne + des sauvegardes incrémentales régulières + des sauvegardes des logs toutes les 5 minutes pourrait être envisageable.

Bref, cet article n’a pas vocation à vous aider à choisir une stratégie de sauvegarde mais plutôt de ne pas oublier des étapes importantes dans votre plan de maintenance.

N’oubliez pas que le but de la sauvegarde des bases de données est de pouvoir les restaurer ! Il faut donc penser à vérifier que vos sauvegardes sont opérationnelles.

Echec restore

Je sauvegarde toujours dans un nouveau fichier, je ne suis pas vraiment adepte de plusieurs sauvegardes dans un seul fichier (bien qu’il y ait quelques avantages). Ce nouveau fichier est horodaté dans son nom. Avec ce système, je suis donc obligé de créer une étape de suppression des anciennes sauvegardes pour ne pas remplir mes disques.

J’ai souvent la question sur la sauvegarde des bases systèmes. Oui il est préférable de sauvegarder au minimum MASTER (pour le mot de passe des logins par exemple), MSDB (pour toutes les données de l’agent) et éventuellement MODEL.

En principe, je sauvegarde également régulièrement dans des fichiers SQL le script des logins, les server objects et les objets de l’agent. Ce n’est pas obligatoire, mais ça permet souvent de gagner du temps.

La vérification d’intégrité

La vérification d’intégrité des bases de données est également incontournable. Si une de vos bases est corrompue, il faut être alerté le plus rapidement possible pour réduire la perte de données.

En général, j’exécute un DBCC CHECKDB avant de faire les sauvegardes complètes. Pour réduire le coût de cette opération, on peut alterner avec un DBCC CHECKDB et l’option WITH PHYSICAL_ONLY.

Integrité de vos bases

Je recommande de conserver la dernière sauvegarde FULL effectuée juste après le dernier DBCC CHECKDB (FULL). En cas de corruption, on peut repartir d’une sauvegarde saine.

Les indexs et statistiques

A chaque insertion / suppression / mise à jour de données dans une table, ses indexs se fragmentent.

Si une maintenance des indexs et des statistiques n’est pas effectuée, les performances globales de la base de données vont se dégrader. En effet, un index fragmenté va obliger à lire plus de pages de données donc plus d’IO et plus de RAM seront nécessaires. Si les statistiques ne sont pas à jour, l’optimiseur pourrait décider d’utiliser un plan d’exécution non adapté ce qui aurait pour effet de consommer trop de ressources et de ralentir les temps d’exécution des requêtes.

Il convient donc de défragmenter régulièrement les indexs et de recalculer les statistiques. Cette tache est une partie du plan de maintenance indispensable à une base de données.

On pourrait être tenté de reconstruire chaque index mais pour une base de données utilisée 24/24h, ça pourrait ne pas coller avec les besoins métier car cette opération peut être très couteuse. L’idéal est donc d’utiliser des scripts et en général j’applique cette règle :
– Si l’index est fragmenté à plus de 30% alors je reconstruis l’index (à la reconstruction de l’index, les statistiques sont recrées donc pas besoin de refaire une mise à jour).
– si l’index est fragmenté entre 10 et 30% alors je réorganise l’index et je recalcule les statistiques.
– sinon je ne fais rien.

defrag

N’oubliez pas qu’en version Enterprise, les indexs peuvent être mis à jour ONLINE. C’est plus lent mais la base est toujours accessible.

Si un index reste toujours fragmenté et possède moins de 8 pages, inutile de s’acharner à le défragmenter…

Le nettoyage

La dernière tache indispensable dans un plan de maintenance est la suppression des historiques de sauvegardes et des jobs de l’agent. En effet, si vous ne le faites pas, la base MSDB deviendra énorme.

Nettoyer les historiquesEssayez de restaurer rapidement une base à partir d’une sauvegarde FULL + quelques sauvegardes de LOGS avec un historique de quelques mois et vous comprendrez ce dont je veux parler 😉

Conclusion

Le plan de maintenance est une des taches de base d’un DBA et devrait être mis en place sur chaque instance. Il n’existe pas un plan de maintenance type car c’est à vous d’adapter votre plan au besoin de l’entreprise.

N’oubliez pas que grâce à Kankuru, vous pouvez surveiller que vos plans fonctionnent correctement sur tous vos serveurs !

Il est possible que vos plans de maintenance contiennent des étapes supplémentaires. N’hésitez pas à les décrire dans vos commentaires.

3 thoughts on “Plan de maintenance – a quoi ça sert ?

  1. Merci super article

    Pourrai tu nous faire un petit shéma sur le plan de maintenance parfait d’apres toi ?

    • Bonjour David,
      Impossible de répondre à cette question sans avoir de contexte (c’est d’ailleurs une réponse de DBA : « ça dépend 🙂 »)
      Je pense que toutes ces étapes sont indispensables, par contre en fonction de ton besoin, de ta topologie, de ton utilisation, la stratégie peut varier.
      Je ne te proposerais donc pas le même plan pour plusieurs serveurs répliqués qui seraient attaqués par des serveurs web en ligne 24/7 que pour une base de comptabilité utilisée du lundi au vendredi de 8h à 18h.
      En effet, ces tâches de maintenance sont assez coûteuses, donc il faut soit viser les périodes creuses soit essayer d’être malin 🙂

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Time limit is exhausted. Please reload CAPTCHA.