Tracking SQL Server crises: Designating operators for automatic alerts

Learn how to complete the three steps of the process to designate an operator to create an automatic alert.

SQL Server alerts provide an elegant administrative tool to automatically notify pre-designated operators of server event occurrences. Organizations benefit quickly, as alerts make their DBAs more proactive to conditions requiring their attention.

You can enable automation of alert notification on the SQL Server 7 platform using the SQLServerAgent service in Windows NT. This service scans through SQL Server event logs and compares each entry with the conditions specified by the alert specification. The service fires off an alert when it finds a match.

Some of the conditions that should be set to trigger alerts include:
  • Fatal hardware or software errors
  • Abnormal termination of SQL Server
  • High CPU utilization
  • Syntax error in user transaction

At the occurrence of an event, the service can perform the following actions:
  • Execute a job
  • Send an e-mail message
  • Send notification to a pager
  • Notify a networked user

Configuring alerts on a SQL Server
Configuring SQL Server alerts is a three-step process:
  • Creating an operator to receive notification
  • Creating alerts by specifying which event(s) to trap
  • Configuring the server for a particular alert mechanism (e-mail, pager, etc.)

Creating operators
The first step in configuring alerts is to designate one or more operators as intended recipients. These operators will be notified by using one of the methods we mentioned above. You can create an operator either using Enterprise Manager or Transact-SQL by calling the sp_add_operator stored procedure. This article will describe the process of using Enterprise Manager.

Begin by clicking to expand Management | SQL Server Agent | Operator. Right-click on Operators and choose New from the pop up menu. In the Name field on the General tab, enter the name of the operator (which is limited to 128 characters). ID: New will indicate that a new operator is being created.

In the Email Name field, enter the operator’s e-mail address if the operator will be notified by e-mail. You need to configure SQLMail (described later in this article) for this option to work.

In the Pager E-mail Name box, enter the pager address of the operator. This option also requires configuration of SQLMail. SQL Server will send an e-mail to the gateway configured on the Email To Pager Gateway, which will, in turn, dial through a modem to send the page to the operator.

You must either install a third-party gateway or subscribe to this service. If you subscribe to this service, you will be given an e-mail address that is unique to your pager. Usually, the e-mail address will look like this:

This includes area code, pager number, pager pin, and an Internet domain name of an e-mail-to-pager gateway.

In the Net Send Address field, specify the recipient (computer or user) of a network message. This method is not supported on Microsoft Windows 9x operating systems.

You can use the Test button to test e-mail, pager, or net send notification.

If a Microsoft Outlook or Exchange client is installed, you can click on the browse button (…)  to get e-mail or pager names from the address book.

In the Pager On Duty Schedule frame, click the days when the operator is available to receive pager notifications. In the Notification tab, select whether the operator will be notified by an alert or a job will be executed. Specify the alerts you’d like sent to this operator. Also, specify the mechanism of alert: e-mail, pager, or net send.

Next, specify operator availability by selecting the Operator Is Available To Receive Notification check box. To enable the e-mail notification, select Send E-mail. The Most Recent Notification Attempts frame will show you the history of latest attempts made to contact the operator via e-mail, pager, or net send.

In tomorrow's follow-up article, I’ll explain how database administrators can configure and test alerts.
If you'd like to share your opinion, please post a comment below or send the editor an e-mail.

Editor's Picks

Free Newsletters, In your Inbox