Database Mail is an SMTP-based asynchronous mailing solution shipped in SQL Server 2005. It relies on SQL Server Service Broker queuing technology for asynchronous guaranteed mail delivery support. Unlike SQL Mail, Database Mail does not allow the user to read mailbox items. In fact, there is no mailbox to speak of; it can only send mail.
Mail Accounts and Mail Profiles
Depending on the configuration, a user can have access to multiple mail profiles for sending mail. Each profile can have multiple mail accounts associated with it.
When a profile is fully configured, it starts delivering mail using the account with the lowest sequence number. If the mail server specified by the account is not available for any reason, the DatabaseMail90.exe process tries it a specified number of times after a pre-configured delay interval and moves on to the next account in sequence. This process when one account fails and another one is used in its place is called account failover and is shown in Figure 10-2. All accounts under Profile1 are going to be exercised according to their sequence numbers: 1, 2, and 3.
Figure 10-2. Mail account failover.
In general, a profile can be public, that is, accessible to any user, or private, that is, accessible only to members of the sysadmin fixed server role or to any user that has been granted explicit access to this role.
Configuring Database Mail
Similar to SQL Mail, Database Mail is OFF by default on a new installation of SQL Server Database Engine and needs to be explicitly enabled by the administrator before it can be used.
You can configure a Database Mail solution by using the Database Mail Configuration Wizard in SQL Server Management Studio, shown in Figure 10-3. You invoke the wizard by clicking on the Database Mail folder inside the Object Explorer. You can run this wizard multiple times, performing different configuration tasks after email is already in operation. For now, it makes sense to select the first option that takes us through the basic setup stages, so let's click the Next button.
Figure 10-3. Database Mail Configuration Wizard.
First, you need to configure the email profile and account. Account configuration can be tricky because you need to specify several important server characteristics. Please reference Figure 10-4 for details. You need to know the name and port number of the outgoing SMTP server. You also need to consider whether the SMTP server supports SSL. Finally, you need to decide on the server authentication mechanism. Database Mail supports the following authentication mechanisms:
Figure 10-4. Configuring New Database Mail Account.
Which one you choose depends on your environment and, more specifically, how much control you have over the back-end server configuration.
Database Mail Security
After the profile is fully configured, you need to decide which user(s) will have access to it. Assuming you don't want to make the profile public, you need to switch to the Private Profiles tab and select the user who will have access to the profile (see Figure 10-4). You also need to decide whether any given profile should be considered the default profile for this user. The default profile is used for sending mail by this particular user, calling sp_send_dbmail if the profile name is not specified as part of the stored procedure call.
Tips and Tricks
If the profile name is not specified for sending mail, Database Mail first checks for a private default profile and then checks the public default profile.
The drop-down list will be populated with all users that are defined in the MSDB database; however, only users that are members of the DatabaseMailUser database role can call sp_send_dbmail and use any profiles for sending mails.
Figure 10-5. Database Mail profile access.
The principal stored procedure used to send mail is called sp_send_ dbmail, which has the following syntax:
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ] [ , [ @recipients = ] 'recipients [ ; ...n ]' ] [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ] [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]'] [ , [ @subject = ] 'subject' ] [ , [ @body = ] 'body' ] [ , [ @body_format = ] 'body_format' ] [ , [ @importance = ] 'importance' ] [ , [ @sensitivity = ] 'sensitivity' ] [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ] [ , [ @query = ] 'query' ] [ , [ @execute_query_database = ] 'execute_query_database' ] [ , [ @attach_query_result_as_file = ] attach_query_result_as_file] [ , [ @query_attachment_filename = ] query_attachment_filename ] [ , [ @query_result_header = ] query_result_header ] [ , [ @query_result_width = ] query_result_width ] [ , [ @query_result_separator = ] 'query_result_separator' ] [ , [ @exclude_query_output = ] exclude_query_output ] [ , [ @append_query_error = ] append_query_error ] [ , [ @query_no_truncate = ] query_no_truncate ] [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]
As you might have noticed, the parameters are very similar to those supplied for xp_sendmail (part of SQL Mail discussed earlier in this chapter), especially those related to formatting.
Tips and Tricks
Do not expect the formatting of the query results to be the same between SQL Mail and Database Mail. In early pre-released versions of SQL Server 2005, both solutions shared the same formatting engine, but that has changed. The main reason for this change is deficiencies of the old SQL Mail-based formatting engine with respect to the new data types introduced in SQL Server 2005, such as nvarchar(max), varchar(max), xml, and so on.
This similarity is intentional because Microsoft wanted to simplify customer migration to Database Mail by providing full backward compatibility. Still, a few extra parameters exist that need to be considered as definite improvements. First, the @body parameter can indicate the HTML body type of a sent message that instructs the SMTP delivery mechanism about special character escaping for HTML element tags or lack thereof that is required to handle the message. Second, there is a capability to specify the importance of the mail message via the @importance parameter that can accept the following values: Low, Normal, and High. There is also the @sensitivity parameter, which accepts Normal, Personal, Private, Confidential flags.
You may also notice that the capability to run the query as a user is no longer there. It is believed that users that are not members of the sysadmin fixed server role won't have necessary privileges to impersonate others, whereas server administrators can simply supply the EXECUTE AS clause as part of the query itself, thus eliminating the need for the extra logic.
Finally, you need to consider the last parameter, @mailitem_id, which gets the actual value of the mail message ID assigned to it automatically for delivery by Database Mail engine. What do you do with this value? You can look later at one of the management views showing mail delivery status and see whether your message has been delivered to the mail server.
Now consider the entire process of calling sp_send_dbmail and look at all the moving parts. Please refer to Figure 10-6 for a sequence diagram representing the mail delivery process: The steps involved are these:
Figure 10-6. Delivering mail.
It is worth noting that after the DatabaseMail90.exe process is launched, it continues running for a pre-defined period of time, even after the External Queue is depleted, that is, there are no more messages to be delivered. This inactivity timeout period and other similar global settings are part of the Database Mail global configuration parameters.
System Configuration Parameters
Let's examine the Database Mail system configuration parameters via the Database Mail Configuration Wizard, shown in Figure 10-7.
Figure 10-7. Database Mail System configuration parameters.
Each parameter value can be changed right inside the grid. To quickly look up what purpose a particular parameter serves, it is sufficient to simply switch focus to the desired grid line and look at the help text at the bottom of the dialog. Table 10-2 contains a brief summary of system configuration parameters and their possible values.
Monitoring and Troubleshooting Database Mail
Database Mail has a log that can contain different types of events, based on a specified preconfigured Logging Level. You can view the Database Mail Log easily by right-clicking on the Database Mail node in the Object Explorer and selecting the View Database Mail Log pop-up menu item. The Database Mail Log File Viewer is shown in Figure 10-8.
Figure 10-8. Database Mail Log File Viewer.
A number of monitoring views and stored procedures have also been added to Database Mail to simplify monitoring and potential troubleshooting of email issues. Although they do not have direct graphical support in SQL Server Management Studio, you can access them by executing simple queries via Query Editor.
For example, the msdb.dbo.sysmail_allitems view enables the user to see all email items processed by the system. There are specialized snapshots of this view for viewing only successfully sent items (msdb.dbo.sysmail_sentitems), unsent items (msdb.dbo.sysmail_unsentitems), and failed items (msdb.dbo.sysmail_faileditems). Any member of the DatabaseMailUserRole MSDB has access to these views, but regular users can see only specific items they personally tried to send.
Stored procedure sysmail_help_status_sp is used to view the status of Database Mail queues. Possible values returned are STARTED and STOPPED.
Tips and Tricks
Under normal operation, you would not have a need to start or stop mail queues yourself, but if you ever want to do this you can use the sysmail_start_sp and sysmail_stop_sp procedures to do this.
There is also a procedure that allows you to view the status of Database Mail queues explicitly. It is called sysmail_help_queue_sp, and it returns a resultset with the status of each mail queue.
Managing Database Mail Storage
Because Database Mail uses the MSDB database as its repository, it is important to be able to clean up old messages. A special management stored procedure exists specifically for this purpose: sysmail_delete_ mailitems_sp, which allows the administrator to delete all mail items sent earlier than a specified date, and optionally allows you to restrict deletion by specific mail item status (sent, unsent, failed, retrying).