SQL Server: Database Mail

Database Mail sends e-mail messages using SMTP. To use Database Mail, DBA/Developer does not have to install an Extended MAPI client on the SQL server. Database Mail is an external process that runs outside of the Database Engine. Therefore, it has minimal impact on the SQL server. Database Mail offers many advantages over using SQL Server Mail. Database Mail can use multiple SMTP accounts and profiles, limit the size and types of attachments, and log all mail events that occur. Mail messages sent by Database Mail use Service Broker to queue and deliver e-mail messages. Therefore, Service Broker must be active in the msdb database to successfully send e-mail messages using Database Mail.Database Mail is disabled by default. DBA/Developer can enable Database Mail using the Database Mail Configuration Wizard in SQL Server Management Studio, the sp_configure system stored procedure, or the Surface Area Configuration Utility.To launch the Database Mail Configuration Wizard, DBA/Developer should first expand the Management node in the Object Explorer in SQL Server Management Studio as shown:

 

After expanding the Management node, DBA/Developer should right-click Database Mail and select Configure Database Mail: This will launch the Database Mail Configuration Wizard. At the first screen, click Next to proceed past the welcome information. At the second screen, select the Setup Database Mail option as shown and click the Next button.

 

The Database Mail Configuration Wizard will walk DBA/Developer through the initial steps of configuring database mail and will enable Database Mail.To enable Database Mail using the sp_configure system stored procedure, DBA/Developer can enable the Database Mail XPs option as follows:

sp_configure 'show advanced options', 1;
GO RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO RECONFIGURE;
GO

To enable Database Mail using the Surface Area Configuration Utility, DBA/Developer should right click the server in Object Explorer and select the Facets option. In the View Facets window, DBA/Developer should select Server Configuration for the Facet option and set the DatabaseMailEnabled property to True as shown:

 

After enabling Database Mail, DBA/Developer can use Database Mail system stored procedures to manage mail accounts and profiles or send e-mail messages. For example, in this scenario, the following code might be used to send an e-mail to a sales representative using Database Mail:

EXEC
msdb.dbo.sp_send_dbmail@recipients =
N 'asmith@virtuart.com',
@body = 'An order > $1000 for a priority customer has shipped.',
@subject = 'Priority Customer Order',
@profile_name = 'VirtuArtMailProfile',
@query =
SELECT *
FROM Sales.SalesOrderHeader h
INNER JOIN Sales.SalesOrderDetail d
ON h.SalesOrderID = d.SalesOrderID
WHERE
TotalDue > 1000
AND CustType = 'Priority'
AND SalesOrderID = '43652',
@attach_query_result_as_file = 1,
@query_attachment_filename
='PriorityOrderDetails.txt'

The sp_send_dbmail system stored procedure is used to send an e-mail message using Database Mail. This procedure accepts parameters defining the details of the e-mail message, such as the recipients, message subject, message body, and importance, and sends the e-mail message. DBA/Developer should not use SQL Server Mail. SQL Server Mail requires an Extended MAPI client be installed on the server, and runs as a server process. This would not allow DBA/Developer to send messages using SMTP and would have more impact on the server than using Database Mail. DBA/Developer should not use Distributed Transaction Coordinator (DTC). Microsoft Distributed Transaction Coordinator (MS DTC) is used to provide for transactional processing across multiple SQL Server instances. DBA/Developer should not use SQL Server alerts. SQL Server alerts provide automatic notification when specific errors or events occur on the SQL server. When a server error or event occurs, it is recorded in the Windows application log. SQL Server Agent reviews the application log entries and fires alerts defined for the recorded events.