Lesson 1: Defining Operators | MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228

3 4

The first step toward automating administrative tasks is to define operators to be notified of the success, failure, or completion of an automated task, or on the occurrence of specified events or conditions. In this lesson you will learn to define operators who can be notified by e-mail, pager, or NET SEND notifications. You will also learn how to create a fail-safe operator to be notified in response to an alert when the designated operator for the alert cannot be paged.

After this lesson, you will be able to

Define operators
Create a fail-safe operator
Troubleshoot operator notification problems

Estimated lesson time: 30 minutes

Methods of Notification

SQL Server Agent can be configured to send notifications to operators with respect to jobs, events, and performance conditions. An operator is a user or message group that is configured to receive notifications from SQL Server Agent using one of three messaging methods: e-mail, pager, or NET SEND.

E-mail

SQL Server Agent can notify an operator using e-mail provided that SQLAgentMail has been configured. As discussed in the previous chapter, SQLAgentMail requires that the SQL Server Agent service use a domain user account. This domain user account must have a MAPI messaging profile on the computer on which SQL Server Agent is running.

Pager

SQL Server Agent can also notify an operator using a pager. Pager notification is implemented using e-mail and third-party paging software. Because pager notification relies on e-mail, SQLAgentMail must be configured in order to enable pager notification.

Note

SQL Server 2000 does not provide any pager software. It relies on you to implement a third-party pager solution. If all operators use the same pager solution, you can provide any special e-mail formatting required for all pager e-mails, including limiting the size of the message (necessary for some pagers), by eliminating the error text. To access this feature in SQL Server Enterprise Manager, expand the Management container for the instance, right-click SQL Server Agent, click Properties, and then click the Alert System tab in the SQL Server Agent Properties dialog box.

NET SEND

SQL Server Agent can also notify an operator via network pop-up using NET SEND. NET SEND is available only with the Windows 2000 and Windows NT 4.0 operating systems. NET SEND uses the Windows Messenger service, which must be running on the recipient computer as well as the sending computer. Messages can be sent to users, computers, or messaging names on the network. A messaging name is an alias that a computer will accept messages for and can be created using the NET NAME command-prompt utility.

Fail-Safe Operators

SQL Server Agent can be configured to notify a fail-safe operator in response to an alert if the designated operator cannot be paged or the SQL Server Agent cannot access system tables in the msdb database. Possible reasons for the inability to page an operator include an incorrect pager address or the designated operator being off duty according to the pager's on-duty schedule that you configured when you created a new fail-safe operator. A reason for the inability to access the system tables is disk failure.

Creating Operators

An operator can be an individual, a messaging group, or a computer that can be contacted using one of the three notification methods. You should create a notification plan for all operators, including on-duty schedules and pager addresses. You can create operators using either SQL Server Enterprise Manager or Transact-SQL system stored procedures.

Note

Using e-mail and pager notifications is only as reliable as your messaging infrastructure.

Using SQL Server Enterprise Manager

To create an operator using SQL Server Enterprise Manager, expand the Management container for the instance, expand the SQL Server Agent container, right-click the Operators container, and then click New Operator to display the New Operator Properties dialog box. See Figure 13.1.

figure 13.1 - creating a new operator.

Figure 13.1

Creating a new operator.

Each operator must have a unique name. In the General tab, you can specify address information for all three types of notifications. You can test the address information (and the underlying infrastructure) by sending a test message. If the message is received by the operator using a particular notification method, the address information for that method is valid and the underlying infrastructure is functioning. You can also specify pager on-duty schedules, including the length and hours of the workday.

Note

Always use fully qualified e-mail addresses to avoid name resolution problems with e-mail aliases. For example, if the display names JohnD and JohnDoe both exist in your e-mail address book, a notification to JohnD cannot be resolved.

You can immediately configure notifications to be sent to this newly created operator by clicking the Notifications tab. See Figure 13.2.

Notice that you can select one or more existing alerts and then designate this new operator to receive notifications with respect to these alerts. You can immediately send e-mail to the operator detailing the newly assigned alert responsibilities. You can also view the most recent statistics on notification attempts for this
operator.

Note

Use the Notifications tab for an existing operator to view the alerts and jobs for which the operator is designated to receive notification.

figure 13.2 - viewing and configuring operator notifications.

Figure 13.2

Viewing and configuring operator notifications.

Using Transact-SQL

