Planning for Database Mail
Planning for Database Mail consists of planning the management of the database mail tables in the msdb database, and deciding which Simple Mail Transport (SMTP) server or servers to use, and configuring mailboxes on those SMTP servers.
Planning for the msdb Tables
Plan a retention policy for e-mail messages and log entries. Database Mail retains outgoing messages and their attachments in the msdb database. If the volume of Database Mail messages and related attachments is high, plan for substantial growth of the msdb database. Delete messages to regain space and to comply with your organizations document retention policies. For example, you can create a SQL Server Agent job to delete messages, attachments, and log entries older than 30 days. For more information, see:
sysmail_delete_mailitems_sp (Transact-SQL)
sysmail_delete_log_sp (Transact-SQL)
Use the MaxFileSize argument of sysmail_configure_sp to limit the size of attachments in Database Mail messages.
Use the ProhibitedExtensions argument of sysmail_configure_sp to restrict the type of attachments sent in Database Mail messages.
Note
SQL Server Service Broker is enabled by default in msdb database but may be deactivated if you attached an msdb database. Enabling 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 Service Broker can obtain the necessary lock.
Deciding on an SMTP Server
The Database Mail external program requires network access to the SMTP servers specified in Database Mail accounts. Therefore, the service account for SQL Server must have permission to access the network, and the SMTP servers must allow connections from the computer that runs SQL Server.
.gif "Security note") Security Note |
|---|
If SQL Server runs as Local System or Local Service, SQL Server does not have permission to make outgoing network connections. In this case, Database Mail cannot contact an e-mail server located on a different computer. |
Consider the number of messages that you expect Database Mail to send each day and the load that this will place on your SMTP server. For example, if you plan to use Database Mail only for notifications from Agent jobs, the load may be relatively light. In contrast, if you are stress testing an application in a development environment, your testing may generate a large amount of e-mail, and you may want that e-mail to go to a server created especially for testing.
Determine the security requirements for your e-mail communication. Database Mail supports Secure Sockets Layer (SSL) if it is required by the SMTP server. Database Mail can make an anonymous connection to the SMTP server if the server accepts such connections, or can provide credentials for basic authentication. If connecting using Windows Authentication, Database Mail uses the credentials of the Windows service for the SQL Server Database Engine to authenticate on the SMTP server.
Configuring Mail Accounts on the SMTP Server
Database Mail accounts do not require any special privileges on the SMTP server. However, these accounts should generally be used only for Database Mail.
For information about how to configure accounts on the SMTP server, see the documentation for your e-mail server.
To set up Database Mail after SMTP configuration
In Object Explorer, in SQL Server Management Studio, expand the Management folder.
Right-click Database Mail, and then select Configure Database Mail. The wizard will guide you through the setup process.