SQL Server 2005 uses administrative Database Mail

A new mail subsystem, called Database Mail, appears in SQL Server 2005. Database Mail is much better than earlier SQL Server versions of SQL Mail. Now you can create multiple accounts and personal data to meet your different application email needs

This article will discuss building and managing Database Mail.

What is a Database Mail?

Database Mail is a mail queue system. e-mail messages are stored in queues in the msdb database waiting to be processed. When an e-mail message arrives in the queue, an external process is triggered to send the message to the appropriate mail server on the queue. Once a message is sent, an e-mail message with a post status is mailed back to SQL Server.

Open Database Mail feature item

Database Mail is not available outside of the module. Because the SQL Server security model defaults to shutting down all options, you need to enable Database Mail if you want to use it. You can use the Surface Area Configuration tool, or use the following T-SQL to enable Database Mail:

　　sp_configure'showadvancedoptions',1;
　　GO
　　RECONFIGURE;
　　GO
　　sp_configure'DatabaseMailXPs',1;
　　GO
　　RECONFIGURE
　　GO

Set up an account

The Database Mail account identifies how SQL Server 2005 should communicate with the SMTP server. This account specifies how the email is formatted and sent. A single account will identify a single SMTP server and authentication method. The account that the Database Mail uses does not correspond to the SQL Server login account.

When setting up an account you need to identify enough information to enable SQL Server 2005 to communicate with the SMTP server and to authenticate if necessary. You can refer to the Books Online for a complete list of options for defining your account. You can use the Database Mail Configuration Wizard to set up your account, which can be found under the Administration folder Database Mail in SQL Server Management Studio, or use the stored procedure (SP) "sysmail_add_account_sp". Here's a script that uses the stored procedures mentioned above to create an account that communicates with an SMTP server that does not require authentication.