Result Data Newsletter - www.resultdata.com

SQL Mail vs. Database Mail

by: Charles Tournear; Senior Consultant, MCT, MCSE, MCSD, MCDBA, CRCP

SQL Server 2005 supports the SQL Mail service that came with SQL 7.0 and SQL 2000, but it also comes with a new component Database Mail. Both allow you to have SQL Server send emails as the result of a SQL Server Agent job process or a SQL Server Notification Service process. Also you can use stored procedures to send emails or email the results of a query to users.

The SQL Mail component was difficult to setup because it required specific installation or configuration options in SQL Server to be setup correctly. SQL Mail required that a Domain user account be created. The SQL Server Service and the SQL Server Agent Service then had to be configured to start using the new user account. Also an email client application such as Outlook or the Lotus Domino client had to be installed on the SQL Server and an email profile had to be created for the user account that SQL Server service started with. SQL Mail only supported email clients that supported MAPI 1.0.

Database Mail does not require that a MAPI client application be installed on the SQL Server. Database Mail uses the standard Simple Mail Transfer Protocol (SMTP) to send mail. Database Mail is not active by default. To use Database Mail, you must explicitly enable Database Mail using the SQL Server Surface Area Configuration tool, or the Database Mail Configuration Wizard. The Database Mail component that delivers e-mail runs outside of SQL Server, in a separate process. SQL Server will continue to queue e-mail messages in the msdb database even if the external process stops or fails. The queued messages will be sent once the outside process or SMTP server comes online.

A Database Mail profile allows you to specify more than one SMTP server. Should an SMTP server be unavailable, mail can still be delivered to another SMTP server. Database Mail allows you to create multiple profiles within a SQL Server instance. When sending an email you have the option to choose the profile that Database Mail will use when you send the message. Each profile can contain multiple failover accounts. You can configure different profiles with different accounts to distribute e-mail across multiple e-mail servers.

To send a Database Mail message, you must be a member of the DatabaseMailUserRole database role in the msdb database. Database Mail keeps copies of messages and attachments sent in the msdb database. You can easily audit Database Mail usage and review the retained messages.

While the help files state that email can be sent in HTML format, there was an issue with the sp_send_dbmail stored procedure in the msdb database that made attachments and messages formatted as HTML to be sent in Unicode format which most HTML readers don’t support. This was fixed in Service Pack 2.

Database Mail Configuration Wizard provides a convenient way to manage Database Mail configuration objects. Database Mail Configuration Wizard performs these tasks:

Set up Database Mail
Manage Database Mail accounts and profiles
Manage profile security
View or change system parameters

The Set up Database Mail option guides you through all of the tasks required to set up Database Mail for the first time. The other options help you accomplish specific set up and maintenance tasks. To start the Database Mail Configuration Wizard right-click Database Mail under Management in the SQL Server Management Studio and choose Configure Database Mail.

Select the task that you wish to perform.

Select Configuration Task Pic

To set up Database mail you must have at least one Profile with at least one account.

Give the new profile a name and click Add to add a new SMTP account.

New Profile Pic

In the account properties you can specify an Email Address, Display Name, Reply information and the server name that manages the Email address. You can also select the type of authentication used by the Email address.

New Account Pic

On some systems I’ve had the wizard lockup and not allow me to add new accounts. If this happens use the stored procedure “sysmail_add_account_sp” in the msdb database to add an account and then rerun the wizard to add the existing account to a profile.

Database Mail provides more flexibility in configuration, failsafe protection, and email protection through mail queues than was provided by SQL Mail. If you are still using the SQL Mail component then you should strongly consider moving to the Database Mail component, especially since SQL Mail is considered a deprecated component and may not exist in the next version of SQL Server.

Go to Top | Return to Newsletter Contents