Operators are special accounts that can be notified when alerts are triggered and when scheduled jobs fail, succeed, or complete. Before operators become available, you need to register them. After you register operators, you can enable or disable them for notifications.
You register operators by completing the following steps:
In SQL Server Management Studio, access the Management folder on the server running SQL Server Agent.
Expand the SQL Server Agent folder.
Right-click the Operators entry in the left pane, and then choose New Operator to display the New Operator dialog box shown in Figure 15-6.
Specify E-Mail, Pager, or Net Send accounts (or all three) to notify.
Tip
If you specify a pager account for the operator, you can set a duty schedule for the pager using the Pager On Duty Schedule area’s text boxes and check boxes. This option is helpful if you have operators who should be notified only during working hours. To set default configuration settings for pagers, access the Alert System page of the SQL Server Agent Properties dialog box.
Select the Notifications page to specify existing alerts that the operator should receive (if any). Existing alerts are listed in the Alert Name column. If you find an alert that the operator should receive, select the corresponding check boxes in the E-Mail, Pager, and Net Send columns as appropriate.
Click OK to register the operator.
When database administrators leave the organization or go on vacation, you may want to delete or disable their associated operator accounts. To do this, complete the following steps:
In SQL Server Management Studio, access the Management folder on the server running SQL Server Agent.
To disable an operator, double-click the operator entry in the right pane to display the Operator Properties dialog box. Clear Enabled on the General page. Click OK.
To delete an operator, click its entry in the right pane, and then press Delete. The Delete Object dialog box is displayed.
If the operator has been selected to receive alert or job notifications, you will see a Reassign To option in the Delete Object dialog box. To reassign notification duty, select a different operator using the Reassign To drop-down list. You can view or change the properties of this operator by clicking Properties.
Click OK to delete the operator.
When things go wrong with notification, operators do not get notified and problems may not be corrected in a timely manner. To prevent this, you may want to designate a fail-safe operator. The fail-safe operator is notified when:
SQL Server Agent cannot access system tables in the msdb database, which is where operator definitions and notification lists are stored.
All pager notifications to designated operators have failed or the designated operators are off duty (as defined in the pager schedule).
Note
Using the fail-safe operator on pager notification failure may seem strange, but it is a good way to ensure that alerts are handled efficiently. E-mail and Net Send messages almost always reach their destination—but the people involved are not always watching their mail or sitting at their computer to receive Net Send messages, so the fail-safe operator is a way to guarantee notification.
To configure a fail-safe operator, complete the following steps:
Right-click the SQL Server Agent entry in the SQL Server Management Studio Management folder, and then select Properties.
In the SQL Server Agent Properties dialog box, select the Alert System page.
Select Enable Fail-Safe Operator to define a fail-safe operator.
Use the Operator drop-down list to choose an operator to designate as the fail-safe operator. You can reassign the fail-safe duty by selecting a different operator or disable the feature by clearing Enable Fail-Safe Operator.
Use the Notify Using check boxes to determine how the fail-safe operator is notified.
Click OK.