Voici une énumération des raisons qui font que jusque ici, je n’ai jamais donné mon aval à la création de plans de maintenance sur des instance SQL Server de production :
=> Les plans de maintenance ne sont pas portables : il est impossible de déployer simplement sur plusieurs instances de SQL Server un même plan de maintenance en une seule fois. D’autre part, une fois la copie réalisée, il est nécessaire de mettre à jour la chaîne de connexion qui est dans celui-ci, et qui correspond a l’instance sur laquelle celui-ci a été créé. Enfin, on peut se heurter à des problèmes d’intégrité des données référentielle dans la base de données système msdb, ce que le DBA (trop) prudent que je suis se refuse catégoriquement à réaliser.
=> Lorsqu’on supprime un plan de maintenance, le package SSIS est supprimé, mais pas le travail de l’Agent SQL Server qui lui est attaché
=> A chaque fois que quelqu’un crée ou modifie un plan de maintenance, il est nécessaire de changer de nouveau le propriétaire de celui-ci et/ou du travail de l’Agent SQL Server qui est attaché à ce plan, car celui-ci est écrasé automatiquement par le nom de connexion de l’utilisateur qui réalise l’édition du plan de maintenance
=> Dans les éditions Enterprise de SQL Server 2005, il est impossible d’obtenir l’option CHECKSUM lorsqu’on souhaite réaliser une sauvegarde de base de données. Sous SQL Server 2008, ce n’est pas possible non plus, mais le CHECKSUM est calculé lorsqu’on active la compression des sauvegardes de base de données.
=> En ce qui concerne la maintenance des statistiques, il est impossible d’indiquer le niveau d’échantillonnage par défaut. D’autre part, l’échantillonnage est soit annulé (FULL), soit il est identique pour toutes les tables, ce qui n’est pas optimal
=> Quand on connaît les dégâts que peuvent causer un rétrécissement de base de données, on comprend qu’une telle tâche ne devrait tout simplement pas exister
=> Les tâches de défragmentation et de reconstruction des index ne permettent pas de spécifier des valeurs seuil pour lesquelles il est préférable de défragmenter plutôt que de reconstruire un index, et inversement.
=> La vérification de l’intégrité des bases de données ne permet malheureusement pas de spécifier le niveau de vérification avec lequel on souhaite l’effectuer.
En revanche les tâches de nettoyage des fichiers, d’envoi de notification à un opérateur, d’exécution d’un travail de l’Agent SQL Server, d’exécution de script T-SQL ou de l’historique sont plutôt bien conçues, même si pour la dernière j’aurai préféré pouvoir spécifier une durée de rétention par type d’historique.
Pour toutes ces raisons, je proscris pour le moment l’utilisation des plans de maintenance sur les serveurs de production, pour lesquels j’ai développé un ensemble de procédures stockées qui me permet de réaliser juste le minimum.
Je pense que les plans de maintenance sont un très bon moyen d’assurer un niveau minimal de qualité d’une base de données avec un minimum d’effort, et sont appropriés pour des serveurs de développement ou des test peu importants, mais pas (encore ?) pour des instances de SQL Server qui supportent la production.
Attention donc à ne pas utiliser les plans de maintenance à tors et à travers 
ElSüket.
Aller un petit commentaire pour Mr l’expert
Globalement on peut utiliser les plans de maintenance sur des bases de données à taille ou nombre d’objets raisonnables. Ces plans sont tout à fait adapter à bon nombre de « petites » instances SQL Server.
On peut effectivement créer son propre plan de maintenance dans BIDS et du coup rendre le plan beaucoup plus générique et paramétrable. C’est d’ailleurs ce qu’utilise un de mes clients pour ses 200 instances SQL. De plus le déploiement et la mise à jour des packages SSIS est tout à fait scriptable via l’utilitaire DTEXEC et les options qui vont bien (pour l’avoir déjà fait).
Tout cela pour dire que la problématique des plans de maintenance à base de package SSIS n’est pas tant le déploiement mais l’exhaustivité des tâches qui peuvent être réalisées. Tu l’as très bien montré dans ton billet d’ailleurs (où il faut utiliser des tâches de script SQL ce qui revient à faire du TSQL au final tu me diras
). Je pense que la politique de MS vis à vis des plans de maintenance consiste à couvrir une gamme de besoin large. On peut se poser la question suivante : combien d’instances / de bases de données sur SQL Server nécessitent un plan de maintenance custom ? Je pense que le ratio ne doit pas être si élevé que cela …
++
Tout à fait d’accord.
En production, mes « plans de maintenance » sont en FULL T-SQL.
On peut tout à faire faire exécuter un package SSIS par un job sans pour autant que cela constitue un plan de maintenance de base de données, puisque toute les tâches SSIS ne sont pas des tâches de maintenance de base de données.
Il est en effet très pratique de pouvoir scripter un job : on peut ainsi déployer le même job sur un groupe de serveurs avec la fonctionnalité Central Management Server et ses groupes de serveurs.
De la même façon, on peut exécuter un job sur un groupe de serveurs en utilisant l’instruction EXEC sp_start_job @job_name = ‘monJob’.
La génération du script du job donne également sa planification : il s’agit de l’appel à la procédure stockée msdb.dbo.sp_add_jobschedule; cela étant, rien n’oblige à planifier un job : si le job que vous avez scripté n’est pas planifié, vous ne trouverez pas l’appel à cette procédure stockée, pas plus que vous ne trouverez de planifications pour celui-ci par un clic-droit sur celui-ci > Propriétés > Planification.
Vous trouverez dans le billet suivant une requête qui permet de consulter l’historique d’exécution des jobs :
http://blog.developpez.com/elsuket/p7895/agent-sql-server/voir-l-historique-d-execution-des-jobs-p/
Dans ce billet que je viens de publier, il y a une requête qui permet d’obtenir un aperçu complet de tous les jobs d’une instance SQL Server : http://blog.developpez.com/elsuket/p11193/agent-sql-server/une-requete-pour-lister-tous-les-travaux/
@++
Donc si je comprends bien, à partir du moment où crée un job sql qui exécute un package SSIS, on a alors un plan de maintenance.
Du coup, je réalise que puisque vous parlez de portabilité, c’est qu’il doit être possible de scripter un job. Et effectivement, chose que je n’avais jamais remarquée jusqu’ici, il y a bien l’option Script Job As lorsqu’on fait un clic droit. Effectivement il faut changer le nom du serveur mais un find/replace va assez vite mais je conviens que ce n’est pas pratique.
Par contre, par curiosité et pour mon information, je m’interroge car je ne trouve nulle part la mention de quand doit être exécuté ce job. Le fait d’exécuter ce script nécessiterait donc de devoir encore paramétré tout ce qui est « scheduling » (je ne trouve pas le mot français^^) du job ?
Je vais donc continuer à tester la méthode de maintenance fournie par sqlpro dans un ces articles qui utilise une table des tâches de maintenance à effectuer sur chaque DB. Cela m’avait pas mal du tout (venant de lui, le contraire eut été étonnant) mais je n’ai pas encore osé passer cela en production… Etant DBA amateur, je joue encore un peu à l’apprenti sorcier et chaque changement sur le serveur de production provoque pas mal de stress ^^
Encore merci pour l’article.
Kropernic.
Merci à tous les deux de vos commentaires.
Je ne savais pas que les plans de maintenance sont disponibles dans BIDS.
En fait un plan de maintenance, c’est la « liaison » entre un travail de l’Agent SQL Server et une tâche SQL Server Integration Services; donc effectivement il doit être possible de spécifier des plans de maintenance dans BIDS.
Il n’est pas absolument nécessaire de remplacer les plans de maintenance par l’exécution d’une procédure stockée qui fait plus ou moins le même travail. Comme bien souvent, cela dépend de la quantité de données que vous ou votre employeur peut se permettre de perdre en cas de crash : si vous pouvez dire que perdre une journée de données n’est pas catastrophique pour le business, alors un plan de maintenance pour les sauvegardes peut tout à fait convenir.
Si en revanche vous ne pouvez pas vous permettre de perdre une minute de changements de données, et que vous avez besoin d’être alerté immédiatement lorsqu’une sauvegarde a échoué, je ne trouve pas qu’un plan de maintenance soit ce qu’il y a de plus adapté.
Finalement, les plans de maintenance ne sont pas catastrophiques pour les sauvegardes de base de données; ils le sont bien plus en ce qui concerne la maintenance des statistiques et des index, ou pire, le rétrécissement des bases de données, qui, je pense, ne devraient tout simplement pas exister.
Si j’ai bien compris, vous parlez des tâches toutes faites et prêtes à l’emploi qui se trouve dans le Business Intelligence Development Studio. Est-ce exact ?
Je voudrais être sûr car j’ai mis en place un backup des DB de prod à l’aide de ces tâches (n’y connaissant rien, j’ai trouvé ça pratique et bien foutu :-/) et je voulais savoir si je pouvais laisser cela en place ou bien s’il fallait que j’écrive les scripts à la main ?
J’abonde totalement en ton sens !