You can create and update operators using the sp_add_operator and sp_update_operator system stored procedures. You can view information about currently defined operators using the sp_help_operator system stored procedure.

Note

Although you can use Transact-SQL system stored procedures, use of the SQL Server Enterprise Manager graphical interface is recommended. Use the Generate Script feature to archive SQL Server objects created with SQL Server Enterprise Manager.

Creating a Fail-Safe Operator

To create a fail-safe operator using SQL Server Enterprise Manager, expand the Management container for the instance, right-click SQL Server Agent, and then click Properties. In the SQL Server Agent Properties dialog box, click the Alert System tab. See Figure 13.3.

You can select an existing operator as the fail-safe operator from the Operator drop-down list, or you can create one on the fly by selecting New Fail-Safe Operator from the drop-down list. Once you designate an operator as the fail-safe operator, you cannot delete the operator until you designate a different operator or select no fail-safe operator.

figure 13.3 - creating a fail-safe operator.

Figure 13.3

Creating a fail-safe operator.

Practice: Creating Operators and Setting a Fail-Safe Operator

In this practice you use the SQL Server Enterprise Manager to create operators and to set a fail-safe operator. (This practice uses NET SEND because not all readers will have a MAPI client installed. However, if you have a MAPI client installed, you might want to configure SQLAgentMail according to the previous chapter and then create e-mail operators for the practice exercises in this chapter.)

To create operators and set a fail-safe operator

Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
Click Start, point to Programs, point to Accessories, and then click Command Prompt.
The Command Prompt window appears.
Type net name failsafe /add, and then press Enter.
The operating system informs you that the message name FailSafe is added successfully.
Close the Command Prompt window.
Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
In the console tree, expand the Microsoft SQL Servers container, expand the SQL Server Group container, expand the default instance container, expand the Management container, and then expand the SQL Server Agent container.
Right-click the Operators container, and then click New Operator.
The New Operator Properties - SelfPacedCPU dialog box appears.
In the Name text box, type Operator.
In the E-Mail Name text box, type E-mail (or use a valid e-mail address).
In the Pager E-Mail Name text box, type Pager (or use a valid page address).
In the Net Send Address text box, type SelfPacedCPU, and then click the Test button for this net send address.
A Test Net Send Address dialog box appears stating that a network pop-up message will be sent to SelfPacedCPU. (The user name, Administrator, is not being used to send messages to this practice because multiple users named Administrator may exist on the network in different domains.)
Click OK.
A Messenger Service message box appears displaying a message from SelfPacedCPU to SelfPacedCPU testing the network pop-up notification.
Click OK to close the Messenger Service message box.
In the Pager On Duty Schedule group box, configure a pager on-duty schedule such that this operator will be not be on duty when you perform the practice exercises in this chapter.
Click OK to close the New Operator Properties - SelfPacedCPU dialog box.
In the details pane, notice that this operator is now displayed.
In the console tree, right-click the SQL Server Agent container, and then click Properties.
The SQL Server Agent Properties - SelfPacedCPU dialog box appears.
Click the Alert System tab.
In the Fail-Safe Operator group box, click (New Fail-Safe Operator) from the Operator drop-down list.
The New Operator Properties - SelfPacedCPU dialog box appears.
In the Name text box, type FailSafe.
In the Net Send Address text box, type FailSafe, and then click the Test button for the net send address.
A Test Net Send Address dialog box appears stating that a network pop-up message will be sent to FailSafe.
Click OK.
A Messenger Service message box appears displaying a message from SelfPacedCPU to FailSafe testing the network pop-up notification.
Click OK to close the Messenger Service message box.
Click OK to close the New Operator Properties - SelfPacedCPU dialog box.
The SQL Server Agent Properties - SelfPacedCPU dialog box appears displaying FailSafe in the Operator drop-down list as the fail-safe operator to be notified using NET SEND.
Click OK.
In the console tree, click Operators.
In the details pane, notice that two operators are now displayed.
Leave SQL Server Enterprise Manager running.

Lesson Summary

Creating operators is the first part of automating administrative tasks. Operators can be users, messaging groups, or computers. Operators can be notified using e-mail, pagers, and network pop-up messages. E-mail and pager notifications require the domain user account used by the SQL Server Agent service to be configured to use a MAPI messaging profile for SQLAgentMail. NET SEND messages require the Windows Messenger service, which is available only on the Windows 2000 and Windows NT 4.0 operating systems.