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.
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.
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.
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.
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.
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.
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.
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.
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.
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
Viewing and configuring operator notifications.
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.
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.
Creating 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
The Command Prompt window appears.
The operating system informs you that the message name FailSafe is added successfully.
The New Operator Properties - SelfPacedCPU dialog box appears.
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.)
A Messenger Service message box appears displaying a message from SelfPacedCPU to SelfPacedCPU testing the network pop-up notification.
In the details pane, notice that this operator is now displayed.
The SQL Server Agent Properties - SelfPacedCPU dialog box appears.
The New Operator Properties - SelfPacedCPU dialog box appears.
A Test Net Send Address dialog box appears stating that a network pop-up message will be sent to FailSafe.
A Messenger Service message box appears displaying a message from SelfPacedCPU to FailSafe testing the network pop-up notification.
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.
In the details pane, notice that two operators are now displayed.
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.