Introduction

We have all seen already that there are a ton of new features in SQL 2005. More realistically put, however, there are a ton of differences & things to change when migrating from 2000 to 2005, which a lot of the time invokes groans and moans, because naturally this means more work (and who cares about working?). But Database Mail, my friend, is a different story. No more Outlook installations....no more MAPI profiles...no more 3rd party smtp connector extended stored procedures...no more crossing your fingers and clicking your heels three times in order to get an email sent from your database system. Database Mail has come to the rescue.

Overview

The main difference between SQL Mail in SQL 2000 and Database Mail in 2005 is this: SQL Mail is a headache and Database Mail is not. After experimenting briefly with Database Mail, I see no reason why one would choose the legacy SQL Mail over the new Database Mail, unless of course for backward compatibility, legacy applications, etc.

Not only does Database Mail handle the somewhat simple task of sending emails rather well. It has some other robust features that should not go unnoticed, such as...

With all these considerations (plus a good number that I’m not including for purposes of brevity), provided you have (or are) a good developer, you can make some use of 2005’s new Database Mail functionality.

Initial Setup

After installing SQL 2005, like a lot of features, Database Mail is not automatically enabled. To enable Database Mail, you must use the Surface Area Configuration Tool. Open the SAC Tool and choose the "Surface Area Configuration for Features". Choose "Database Mail" and click the checkbox.

An alternate way of enabling Database Mail is by using SSMS’s (SQL Server Management Studio) object browser. Connect to the server you are interested in, browse to Management and then Database Mail. Right click Database Mail and choose "Configure Database Mail". According to Books Online, if Database Mail has not been enabled, you will receive the message: “The Database Mail feature is not available. Would you like to enable this feature?” If you respond with “Yes”, this is equivalent to enabling Database Mail using the SQL Server Surface Area Configuration tool.

Database Mail Accounts and Profiles

Profiles
Database Mail profiles are simply an "ordered collection of related Database Mail accounts" (Microsoft). 2005 allow you to compile a collection of outgoing (SMTP) servers for your messages, to provide some fault tolerance, as well as load balancing. SQL Server attempts to send your message through the last successful SMTP server that sent a Database Mail message, or the server with the lowest sequence number if a message has never gone out. If that server fails to transfer the message, then it goes onto the next one. Profiles can be public or private. Private profiles are only available to specified users. Public profiles are available to all users in the mail host (msdb) database. You can find out more information about public and private profiles here.

Accounts
Simply put, Database mail accounts contain information relating to email (SMTP) servers. This will remind you of the last time you set up Eudora, Thunderbird, Outlook, or any other simple email client..

Without further procrastination, let’s get into setting up the Database Mail....Of course Microsoft takes care of all this by providing you with a wizard.

Configuring Database Mail

Using SSMS, browse to Database Mail and right click. Choose "Configure Database Mail". You'll get an initial screen with some different options on setting up or managing Database Mail.

Leave the default for now, and choose next. Now fill in your profile name & description and click "Add". You’ll get another window to fill in your SMTP server (or Database Mail account) information. Add multiple Mail accounts and attach them to this profile if you need to. Your email or server administrator should have an SMTP server or gateway setup, in order for you to complete this form.

After clicking Next, you’ll be able to set your security on this profile….whether or not you’d like it to be public or private, if you’d like it to be the default, etc. In order for users to show up on the Private tab, they must exist in the DatabaseMailUserRole on MSDB. See Books online for more details.

Finally, you’ll be able to set some default system parameters concerning attachment size limits, file extension trapping and other system level details. From here you can go on to modifying your jobs to use Database Mail to alert you via email, or using sp_send_dbmail in your stored procedures or applications.

Summary

I always thought SQL Mail was a pain, and from the sounds of newsgroups and other SQL communication areas out there, others thought the same. The good news is that Database Mail is here to help, and here to stay with SQL 2005.