Database Mail - SQL Server 2008

Warning: mentioning SQLMail to some DBAs may cause undesirable reactions. The notoriously bad press that surrounded the SQLMail feature in previous versions of SQL Server can now come to an end. So can the hassles of configuring MAPI profiles, installing the Outlook client on your production server, and living with the risk of a disruptive xp_sendmail call taking down the SQL Server process.

Database Mail is an SMTP-based e-mail solution for SQL Server. This mail feature provides users with the following:

  • Asynchronous mail solution
  • Restricted user access to Database Mail
  • Scalable Simple Mail Transfer Protocol (SMTP) server configuration
  • Attachment and query restrictions

Before we dive into the Database Mail internals, let’s define a few basic concepts in the Database Mail world. An account is name, description, and authentication information for a specific SMTP account. A group of these accounts is referred to as a profile. Having a group of accounts is useful, since this adds a layer of reliability, because the mail would still be sent by a different account in the profile if one particular SMTP server was down. If you’re a visual person, you may see things more clearly by looking.

Pictoral representation of a Database Mail profile

Pictoral representation of a Database Mail profile

Figure 8-24 shows one profile defined as MyProfile. This profile has two accounts created within it: Rob_DBA and Tammie_DBA.

When users send mail using Database Mail, they use the new stored procedure sp_send_dbmail, as in this example:

sp_send_dbmail @profile_name='MyProfile',
@recipients='testuser@apress.com',
@subject='Test Message',
@body='This is the body of the message.',
@importance='HIGH'

One of the parameters in this stored procedure is @profile_name. When the user specifies a profile, Database Mail looks at the first account that is defined for the particular profile and attempts to send the e-mail using this account. If for whatever reason the send fails, such as when the SMTP server is offline, Database Mail will wait 100 seconds (the default Account Retry Delay setting), and then proceed down the account list and attempt to send the mail using the second account. If this account fails, it will continue to the next account, and so on. When it has reached the end of the account list, the send will fail. If you wanted Database Mail to loop back around and attempt the first account again, you could set the Account Retry Attempts setting to the number of times Database Mail will “round-robin” the account list. These global settings will be described in detail.

So are profiles just a way to group accounts? Well, sort of. There are two types of profiles: public and private. Public profiles can be accessed by any valid msdb database user. With private profiles, administrators can limit which users can use a specific profile.

Database Mail installs both configuration and mail item information in the msdb database.

Configuring Database Mail

The Database Mail Wizard can be accessed from the Configure Database Mail context menu off the Database Mail node. This wizard is used to manage accounts, profiles, and Database Mail global settings. Clicking Next on the welcome screen will bring you to the Select Configuration Task page. This page will determine which function the wizard should perform. By default, the option to configure the mail host database is selected, so you can simply click the Next button.

Select Configuration Task page

Select Configuration Task page

You need to have at least one profile configured in order to use Database Mail. Add a profile through the New Profile page. The profile name is what users will use when sending e-mail, so make sure it’s easy to remember. The description is optional and is used to provide more information about the profile.

New Profile page

New Profile page

At the bottom of this page, you can define one or more accounts that this profile will use. Clicking Add will pop up a dialog box that allows you to select an existing account or create a new account. Once an account is defined, it’s available for all profiles.

Clicking Next now brings you to one of the more interesting user interface conglomerations without SQL Server. The Manage Profile Security page.

Manage Profile Security page

Manage Profile Security page

The first thing to notice about this page is it has two tabs: Public Profiles and Private Profiles. When a new profile is created, it’s neither public nor private; basically only sysadmins can use this profile. If the sysadmin marks a profile as public, then any database user in the msdb database can send mail using this profile.

If you click the Private Profiles tab, you’ll notice a combo box that allows you to specify a database user. From the User Name combo box, you can select any username and check the profiles to which to grant them access. Once you finish the wizard, the private profile won’t generally show up unless you have the username selected in the combo box.

Manage Profile Security, Private Profiles tab

Manage Profile Security, Private Profiles tab

The last significant property that needs to be set on a profile is the default profile. If there are no profiles that are set for the default, users will always need to specify the @profile_name parameter in the sp_send_dbmail stored procedure call. If the sysadmin has a default profile defined, users can omit the profile, and Database Mail will use the default profile that is defined.

Clicking Next in the wizard will bring you to the last configuration page, called Configure System Parameters. This page allows you to set the global settings of Database Mail. Table describes each of these parameters.

Configure System Parameters page

Configure System Parameters page

Database Mail System Parameters

Database Mail System Parameters

The next page in the wizard is the summary page describing the actions that will be performed. Following that page is a progress page that will show you the live status of the actions being performed on your database.

Depending on which options you selected, you’ll have a variable number of actions to be performed. In the event of a failure, the status would read “Failed,” and there would be an error message hyperlink in the message column. Clicking this link may provide you with additional information about this error, provided you have an active Internet connection. This online error help feature can be seen throughout all of the SQL Server Management Studio dialog boxes and wizards.

The Database Mail feature isn’t enabled by default on SQL Server. This feature can be enabled via the wizard. If you’re planning on configuring Database Mail entirely through scripts and want to enable Database Mail, simply enable the Database Mail XPs parameter through the sp_configure stored procedure:

SP_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
SP_CONFIGURE 'Database Mail XPs", 1
GO
RECONFIGURE
GO

After you’ve configured Database Mail, you’re ready to send mail.

Sending Mail

For purposes of this discussion, consider an example where the administrator has created a single profile called MyDatabaseMailProfile. This profile contains two accounts: Rob_DBA using SMTP server SMTP_SVR1, and Tammie_DBA using SMTP server SMTP_SVR2. At this point, the administrator issues the following T-SQL command:

Use msdb
GO
EXEC sp_send_dbmail @profile_name='MyDatabaseMailProfile',
@recipients='username@foo.bar',
@subject='Test message',
@body='This is the body of the test message.'

The stored procedure will first validate that the required parameters are entered, and then write the message in the sysmail_mailitems table. Having an immediate copy of the mail before it’s “sent” allows the administrator to easily troubleshoot mail problems. After the mailitems entry is created, the stored procedure will call another stored procedure to create an XML document that wraps the mail. At this point, the XML document is sent to a predefined Service Broker queue. Service Broker provides queuing and reliable messaging. Using this feature allows Database Mail to asynchronously send large amounts of e-mail without disrupting other server operations.

When messages are placed on this queue, a SQL Server event notification is raised. At this point, another Service Broker queue is listening for this event, and upon reception, it will spawn the DatabaseMail90.exe process. When this process is instantiated, it obtains configuration information from msdb, pulls messages from the queue, and sends the mail. Upon completion, it will update the sysmail_mailitems table with delivery status and update the sysmail_log table. Since spawning a process is an expensive operation, the process itself will stay alive after processing all the mail items for a time period that is defined in the Database Mail global configuration settings as Database Mail Executable Minimum Lifetime. The default value is 600 seconds, or 10 minutes, and can be changed through the Database Mail Configuration Wizard, as discussed in the previous section.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics