SQL Server's Got Mail!

SQL Server's email functionality—which you can use to notify DBAs of potential problems and of task completion—is a powerful means of keeping your server running. In SQL Server 6.5, the SQL Mail function isn't the easiest to configure; SQL Server 7.0 improved the configuration process. (For information about how to configure SQL Mail on SQL Server 7.0 or 6.5, see "INF: How to Configure SQL Mail.") Now with SQL Server 2000, which can use either POP3 or Microsoft Exchange Server as email servers, the mail function is even easier to set up. Let's look at a couple of ways to configure SQL Mail for SQL Server 2000.

Configure SQL Mail

To configure SQL Server's mail profile in Windows 2000 Advanced Server by using Exchange as a mail server, you first establish an email account on your mail server. This email account is the account from which SQL Server can send and receive mail. I typically get a domain account that I use only for the MSSQLServer service and the SQLAgent service. You then affiliate the mail account with this domain account. Next, go to Control Panel, Services. Then, select the account that SQL Server uses for its service account. This account must exist in the domain account. Log in to the SQL Server system with the account affiliated with the email account you just created. After you log in, go back to Control Panel and select Mail. Make sure you stop and restart any affected services when you finish.

To create a mail profile, you can either add a new mail configuration or copy and modify an existing configuration. You complete the information as you would for any other email account, except that you make the host Messaging API (MAPI)-compliant. SQL Server requires MAPI-compliant host messaging so that it can connect with the mail host. I recommend that you use different accounts with appropriate names for each SQL Server to make mail management easier. For example, using different accounts lets you sort email from a development SQL Server separate from a production SQL Server so that you can prioritize any errors.

You then set up SQL Server's email function through Enterprise Manager by expanding the Server Group icon and expanding the server you want to send mail from. Select the Support Services folder under the server name, right-click SQL Mail, and select Properties. The Profile name option appears in the SQL Mail Configuration Properties dialog box, as Figure 1 shows. Enter a preconfigured profile from Exchange or select from the drop-down menu the profile that you just created. I recommend that you select Autostart SQL Mail when SQL Server starts in the SQL Mail Configuration dialog box to ensure that the DBA doesn't miss any messages. To find this option from the Server Group option, right-click the server name you want to send mail from and select Properties. As a safety precaution, you should also test an email account before you use it on a production system.

Add an Operator

Although you don't need a SQL Server operator to use some SQL Mail functions, I recommend that you set up an operator to make best use of the mail capabilities. An operator is an alias for an email account that some predefined functions look for. For example, if you want SQL Server to email someone if a backup fails, SQL Server sends the email to the predefined operator's email account. You add an operator to the Operators option under Management—SQL Server Agent by right-clicking Operator and selecting New Operator. You then complete the Name and Email input fields.

You might also want to set up a dba operator when you add your email operators. The name dba helps identify which operator to notify. Similarly, setting up an operator such as developer helps direct email to the operator most likely to respond to development-related emails. Whenever a DBA or primary developer leaves a job and a new person takes over, you just need to change or redirect the dba or developer operator's email address to ensure that the email goes to the correct operator. To change the operator's destination email address, select SQL Server Agent, Operators. Find the operator's name, edit that operator's properties, and change the email to the new destination.

Set Up Automated Notifications

After you configure the SQL Mail options and add the operators, you can configure the events that trigger error alerts, completion messages, or job success or failure messages. To configure alerts and warnings to use the email function, select Operators under Management—SQL Server Agent, right-click whichever operator you want to receive the email message, then select Properties. The Notifications tab, which Figure 2 shows, contains a list of potential operator alerts, including Full msdb log, Full tempdb, and errors with a severity level of 19 or higher.

I recommend that you select both Full msdb log and Full tempdb. A full msdb log halts the execution of all automated tasks because SQL Server can't make any log entries until you clear the log or allocate more space. A full tempdb stops the server from executing any transactions that require space in tempdb. I also recommend that you notify the operator for errors with a severity of 19 or higher, which indicate problems in system resources or batch termination. Errors with severity levels of 20 or higher are fatal errors that can crash the server. Each severity-level alert indicates that a server might be down or in trouble.

Modify Jobs to Use SQL Mail

Before a server reaches a state that limits its operability, you should perform backups—not only on all user databases but also on system databases. Whether you back up these databases through a maintenance plan or manually, you can configure SQL Mail to notify an operator of the backup's success or failure. However, configuring databases to notify operators about a backup's success is overly aggressive. The DBA can become accustomed to seeing successful backup emails and overlook a failure message. Email that arrives only upon failure receives the necessary attention.

