Database Mail is an essential part of database automation. You must configure Database Mail so that alerts and other types of messages can be sent to administrators or other users. Database Mail provides SQL Server with the ability to generate and send e-mail messages as a mail client using the Simple Mail Transfer Protocol (SMTP). The Database Mail configuration process:
Database Mail is a full-featured replacement for SQL Mail. Database Mail acts as a mail client and sends its messages to designated SMTP servers. Any SMTP server including Microsoft Exchange can receive and deliver messages generated by Database Mail.
Like most mail clients, Database Mail uses mail profiles and mail accounts to send e-mail messages. The profile defines the mail environment Database Mail will use and can be associated with one or more SMTP mail accounts. Because the mail accounts are used in priority order, you can configure multiple accounts on different mail servers as a safeguard against mail server failure or network problems that could prevent message delivery and then configure the Database Mail profile to use these separate accounts. If mail cannot be delivered to the first account listed in the profile, the second one is tried, and so on.
The mail profile can be public or private. A public profile is available to any user or application for any configured database mail host on the current server instance. A private profile is only available to explicitly defined users and applications. If you are configuring Database Mail for SQL Server Agent or a specific application, you will usually want to use a private profile. If you are configuring Database Mail for general use, you will usually want to use a public profile.
Before you configure Database Mail, you should create the SMTP accounts Database Mail will use or have your organization’s mail administrator do this. If you are configuring Database Mail for SQL Server Agent, it is a good idea to have the e-mail address and account name to reflect this. For example, set the user name as SQL Agent and the e-mail address as <[email protected]>. To configure Database Mail, you need the account user name, e-mail address, and SMTP server name. If the SMTP server requires authentication, and most do, you will also need the logon user name and password for the account.
Real World
Some database maintenance tasks may require exclusive access to SQL Server. For example, if a database on the current server instance is in single-user mode and there is an active connection to the database, you may not be able to perform a maintenance task. You need to put the database back in multiuser mode before continuing. If you cannot get access to the database to put it back in multiuser mode, you can force the database mode change by following these steps:
Log on to the server and start a command prompt. Use the NET STOP command to stop the SQL Server instance you want to change. For example, if you wanted to stop the default SQL Server instance, you would type net stop mssqlserver.
Use the CD command to change to the Binn directory for the SQL Server instance. For example, type cd "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn" or cd C:Progra~1Micros~1 MSSQL.1MSSQLBinn.
Put the database in single-user mode by typing sqlservr –m.
Start a second command prompt and open a dedicated administrator connection to SQL Server by typing sqlcmd–A. Be sure to provide a user name and password if necessary, using the –U and –P parameters.
Perform the necessary maintenance tasks using sqlcmd.
Set the database in single-user mode back to normal mode using sp_dboption. For example, if the database is named cust, you would type:
use master exec sp_dboption 'cust', 'single user', 'FALSE'; go
At the first command prompt (where SQL Server is running), press Ctrl+C to stop SQL Server. When prompted to confirm, type Y.
Start the SQL Server instance you are working with by using NET START. For example, if you wanted to start the default SQL Server instance, you would type net start mssqlserver.
You can use SQL Server Management Studio to configure Database Mail for the first time by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server instance of your choice, and then expand the server’s Management folder.
Right-click Database Mail, and then select Configure Database Mail. This starts the Database Mail Configuration Wizard. Click Next.
To configure Database Mail for the first time, accept the default value of Setup Database Mail, and then click Next.
When prompted to enable the Database Mail feature, click Yes.
On the New Profile page, type the name and description of the mail profile that Database Mail will use, such as Mail Profile For SQL Server Agent. The profile is used to define the mail environment Database Mail will use.
To specify an SMTP account the profile will use to send e-mail messages, click Add. This displays the New Database Mail Account dialog box shown in Figure 15-1.
In the Account Name and Description text boxes, enter the name and description of the account you are configuring for use with Database Mail. This information is only used with Database Mail and is shown in SQL Server dialog boxes.
In the E-Mail Address box, type the e-mail address of the Database Mail account, such as <[email protected]>.
In the Display Name box, type the name that will appear in the From field of outgoing messages.
In the Reply E-Mail box, type the e-mail address to which replies to Database Mail messages can be sent. For example, if you want administrators to send follow-up information to a lead administrator, you would put the administrator’s e-mail address in the Reply E-Mail text box.
In the Server Name box, type the host name of the mail server, such as smtp. You can also type the fully qualified domain name of the mail server, such as smtp.cpandl.com. Using the full domain name ensures a successful connection when the mail server is in a different domain.
Database Mail will need to log in to the mail server to submit mail for delivery. Select the appropriate authentication option SMTP, based on your mail server configuration:
Windows Authentication Using Database Engine Service Credentials. SQL Mail logs in to the designated mail server using the credentials of the SQL Server service (MSSQLService) for the current Database Engine instance.
Basic Authentication. SQL Mail logs in to the designated mail server using the user name and password you have provided. Enter a user name for the account. Type and confirm the password for the account in the text boxes provided.
Anonymous Authentication. SQL Mail logs in to the designated mail server as an anonymous user. The mail server must be configured to allow anonymous log in (which is not a good security practice).
Click OK to close the New Database Mail Account dialog box.
Repeat steps 6 through 13 to specify other mail accounts to associate with the Database Mail profile. The account listed first is the account that Database Mail will try to use first. As necessary, use the Move Up and Move Down buttons to set the usage priority for multiple accounts. Click Next.
If you are creating a public profile, select the Public check box on the Public Profiles tab. To make the profile the default for all mail host databases and users, set Default Profile to Yes (see Figure 15-2).
If you are creating a private profile, select the Private Profiles tab. Use the User Name drop-down list to select a user to which you will grant profile access. The default user is the SQL Server Agent service account. After you select a user on the drop-down list, select the Access check box to grant access to the profile, and then repeat as necessary to grant access to other users. To make the profile the default for the selected mail host database and user, set Default Profile to Yes.
System parameters are used by all database mail hosts configured for a SQL Server instance. Configure the default system parameters using the following options, and then click Next:
Account Retry Attempts. Sets the number of times to retry sending the message. The default is 1. If you have configured multiple accounts, this may be sufficient because it provides for one retry. However, when you are configuring mail for SQL Server Agent, you usually want to set this to try three to five times to send a message.
Account Retry Delay. Sets the delay (in seconds) between retry attempts. The default is 60 seconds, which is far too long if Database Mail is trying to deliver critical alerts. A retry delay of 30 to 60 seconds may be preferred when you are configuring mail for SQL Server Agent.
Maximum File Size. Sets the maximum size (in bytes) for any generated message, including headers, message text, and included attachments. The default is 1,000,000 bytes (976 KB). When you are configuring mail for SQL Server Agent, this is usually sufficient. If applications generate messages which include graphics or multimedia, however, this may not be sufficient.
Prohibited Attachment File Extensions. Sets the types of files that cannot be sent as attachments according to their file extension. To prevent abuse of Database Mail, a more inclusive list would include all file extensions designated as high risk by Attachment Manager in Group Policy, including: .ade, .adp, .app, .asp, .bas, .bat, .cer, .chm, .cmd, .com, .cpl, .crt, .csh, .exe, .fxp, .hlp, .hta, .inf, .ins, .isp, .its, .js, .jse, .ksh, .lnk, .mad, .maf, .mag, .mam, .maq, .mar, .mas, .mat, .mau, .mav, .maw, .mda, .mdb, .mde, .mdt, .mdw, .mdz, .msc, .msi, .msp, .mst, .ops, .pcd, .pif, .prf, .prg, .pst, .reg, .scf, .scr, .sct, .shb, .shs, .tmp, .url, .vb, .vbe, .vbs, .vsmacros, .vss, .vst, .vsw, .ws, .wsc, .wsf, and .wsh.
Database Mail Executable Minimum Lifetime. Sets the minimum time for Database Mail to run while generating a message. The lifetime should be set to optimize usage of the Database Mail executable file. You do not want the server to create the related objects in memory and then remove them from memory over and over again. You do want the related objects to be cleared out when they are not needed. The default 600 seconds (10 minutes) is typically sufficient.
Logging Level. Determines the level of logging with regard to Database Mail. The default value, Extended, configures Database Mail to perform extended logging of related events. To reduce logging, you can set the level to Normal so only important events, such as warnings and errors, are logged.
Review the setup actions that will be performed, and then click Finish. The Configuring page shows the success or failure of each action. Click the link provided for any error message to see details about the error that occurred, and then take any necessary corrective action. Click Close.
Tip
If you are enabling Database Mail for use with SQL Server Agent service, you must ensure the service is running and configured for automatic startup. See the subsection titled "Configuring the SQL Server Agent Service" later in this chapter for details. You can check the status of SQL Server Agent in Object Explorer view in SQL Server Management Studio. If the service is not running, right-click the SQL Server Agent node, and then select Start.
Database Mail can be configured to use one or more mail profiles, and each of those mail profiles can have one or more mail accounts associated with it. Database Mail profiles can be:
Public. Available to any user or application on the current server instance.
Private. Available only to explicitly defined users and applications.
Database mail accounts are used in priority order and are a safeguard against mail server failure or network problems that could prevent message delivery. If mail cannot be delivered to the first account listed in the profile, the second one is tried, and so on.
To manage profiles and their accounts or add a profile, follow these steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server instance of your choice, and then expand the server’s Management folder.
Right-click Database Mail, and then select Configure Database Mail to display the Database Mail Configuration Wizard. Click Next.
Select Manage Database Mail Accounts And Profiles, and then click Next.
If you have configured multiple database mail hosts on this server instance and want to define separate profiles for these database mail hosts, select Create A New Profile, click Next, and then follow steps 5 through 13 in the subsection titled "Performing the Initial Database Mail Configuration" earlier in this chapter to define the new profile and the accounts associated with this profile.
If you want to modify an existing profile or add an account to an existing profile, select View, Change, Or Delete An Existing Profile, and then click Next. Use the Profile Name drop-down list to select the profile to manage. You can then add, remove, or prioritize accounts for this profile, as discussed in steps 6 through 13 in the subsection titled "Performing the Initial Database Mail Configuration" earlier in this chapter to define the new profile and the accounts associated with this profile.
Click Next, and then click Finish. The Configuring page shows the success or failure of each action. Click the link provided for any error message to see details about the error that occurred, and then take any necessary corrective action. Click Close.
To set a mail profile as public or private, follow these steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server instance of your choice, and then expand the server’s Management folder.
Right-click Database Mail, and then select Configure Database Mail to display the Database Mail Configuration Wizard. Click Next.
Select Manage Profile Security, and then click Next.
The Public Profiles tab shows public profiles. Clear the Public check box if you want to make a profile private. To make a public profile the default for all mail host databases and users, set Default Profile to Yes.
The Private Profiles tab shows private profiles which are accessible only to a specific database and user. Use the drop-down lists to select the database and user for which you want to configure a private profile. After selecting a user on the drop-down list, select the Access check box to grant access to the profile and repeat as necessary to grant access to other users. To make a private profile the default for the selected mail host database and user, set Default Profile to Yes.
Click Next, and then click Finish. The Configuring page shows the success or failure of each action. Click the link provided for any error message to see details on the error that occurred, and then take any necessary corrective action. Click Close.
Database Mail system parameters are set globally for each SQL Server instance. If you want to manage the global system parameters for Database Mail, follow these steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server instance of your choice, and then expand the server’s Management folder.
Right-click Database Mail, and then select Configure Database Mail to display the Database Mail Configuration Wizard. Click Next.
Select View Or Change System Parameters. Click Next.
Make changes as appropriate to the system parameters. See the subsection titled "Performing the Initial Database Mail Configuration" earlier in this chapter for details on configuring individual parameters.
Click Next, and then click Finish. The Configuring page shows the success or failure of each action. Click the link provided for any error message to see details about the error that occurred, and then take any necessary corrective action. Click Close.