Systems Engineering and RDBMS

Database Mail vs SQL Mail

Posted by decipherinfosys on July 9, 2008

“What is the difference between Database Mail and SQL Mail?  What should I use with SQL Server 2005?”

This question came up a couple of times when working at client sites as well as in the e-mails from our readers so it’s time to put a blog post up on it. Sending an e-mail from a system for alerts or notifications is an important piece of many of the systems. SQL Mail used to be one of the available solutions prior to SQL Server 2005. There were lots of restrictions and limitations of SQL Mail because of which it never became a favorite among database developers/DBAs. MAPI profiles, third party SMTP connectors, extended stored procedures, Outlook installation are some of the implementation difficulties with SQL Mail not to mention performance issues with SQL Mail. The KB article 315886 addresses the common SQL Mail problems.

In SQL Server 2005, even though SQL Mail is still present, a new solution – Database Mail was included with the product. In addition, for notifications, in SQL Server 2005 one could use Notification Services as well for developing and deploying applications to generate and send notifications to the subscribers. We would cover notification services in another post. In this post, we will discuss Database Mail and how to go about enabling this feature and using it. A couple of benefits of Database Mail over SQL Mail are:

1) Database Mail has the option of limiting file sizes to prevent sending large attachments. One can also prevent certain attachments based on extensions, for example the batch files or executables can be prevented as attachments from being sent from the database server.

2) Database Mail can be configured with multiple SMTP accounts and with multiple profiles. This helps in increasing the reliability and robustness of the system since even if one mail server fails, the other one can pick up.

3) The mailing process is external (which means not much impact on the database performance) and is handled via DatabaseMail90.exe which is located under the Binn directory. And even if this external process fails, SQL Server can queue the messages and then send them out later.

4) Having access to the mail history was a much needed addition. All mail events are logged.

So, how can we go about enabling Database Mail? We can do that in a couple of ways:

a) Using the Surface Area Configuration Utility:

The image above is self explanatory – just enable the option.

b) Using SQL Server Management Studio:

This will prompt you to enable the option if it is disabled. And if it is enabled already, then you can configure Database Mail.

c) By using sp_configure:

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
/*
As you know, use 0 to Disable and 1 to enable when using sp_configure
*/
sp_configure ‘Database Mail XPs’, 1;
GO
RECONFIGURE
GO

Now, let’s go ahead and configure database mail. We will use SSMS and right click on database mail and select “Configure Database Mail” as we did in option # (b) above (there are T-SQL commands also which we will cover after we show the GUI options). You will see the following screen and option #1 is a combination of all the other options so let’s keep that and move on to the next screen.

On the next screen, we get to configure the profile and then put the information as shown in the image below:

You can add more than one SMTP account and then move them up and down to set the priority. Also, if you look at the above image, you will see that we have three modes of authentication: Windows, Basic Authentication and Anonymous Authentication. The last one is rarely used due to security issues. More than likely you will use the Basic Configuration option. Here you can configure your database authentication credentials to log into the SMTP server.

Next screen will show you the public/private profiles. Members of the msdb database role DatabaseMailUserRole can use the public profile. For private profile, Database Mail maintains a list of users that can use the profile. On the next screen, one can see the system parameters that one can configure:

All the options are self explanatory – you can restrict files with certain extensions as well as limit them by size etc. And that is it. Once you click next, you get the summary and then click Finish to get it all done:

As you are doing this, the best way to learn about the T-SQL commands that get fired up in the back end is to trace it using SQL Server Profiler. You will see that the commands that get fired off are: msdb.dbo.sysmail_add_account_sp, msdb.dbo.sysmail_add_profile_sp, msdb.dbo.sysmail_add_profileaccount_sp, msdb.dbo.sysmail_add_principalprofile_sp. You can look up BOL for the different parameters and their explanations.

One can then use the sp_send_dbmail stored procedure to send out database mails. This system stored procedure resides in the MSDB database. In order to be able to send out the mails, you have to be the member of the DatabaseMailUserRole group in the MSDB database and need to have access to atleast one Database Mail Profile.

We had mentioned logging before and in order to view the logs, all you need to do is right click Database Mail in SSMS and click on “View Database Mail Log” and you can view all the log events using the log file viewer. You get all the advantages of the log file viewer as we had discussed before in one of the posts i.e. searching, filtering etc. If using T-SQL, you can query the view sysmail_event_log in the MSDB database.

We hope that you got enough information on Database Mail in this post to be able to get started on playing with it and hopefully using it in your environments.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d bloggers like this: