SQL Job Notification

 

Lors d’un Scheduling de Job SQL la mise en place de notifications devrait être systématique, au moins en cas d’erreur. En réalité, chez certains clients, ce sont les utilisateurs finaux qui remontent à l’équipe de Production qu’un Job n’a pas tourné dans la nuit 😮

« Ces mots reviennent sans cesse, mais le problème demeure » IAM & Cut Killer

Il existe pourtant une multitude de solutions afin d’être notifié en cas d’erreur d’exécution d’un Job. Le choix de la configuration doit être réalisé en fonction des environnements et des différentes contraintes que les équipes de Sécurités et de Productions peuvent fournir.

Dans cet article je vais vous citer, UNE parmi plusieurs configurations possibles. Cette configuration permet d’envoyer des emails en cas de succès / échec de taches en y incorporant (ou non) une pièce jointe comportant les détails de l’erreur.

 

Mail Configuration

Dans un premier temps nous devons configurer l’envoi d’email à partir du moteur SQL Server.

L’architecture

Activation du service

sp_configure
GO
sp_configure 'show advanced options', 1;	-- Turn on advance options
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;		-- 1 indicate that Database Mail is available
GO
RECONFIGURE

 

Démarrage du Service Broker

Le Service Broker de la base de données MSDB doit être activé, il fournit la prise en charge de files d’attente dans le moteur SQL server.

USE MASTER
GO
ALTER DATABASE msdb SET ENABLE_BROKER

Vérification : SELECT is_broker_enabled FROM sys.databases WHERE name=’msdb’;

 

Démarrage du service

EXECUTE msdb.dbo.sysmail_stop_sp	-- Stop the service
EXECUTE msdb.dbo.sysmail_add_account_sp	-- Start the service
EXECUTE msdb.dbo.sysmail_help_status_sp	-- Veryfy the status of Database Mail

 

Création d’un compte

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'MyAccount name',
    @description = 'MyAccount description',
    @email_address = 'MyAccount email adress',	-- Email address to send the message from
    @display_name = 'MyAccoun dispplay name',
    @mailserver_name = 'Serveur SMTP',
    @use_default_credentials=1;	-- 1 indicate that we are using the DB Engine credentials

 

Création d’un profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'MyProfile name',
    @description = 'MyProfile description';

Vérification : EXECUTE msdb.dbo.sysmail_help_profile_sp;

 

Association du compte et du profil

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'MyProfile name',
    @account_name = 'MyAccount name',
    @sequence_number = 1; 	-- Determines the order in which accounts are used

Vérification : EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

 

Autoriser l’utilisation du profil

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @principal_name = 'Public',	-- Database User or Role or 'public'
    @profile_name = 'MyProfile name',
    @is_default = 1;	-- 1 indicate that the profile is the default for the principal

Vérification : EXECUTE msdb.dbo.sysmail_help_principalprofile_sp;

Remarque : il faut être membre du rôle « DatabaseMailUserRole » pour exécuter la procédure sp_send_dbmail.

 

Test

EXECUTE msdb.dbo.sp_send_dbmail
    @profile_name = 'MyProfile name',
    @recipients = 'r.casteres@gmail.com',
    @body = 'Hello World!',
    @subject = 'Test';

Vérification : EXECUTE msdb.dbo.sysmail_help_queue_sp;

 

Création d’un opérateur (destinataire)

EXECUTE msdb.dbo.sp_add_operator 
    @name=N'Operator Name', 
    @enabled=1, 
    @weekday_pager_start_time=80000, 
    @weekday_pager_end_time=180000, 
    @saturday_pager_start_time=80000, 
    @saturday_pager_end_time=180000, 
    @sunday_pager_start_time=80000, 
    @sunday_pager_end_time=180000, 
    @pager_days=127, 
    @email_address=N'r.casteres@hotmail.com; r.casteres@gmail.com', 
    @category_name=N'[Uncategorized]';

Remarque : un opérateur peut être vu comme un groupe de destinataire, il suffit de les séparer par un point virgule.

 

Procédure T-SQL

J’ai modifié la procédure MailAfterJob créée par Tibor Karaszi pour avoir des informations dans le corps du mail en plus de celles présentent en pièce jointe.

Vous pouvez la télécharger à cette adresse : SQL Job Notification.sql

Voici comment l’exécuter :

EXECUTE ###.dbo.MailAfterJob 
    @job_id = $(ESCAPE_SQUOTE(JOBID)),
    @strtdt = '$(ESCAPE_SQUOTE(STRTDT))',
    @strttm = '$(ESCAPE_SQUOTE(STRTTM))',
    @operator_name = 'Operator Name',
    @mail_on_success = 'Y',
    @attach_output_file = 'ALWAYS' --'ALWAYS', 'NEVER', 'ON_FAILURE';

 

Configuration du Job SQL

Pennons comme exemple un Job contenant une tache de script T-SQL. Si son exécution se déroule sans erreur, le Job sera en statut Success. En cas d’erreur, l’étape 2 exécutera la procédure MailAfterJob et transmettra par mail les infos à l’opérateur.

Pour envoyer en pièce jointe les informations sur l’exécution de la tâche l’Output file doit être configuré :

Remarque : L’utilisateur exécutant la tâche doit avoir les droits d’écriture sur le dossier de l’Output file 😉

 

Résultat