Very nice article on initial Database Mail configuration.  I wanted to point out that there are a few more steps required to have SQL Server Agent be able to use Database Mail for jobs, alerts, etc.  This is from SQL Server 2005 BOL, "Using Database Mail with SQL Server Agent":

1. Enable Database Mail.

2. Create a Database Mail account for the SQL Server Agent service account to use.

3. Create a Database Mail profile for the SQL Server Agent service account to use and add the user to the DatabaseMailUserRole in the msdb database.
4. Set the profile as the default profile for the msdb database.
5. Choose Database Mail as the mail system for SQL Server Agent. For more information, see How to: Configure SQL Server Agent Mail to Use Database Mail (SQL Server Management Studio).
6. Restart SQL Server Agent.

(I struck through the steps that were covered in the article, assuming that the account and profile created are Ok to use for SQL Server Agent Mail.)

It's also important to note that SQLMail issues typically fell into a couple of major categories:

• Reliability problems
• Configuration & maintenance overhead

With SQLMail you could spend a lot of time installing Outlook, creating operators, assigning notifications to jobs, alerts, etc., on each and every SQL Server (Config overhead)... and even then it may or may not work (Reliability)  

By taking MAPI/SQLMail out of the mix and going to SMTP with failover Microsoft has addressed biggest element of the "reliability" issue.  However, there is still the fact that Database Mail is a distributed system -- if there is some problem with it on one or more SQL Servers, then you may not receive your notifications for those servers.  In other words, although each individual Database Mail instance is inherently more reliable than SQLMail when properly configured and maintained, there is still no outside-looking-in process making objective decisions on the health of Database Mail on all servers, which goes towards "enterprise reliability".

In terms of maintenance overhead, I am sure many of those familiar with setup of SQLMail will look at the Database Mail setup & config process and think, "Well, it sure is different... and easier in some respects... but it's still quite a bit of work".

Database Mail still needs to be configured and tested on each SQL Server, operators need to be created, and the DBA must remember to assign notifications to all jobs & alerts, including new jobs & alerts added after initial setup.  When you start multiplying this by 50, 100, 250 or more SQL Servers as is commonplace now for many organizations, a migration from 2000/SQLMail to 2005/Database Mail starts to get a bit scary.

Microsoft has certainly made a big move in the right direction with Database Mail, but there is still a ways to go before it can truly be considered an "enterprise friendly" notification solution.

Greg Gonzalez

sqlSentry