Page 107 - DCAP508_DATABASE_ADMINISTRATION

Unit 7: Automating Administrating Tasks

To start the Database Mail Configuration Wizard Notes
1. From Object Explorer, connect to an instance of SQL Server.
2. Expand Management, right-click Database Mail, and select Configure Database Mail.
3. Choose the Set up Database Mail option to set up Database Mail for the first time.

Notes Enabling SQL Server Service Broker in any database requires a database lock. If
Service Broker was deactivated in msdb, to enable Database Mail, first stop SQL Server
Agent so that Service Broker can obtain the necessary lock.
4. Choose one of the other options for specific maintenance tasks:
i. Manage Database Mail accounts and profiles.
ii. Manage profile security.
iii. View or change system parameters.

7.1.6 Database Mail Architecture

Database Mail consists of the following main components:
1. Configuration and security components: Database Mail stores configuration and security
information in the msdb database. Configuration and security objects create profiles and
accounts used by Database Mail.
2. Messaging components: The msdb database acts as the mail-host database that holds the
messaging objects that Database Mail uses to send e-mail. These objects include the
sp_send_dbmail stored procedure and the data structures that hold information about
messages.
3. Database mail executable: The Database Mail executable is an external program that reads
from a queue in the msdb database and sends messages to e-mail servers.

4. Logging and auditing components: Database Mail records logging information in the
msdb database and the Microsoft Windows application event log.
You do not have to have a complete understanding of the Database Mail architecture to use
Database Mail effectively. However, understanding the Database Mail components and how the
components interact might help you design your applications and troubleshoot any problems
that may occur.
The following Figure 7.1 shows an overview of the Database Mail architecture.
Database Mail is designed on a queued architecture that uses service broker technologies. When
users execute sp_send_dbmail, the stored procedure inserts an item into the mail queue and
creates a record that contains the e-mail message. Inserting the new entry in the mail queue
starts the external Database Mail process (DatabaseMail.exe). The external process reads the e-
mail information and sends the e-mail message to the appropriate e-mail server or servers. The
external process inserts an item in the Status queue for the outcome of the send operation.
Inserting the new entry in the status queue starts an internal stored procedure that updates the
status of the e-mail message. Besides storing the sent, or unsent, e-mail message, Database Mail
also records any e-mail attachments in the system tables. Database Mail views provide the status
of messages for troubleshooting, and stored procedures allow for administration of the Database
Mail queue.

LOVELY PROFESSIONAL UNIVERSITY 101