Plan de maintenance pour SQL Server : ce qu’il faut savoir avant de se lancer

Présentation des plans de maintenance pour SQL Server

Un plan de maintenance est un workflow défini qui exécute un ensemble de tâches de maintenance préconfigurées, telles que la sauvegarde de bases de données, la reconstruction d'index ou l'exécution de jobs SQL Server Agent. Les tâches sont configurées pour être exécutées dans un ordre spécifique ou regroupées dans des sous-plans et planifiées pour être exécutées à différents moments.

SQL Server supporte également des plans de maintenance qui couvrent plusieurs instances SQL Server. Un plan multi-serveurs nécessite qu’une instance soit désignée comme serveur maître et que les autres instances soient les serveurs cibles. Le plan doit être créé et géré depuis le serveur maître, mais il peut être consulté sur les serveurs cibles.

Ils peuvent être exécutés manuellement ou être planifiés à différents intervalles. Lorsqu'un DBA crée un plan directeur, SQL Server génère un package SQL Server Integration Services (SSIS) composé de tâches de maintenance individuelles. SQL Server configure également un job Agent Agent SQL Server, qui exécute le package SSIS.

Un plan de maintenance soigneusement conçu et planifié garantit que les tâches administratives sont exécutées au bon moment, mais tout en retirant la contrainte de l’exécution manuelle. Ce qui soulage les administrateurs de bases de données et leur permet de consacrer plus de temps à d’autres tâches. Plus important encore, ces plans peuvent garantir une optimisation continue des bases et que les performances ne se dégradent pas avec le temps. Une base de données qui n'est pas soigneusement maintenue peut avoir un impact sur la performance des applications et la productivité des utilisateurs. Les données se retrouvent également fragilisées.

Mise en œuvre des tâches

Dans SQL Server, un plan de maintenance est composé d'une ou de plusieurs tâches prédéfinies configurées pour répondre aux besoins spécifiques d’une entreprise. Par exemple, plusieurs tâches sont définies pour sauvegarder les fichiers et les logs des transactions. Les DBA ont la possibilité de planifier des sauvegardes complètes ou différentielles, de cibler des fichiers ou des bandes spécifiques et de préciser la durée de conservation des sauvegardes.

SQL Server propose également des tâches prédéfinies pour réorganiser et reconstruire les index. Une opération de réorganisation permet de compacter et de défragmenter les index regroupés et non regroupés sur les tables et les vues, pour améliorer les performances. Une opération de reconstruction réorganise les données, ce qui peut améliorer les performances de recherche. La reconstruction des index optimise également la façon dont les données et l'espace libre sont répartis sur les pages d'index.

En outre, SQL Server permet de redimensionner (à la baisse) les bases de données, un processus qui récupère l'espace disque en supprimant les données vides et les logs. Pour ce faire, le moteur de la base doit déplacer les données vers la fin du fichier pour vider l'espace près de l'avant du fichier. Bien qu'il en résulte plus d'espace disque, cela provoque également une fragmentation importante. Cela explique pourquoi de nombreux DBA expérimentés déconseillent l'utilisation de cette option. Si cette réduction est inévitable, les index devront être reconstruits par la suite.

SQL Server fournit également des tâches pour mettre à jour les statistiques et vérifier l'intégrité de la base de données. Lorsque les statistiques sont mises à jour, le moteur fournit à l'optimiseur de requêtes des informations actualisées sur les données des tables et des index. L’idée est de pouvoir optimiser les requêtes. Lorsqu'un contrôle d'intégrité de base de données est effectué, le moteur génère un rapport qu'ils peuvent utiliser pour résoudre ces problèmes ultérieurement.

Des tâches sont également disponibles pour exécuter les instructions T-SQL et Agent SQL Server, ainsi que pour nettoyer les fichiers. Par exemple, un plan de maintenance peut inclure une tâche qui supprime les données historiques après une sauvegarde, une restauration,  une opération de maintenance.

Création de plans de maintenance dans SQL Server

Il existe trois méthodes pour créer des plans de maintenance SQL Server : exécuter l'Assistant dans SQL Server Management Studio (SSMS), créer un package dans Maintenance Plan Design Surface - qui est également dans SSMS - ou écrire un script T-SQL qui définit manuellement le plan de maintenance.

L'approche la plus simple consiste à utiliser l'Assistant, qui guide les DBA à chaque étape de la création du plan. Celui-ci choisit les tâches que le plan doit inclure, les organise suivant un ordre d’exécution et configure chaque tâche sélectionnée en fonction des besoins. L'assistant construit ensuite le plan à partir de ces données.

Bien que l'assistant soit pratique pour les plans basiques, les DBA auront plus de contrôle avec Maintenance Plan Design Surface, qui fonctionne de manière similaire au SSIS. L'administrateur de projet y organise les tâches et les relie ensuite aux contraintes de priorité qui déterminent l'ordre dans lequel les tâches doivent être exécutées.

La troisième approche, qui utilise un script T-SQL pour définir un plan de maintenance, commence par la création d'un job Agent SQL Server. On y ajoute une ou plusieurs étapes, on définit la fréquence d’exécution et on associe cette fréquence au job. Un DBA peut aussi utiliser les procédures suivantes pour définir un plan :

• sp_add_job : Ajoute un job au service Agent SQL Server.
• sp_add_jobstep : Ajoute une étape au job du plan de maintenance. Il inclut le code T-SQL nécessaire pour effectuer une opération de maintenance spécifique, comme la reconstruction d'index ou la sauvegarde. Un job peut comporter plusieurs étapes.
• sp_add_schedule : Ajoute une planification à Agent SQL Server. Cela peut être appliqué à une ou plusieurs étapes du job. Un calendrier définit certains points tels que l'heure et la fréquence de début. Un plan d'entretien peut comprendre plusieurs calendriers.
• sp_attach_schedule : Associe un calendrier à un job.

L'approche choisie par les DBA dépendra de leur niveau d'expertise et des exigences de chaque plan. Dans certains cas, l'assistant peut suffire. Mais dans d’autres, plus de contrôle est nécessaire. Ils peuvent également utiliser l'assistant, puis Maintenance Plan Design Surface pour l'éditer.