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

Ce billet présente comment implémenter un bon plan de maintenance alliant sauvegardes et optimisations pour les instances SQL Server ne supportant pas le SQL Server Agent (cas de SQL Server Express, mais aussi SQL Server Compact Edition), et donc les plans de maintenance en natif.

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…

Présentation rapide d’ExpressMaint

ExpressMaint est une application développée par jasper Smith et permettant d’effectuer des tâches de maintenance sur des instances SQL Server, à partir de la version 2005. Elle est très utile pour les éditions ne supportant pas le SQL Server Agent, et donc l’utilisation native d’un plan de maintenance.

L’application est téléchargeable ici (section Downloads). Et nous supposerons que notre instance MSSQL s’appelle Mac-ROG\SQLEXPRESS (ou (local)\SQLEXPRESS).

Pour connaître les options de l’utilitaire ExpressMaint, vous pouvez simplement taper expressmaint, dans l’invite de commandes MS-DOS :


Contexte des besoins

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, à 2h30, 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 ». Dans notre cas, une seule base de données : MaBase.
  • Chaque dimanche, à 6h du matin: nettoyage de l’historique des jobs de maintenance datant de plus d’1 mois.

Chaque tâche (ou sous-plan) aura un nom spécifique (préfixé de « Full_MaintenancePlan » lors de la planification dans le Task Scheduler de Windows pour des raisons de lisibilité et d’organisation ; voir plus loin) :

  • Check_integrity, pour la vérification de l’intégrité des bases de données.
  • Reorganize_indexes, pour la réorganisation des indexes.
  • Update_stats : pour la mise-à-jour des statistiques.
  • Rebuild_indexes, pour la reconstruction des indexes des bases de données.
  • Full_backups, pour la sauvegarde complète des bases de données (et fera également un nettoyage des sauvegardes).
  • Transactional_backups, pour la sauvegarde transactionnelle des bases de données « critiques ».

Modus operandi de mise-en-place de plans de maintenance au niveau de l’OS

Pour mener à bien la mise-en-place des plans de maintenance pour une instance MSSQL gratuite (de version 2005 minimum), il faut, au préalable, créer un dossier dédié aux sauvegardes (si ce n’est déjà fait : D:\MSSQL\SQLBackups, par exemple) et un autre dédié aux rapports d’activité des plans de maintenance (i.e., D:\MSSQL\SQLReports).

Remarquez qu’il n’est nul besoin de spécifier votre login et mot de passe de connexion si vous tentez d’accéder à l’instance MSSQL via vos informations d’authentification Windows. De plus, vu que le script T-SQL déployé reconfigure déjà les options paramétrées, vous pouvez ignorer les messages à ce sujet.

  • Scripting des tâches de maintenance.

ExpressMaint ayant été déployé avec succès, un scripting des plans de maintenance en tenant compte de la stratégie de maintenance exprimée plus haut s’impose.

    • Scripting d’une tâche de maintenance Check databases :
expressmaint -S (local)\SQLExpress -D ALL -T CHECKDB -R « D:\SQL Server\MSSQL\ExpressMaint\SQL Reports » -RU WEEKS -RV 4
    • Scripting d’une tâche de maintenance Reorganize indexes :
expressmaint -S (local)\SQLExpress -D ALL -T REINDEX -R « D:\SQL Server\MSSQL\ExpressMaint\SQL Reports » -RU WEEKS -RV 4
    • Scripting d’une tâche de maintenance Update_stats (en mode full scan:
expressmaint -S (local)\SQLExpress -D ALL -T STATSFULL -R « D:\SQL Server\MSSQL\ExpressMaint\SQL Reports » -RU WEEKS -RV 4
    • Scripting d’une tâche de maintenance Rebuild indexes et encapsulation dans un fichier rebuild_indexes.sql :
expressmaint -S (local)\SQLExpress -D ALL -T REINDEX -R « D:\SQL Server\MSSQL\ExpressMaint\SQL Reports » -RU WEEKS -RV 4
    • Scripting d’une tâche de maintenance Full_backups :
expressmaint -S (local)\SQLExpress -D ALL -T DB -R « D:\SQL Server\MSSQL\ExpressMaint\SQL Reports » -RU WEEKS -RV 4 -B « D:\SQL Server\MSSQL\SQL Backups\BAK » -BU HOURS -BV 1 -V -C
    • Scripting d’une tâche de maintenance Transactional_backups:
expressmaint -S (local)\SQLExpress -D MaBase -T LOG -R « D:\SQL Server\MSSQL\ExpressMaint\SQL Reports » -RU WEEKS -RV 4 -B « D:\SQL Server\MSSQL\SQL Backups\TRN » -BU HOURS -BV 1 -V -C
  • Planification du lancement des tâches de maintenance via le Task Scheduler de Windows.

L’étape précédente a permis de montrer comment scripter les tâches de maintenance suivant nos besoins. Il reste maintenant à les automatiser, et pour s’y prendre correctement, il suffit de procéder comme suit (la mini-démonstration est faite avec le Task Scheduler) :

    • Création d’une tâche à planifier, par exemple Full_MaintenancePlan.Full_backups :

    • Paramétrage de la tâche.
      • Paramétrage général :

N’oubliez pas de cocher la case Run whether user is logged on or not. En effet, sans cela, et si le compte d’exécution est déconnecté, la tâche ne s’exécutera pas.
      • Spécification des actions :

Dans la zone Program/script, vous pouvez soit copier/coller (ou rédiger) le script de la tâche concernée (voir étape Scripting des tâches de maintenance) ou (via Browser, si nécessaire) spécifier le chemin d’accès du batch contenant le script idoine.

La partie Ajouter des arguments (facultatif) contiendra automatiquement tous les paramètres après suivant la commande expressmaint :


Après avoir validé la configuration, voici à quoi ressemblera l’onglet Actions :

      • Planification du déclencheur :

Ce qui donnera cet aspect, dans l’onglet Triggers :

En appliquant le même modus operandi pour les autres tâches à planifier suivant les besoins exprimés dans la section Avant de commencer…, voici ce que cela donne :


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 (reprendre l’exemple de création de scripts de sauvegardes dans ce billet, en remplaçant l’option DB ou LOG par DIF). Pour comprendre l’utilité d’une sauvegarde différentielle, voir ici.
  • Utiliser, éventuellement, un mode de scan des statistiques par échantillonnage (sampling) en remplaçant l’option STATSFULL par STATS.
  • Ne planifier l’optimisation des bases systèmes que pour le week-end, au lieu de toutes les nuits.
  • Incorporer le script de mise-à-jour de statistiques au sein de celui relatif à la réorganisation d’indexes de façon à pouvoir déclencher rapidement la phase de mise-à-jour des statistiques après celle relative à la réorganisation des indexes. Ou bien, créer un sous-déclencheur au sein de la tâche Full_MaintenancePlan.Reorganize_indexes destiné à lancer la mise-à-jour des statistiques.

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