Database Mail

Database Mail is new to SQL Server 2005 and is a welcome relief from SQLMail, although you may still use SQLMail if you wish. Database Mail and SQLMail allow you to notify operators via e-mail and to send e-mails via stored procedures.

SQL Mail required Extended Messaging Application Programming Interface (MAPI) support, which could be problematic. Installing a new version of Office could cause SQLMail to fail, by installing a version of the MAPI driver that would be incompatible with SQLMail.

Database Mail is more secure, more reliable, and does not rely on MAPI. It uses Simple Mail Transfer Protocol (SMTP). It is cluster-aware, and allows automatic retry of failed e-mail messages as well as failover to another SMTP server, should the first become unavailable. Database Mail also allows you to set up multiple accounts and provide secured or public access to the accounts.

Note

Database Mail is not available in SQL Server 2005 Express Edition.

Architecture

Database Mail is loosely coupled to SQL Server and is based on queuing, as shown in Figure 5-14. When an e-mail is sent, either by calling sp_send_dbmail or from SQL Agent notifications, security is checked. The email is stored in a table in msdb, and a message is placed in the Service Broker message queue in msdb. This activates an external program: DatabaseMail90.exe, located in the MSSQL\Binn directory. DatabaseMail90.exe reads the message and sends the e-mail with any attachments to one or more SMTP mail servers. It then places a message in the status queue, containing the results of the send process. The status queue insert activates a stored procedure in msdb to update the status of the e-mail in msdb.

Figure 5-14

Security

For security reasons, Database Mail is disabled by default. You can enable and configure it using the SQL Server Surface Area Configuration Tool, as shown in Figure 5-15, or the Database Mail Configuration Wizard.

Figure 5-15

Additionally, to send notifications via Database Mail from SQL Agent, SQL Agent must be configured to use Database Mail. This is covered in the "SQL Agent Configuration" section of this chapter.

The external program, DatabaseMail90.exe, must have network access to the SMTP servers. It runs using the security credentials for the SQL Server Service account. Therefore, the SQL Server Service account must have network access, and the SMTP servers must allow connections from the SQL Server computer. Database Mail supports Secure Sockets Layer (SSL) if it is required by the SMTP server.

Note

Local system and Local Service do not have network access, and cannot be used as service accounts for SQL Server if you use Database Mail. Database Mail would not be able to connect to another computer (the SMTP server).

To send database mail, you must either be a member of the sysadm fixed server role or a member of the DatabaseMailUserRole in msdb. You can place a size limit on mail attachments and prohibit attachments with certain file extensions.

Configuration

To use database mail, you'll need to do some configuration. You'll need to set up the database mail account, configure the mail procedure itself, and set up archiving.

Database Mail Account

A Database Mail Account is a basic unit of configuration for Database Mail. An account contains the following configuration information:

• "From" information for the e-mail messages. All outgoing e-mail messages will say they are from the account you provide here. This does not have to be a real e-mail account.

• "Reply to" e-mail address. If the recipient of one of these e-mails tries to reply, the reply will be sent to the e-mail address provided for this account.

• SMTP connection information. The SMTP Server name and port number are included in the account configuration. Database Mail supports encrypted and unencrypted messages. Encryption is done via Secure Sockets Layer (SSL). Whether you wish the messages from this account to be encrypted is included in the account configuration.

• E-mail retry configuration. You may specify how many times to retry sending an e-mail and a wait period between retries.

• E-mail size limits. You may set a maximum size limit allowed for e-mails from this account.

• Excluded attachment extension list. You may provide a list of file extensions. Any attachment that has a file extension in the prohibited list will not be allowed.

• Logging level. You may specify how much logging should be done for this account.

Note

If your SMTP Server requires a log in, you may wish to set up a specific local account, with minimum permissions, specifically for sending SMTP mail. The purpose of this account is to follow the principle of least privileges and should be used for no other purpose.

You may set up many accounts, but you should plan your implementation. You may wish to have accounts for several different SMTP servers. This will allow you to automatically fail over from one to another. This is done via profiles, which will be covered next. You may also wish to set up a special account that allows dangerous extensions or large attachments to be sent and restrict that account to special users.

Another reason to set up multiple accounts is to provide different From and Reply-to addresses. You may wish to do this for several departments in your company. For instance, you might set up an account named Accounts Payable, which has a from address of [e-mail] and a reply address of [e-mail]. This reply address could be an e-mail group that sends to the accounts payable service reps at your company.

Setup

In this section, you will set up Database Mail. Use the wizard by expanding Management in SQL Server Management Studio. Right-click Database Mail and choose Configure Database Mail.

Click through the introduction dialog box. The next box is the Select Configuration dialog box. Check the top radio button; we are setting up Database Mail for the first time. Click Next.

You will then receive a message box, asking if you wish to enable the Database Mail feature. Choose yes and continue.

This brings you to the New Database Mail Account dialog box, shown in Figure 5-16. This is where you provide the information needed to communicate with an SMTP server. Choose a name and description for this account.

Figure 5-16

The next section is the Outgoing Mail Server (SMTP). The e-mails sent from this account will be tagged from the e-mail address and display name that you set in this section. If the recipients reply to the e-mail, the reply will be sent to the address you supply in the Reply e-mail textbox. In the Server Name textbox, you provide the name of the SMTP server. This is usually in the form of smtp.myserver.com. You do not provide the complete URL, such as http://smtp.myserver.com. Database Mail does this for you. If you check the box labeled "This server requires a secure connection (SSL)", the URL created will be https://smtp.myserver.com. The default port number of 25 will do, unless you have changed the SMTP port number.

You provide the SMTP login information in the SMTP authentication section. Not all SMTP servers require authentication; some require only a known sender e-mail, and others require nothing. Then click OK.

You will be taken to the Manage Profile Security dialog box, as shown in Figure 5-17.

Figure 5-17

Here you set up public and private profiles. Check the Public checkbox to make this a public profile. You may also wish to set this as a default profile. Click Next.

You should now be at the System Configuration Parameters dialog box, as shown in Figure 5-18.

Figure 5-18

Set up the parameters as we discussed earlier to meet your needs. Click Next.

Then the setup will begin, and in the final dialog box you will see lots of green. Click Close.

To ensure things are working properly, you should send a test e-mail. In SQL Server Management Studio, expand Management, right-click Database Mail, and choose Send Test E-Mail. You will be prompted to enter an e-mail address. Send the mail and wait for receipt.

Archiving

You can access mail information in the sysmail_allitems view. Attachments can be accessed via sysmail_mailattachments, and the mail log is in sysmail_eventlog. The tables under these views are not automatically maintained. They will just get larger and larger. As a DBA, you will probably wish to do some maintenance. Microsoft provides stored procedures to delete items from these tables: msdb.dbo.sysmail_delete_mailitems_sp and msdb.dbo.sysmail_delete_log_sp. Both of these have two parameters: @sent_before and @sent_status.

@sent_before takes a datetime value and will delete all log or mail items with a time before the parameter time. When you do not specify a sent_status, all mail items that have a send_request_date prior to the parameter will be deleted, whether or not they have been sent. So be careful.

@sent_status can have values of unsent, sent, failed, or retrying. When you specify a sent_status, only the mail items that have a sent_status equal to the parameter will be deleted.

You may wish to archive this information prior to its deletion. Books Online will get you started with a small example that runs monthly and archives the month's mail, attachments, and logs into tables called DBMailArchive_<year>_<month>, DBMail_Attachments_<year>_<month>, and DBMail_Log_<year>_<month>.