With SQL Server 2005 comes a new mail sub-system, called Database Mail. Database Mail is a great improvement over SQL Mail that came with earlier versions of SQL Server. Now you are able to set up multiple accounts and profiles to support your different application email needs. This article will discuss setting up and managing Database Mail.
What is Database Mail?
Database Mail is a mail queuing system. The email messages are stored in a queue within the msdb database waiting to be processed. When an email message is placed in the queue, an external process is triggered to send the email messages in the queue to the appropriate mail server. Once the email has been sent an email message with the status of the delivery is then posted back to SQL Server.
Enabling Database Mail
Database Mail is not available out of the box. Since SQL Server security model has everything turned off by default, you will need to enable Database Mail if you want to use it. You can use either the Surface Area Configuration tool, or the following T-SQL to enable Database Mail:
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Database Mail XPs’, 1;
GO
RECONFIGURE
GO
Setting up an Account
A Database Mail account identifies how SQL Server 2005 should communicate with an SMTP server. The account specifies how email should be formatted and sent. A single account will identify a single SMTP server, and authentication method. An account used for Database Mail does not correspond to a SQL Server login account.
When setting up an account you need to identify enough information so SQL Server 2005 can communicate with the SMTP server and authenticate if necessary. Refer to Books Online for a complete list of options for defining an account. You can set up an account by using the Database Mail Configuration Wizard, which can be found under “Database Mail” in the “Management” Folder within SQL Server Management Studio, or by using the stored procedure (SP) “sysmail_add_account_sp”. Here is a script that uses the SP mentioned above for creating an account that communicates with an SMTP server that does not require authentication:
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘Database Administration Account’,
@description = ‘Mail account for used by DBA staff’,
@email_address = ‘ProdServer01@databasejournal.com’,
@display_name = ‘ProdServer01 DBA Mail’,
@replyto_address = ‘Greg.Larsen@databasejournal.com’,
@mailserver_name = ‘mailserver.databasejournal.com’;
This account has a named “Database Administration Account”, and has an email address of ProdServer01@databasejournal.com. One of the advantages of using Database Mail is this email address no longer needs to be a valid email account in your mail system. In addition, you can associate a “reply to” address with your database mail account. In my example above, I defined “Greg.Larsen@databasejournal.com” as the @replyto_address. So now, when someone gets an email from any of my automated email notification processes they can reply to it and the email can be routed directly to me.
With Database Mail, you can now set up as many email accounts as you might need. Later on, I will discuss some reasons why you might want to set up multiple Database Mail accounts.
Setting up a Profile and Associating it with Accounts
Before you can send Database Mail to an SMTP server identified in an account, you need to associate the account with a profile, and grant access to the profile. Database Mail profiles are used to improve mail security. There are two kinds of profiles, public and private. A public profile is available to anyone that has been given access to the msdb database and is a member of the DatabaseMailUserRole in the msdb database, whereas a private profile can only be used by specific users that have been granted access to the private profile. A mail profile can be associated with one or more accounts. You can manage profiles using the Database Mail Configuration Wizard, or use some T-SQL command similar to the following to set up a mail profile:
— Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘Database Administration Profile’,
@description = ‘Mail Profile for use by DBA processes’;
Here I just create a profile named “Database Administration Profile”. Now just because you have a profile created doesn’t mean you can now use this profile to send email. You still need to associate that profile with a least one Database Mail account and one user within the msdb database. In order to do this I can run the following two statements:
— Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘Database Administration Profile’,
@account_name = ‘Database Administration Account’,
@sequence_number =1 ;
— Grant access to the profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘Database Administration Profile’,
@principal_name = ‘ProdServer01’,
The first EXECUTE statement associates my profile with an account. The second EXECUTE statement associates my profile with the ‘ProdServer01’ msdb database user. This users needs to be granted membership to the DatabaseMailUserRole before they can send mail. Since I associated this profile with a specific user, this profile is known as a private profile. To create a public profile you would associated a profile with the “public” database role.
Sending Database Mail
SQL Server provides the SP “sp_send_dbmail” to send mail. The following syntax is used to call this SP:
sp_send_dbmail [ [ @profile_name = ] ‘profile_name‘ ]
[ , [ @recipients = ] ‘recipients [ ; …n ]‘ ]
[ , [ @copy_recipients = ] ‘copy_recipient [ ; …n ]‘ ]
[ , [ @blind_copy_recipients = ] ‘blind_copy_recipient [ ; …n ]‘ ]
[ , [ @subject = ] ‘subject‘ ]
[ , [ @body = ] ‘body‘ ]
[ , [ @body_format = ] ‘body_format‘ ]
[ , [ @importance = ] ‘importance‘ ]
[ , [ @sensitivity = ] ‘sensitivity‘ ]
[ , [ @file_attachments = ] ‘attachment [ ; …n ]‘ ]
[ , [ @query = ] ‘query‘ ]
[ , [ @execute_query_database = ] ‘execute_query_database‘ ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] ‘query_result_separator‘ ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]
As you can see this SP supports a number of different parameters. For information on each of these parameters, please refer to Books Online.
Below is an example of how I would use my private profile created above to send me a simple email message regarding an automated database re-indexing process.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Database Administration Profile’,
@recipients = ‘greg.larsen@databasejournal.com’,
@body = ‘Reindex database process completed successfully’,
@subject = ‘Reindex Database’ ;
Since Database Mail works off a queue when I run the above command I will get a message returned saying “Mail queued.”. When you execute the sp_send_dbmail SP the mail isn’t sent immediately, but instead it is stored in the mail queue within the msdb database. The execution of this SP triggers an external mail process (DatabaseMail90.exe) to run. This executable reads the mail in the queue and sends it to the appropriate mail server.
Different uses for Accounts and Profiles
There are a number of different ways to take advantage of the multiple accounts and profiles that Database Mail allows you to set up.
One of the obvious advantages of having multiple accounts is to configure your Database Mail profile to allow for failover should one of your SMTP servers become unavailable. When adding accounts to a Database Mail profile you can give them a sequence_number. When sending a new email message Database Mail tries to send it using the lowest sequence number first. Should sending email using that account fail, then Database Mail uses the next highest sequence number. Database Mail continues doing this until the mail is either sent successfully or all accounts have been tried.
Another valuable option of having multiple accounts and profiles is to support having mail messages delivered with different email addresses. If you have multiple applications that need to send email, now each application can have its own email address. Doing this helps the recipients of an automated email to differentiate what process sent the email by just looking at the email address.
Lastly, if you use private profiles then you can associate these profiles with different security principles. Doing this allows you to control which msdb users will be allowed to use a particular Database Mail profile.
Monitoring Database Mail
SQL Server provides 6 different system views in the msdb database for monitoring and returning Database Mail information. These views can be used to retrieve information in the msdb database regarding the status of all Database Mail or just a particular email message. These views can be useful to identify what mail has been processed, as well as why some mail messages might not have been delivered to the requested mail server. These views are great tools to monitor and troubleshoot Database Mail issues. For more information about these views, please refer to Books Online.
sysmail_allitems – This view allows you to return a record set that contains one row for each email message processed by Database mail.
sysmail_event_log – This view returns a row for each Windows or SQL Server error message returned when Database Mail tries to process an email message.
sysmail_faileditems – This view returns one record for each email message that has a status of failed.
sysmail_mailattachments – This view contains one row for each attachment sent
sysmail_sentitems – This view contains one record for every successfully email sent
sysmail_unsentitems – This view contains one record for every email that is currently in the queue to be sent, or is in the process of being sent.
Maintain Messages in the MSDB Database
Since all email messages are stored in the msdb database, you need to consider how you want to manage this information. Depending on your email retention policies, you should set up routines to periodically clean out email messages that are no longer needed. SQL Server 2005 provides two different stored procedures to remove mail records from the msdb database.
sysmail_delete_mailitems_sp – This SP permanently deletes email messages from the msdb internal Database Mail tables
sysmail_delete_log_sp – This SP deletes Database Mail log messages
Review Books Online for more information on these SP’s.
The following T-SQL will delete all mail that was sent over a month ago:
DECLARE @delete_date datetime
SET @delete_date = dateadd(MM,-1,getdate())
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before=@delete_date
Conclusion
Database Mail in SQL Server 2005 is a refreshing improvement over SQL Mail provided in prior versions of SQL Server. Database Mail provides a mail sub system with lots of features to better secure and manage emails being sent using T-SQL. With the availability of Database Mail within SQL Server 2005, you no longer need to build a kludge of a solution using CDOSYS to send email using functionality that old versions of SQL Mail could not support. If you are looking for ways to support email from T-SQL, then make sure you look at Database Mail.
