Operators

Article
04/01/2012

Operators are aliases for people or groups that can receive electronic notification when jobs have completed or alerts have been raised. The SQL Server Agent service supports the notification of administrators through operators. Operators enable notification and monitoring capabilities of SQL Server Agent.

Operator Attributes and Concepts

The primary attributes of an operator are:

Operator name
Contact information

Naming an Operator

Every operator must have a name. Operator names must be unique within the SQL Server instance and can be no longer than 128 characters.

Contact Information

An operator's contact information defines how the operator is notified. Operators can be notified by e-mail, pager, or through the net send command:

Important

The Pager and net send options will be removed from SQL Server Agent in a future version of Microsoft SQL Server. Avoid using these features in new development work, and plan to modify applications that currently use these features.

E-mail notification

E-mail notification sends an e-mail message to the operator. For e-mail notification, you provide the e-mail address for the operator.
Pager notification

Paging is implemented by e-mail. For pager notification, you provide the e-mail address where the operator receives pager messages. To set up pager notification, you must install software on the mail server that processes inbound mail and converts it to a pager message. The software can take one of several approaches, including:
- Forwarding the mail to a remote mail server at the site of the pager provider.
  
  The pager provider must offer this service, although the software required is generally available as part of the local mail system. For more information, see your pager documentation.
- Routing the e-mail by way of the Internet to an e-mail server at the pager provider's site.
  
  This is a variation on the first approach.
- Processing the inbound e-mail and dialing the pager by using an attached modem.
  
  This software is proprietary to pager service providers. The software acts as a e-mail client that periodically processes its inbox either by interpreting all or part of the e-mail address information as a pager number, or by matching the e-mail name to a pager number in a translation table.
  
  If all of the operators share a pager provider, you can use SQL Server Management Studio to specify any special e-mail formatting that is required by the pager-to-e-mail system. The special formatting can be a prefix or a suffix and can be included in the following lines of the e-mail:
  
  Subject:
  
  Cc:
  
  To:
Note

If you use a low-capacity alphanumeric paging system, you can shorten the text that is sent by excluding the error text from the pager notification. An example of a low-capacity alphanumeric paging system is one that is limited to 64 characters per page.
net send notification

This sends a message to the operator by means of the net send command. For net send, specify the recipient (computer or user) of a network message.

Note

The net send command uses Microsoft Windows Messenger. To successfully send alerts, this service must be running on both the computer on which SQL Server is running and the computer that the operator uses.

Alerting and Fail-Safe Operators

You can choose which operators are to be notified in response to an alert. For example, you can assign rotating responsibilities for operator notification by scheduling alerts. For example, Individual A is notified of alerts that occur on Monday, Wednesday, or Friday, and Individual B is notified of alerts that occur on Tuesday, Thursday, or Saturday.

The fail-safe operator receives an alert notification after all pager notifications to the designated operators have failed. For example, if you have defined three operators for pager notifications and none of the designated operators can be paged, the fail-safe operator is notified.

The fail-safe operator is notified when:

The operators responsible for the alert could not be paged.

Reasons for failure to reach primary operators include incorrect pager addresses and off-duty operators.
SQL Server Agent cannot access system tables in the msdb database.

The sysnotifications system table specifies operator responsibilities for alerts.

The fail-safe operator is a security feature. You cannot delete the operator assigned to fail-safe duty without reassigning fail-safe duty to another operator, or deleting the fail-safe assignment altogether.

Notifying an Operator

You must set up one or more of the following in order to notify an operator:

To send e-mail with Database Mail functionality, you must have access to an e-mail server that supports SMTP.
For paging, you must have third-party pager-to-e-mail software and/or hardware.
To use net send, the operator must be logged on to the specified computer, and the specified computer must allow messages from Windows Messenger.