Alerts and Operators

To master SQL Server 7.0's automated administrative tasks, you need to know how to configure alerts and set up operators, the people SQL Server contacts when alert conditions arise. The SQL Server 7.0 Administration (70-028) online exam guide includes the following task automation topics: defining jobs, defining alerts, defining operators, and setting up SQL Mail Agent for job notification and alerts. Jobs can trigger alerts, for example, when a scheduled job fails or when a log file fills up. You can also define alerts to trigger jobs in response to an alert condition. This article shows you how to set up alerts and operators. Next month, I'll show you how to use some SQL Server 7.0 enhancements to define jobs and automate administrative tasks. I'll cover the more complex task of setting up SQL Server as a mail client in a future column.

Related: Practice Questions: Alerts and Operators

New in SQL Server 7.0

In SQL Server 6.5 and earlier, Performance Monitor must be running for you to receive alerts. Having Performance Monitor running all the time drags down overall performance. In addition, the alert process in these versions is complex: SQL Server updates its Performance Monitor counters, which then might exceed the critical value you set in the Performance Monitor Alerts module, activating a program that notifies the SQL Server Executive, which triggers the alert.

SQL Server 7.0's alert process is much improved and doesn't require Performance Monitor to be running to fire the alert. Also, you no longer have to configure the alerts, counters, and critical values in Performance Monitor; you can set them all within the SQL Server Agent. However, the biggest change in SQL Server 7.0 is that you can use a net send command, instead of an email message, to notify an operator of a problem. Net send is easier to configure than email although not as flexible. For example, you can't receive a net send notification at home when a task fails. Then again, that limitation could be a benefit.

Creating a New Alert

To see existing alerts defined on a server, access SQL Server Enterprise Manager and expand the hierarchy so that you see the name of the server you want to work with. Then, expand the underlying Management item to show the SQL Server Agent, which you expand to see the Alerts, Operators, and Jobs items. Click Alerts.

Screen 1 shows some alerts already listed in the right window. If you haven't added alerts on your server, you'll see nine alerts, all beginning with the word Demo. These alerts cover severity level 19 through 25 errors (fatal errors) and a full msdb log and tempdb database. Because these alerts are examples, they don't have any responses defined. After you set up your operators, you might want to return to these alerts and set up responses for them. If you do set up responses, remove the word Demo so that you'll know these alerts are real.

To create a new alert, right-click Alerts or anywhere in the right window under the predefined demo alerts, then select New Alert. The resulting New Alert Properties dialog box you see in Screen 2, page 66, lets you enter a name for the alert, such as Backup Alert. The Backup Alert is an event alert, so you need to associate it with an event and the message that event returns. (I show you how to set up a performance alert later in the article.) Select the Error number option, then click the ellipsis to the right of the Error number box. (Ignore the Not a valid error number message that pops up next to the box; the message always appears when you first configure the alert because the Error number box is blank.) From the resulting Manage SQL Server Messages dialog box, which Screen 3 shows, you can locate the error messages a backup problem might generate. Clicking Find with no criteria specified displays a list of the hundreds of error messages defined on the server. To narrow your search, click the Search tab, type backup in the Message text contains window, then click Find. Now you should see only 58 messages.

The messages' severity levels vary from 10 to 17, with the 10s being informational. Again, click the Search tab, and in the Severity box, click level 013. Hold down the Ctrl key, select levels 014 through 025, then click Find. Doing this eliminates the level 10 messages, leaving only severity level 16 and 17 messages. Note that SQL Server marks some of the messages as logged, which means the database writes these messages to the Windows NT event log when they happen. However, error 3013, which reports the abnormal termination of a backup job, isn't logged. For this example, select error 3013. To see the full text of any message, click Edit. You can also add messages from this interface, although you would probably do so while building an SQL application. After choosing the error message you want to define for the alert, click OK to close the Manage SQL Server Messages dialog box.

Note that if you had seen error 3013 during your backups, you could have searched for that error number and set an alert for it. You also could have set the alert for any error containing a specified text string. For example, you could set the alert for severity level 17 errors whose error message contains the word backup. Finally, you can define an alert for one selected database or for all databases, but you can't apply an alert to multiple databases without applying it to all databases.

Creating a Response

With your alert set up, the next step is to select the appropriate response. From the New Alert Properties dialog box, click the Response tab. You'll see the option to respond by running a job. You can either select a job that you've already set up in the SQL Server Agent or define a new job. Let's say you choose a job from the list of jobs already defined. You can then click the ellipsis to the right of the job name to bring up the Jobs window, which lets you check or change the job's actions and properties.

