Configure Database Mail and SQL Agent Alerts

Configuring Database Mail and SQL Agent’s alerts via SQL Server Management Studio after every SQL Server installation is tedious.  Here’s a script I put together that will do it all without the clicking.  It will:

  1. Enable Database Mail
  2. Create a Profile
  3. Create an Account
  4. Assign the Account to the Profile
  5. Make the Profile the default
  6. Set a Fail-Safe Operator for SQL Agent
  7. Configure SQL Agent to use Database Mail for Alerts
  8. Configure SQL Agent to use the default Profile for Alerts

FYI, the script has been successfully tested against SQL Server Denali CTP3.

-- Use this script to configure the default mail profile, account and SQL Agent settings for Database Mail
USE [msdb]
GO
 
EXECUTE sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXECUTE sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO
 
DECLARE @ProfileID int
  , @AccountID int
  , @ReturnCode int = 0
 
-- Update all variables below this line to confgure Database Mail as needed
  , @ServerName sysname = N'MySQLServer'
 
DECLARE @AccountDescription nvarchar(256) = N'The default Database Mail account'
  , @AccountName sysname = N'Public Mail Account'
  , @DisplayName nvarchar(128) = @ServerName + ' SQL Server'
  , @EmailAddress nvarchar(128) = @ServerName + N'@MyDomain.com'
  , @FailSafeOperator nvarchar(255) = N'DBA'
  , @MailServer sysname = N'SMTP'
  , @ReplyToAddress nvarchar(128) = N'DBA@MyDomain.com'
  , @ProfileDescription nvarchar(256) = N'The default Database Mail profile'
  , @ProfileName sysname = N'Public Mail Profile'
 
BEGIN TRY
    BEGIN TRANSACTION
	-- Create the default mail profile
    EXECUTE @ReturnCode = dbo.sysmail_add_profile_sp @profile_name = @ProfileName, @description = @ProfileDescription, @profile_id = @ProfileID OUTPUT
 
	-- Create the default mail account
    EXECUTE @ReturnCode = dbo.sysmail_add_account_sp @account_name = @AccountName, @email_address = @EmailAddress, @display_name = @DisplayName,
        @replyto_address = @ReplyToAddress, @description = @AccountDescription, @mailserver_name = @MailServer, @use_default_credentials = 1,
        @account_id = @AccountID OUTPUT
 
	-- Add the default account to the default profile
    EXECUTE @ReturnCode = dbo.sysmail_add_profileaccount_sp @profile_id = @ProfileID, @account_id = @AccountID, @sequence_number = 1
 
	-- Set the default Database Mail profile to be our shiny new profile
    EXECUTE @ReturnCode = dbo.sysmail_add_principalprofile_sp @principal_id = 0, @profile_id = @ProfileID, @is_default = 1
 
    COMMIT TRANSACTION
 
	-- Configure SQL Server Agent to send emails via Database Mail using the Default Profile
    EXECUTE [master].dbo.sp_MSsetalertinfo @failsafeoperator = @FailSafeOperator, @notificationmethod = 1
    EXECUTE msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder = 1
 
    EXECUTE [master].dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
    EXECUTE [master].dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', @ProfileName
 
END TRY
 
BEGIN CATCH
    IF (@@TRANCOUNT > 0)
        ROLLBACK TRANSACTION
 
    DECLARE @ErrorMessage nvarchar(2048) = ERROR_MESSAGE()
      , @ErrorSeverity int = ERROR_SEVERITY()
 
    RAISERROR(@ErrorMessage, @ErrorSeverity, 1)
END CATCH

Leave a Reply

Your email address will not be published. Required fields are marked *