Implementing SQL Server Database Mail


Have you ever wished your database could tell you what’s wrong or let you know when a task has completed? SQL Server’s Database Mail allows the database to send out messages over SMTP. Deanna Dicken shows you how to set up Database Mail and send some messages.

Introduction to Database Mail

Have you ever wished your database could talk to you, to tell you what’s
wrong or let you know when a task has completed? I certainly have. Fortunately,
SQL Server comes equipped with a utility for just such a task. SQL Server’s
Database Mail allows the database to send out messages over SMTP. Let’s have a
look at setting it up and sending some messages.

Overview of Database Mail

Database Mail is a robust SQL Server utility for enabling email from your
database. Email messages can be sent to multiple recipients, with multiple
attachments. You can even include a query to be executed and the results
attached to or included in the email.

The beauty is it doesn’t require an extended MAPI (Messaging
Application Programming Interface) client to be installed on the SQL Server.
All you need is an SMTP
(Simple Mail Transfer Protocol) server. Multiple SMTP servers can be utilized
for resilience.

The utility is enterprise-ready. Database Mail is cluster aware. It provides
for redundancy in profiles, accounts, and SMTP servers supporting both failover
and distribution of load. It performs asynchronously, queuing up messages via
Service Broker, allowing for decoupling from the email transport mechanism.

Copies of the emails and attachments are retained in the MSDB database and
usage is logged in the database as well as the event log. This allows for
auditing and support of Database Mail.

To provide security around Database Mail, users must be granted rights to
the profiles used by Database Mail (though they could be granted to public if
you choose). Also, the account executing it must be a member of the
DatabaseMailUserRole in the MSDB database.

Additional security is provided with regards to attachments. Database Mail
can be configured to limit the size of email attachments as well as the
allowable attachment extensions.

Database Mail Installation and Configuration

To prepare for Database Mail, you will need to determine what SMTP server
you want to utilize as well as the account that will be sending the email.
Additionally, the SMTP server will need to allow communication from the SQL
Server server and the account will need to be granted rights to send mail. SQL
Server can connect to the SMTP server using SSL (if required),
anonymous access (if allowed), or basic authentication.

Consideration must be given to the amount of traffic Database Mail will be
generating through your solution and if the selected SMTP server can handle the
additional traffic. Once the traffic volume is understood, you can estimate any
growth impact on MSDB, which serves as a repository for outgoing email messages
and logs.

To control the growth of MSDB, clean up tasks can be scheduled to delete
messages and/or clean up the log. System stored procedures
sysmail_delete_mailitems_sp and sysmail_delete_log_sp are provided to assist with
these tasks.

Database Mail, which runs in MSDB, relies on SQL
Server Service Broker
under the covers. By default, Service Broker is
enabled in MSDB. If it is not enabled in your MSDB, you will need to enable it.
This requires a database lock, which will require SQL Agent to be stopped.

Once SMTP and Service Broker are configured, Database Mail can be enabled on
your SQL Server instance. First, make sure the sending account is a member of
the DatabaseMailUserRole database role in the MSDB system database. Then, while
in SQL
Server Management Studio
, expand your connection to the instance. Navigate
to Management and right-click on Database Mail. Choose Configure Database Mail
to kick off the Database Mail Configuration Wizard. Alternatively, you can use
sp_configure to enable Database Mail. Let’s walk through the Configuration
Wizard screens.

Database Mail Configuration Wizard Welcome Screen

Database Mail Configuration Wizard Select Configuration Task

Select the "Set up Database Mail by performing the following
tasks" option in the Configuration Wizard and answer "Yes" to
enable Database Mail.

Database Mail Configuration Wizard, New Database Mail Account

After naming the profile, add the account or accounts the profile should use
to send mail. The accounts will be used in order should there be a problem with
one or more of them. Here you will also set your authentication mode to the
SMTP server.

Database Mail Configuration Wizard, Manage Profile Security

On this screen, you can specify access to the profiles as appropriate.

Database Mail Configuration Wizard, Configure System Parameters

Finally, you can specify characteristics of Database Mail such as the retry
count, maximum file sizes, prohibited attachment extensions, and the logging
level.

Database Mail Configuration Wizard, Complete the Wizard

Review the summary and click finish. You’re ready to send mail.

Examples of Utilizing Database Mail

Now that Database Mail is set up on your SQL Server instance, let’s look at
a couple examples of utilizing Database Mail.

The following example shows a simple call to the sp_send_dbmail stored
procedure. You could put a call like this at the end of an application stored
procedure or SQL job to alert on success or failure of the processing for
instance.

    EXEC msdb.dbo.sp_send_dbmail
         @profile_name = 'DBMail'
       , @recipients =  'GroupSQLDBA@MyCo.com'
       , @copy_recipients = 'GroupAppSupport@MyCo.com'
       , @from_address = 'DBMail@MyCo.com'
       , @subject = 'Success'
       , @body = 'SQL Job xyz completed successfully';

A more sophisticated example would be executing a query and including the
result set as an attachment to the email. Let say you’ve been concerned with
excessive locking in your database, but can’t spend all day looking at the
locking reports to see when excessive lock waits are occurring. So, you set up
a job that runs every 15 minutes and sends you the latest information on all
exclusive and intent exclusive locks for instance.

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'DBMail'
    ,@recipients = 'GroupSQLDBA@MyCo.com'
    ,@from_address = 'DBMail@MyCo.com'
    ,@query = 'SELECT resource_type, resource_database_id, 
                    request_mode, request_session_id 
               FROM sys.dm_tran_locks
              WHERE request_mode IN (''IX'', ''X'')' 
    ,@subject = 'Exclusive Locks'
    ,@attach_query_result_as_file = 1 ;

When this executes, the query will be run and the result set will be added
as an attachment to the email message. Now you can look through your emails for
trends in locking in one or more of your databases and investigate further if
need be.

Conclusion

Whether for administration or business application, Database Mail is an easy
and convenient way to add communication to your SQL processes. With consideration
for reliability, security, scalability, and supportability, this solution can
be used in shops small and large including those running SQL clusters.

For More Information

Database
Mail

Planning for
Database Mail

Database
Mail Architecture

Database
Mail Configuration Wizard

Troubleshooting
Database Mail

Database
Mail and SQL Mail Stored Procedures (Transact-SQL)

»


See All Articles by Columnist

Deanna Dicken

Deanna Dicken
Deanna Dicken
Deanna Dicken is an architect with approximately 20 years of IT experience. In that time she has worked on many large-scale, mission-critical applications and has been involved at all phases of the lifecycle. She has also contributed to three SQL Server MCSE books, co-authored Learn SQL in a Weekend, and tech edited many other titles. She lives on the outskirts of Indianapolis, Indiana with her husband, Curtis, and children, Kylee and Zach.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles