E-Mail Functionality in SQL Server 2005

By : Dinesh Asanka Dec 21, 2006

Printer friendly

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.