You are a database developer for VirtuArt Corporation on an instance of SQL Server 2008. You are creating an application for the order processing department.
The application will send e-mail notifications to sales representatives when specific orders for priority customers are shipped.
When an order from a priority customer that is over $1000.00 is shipped, the application must e-mail the customer’s assigned sales representative and include details of the order as an e-mail attachment, and also meet the following requirements:
E-mail should be sent using Simple Mail Transfer Protocol (SMTP).
Impact on the SQL server should be minimized.
Which SQL Server component should you use?
A. Database Mail
B. SQL Server Mail
C. Distributed Transaction Coordinator (DTC)
D. SQL Server alerts
Correct Answer: A
Explanation/Reference:
Database Mail sends e-mail messages using SMTP. To use Database Mail, you do 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.
In this scenario, you could include code in the application to send an e-mail to the assigned sales representative with an attachment containing the order details when an order over $1,000 ships to a priority customer. 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. You 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, you should first expand the Management node in the Object Explorer in SQL Server Management Studio as shown:
After expanding the Management node, you 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 you through the initial steps of configuring database mail and will enable Database Mail.
To enable Database Mail using the sp_configure system stored procedure, you 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, you should right-click the server in Object Explorer and select the Facets option. In the View
Facets window, you should select Server Configuration for the Facet option and set the DatabaseMailEnabled property to True as shown:
After enabling Database Mail, you 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’[email protected]’,@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.
You 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 you to send messages using SMTP and would have more impact on the server than using Database Mail.
You 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.
You 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.