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. I will show 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.
Select the "Set up Database Mail by performing the following tasks" option in the Configuration Wizard and answer "Yes" to enable Database Mail.
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.
On this screen, you can specify access to the profiles as appropriate.
Finally, you can specify characteristics of Database Mail such as the retry count, maximum file sizes, prohibited attachment extensions, and the logging level.
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.
