Name: SQL Server 2022 Apprendre à administrer un serveur de base de données
Rating: 5 (1 reviews)

Introduction

SQL Server donne la possibilité d’automatiser les tâches administratives. Ceci peut être utilisé par l’administrateur pour gérer les sauvegardes, plans de maintenance, mais également par les développeurs pour lancer par exemple une procédure stockée.

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 qui stocke toutes les informations relatives à l’Agent SQL Server (configuration, historique d’utilisation...).

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 de surgir. Bien entendu, l’exécution d’une tâche peut conduire au déclenchement de nouvelles alertes et ainsi de suite.

D’autres tâches planifiées vont être exécutées par le service SQL Server Agent, non pas en réponse à une erreur mais sur une base de temps. Par exemple, une reconstruction des index peut être planifiée une fois par semaine dans la nuit du samedi au dimanche.

L’agent SQL Server permet de réaliser une administration préventive des problèmes qui peuvent se poser lors de l’exploitation courante d’un serveur de bases de données.

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 de tâches. Le même niveau de sécurité est défini au niveau Transact SQL.

2. Configuration de la messagerie

La gestion des mails avec SQL Server passe par la messagerie de base de données. Cette fonctionnalité peut servir dans le cadre des bases de données utilisateurs afin...

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.

Voici les principales caractéristiques d’un opérateur :

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 de l’explorateur d’objets de SQL Server Management Studio.

Transact SQL

Il est possible de créer un nouvel opérateur par l’intermédiaire de la procédure stockée sp_add_operator.

Exemple

USE [msdb] 
GO 
EXEC msdb.dbo.sp_add_operator @name=N'operateur_test',  
               @enabled=1,  
               @pager_days=0...

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

Voici les principales caractéristiques d’un travail :

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...

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é est 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...

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 1^er de chaque mois à 23h et dont l’objet sera de supprimer les commandes antérieures au 1^er 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 1^er de chaque mois à 23h. L’écran ci-dessous illustre cette planification :