Set Up SQL Server Database Mail
Database Mail in Microsoft SQL Server is a powerful, but under-utilized ally for any Database Administrator. It's been around for years but often goes un-noticed to this day. We find ourselves relying upon Database Mail (let's just call it DBM through the remainder of the article to make it easy on me) to field such tasks as:
Notifying DBAs when SQL Server Agent jobs complete, fail, or succeed
Sending the results of queries via email to interested individuals
Sending alerts when certain conditions arise on my SQL Server instances DBM is located in the SQL Server Agent node of the Object Explorer window:
Double-clicking Database Mail will launch a process to step you through creating a DBM profile and an account to associate it with. It's important before we go any farther that we clear up some terminology though between accounts and profiles.
Database Mail Accounts are closely associated to an SMTP (Simple Mail Transfer Protocol) account. The DBM account is essentially the information stored about the mail server, the security process to connect to the mail server, and how email sender in this process will be identified to recipients. You need to supply the following criteria when setting up a DBM account:
Account Name
Account Description
Outgoing SMTP Mail Server Information:
o
Email Address
oDisplay Name
oReply Email Address
o
SMTP Server Name (IP or DNS name are acceptable)
oPort Number
o
SSL information
SMTP Authentication Method (Windows, Basic, or Anonymous)
Database Mail Profiles are no more than identifiers for SQL Server to use to associate a request (the what) to the logistical information for the delivery (the how). All that is required is a name to identify the profile, an optional description for the profile, and then the account name to link to the profile. Multiple accounts can be associated with a given profile and if the first account fails to send a transmission the next account will be used.
Now that you know the information you'll need to create DBM Profiles and Accounts we can move on to the script you'll run to do this all in one query.
Now we have to copy the query below into a new query window in SQL Server management Studio active on the instance we want to configure for DBM.
The Query:
--================================================================
-- DATABASE MAIL CONFIGURATION
--================================================================
--==========================================================
-- Create a Database Mail account
--==========================================================
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = '<account_name, DBM account name, Database Mail Primary Account>', @description = '<description, , SQL Server Notification Service>',
@email_address = '<email_address, email address for DBM. Does not need a valid mail account ,>',
@replyto_address = '<replyto_address, reply email address for DBM. Does not need a valid mail account ,>',
@display_name = '<display_name, friendly name for emails sent via DBM, Database Mail Account>',
@mailserver_name = '<mailserver_name, smtp mail server name,>', @port = <port_number, port number of the mailserver, 25>;
--==========================================================
-- Create a Database Mail Profile
--==========================================================
DECLARE @profile_id INT, @profile_description sysname;
SELECT @profile_id = COALESCE(MAX(profile_id),1) FROM msdb.dbo.sysmail_profile SELECT @profile_description = 'Database Mail Profile for ' + @@servername
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = '<profile_name, DBM profile name, Database Mail Primary Profile>', @description = @profile_description;
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = '<profile_name, DBM profile name, Database Mail Primary Profile>', @account_name = '<account_name, DBM account name, Database Mail Primary Account>', @sequence_number = @profile_id;
-- Grant access to the profile to the DBMailUsers role EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = '<profile_name, DBM profile name, Database Mail Primary Profile>', @principal_id = 0,
@is_default = 1 ;
--==========================================================
-- Enable Database Mail
--==========================================================
USE master;
GO
sp_CONFIGURE 'show advanced', 1 GO
RECONFIGURE GO
sp_CONFIGURE 'Database Mail XPs', 1 GO
RECONFIGURE GO
--EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'' --EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1 --GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder = 0 GO
--==========================================================
-- Review Outcomes
--==========================================================
SELECT * FROM msdb.dbo.sysmail_profile;
SELECT * FROM msdb.dbo.sysmail_account;
GO
--==========================================================
-- Test Database Mail
--==========================================================
DECLARE @sub VARCHAR(100)
DECLARE @body_text NVARCHAR(MAX)
SELECT @sub = 'Test from New SQL install on ' + @@servername
SELECT @body_text = N'This is a test of Database Mail.' + CHAR(13) + CHAR(13) + 'SQL Server Version Info: ' + CAST(@@version AS VARCHAR(500))
EXEC msdb.dbo.[sp_send_dbmail]
@profile_name = '<profile_name, DBM profile name, Database Mail Primary Profile>' , @recipients = '<test_email_address, email address to send test email,>'
, @subject = @sub , @body = @body_text
--================================================================
-- SQL Agent Properties Configuration
--================================================================
EXEC msdb.dbo.sp_set_sqlagent_properties
@databasemail_profile = '<profile_name, DBM profile name, Database Mail Primary Profile>'
, @use_databasemail=1 GO
The script is broken down into the following sections:
Creation of Database Mail Account
Creation of Database Mail Profile
Enable Database Mail for the SQL Server instance
Returning list of accounts and profiles for the SQL Server instance
Send test Database Mail