After you configure SQL Server to use email, you can easily modify backup jobs and maintenance plans to use SQL Mail. One way to modify a maintenance plan is through the Maintenance Plan Wizard. To access the Maintenance Plan Wizard from Enterprise Manager, select a server, then expand the Databases folder. In the Database Information window, select the database you want to use the wizard for and select New Maintenance Plan. Or, to modify an existing plan, in Enterprise Manager select the server you want to modify a plan for and select the Management folder, then select the Database Maintenance Plans to view the server's existing plans. Double-click the plan name and select the Reporting tab. On the Reporting tab, you can add an operator to receive an email message about any of the wizard's operations. To add new operators, select New.

The steps to run a backup are similar with or without a maintenance plan. Without a maintenance plan, right-click the Backup icon and select Backup a Database to set up the job. To run a scheduled backup by using Enterprise Manager, select the Management folder, expand the SQL Server Agent, and click the Jobs option. You should see the scheduled backup job you're looking for. To edit the job, double-click the job name, select the Notifications tab (which Figure 3 shows), click E-mail operator, then select the operator (e.g., dba) that you want to receive the email. To choose the relative time to send the email, select When the job fails, When the job succeeds, or When the job has completed.

SQL Mail and Stored Procedures

The SQL Mail function has more applications than just administration. You can also use the xp_sendmail extended stored procedure in triggers, stored procedures, and batch commands to incorporate the mail function into code. The xp_sendmail extended stored procedure is in the master database, but with the proper syntax, you can call it from any database. The proper syntax for calling xp_sendmail from another database is

master.dbo.xp_sendmail

followed by any of the input parameters in Table 1 (a list of these parameters is also available in SQL Server Books Online—BOL).

You should be aware of the following rules when using the xp_sendmail extended stored procedure:

Using xp_sendmail from SQL Server with the proper planning about when and who should receive these email messages can simplify administration and troubleshooting.

The xp_sendmail extended stored procedure makes life easier for developers and DBAs. For example, let's say the Get-Big Health Club's salespeople must get only basic information from new clients. Other departments gather more in-depth information about accounting or prior health status. But how do other departments know when they need to gather this information about a new client? One solution is to set up a job that executes every night and searches for any missing accounting or health information. The job then sends the results to the appropriate departments for follow-up. A report could notify these departments of new clients every 24 hours to ensure that the data is complete.

Another possible solution is to set up a trigger on the table into which the salespeople enter the customer information. The trigger could then recognize any new records in the table regardless of the source, then send email immediately to the appropriate department. One thing to keep in mind if you use xp_sendmail in a trigger is that it can't refer to the logical inserted and deleted tables. Instead, you might need to refer to a datetime field or search for missing data.

Xp_sendmail can notify a specific operator if an automated task fails. For example, suppose the accounting department runs reports at night because the Get-Big Health club is open 24 * 7 but the majority of database traffic is during daylight hours. Some tables won't change every day. But because the club populates these tables every night, the report programs might not be able to tell that a failure occurred. You could add a conditional branch in the stored procedure to populate the table if the @@error system variable returns anything but 0. Then, the stored procedure could send an email message to the operator responsible for troubleshooting the reports, as the following example shows:

IF @@error <>0  BEGIN    Master..xp_sendmail @recipients =  "[email protected]",      @message = "Report of new clients failed.",      @subject = "Failure"END

Such a solution could let the reporting department know in the morning that the data population failed and needs attention.

Another solution that might be more efficient if you run many reports at night is to create a table to store the various statuses of a night's reports. When all the reports should have finished, a stored procedure could execute and send an email message about that night's activities. To implement this solution, create a table like the following:

CREATE TABLE report_status(report_name varchar(100) NOT NULL,start_time datetime NOT NULL,finish_time datetime NULL)GO

The reports that execute insert their names and start times at the beginning of the report. When a report finishes, it updates the existing record with its finish time. Because the report also records the finish time, the existence of NULL values would signify that certain reports took an inordinate amount of time to run. Report Status could be another stored procedure. You could then set up a stored procedure to select all the table's records and send an email message containing the status of the previous night's reports to the responsible party, as the following example shows:

CREATE PROCEDURE sp_send_report_statusAS    Master..xp_sendmail @recipients =    "[email protected]",    @query = "SELECT * FROM report_status",    @subject = "Status Report"GO

You could add a line to the sp_send_report_status stored procedure to send an email message to the DBA if the stored procedure found a NULL value in the finish_time field; this email would notify the DBA that a running report is having problems. To make sure the report_status table didn't grow too large, you would either have to archive the report_status table often or truncate it on occasion.

Neither Rain Nor Sleet

Take advantage of SQL Server's email funtionality to help keep your server running. You can set up SQL Mail to notify the appropriate operator of potential problems, or you can use the xp_sendmail extended stored procedure for the same purpose. With SQL Server 2000, the email function is easier to set up than ever before. By using email to notify an operator of a potential problem, tasks that fail get the attention they need. However, sending both success and failure messages might lessen the impact of messages requiring attention. Remember, neither rain nor sleet nor any other weather situation can keep SQL Mail from its intended rounds.