Database Mail


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.


  • Anonymous authenticationThe least secure option.

  • Basic authenticationBy providing user name and password in clear text.

  • Windows authenticationBy using Database Engine service credentials. This is by far the most secure option and it leverages network security.

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.


Sending Mail

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.


1.

Sender connects to SQL Server Database Engine and calls sp_send_dbmail in the context of MSDB database.

2.

The entire body of the message (including necessary attachments) gets loaded into one of the internal MSDB tables supporting the Database Mail solution.

3.

A special message ID is inserted into the External Service Broker queue.

4.

Service Broker, in response to the newly inserted queue item, calls a special activator stored procedure.

5.

The activator procedure checks whether a DatabaseMail90.exe process is already running and launches it if necessary.

6.

The DatabaseMail90.exe process connects back to the SQL Server Database Engine instance and reads the Service Broker Queue Message.

7.

After the message is read, the mailer process selects the message details from the internal mail table by its message ID.

8.

Message details include a profile name that gets resolved into the appropriate mail accounts to be used for delivery.

9.

The DatabaseMail90.exe process attempts to contact all mail servers, following the normal mail account failover routine as described earlier.

10.

When the message is successfully delivered to the mail server, DatabaseMail90.exe posts a response message onto a special Service Broker Internal Queue.

11.

The Service Broker Internal Queue activation stored procedure gets launched.

12.

The activation procedure reads the response message and updates the internal status table for a given message ID, indicating successful delivery.

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.

Table 10-2. System Configuration Parameters

Parameter Name

Recommended Value(s)

Possible Values

Purpose

Account Retry Attempts

13

Any positive number

Number of times the DatabaseMail90.exe process should try to connect to a given mail server before giving up. If more than one mail account is associated with the mail profiler, the failover sequence is started.

Account Retry Delay

1060

Any positive number

Number of seconds to wait before trying to connect to the same mail server again.

Maximum File Size

100,0001,000,000

Any positive number

Maximum size of the attachment file that can be sent via email.

Prohibited Attachment File Extensions

exe,dll,vbs,js

Comma-separated string of the file extensions

If the attachment has one of the specified potentially dangerous not be sent extensions, it will not be sent out.

Database Mail Executable Minimum Lifetime

60180

Any positive number

Number of seconds the DatabaseMail90.exe process will wait before shutting down after all mail is sent.

Logging Level

 

2-Extended, Normal, Extended, Verbose

NormalLog errors only. ExtendedLog all errors and warnings. VerboseLog all errors, warnings, and informational messages.


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).




Inside SQL Server 2005 Tools
Inside SQL Server 2005 Tools
ISBN: 0321397967
EAN: 2147483647
Year: 2006
Pages: 149

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net