E-Mail Functionality in SQL Server 2005

Sending an e-mail has become very important in any system for purposes such as sending notifications. SQL Server database has an integrated mailing system. With the arrival of SQL Server 2005, users now have the new functionality of Database Mail, which is different from SQL Server 2000 SQL Mail. The purpose of this article is to introduce Database Mail and highlight the advantages of using it over legacy SQL Mail.

Issues With SQL Mail

If you have experience in SQL Server 2000 SQL Mail, you will know the headaches of SQL Mail. Personally, I have not used SQL Mail much recently due to the implementation difficulties. Outlook installations, Messaging Application Programming Interface (MAPI) profiles, third party Simple Mail Transfer Protocol (SMTP) connector, and extended stored procedures are all needed for SQL Mail. More importantly, SQL Mail will degrade SQL Server performance.

Check out KB article 315886 for common SQL Mail problems. Due to these, users were forced to look for other means such as stored procedures with CDO to send mail from SQL Server.

Features of Database Mail

Before going into the detail about configuring Database Mail, it is worth highlighting the main features:

• Database Mail can be configured with multiple profiles and multiple SMTP accounts, which can be on several SMTP servers. In the case of failure of one SMTP server, the next available server will take up the task of sending e-mails. This increases the reliability of the mailing system.
• SQL Server continues to queue messages when the external mailing process fails. Whenever the process is successful, it starts to send queued messages.
• Mailing is an external process so it does not decrease your database performance. This external process is handled by an executable called DatabaseMail90.Exe located in the MSSQL\Binn directory.
• Availability of an auditing facility is a major enhancement in Database Mail. Previously, DBAs could not verify whether the system had sent an e-mail. All mail events are logged so that DBAs can easily view the mail history. In addition, DBAs can view the errors to fix SMTP related issues. Plus, there is the capability to send HTML messages.
• Database Mail has the option of limiting files sizes to prevent sending large files that would degrade mail server performance. In addition, you have the option of limiting files by their extensions. For example, .exe.com can be prevented from being sent from the database server.

Enabling Database Mail

In SQL Server 2005, Database Mail is disabled by default. So you have to enable it after installation. I believe it is not provided at installation because of security reasons. There are several ways of enabling it.

One way is from the SQL Server Surface Area Configuration (SSSAC), which is located under Configuration Tools of SQL Server 2005 installation. Run SSSAC and select Surface Area Configuration for Features, select Database Mail from the SQL Server instance you need and then select Enable Database Mail stored procedure option. This means that Database Mail is enabled for a particular SQL Server instance.

Another option is selecting from SQL Server Management Studio (SSMS).

By right-clicking Database Mail and selecting Configure Database Mail option, you will be prompted to enable this option if it was not enabled. Probably, this is the easiest of all the available options.

The final option is using a T-SQL script. The following script will enable the Database Mail option.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1; -- 0 to Disable and 1 to enable
GO
RECONFIGURE
GO

Configuring Database Mail

Now that we have examined the background of Database Mail, it is time to get your hands dirty with this new feature.

Right click the previously shown option in the screen shot above. If Database mail is not configured you will be asked to enable it with this wizard.

You will be taken to Database Mail Configuration window, which is shown below.

Even though there are four options in the windows, the first option is a combination of the other three options, so this article will focus on the first option.

You are required to create a Database Mail profile with a profile name and description. With a profile, as mentioned earlier, there can be multiple SMTP accounts.

The priority can be set by using the Move Up and Move Down buttons. According to the above example, the profile Dinesh will send mail via dinesh@dynanet.lk. Failing that, it will send mail via dinesh@dinesh.com.

The following dialog gives you the option of configuring SMTP accounts. Apart from the basic SMTP account configuration, three authentications are available. The most commonly used authentication is basic authentication. You can configure your database authentication credentials to log into SMTP server. This is good practice. If you change your windows authentication, you do not have to change the Database Mail configuration.

Anonymous authentication is also available but not recommended for security reasons. If you are using this option, you will need to ensure that the mail server has not disabled anonymous authentication.

Next, you have the option of configuring system parameters. Here, you can define the maximum file size and prohibited extensions. These configurations are also possible in the mail server. However, in SQL Server 2005 you have the option of configuring them profile-wise. Because of this, you have the option of selecting profiles depending on your requirements.

Mail profiles can be either public or private. For a private profile, Database Mail maintains a list of users that use the profile. For a public profile, members of the msdb database role DatabseMailUserRole can use the profile. Also, there can be a default public profile for the server and a default private profile for a user.

The above configuration can be also done via T-SQL scripts.

Use the sysmail_add_account procedure as follows to create a Database Mail account, using mail.dynanet.com as the mail server and dinesh@dynanet.com as the e-mail account:

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Dinesh',
@description = 'Dinesh Mail on dynanet.',
@email_address = 'dinesh@dynanet.com',
@display_name = 'Dinesh Asanka',
@mailserver_name = 'mail.dynanet.com'

Use the sysmail_add_profile procedure to create a Database Mail profile called Dinesh Mail Profile:

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Dinesh',
@description = 'Dinesh Profile'

User the sysmail_add_profileaccount procedure to add the Database Mail account and Database Mail profile you created in previous steps.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Dinesh',
@account_name = 'Dinesh',
@sequence_number = 1

Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile:

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Dinesh',
@principal_name = 'public',
@is_default = 1 ;

After configuring Database Mail, it should be tested using Database Mail's testing functionality. Select the Send Test E-Mail option. You can select the profile to test and send the mail and then verify.

Working With E-Mails From Database Mail

sp_send_dbmail is the built-in stored procedure in the msdb database that can be used to send e-mails. The following is a simple example of using sp_send_dbmail.

To send mail, you must be a member of the DatabaseMailUserRole in the msdb database, and have access to at least one Database Mail profile.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'dinesh',
@recipients = 'anybody@anywhere.com',
@body = 'Use new feature of Database mail. No MAPI client needed'
@subject = 'New Feature of Database Mail';

Another important feature in this stored procedure is the ability to send query results. Let us try a real world example. Very recently, a client asked me to send a daily e-mail of the error log count. You can use @query and @attach_query_results_as_file parameters to satisfy above requirement:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Error Logs',
@recipients = 'DBA@customer.com',
@query = 'SELECT COUNT(*) FROM crm.Admin.errorlogs
WHERE DATEDIFF(dd, "2006-09-02", getdate()) =0',
@subject = 'Error Log Count',
@attach_query_result_as_file = 1 ;

This feature is available in SQL Mail, too. However, in SQL Mail you cannot send it an attachment, which is possible in Database Mail. In addition, there are also a several new parameters in sp_send_dbmail.

@body_format specifies the format of the e-mail message: TEXT or HTML.

@importance specifies the importance of the e-mail message.

@sensitivity specifies the sensitivity of the e-mail message.

Let us look at where this information is stored in SQL Server 2005. System views in the msdb database contain the e-mail messages and attachments sent from Database Mail and the status of each message.

Database Mail updates these tables when each message is processed. Query the sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, and sysmail_faileditems views to check the delivery status of an individual e-mail message.

The sysmail_mailattachments view contains the e-mail attachments in Database Mail messages.

Converting SQL Mail to Database Mail

There are several changes required for changing over to Database Mail.

As specified before, sp_send_dbmail was used to send mail while in SQL mail xp_sendmail was used. So you will need to map both stored procedure parameters

xp_sendmail Argument	sp_send_dbmail Argument
@recipients	@recipients
@message	@body
@query	@query
@attachments	@file_attachments
@copy_recipients	@copy_recipients
@blind_copy_recipients	@blind_copy_recipients
@subject	@subject
@type	N/A
@attach_results	@attach_query_result_as_file
@no_output	@exclude_query_output
@no_header	@query_result_header
@width	@query_result_width
@separator	@query_result_separator
@echo_error	N/A
@set_user	N/A
@dbuse	@execute_query_database

(Source: SQL Server 2005 Documentation)

Auditing and Logging

Auditing is an important new feature in SQL Server 2005. Right-click the Database Mail option and select the View Database Mail Log option. With this log file viewer, you have the ability to filter and search. Filtering is possible on User, Computer, Start Date, End Date, Message Containing Text, and Source.

In addition, you can get the mail log by querying the sysmail_event_log system view in the msdb database.

Conclusion

Database Mail is a substantial improvement over SQL Mail and can be used to enhance the functionality of your database. Please let us know your experiences using Database Mail.