Server Configuration

Configure Database Mail and Test it – All using T-SQL

When configuring a new SQL Server instance, you probably want to configure Database Mail. As with all your post install configuration changes, it’s better to do it using a script to save yourself a whole heap of time while ensuring all your SQL Server instances are standardised and consistent. Unfortunately it’s not as easy to find out the commands to do this, since the Database Mail configuration window in SSMS doesn’t have the “Script As” button. I’ll show the commands which you could use to configure Database Mail and then to test it.

Here is a script template that I use a lot:

USE [master];
GO

SET NOCOUNT ON;

DECLARE @MailDetails sysname,
		@SMTPServerName VARCHAR(255),
		@EmailAddress VARCHAR(255),
		@InstanceName NVARCHAR(128),
		@IsDefaultProfile BIT;
		
-- DB Mail account/profile setup parameters	
SELECT  @InstanceName = REPLACE(@@SERVERNAME, N'\', N'$'), -- If it's a named instance, swap the "\" with a "$". It doesn't like "\" '
		@MailDetails = @InstanceName + N' Email',
        @SMTPServerName = N'<enter the smtp server name or IP here>',
		@EmailAddress = @InstanceName + N'_Email@testemails.com',
		@IsDefaultProfile = 1;
	
-- If database mail xps is not yet enabled, then enable it
IF  EXISTS ( SELECT * FROM sys.configurations WHERE name = 'Database Mail XPs' AND value = 0)
BEGIN	
	EXECUTE sp_configure 'show advanced options', N'1';
	RECONFIGURE WITH OVERRIDE;
	EXECUTE sp_configure 'database mail xps', N'1';
	RECONFIGURE WITH OVERRIDE;
END	

--------------------------- Database mail setup (Management > Database Mail) -----------------------------------

-- If DB Mail account already exists, then stop the script
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @MailDetails )
BEGIN
  PRINT 'The Database Mail account specified already exists and will not be recreated';
  SET NOEXEC ON;
END;

-- Creates the DB Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
	@account_name = @MailDetails,
	@description = @MailDetails,
	@email_address = @EmailAddress,
	@display_name = @MailDetails,
	@mailserver_name = @SMTPServerName;

-- If the DB Mail profile already exists, then stop the script
IF  EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @MailDetails)
BEGIN
  PRINT 'The Database Mail profile specified already exists and will not be recreated';
  SET NOEXEC ON;
END;

-- Creates the DB Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp 
	@profile_name = @MailDetails,
	@description = @MailDetails;

-- Adds account to the profile	
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
	@profile_name = @MailDetails,
	@account_name = @MailDetails,
	@sequence_number = 1;

-- Set the profile as public and default is specified in parameters
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = @MailDetails,
    @principal_name = 'public',
    @is_default = @IsDefaultProfile ;

---------------------------------------------------------------------------------------------------------------		
	
-------- SQL Server Agent Alert Settings (SQL Server Agent properties > Alert System > Mail Session) ----------
	
-- Enables SQL Agent to use DB Mail for it's mail
EXECUTE master.dbo.xp_instance_regwrite 
	@rootkey = N'HKEY_LOCAL_MACHINE', 
	@key = N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
	@value_name = N'UseDatabaseMail', 
	@type = N'REG_DWORD', 
	@value = 1;

-- 'Set the DB mail profile for SQL Agent to use.
EXECUTE master.dbo.xp_instance_regwrite 
	@rootkey = N'HKEY_LOCAL_MACHINE',
    @key = N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
	@value_name = N'DatabaseMailProfile',
    @type = N'REG_SZ', 
	@value = @MailDetails;

---------------------------------------------------------------------------------------------------------------	

PRINT N'DB Mail profile and account has been created and configured.';


SET NOEXEC OFF;

The script does a few things:

First, it defines the values of the DB Mail profile and account that you are going to create. It gets the SQL Server instance name because I like to call my default mail profile the name of the SQL Server instance name, but you can call it whatever you want. It then requires you to enter the smtp server name and the email address which will be the sending the emails from. If you don’t know the smtp server name or IP, then ask your Exchange or infrastructure admins and I’m sure they’ll happily oblige. The sender email address can be anything, however if your company has a policy on what this email address should be, then you should follow that.

Next, if the instance level configuration setting Database Mail XPs is not enabled, it enables it. We wont get very far if this isn’t done. It then creates the DB Mail account, then the profile, then links the account to the profile. This will also set it to be the default DB Mail profile and set it to be public so anybody on the server can use it (rather than it being private). If you don’t want it to be the default profile, then you can set that parameter to 0.

Finally, we want to enable SQL Server Agent to use Database Mail. Since we’ve just created the default DB Mail profile, we want to set SQL Server Agent to use this DB Mail profile. This is an important step if you want emails to be sent on completion of SQL Agent jobs or to operators as configured in SQL Agent alerts.

Can’t Trust it Until It’s Tested

However, as per usual, I don’t trust this until I’ve tested it. So I’ve also put the testing into a script. I need the test to do two things, 1) output a result set to the SSMS query window or PowerShell window showing me the values of the DB Mail account and profile so I can check it quickly all in one place 2) send a test email to myself.

use [master];
GO

SET NOCOUNT ON;

-- For Powershell so it displays nicely
PRINT CHAR(13);

DECLARE @MailDetails sysname,
		@InstanceName NVARCHAR(128),
		@TestEmailRecipients NVARCHAR(200),
		@TestEmailBody NVARCHAR(100);
		
-- DB Mail account/profile setup parameters	
SELECT  @InstanceName = REPLACE(@@SERVERNAME, N'\', N'$'), -- If it's a named instance, swap the "\" with a "$". It doesn't like "\" '
		@MailDetails = @InstanceName + N' Email', 
-- Test email parameters
		@TestEmailRecipients = N'myemail@email.com',--@UserName + @TestEmailRecipientDomain,
		@TestEmailBody = N'This is a test e-mail sent from Database Mail on ' + @InstanceName + N'.';

-------------------------------- Checking DB Mail account and profile -----------------------------------------

SELECT 
	CONVERT(NVARCHAR(30), a.name) AS AccountName, 
	CONVERT(NVARCHAR(30), a.[description]) AS AccountDescription, 
	CONVERT(NVARCHAR(50), a.email_address) AS EmailAddress, 
	CONVERT(NVARCHAR(30), a.display_name) AS DisplayName,
	CONVERT(NVARCHAR(15), s.servername) AS SMTPServerName
FROM msdb.dbo.sysmail_account AS a
	INNER JOIN msdb.dbo.sysmail_server AS s ON a.account_id = s.account_id
WHERE a.[name] = @MailDetails;

SELECT 
	CONVERT(NVARCHAR(30), p.name) AS ProfileName, 
	CONVERT(NVARCHAR(30), p.[description]) AS ProfileDescription, 
	pp.is_default AS ProfileIsDefault
FROM msdb.dbo.sysmail_profile AS p
	INNER JOIN  msdb.dbo.sysmail_principalprofile AS pp ON p.profile_id = pp.profile_id
WHERE p.[name] = @MailDetails;	

---------------------------------------------------------------------------------------------------------------

------ Send test email to the current user (Management > Database Mail (Right Click) > Send Test Email) -------

EXECUTE msdb.dbo.sp_send_dbmail
		@profile_name = @MailDetails,
		@recipients = @TestEmailRecipients,
		@subject = N'Database Mail Test',
		@body = @TestEmailBody;

PRINT N'Test email has been sent to ' + @TestEmailRecipients;
		
---------------------------------------------------------------------------------------------------------------	

I purposely convert all the values in the select query to small values so that they display nicely in a Powershell window, otherwise the query result is really wide and displays horribly.

This is what the output looks like:

And I get this lovely looking email turn up in my inbox:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s