Editions ENI Livres | Vidéos | e-Formations
Tâches planifiées
Introduction
SQL Server donne la possibilité d’automatiser les tâches administratives. Il n’est bien sûr pas possible d’automatiser toutes les tâches mais les tâches planifiées représentent un bon complément à l’optimisation faite par défaut par SQL Server. De plus, avec ces tâches prédéfinies, l’administrateur possède un rôle d’anticipateur, ce qui lui donne plus de possibilités pour en tirer le meilleur tant au niveau des performances que de la fiabilité.
La gestion des tâches planifiées, des alertes et des opérateurs sont des services rendus par l’agent SQL Server. Ce service doit être démarré afin que ces éléments soient gérés. L’agent SQL Server travaille avec l’Observateur d’événements pour la gestion des erreurs SQL Server, l’Analyseur de performances pour la gestion des alertes sur des conditions de performances, et la base MSDB afin de connaître la réponse à appliquer face à une alerte, ou bien les tâches planifiées à exécuter.
Principe de fonctionnement
Face à une alerte, l’agent peut réagir en exécutant un travail et/ou en prévenant un opérateur afin que ce dernier soit au courant du problème qui vient...
Configuration des services
Comme l’exécution automatique de travaux administratifs repose sur le service SQL Server Agent, il est important que ce dernier soit correctement configuré.
La configuration du service MSSQL Server a été abordée lors de l’installation.
1. La sécurité de SQL Server Agent
Le service SQL Server Agent permet la gestion de nombreux éléments. Si le service doit posséder des droits élevés sur le serveur pour être capable de réaliser correctement toutes les tâches qui lui sont assignées, l’utilisation de ce service doit être contrôlée au plus juste. Ce contrôle est assuré par les trois rôles de base de données définis sur la base msdb :
-
SQLAgentUserRole : peuvent créer leurs propres travaux
-
SQLAgentReaderRole: peuvent en plus lister tous les travaux du serveur
-
SQLAgentOperatorRole : ont tous les droits sur la gestion des travaux, alertes et opérateurs
L’appartenance à ces rôles n’est nécessaire que pour les utilisateurs non-membres du rôle de serveur sysadmin.
Par exemple, si un utilisateur se connecte à la console graphique SQL Server Management Studio sans être membre de l’un de ces trois rôles, alors l’outil ne présentera tout simplement pas le nœud relatif à SQL Server Agent. Ainsi, l’utilisateur n’est pas capable de modifier, ni même de connaître le travail réalisé au niveau de l’automatisation...
Les opérateurs
Un opérateur peut correspondre à une personne physique ou bien à un rôle joué dans l’entreprise. Ainsi, en fonction de la taille de l’entreprise, une même personne physique peut jouer le rôle de plusieurs opérateurs, ou bien un même opérateur peut correspondre à plusieurs personnes physiques.
Les opérateurs seront utilisés par l’agent SQL Server pour prévenir, par mail, de la fin d’exécution d’un travail ou bien lors du déclenchement d’une alerte pour les informer de la gravité de la situation.
La définition de tous les opérateurs est stockée dans la base msdb.
1. Création
La définition des opérateurs avant celle des alertes et des travaux est préférable car les opérations administratives s’enchaînent alors dans un ordre logique.
Les principales caractéristiques d’un opérateur sont :
-
Son nom.
-
Son adresse de messagerie (il est souhaitable d’utiliser des adresses de groupes de distribution dans le cas où plusieurs personnes doivent être notifiées simultanément).
SQL Server Management Studio
Pour créer un nouvel opérateur, il faut sélectionner Nouvel opérateur depuis le menu contextuel associé au nœud SQL Server Agent - Opérateurs...
Les travaux
L’automatisation de certaines tâches d’administration répétitives est possible par l’intermédiaire d’un travail et de la planification de son exécution. Il est toutefois possible de lancer manuellement l’exécution d’un travail.
Les travaux sont constitués d’un ensemble de tâches. À la fin de chaque tâche deux cas se présentent : soit la tâche a été exécutée avec succès, soit la tâche a échoué.
Le travail, qui est un enchaînement de tâches doit définir toutes les solutions possibles afin qu’il se déroule correctement.
Tous les travaux sont stockés au sein de la table dbo.sysjobs dans la base msdb.
1. Mise en place
Les principales caractéristiques d’un travail sont :
-
Le nom : il doit être unique sur le serveur et limité à 128 caractères.
-
La catégorie : elle permet d’organiser les travaux en fonction des opérations qu’ils réalisent. Il existe, dès l’installation du serveur, des catégories prédéfinies telles que : Texte intégral, Maintenance de la base de données...
-
Le propriétaire : celui-ci peut être différent de l’utilisateur qui l’a créé.
-
La description.
-
Les étapes du travail.
-
La planification.
-
La notification.
Chaque travail peut être lié à une catégorie. Le regroupement par catégorie permet un regroupement logique des différents travaux.
La création d’un travail n’est possible que pour un administrateur du système (sysdamin) ou bien pour un utilisateur membre de l’un des trois rôles de base de données liés à SQL Server Agent.
La modification et la suppression d’un travail n’est possible que par le propriétaire ou bien un administrateur du système.
La définition d’un travail peut être réalisée soit par SQL Server Management Studio, soit au moyen de la procédure stockée sp_add_job.
USE [msdb]
GO
EXEC msdb.dbo.sp_add_job @job_name=N'travail_test',
@enabled=1, ...Les alertes
Les alertes vont être définies afin de déclencher un traitement automatique pour corriger le problème et/ou avertir un opérateur qui sera en mesure d’agir rapidement afin de résoudre le problème.
1. Présentation
Lors du fonctionnement du serveur, des erreurs, des messages ou des événements générés par SQL Server sont inscrits dans l’Observateur d’événements de Windows. L’agent SQL Server va lire le journal Application à la recherche des informations qu’il est en mesure de traiter en les comparant aux alertes qui sont définies dans la table dbo.sysalerts de la base msdb.
Une alerte peut également être déclenchée suite au dépassement d’une valeur limite (fixée) par un compteur de performance. Enfin une alerte peut être déclenchée suite à un événement WMI (Windows Management Instrumentation) particulier. Dans ce dernier cas, l’agent SQL Server est un client de l’espace de noms WMI et lors de la définition de l’alerte, il est nécessaire de spécifier l’événement WMI qui va déclencher l’alerte.
Les alertes associées à une erreur SQL Server sont les plus fréquentes.
a. Comment inscrire une information dans le journal Application ?
Trois types d’événement sont inscrits dans l’Observateur d’événements :
-
Les messages dont le niveau de gravité et supérieur à 19. Les messages sont stockés dans la table dbo.sysmessages de la base Master. Pour forcer un message dont le niveau de gravité est inférieur à 19 à s’inscrire dans le journal, il faut exécuter la procédure sp_altermessage.
-
Toutes les instructions RAISERROR avec l’option WITH LOG.
-
Tout événement consigné avec xp_logevent.
La taille du journal Application de l’Observateur d’événements de Windows doit être suffisante pour contenir tous les messages.
b. Comment réagit l’agent SQL Server ?
L’agent SQL parcourt le journal Application à la recherche des messages provenant de SQL Server. Il compare alors l’erreur avec les alertes définies dans la table...
Exercice : planifier les tâches
1. Énoncé
Définissez une tâche planifiée Suppression_commandes sur la base Gescom qui va s’exécuter tous les 1er de chaque mois à 23h et dont l’objet sera de supprimer les commandes antérieures au 1er janvier de l’année en cours.
2. Corrigé
Après avoir vérifié que le Service SQL Server Agent est bien démarré, il faut se positionner sur le nœud Travaux et sélectionner Nouveau travail depuis le menu associé.
Sur la page Général, renseignez le nom du travail puis rendez-vous sur la page Étapes. Il faut à ce niveau demander la création d’une nouvelle étape (bouton Nouveau) et compléter cette nouvelle étape comme illustré ci-dessous :
En cas de doute sur la syntaxe Transact SQL, le bouton Analyser permet de s’assurer que le script est syntaxiquement correct.
Il faut ensuite se rendre sur la page Planification pour programmer l’exécution de cette tâche tous les 1er de chaque mois à 23h. L’écran ci-dessous illustre cette planification :