If, instead of running a job, you want to respond to the alert by notifying someone or a whole group of people about the backup problem, you must set up an operator definition for each person you want to notify. SQL Server can send each operator an email, net send, or pager message. (To receive a pager message, however, you must use an email-enabled pager; SQL Server won't dial a telephone number to send the message. For more information about using SQL Server with a pager, see "Check SQL Server's Status," page 17.) To give the operators more information, you can include the error message text, instead of just the error number, and other notification information, such as the name of the database for which SQL Server generated the alert or the problem's urgency.

To keep operators' pagers from constantly going off or their mailboxes from filling up with the same alert, set an appropriate delay for how long SQL Server waits for a response before resending the alert. The default delay of 1 minute is too short for many situations. Assuming backups occur at night or on the weekend, when the operator isn't in the building and won't be able to respond immediately, you could set the delay for 15 minutes.

Defining an Operator

You can set up operators from the SQL Server Agent's Operators item before you set up any alerts. Alternatively, you can set up new operators from the Response tab on the New Alert Properties dialog box. First, click New Operator to display the New Operator Properties dialog box, which Screen 4 shows. You supply the operator's name and email addresses for the operator and the operator's pager, if applicable. You must configure SQL Server's SQL Mail option before SQL Server can send email messages. But for testing the alert, you need supply only a net send address. The address can be either the name of the computer you use to administer the server or the username for the account you use to log on to your computer. You can then use the Test option to send a test message to each address you supplied.

The New Operator Properties' Pager on duty schedule section asks you to define Workday begin and Workday end times. SQL Server will page the operator only between these times. To configure SQL Server to send messages to operators after usual business hours, make sure you set Workday begin to 5:00 p.m., for example, and Workday end to 8:00 a.m. Because many DBAs work strange hours, the safest course of action might be to set the pages to happen any time; the DBAs can then turn off their pagers when they're not on call. You can then click OK to close the New Operator Properties dialog box.

If you define your alerts before defining your operators, go back to the New Alert Properties dialog box and verify that you selected the appropriate operator address for SQL Server to use. No matter what address you enter in the New Operator Properties dialog box, SQL Server uses the address you selected in the alert definition. To complete the alert setup, click OK.

NT Event Logging Required

After you click OK, SQL Server will display a pop-up box that tells you error 3013 can't invoke the alert because the database, by default, doesn't log that error. SQL Server will send the alert only if the database writes the error to the NT event log, which passes the information to the SQL Server Agent, which then sends the alert. The pop-up box asks whether you want this error to always invoke the alert. If you select Yes and then display the list of error messages again, you'll find that SQL Server has marked error 3013 to be logged.

You can now find the Backup Alert in the list of alerts in Enterprise Manager's right window. To open the alert for editing or to see when the database last activated it, double-click the alert. You'll see that the alert has the Enabled check box selected. However, you can disable an alert for troubleshooting or other maintenance. In the SQL Server Agent's Operators window, you should see the new operator you defined. Double-click the operator name, then select the Notifications tab to see the new Backup Alert that lists the new operator. This display features the option Operator is available to receive notifications. If an operator goes on vacation or moves to a different position, you can clear this option.

Performance Alerts

So far, you've seen how to set up event alerts, but you can also set up performance alerts in SQL Server 7.0 to notify operators of conditions that might slow down database processing. To define a new performance alert, right-click in the SQL Server Agent's Alerts window, then select New Alert from the pop-up menu. At the New Alert Properties dialog box, select the Type box, then choose the drop-down option SQL Server performance condition alert. The resulting New Alert Properties dialog box will display drop-down boxes for the object and counter you want to monitor and the instance, if appropriate, for the counter. You can then set the threshold at which SQL Server will activate the alert. You can define the threshold as a value the counter exceeds or drops below. For example, you could set SQL Server to send a performance alert when the Percent Log Used counter for the SQL Server:Databases object exceeds 80 percent for a database.

After you've set up some alerts and operators, you're ready to define jobs. But that discussion is for next month's column. For now, test your knowledge of alerts and operators by taking the quiz "Practice Questions: Alerts and Operators" at http://www.sqlmag.com. For more examples of how to define alerts and operators, see the lab files that accompany this article at http://www.sqlmag.com.