Datafly.pro

Activer et configurer Database Mail : modifiez les variables en début de script selon votre environnement et exécutez le script :
 

DECLARE @server_email_address NVARCHAR(128) = N'myserver.mydomain.com' -- notifications : sender email address
      , @mailserver_name NVARCHAR(128) = N'192.168.0.1'                -- smtp server name or ip address
      , @smtpport INT = 25                                             -- smtp server port
      , @smtpenable_ssl BIT = 0                                        -- smtp server : enable ssl (0|1)
      , @smtpusername NVARCHAR(128) = NULL                             -- smtp server authentication username
      , @smtppassword NVARCHAR(128) = NULL                             -- smtp server authentication password
      , @display_name NVARCHAR(128) = N'SQL Alerting - ' + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128));

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE WITH OVERRIDE;
IF NOT EXISTS
(
    SELECT *
    FROM msdb.dbo.sysmail_profile
    WHERE name = N'AdminDBMail'
)
    EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'AdminDBMail'
                                          , @description = 'Notification mail profile';
ELSE
    PRINT 'Mail profile already existing, not modified';

IF NOT EXISTS
(
    SELECT *
    FROM msdb.dbo.sysmail_account
    WHERE name = N'SQLAlert'
)
    EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'SQLAlert'
                                          , @description = 'SMTP account for SQL Agent Notifications'
                                          , @email_address = @server_email_address
                                          , @display_name = @display_name
                                          , @mailserver_name = @mailserver_name
                                          , @port = @smtpport
                                          , @enable_ssl = @smtpenable_ssl
                                          , @username = @smtpusername
                                          , @password = @smtppassword;
ELSE
    PRINT 'Mail account already existing, not modified';

IF NOT EXISTS
(
    SELECT *
    FROM msdb.dbo.sysmail_profileaccount PA
        JOIN msdb.dbo.sysmail_account A
            ON PA.account_id = A.account_id
        JOIN msdb.dbo.sysmail_profile P
            ON PA.profile_id = P.profile_id
    WHERE A.name = N'SQLAlert'
          AND P.name = 'AdminDBMail'
)
BEGIN
    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'AdminDBMail'
                                                 , @account_name = 'SQLAlert'
                                                 , @sequence_number = 1;

    EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder = 1
                                           , @databasemail_profile = N'AdminDBMail'
                                           , @use_databasemail = 1;
END;
ELSE
    PRINT 'Profile_account already existing, not modified';

Configurons maintenant l'opérateur, activons les notifications sur échec des jobs de maintenance et configurons les alertes indispensables : même principe, modifions les variables de début de script avant de l'exécuter.

DECLARE @operator_name NVARCHAR(128) = N'SQLAdmin'                -- operator name
      , @operator_email NVARCHAR(128) = N'myemail@mydomain.com';    -- operator email

IF NOT EXISTS
(
    SELECT *
    FROM msdb.dbo.sysoperators
    WHERE name = @operator_name
)
    EXEC msdb.dbo.sp_add_operator @name = @operator_name
                                , @enabled = 1
                                , @pager_days = 0
                                , @email_address = @operator_email;
ELSE
    PRINT 'Operator already existing, not modified';

-- Configuration des notifications
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseIntegrityCheck - SYSTEM_DATABASES',
        @notify_level_email=2,
        @notify_level_page=2,
        @notify_email_operator_name=@operator_name;
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseIntegrityCheck - USER_DATABASES',
        @notify_level_email=2,
        @notify_level_page=2,
        @notify_email_operator_name=@operator_name;
EXEC msdb.dbo.sp_update_job @job_name=N'IndexOptimize - USER_DATABASES',
        @notify_level_email=2,
        @notify_level_page=2,
        @notify_email_operator_name=@operator_name;
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseBackup - SYSTEM_DATABASES - FULL',
        @notify_level_email=2,
        @notify_level_page=2,
        @notify_email_operator_name=@operator_name
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseBackup - USER_DATABASES - FULL',
        @notify_level_email=2,
        @notify_level_page=2,
        @notify_email_operator_name=@operator_name
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseBackup - USER_DATABASES - DIFF',
        @notify_level_email=2,
        @notify_level_page=2,
        @notify_email_operator_name=@operator_name
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseBackup - USER_DATABASES - LOG',
        @notify_level_email=2,
        @notify_level_page=2,
        @notify_email_operator_name=@operator_name
-- Configuration des alertes
IF NOT EXISTS
(
    SELECT *
    FROM msdb.dbo.sysoperators
    WHERE name = @operator_name
)
    EXEC msdb.dbo.sp_add_operator @name = @operator_name
                                , @enabled = 1
                                , @pager_days = 0
                                , @email_address = @operator_email;
ELSE
    PRINT 'Operator already existing, not modified';

-- Configure alerts
DECLARE @sev INT = 19;
DECLARE @alert_name sysname;
WHILE @sev <= 25
BEGIN
    SET @alert_name = @@SERVERNAME + ' Alert - ' + 'Severity level ' + CAST(@sev AS VARCHAR);
    IF NOT EXISTS (SELECT * FROM msdb.dbo.sysalerts WHERE name = @alert_name)
    BEGIN
        EXEC msdb.dbo.sp_add_alert @name = @alert_name
                                 , @severity = @sev
                                 , @delay_between_responses = 600     --10 minutes
                                 , @include_event_description_in = 1; --Email
        EXEC msdb.dbo.sp_add_notification @alert_name = @alert_name
                                        , @operator_name = @operator_name
                                        , @notification_method = 1;
    END;
    ELSE
        PRINT @alert_name + ' : alert already existing, not modified';
    SET @sev = @sev + 1;
END;