Créer un plan de maintenance SQL Server
Les activités classiques que vous pouvez planifier pour une maintenance SQL Server régulière sont les suivantes :
- Sauvegardes de la base de données et du journal des transactions
- Vérifications de cohérence de base de données
- Maintenance d’index
- Mises à jour des statistiques
Il est essentiel de comprendre l’importance des sauvegardes, ainsi que la maintenance des index et des statistiques, pour toutes vos bases de données. Les vérifications de cohérence de base de données, également appelées CHECKDB (à l’aide de la commande DBCC CHECKDB), sont tout aussi importantes, car elles sont la seule façon de vérifier l’intégralité d’une base de données pour la corruption. En fonction de la taille de vos bases de données et de vos besoins en temps d’activité, vous pouvez effectuer toutes ces activités tous les soirs. Toutefois, dans les systèmes de production, les opérations de maintenance sont souvent réparties au cours de la semaine, car la maintenance des index et les vérifications de cohérence sont très intensives et généralement effectuées pendant les heures de fin de semaine.
De nombreux administrateurs de bases de données échelonnent les sauvegardes de bases de données volumineuses, effectuant une sauvegarde complète par semaine et utilisant des sauvegardes différentielles et des journaux des transactions pour gérer la récupération à un moment précis. SQL Server offre un moyen intégré de gérer toutes ces tâches à l’aide de plans de maintenance. Les plans de maintenance créent un flux de travail de tâches pour prendre en charge vos bases de données et sont créés en tant que packages Integration Services, ce qui vous permet de planifier vos activités de maintenance. En outre, de nombreux administrateurs de base de données utilisent des scripts open source pour la maintenance de base de données afin d’obtenir plus de flexibilité et de contrôle sur les activités de maintenance.
Meilleures pratiques pour les plans de maintenance
Les plans de maintenance vous aident non seulement à effectuer la maintenance de la base de données, mais également à offrir des options permettant de réduire les données de la base de données msdb, qui sert de magasin de données pour SQL Server Agent. En outre, les plans de maintenance vous permettent de spécifier la suppression d’anciennes sauvegardes de base de données à partir du disque. La suppression des anciens fichiers de sauvegarde réduit la taille de votre volume de sauvegarde et permet de gérer la taille de la base de données msdb.
Assurez-vous que la période de rétention de sauvegarde est supérieure à votre fenêtre de vérification de cohérence. Par exemple, si vous exécutez un contrôle de cohérence hebdomadaire, vous devez conserver suffisamment d’historique de sauvegarde pour récupérer à partir d’une altération potentielle détectée pendant les vérifications de cohérence. Notez que l’opération de sauvegarde ne détecte pas l’altération dans une base de données. Il est donc possible d’avoir une altération dans un fichier de sauvegarde. Les activités du plan de maintenance sont planifiées en tant que tâches du SQL Server Agent pour être exécutées.
Créer un plan de maintenance
Vous pouvez créer un plan de maintenance à l’aide de SQL Server Management Studio, comme indiqué ci-dessous. Dans l’exemple, plusieurs tâches de maintenance sont combinées en un seul plan de maintenance. Toutefois, la meilleure pratique consiste à créer un plan de maintenance distinct pour chaque type de tâche, et éventuellement même pour des bases de données spécifiques sur votre serveur. Par exemple, vous pouvez créer un plan de maintenance pour sauvegarder des bases de données système et une autre pour sauvegarder des bases de données utilisateur. En outre, vous pouvez avoir un plan de maintenance distinct pour gérer la sauvegarde d’une base de données utilisateur particulièrement volumineuse. L’image ci-dessous et les exemples suivants montrent comment créer un plan de maintenance à l’aide de l’Assistant Plan de maintenance.
L’image montre le premier écran de l’Assistant Plan de maintenance dans SQL Server Management Studio (SSMS). Vous devez spécifier un nom pour votre plan de maintenance ainsi qu’un compte d’exécution. La plupart des tâches de maintenance s’exécutent en tant que compte de service SQL Server Agent, mais à des fins de sécurité, certaines tâches peuvent avoir besoin d’être exécutées en tant que compte différent. Par exemple, si vous devez sauvegarder sur un partage de fichiers accessible uniquement par un compte spécifique, vous utilisez un utilisateur proxy, qui est un composant de l’Agent SQL Server.
Qu’est-ce qu’un compte proxy ?
Un compte proxy est un compte avec des informations d’identification stockées que SQL Server Agent peut utiliser pour exécuter des étapes de travail spécifiques en tant qu’utilisateur désigné. Les informations de connexion de cet utilisateur sont stockées en tant qu’informations d’identification dans l’instance SQL Server. Les comptes proxy sont généralement utilisés lorsque des étapes de travail spécifiques nécessitent des droits de sécurité très granulaires.
Supposons que vous avez un travail SQL Server Agent qui doit sauvegarder une base de données dans un partage de fichiers réseau. Si le compte de service SQL Server Agent n’a pas accès au partage de fichiers, vous pouvez créer un compte proxy avec les autorisations nécessaires. Ce compte proxy peut ensuite être utilisé pour exécuter l’étape de sauvegarde, ce qui garantit qu’il dispose des droits d’accès requis.
Calendriers de travaux
Les planifications de travaux font partie du système de travail dans la base de données système msdb. Les tâches et les horaires de SQL Server Agent ont une relation de plusieurs à plusieurs, ce qui signifie que chaque tâche peut avoir plusieurs horaires et chaque horaire peut être assigné à plusieurs tâches. Toutefois, l’Assistant Plan de maintenance ne permet pas de créer des calendriers indépendants. Au lieu de cela, il crée une planification spécifique pour chaque plan de maintenance.
L’exemple suivant montre la planification d’une exécution hebdomadaire, mais vous avez également la possibilité de créer une planification avec une périodicité horaire ou quotidienne.
L’étape suivante consiste à sélectionner les tâches de maintenance à ajouter au plan. L’exemple suivant montre les opérations disponibles pour être effectuées par votre plan de maintenance.
Vérifier l’intégrité de la base de données : cette tâche exécute la commande DBCC CHECKDB pour valider la cohérence logique et physique de chaque page de base de données. Vous devez effectuer cette tâche régulièrement et l’aligner sur votre fenêtre de rétention de sauvegarde. Veillez à effectuer une vérification de cohérence avant de supprimer les sauvegardes antérieures afin d’éviter toute altération.
Réduire la de base de données : cette tâche réduit la taille d’un fichier de base de données ou de journal des transactions en déplaçant les données dans un espace libre sur les pages. Une fois que suffisamment d’espace est libéré, il peut être retourné au système de fichiers. Il est recommandé de ne pas inclure cette action dans une maintenance régulière, car elle provoque une fragmentation d’index grave, ce qui nuit aux performances de la base de données. L’opération est également très intensive en E/S et en UC, ce qui peut avoir un impact significatif sur les performances du système.
réorganiser/réorganiser l’index : cette tâche vérifie le niveau de fragmentation dans les index d’une base de données et régénère ou réorganise l’index en fonction du niveau de fragmentation défini par l’utilisateur. La reconstruction d’un index met également à jour ses statistiques.
Mettre à jour les statistiques : cette tâche met à jour les statistiques de colonne et d’index utilisées par SQL Server pour générer des plans d’exécution de requête. Les statistiques précises sont cruciales pour l’optimiseur de requête afin de prendre les meilleures décisions. Vous pouvez choisir les tables et les index à analyser, ainsi que le pourcentage ou le nombre de lignes à analyser. Le taux d’échantillonnage par défaut est généralement suffisant, mais vous aurez peut-être besoin de statistiques plus détaillées pour des tables spécifiques.
historique de nettoyage : cette tâche supprime l’historique des opérations de sauvegarde et de restauration de la base de données msdb, ainsi que l’historique des travaux DE SQL Server Agent. Il permet de gérer la taille de la base de données msdb.
exécuter le travail SQL Server Agent : cette tâche exécute un travail SQL Server Agent défini par l’utilisateur.
base de données de sauvegarde (complète/différentielle/journal) : cette tâche sauvegarde les bases de données sur une instance SQL Server. Une sauvegarde complète capture l’intégralité de la base de données et sert de point de départ pour une restauration. Les sauvegardes différentielles capturent les pages qui ont changé depuis la dernière sauvegarde complète, fournissant un point de restauration incrémentiel. Les sauvegardes du journal des transactions capturent les pages actives dans votre journal des transactions, ce qui vous permet de définir votre objectif de point de récupération. Notez que les sauvegardes du journal des transactions ne peuvent pas être effectuées sur des bases de données en mode de récupération SIMPLE.
Par exemple, si vous effectuez une sauvegarde complète le dimanche et une sauvegarde différentielle chaque semaine, pour restaurer votre base de données à midi le jeudi, vous restaurez la sauvegarde complète du dimanche, la sauvegarde différentielle du mercredi et les sauvegardes du journal des transactions du mercredi au jeudi à midi.
tâches de nettoyage de maintenance : cette tâche supprime les anciens fichiers liés aux plans de maintenance, y compris les rapports texte et les fichiers de sauvegarde. Il supprime uniquement les sauvegardes dans les dossiers spécifiés. Par conséquent, tous les sous-dossiers doivent être explicitement répertoriés ou ignorés.
Chaque tâche peut être étendue aux bases de données utilisateur, aux bases de données système ou à une sélection personnalisée de bases de données, et chacune a des options de configuration spécifiques.
Une fois la création terminée, le plan s’affiche sous la forme d’un travail dans le SQL Server Agent. Si vous avez ajouté une planification pendant le processus de création ou après, cette tâche est exécutée et les tâches de maintenance sont exécutées.
Environnement multiserveur
Dans un environnement multiserveur, SQL Server Agent vous permet de désigner un serveur comme serveur principal qui peut exécuter des travaux sur d’autres serveurs, appelés serveurs cibles. Le serveur principal stocke la source principale des travaux et les distribue aux serveurs cibles. Les serveurs cibles se connectent régulièrement au serveur principal pour mettre à jour leurs planifications de travail. Cette configuration vous permet de définir un travail une seule fois et de le déployer dans votre entreprise. Par exemple, vous pouvez configurer des tâches de maintenance de base de données sur le serveur principal et les envoyer vers un groupe de serveurs cibles, ce qui garantit un déploiement cohérent.