Using the SQL Server Agent


To automate administration, you need to perform the following activities:

  • Establish which administrative responsibilities or server events occur regularly and can be administered programmatically.

  • Define a set of jobs, alerts, and operators by using SQL Server Management Studio, T-SQL scripts, or SQL-SMO objects.

  • Enable the SQL Server Agent service.

Jobs, alerts, and operators are the three main components of SQL Server’s automatic administration services.

A job is a specified series of operations that you must define to be performed sequentially by the SQL Server Agent. You use jobs to define the administrative tasks to be executed. The jobs can be executed one or more times and can be monitored for success or failure each time a job is executed.

You can execute jobs as follows:

  • On a local server or servers; or on multiple remote servers

  • According to a schedule

  • On the occurrence of one or more alerts

Alerts signal the designated operator that an event has occurred. An event can be any action or process that executes on the server. For example, it can be a job starting, like a backup, or system resources reaching a certain threshold, such as CPU pegged at 100 percent for a certain amount of time, suggesting that a resource is locked or has failed. You must first define the conditions under which an alert is generated. You also need to define which actions the alert will take. For example, you can program alerts to do the following:

  • Notify one or more operators.

  • Forward the event to another server.

  • Execute a job.

An operator is the individual responsible for the maintenance of one or more instances of SQL Server. In a large data center or corporation, the operator responsibilities are assigned to many individuals. A large, busy data center with many servers might employ many individuals to share operator responsibilities. They watch the servers for alerts and fix small things. The operators usually escalate problems to DBAs that are usually off duty. Operators do not also have the skills necessary to fix database problems when they occur. In a small company, however, an operator might be the DBA, and possibly even the CTO. An operator is notified of alerts in one or more of the following ways:

  • E-mail   Through e-mail you can define the alias of an operator or the alias for a group of individuals. When something acts up, all the aliases are notified at the same time. With Windows Server 2003 you can create a distribution group and send e-mail to it. All members of the group will then be notified.

  • Pager   A message can be sent to a pager using a dial-up service. This would obviously require you to set up a modem and configure dial-up services to a host network. An e-mail can also be used to send a pager message.

  • Net send   This facility is an ideal mechanism to write net send commands to the command console.

Defining Operators

The primary attributes that define an operator are name and contact information. You need to define operators before you define alerts. To notify an operator, you must set up one or more of the following in the following order:

  • When sending e-mail, if you are using the legacy SQL Mail for any particular reason, you must configure a MAPI-1-compliant e-mail client. The SQL Server Agent demands a valid mail profile be set up in order to send e-mail. Examples of MAPI-1 clients include Microsoft Outlook. Your mail server can be any MAPI-compliant mail server, such as Sendmail (essentially obsolete) or the modern, highly sophisticated Exchange 2003 or Exchange 2007 Servers. You should, however, use the newer architecture in SQL Server 2005 encompassing Database Mail.

  • When sending a page, you need third-party pager-to-e-mail software and/or hardware. Intel has such functionality in its original LANDesk product, acquired from its purchase of WinBeep, a beeper/pager utility that gets activated through Windows. You need such technology in place before you can start a vibration on anyone’s belt.

  • To use net send messages, you must be running on the Microsoft Windows Server 2003 or later operating system, and the Messenger Service must be running on the target computer you need to hit with a message. I find net send is the most ineffective method used to raise the attention of an operator. It unusually ends up alerting hackers that a net send facility on your server is worth breaking open.

Naming an Operator

Every operator must be given a name. The operator names must be unique, and they can be no longer than 128 characters. You also need to include the operator’s contact information, which defines how you notify the operator. When using e-mail notification, the SQL Server Agent establishes its own mail session using the mail profile information supplied in the SQL Agent Properties dialog box.

When using pager notification, paging is typically implemented using an e-mail service (using a modem to direct-dial a network can be cumbersome, yet more certain of getting a page out). To set up pager notification, you must install software somewhere that can read mail and convert it to a pager message and send it to a pager network. The software can take one of several approaches, including forwarding the mail to a remote mail server at the pager provider’s site. For this to happen, the pager provider must offer this service. In many cases the software you need is part of the local mail system. If it isn’t, it is not hard to find, nor is it very difficult to create such a facility using Visual Basic 2005 or C#.

A variation on the first approach is to route the mail by way of the Internet to a mail server at the pager provider’s site. The mail server processes the inbound mail and then dials the network using an attached modem. The associated software is proprietary to pager service providers. The software acts as a 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 your operators share a pager provider, you can use SQL Server Management Studio to specify peculiar e-mail formatting that might be required by the pager-to-e-mail system. The special formatting can be a prefix or a suffix, as follows:

  • A Subject line

  • A CC line

  • A To line

If you are using a low-end alphanumeric paging system such as a system that is limited to the transmission and reception of only 64 characters per page, you might have to shorten the message sent by excluding the error text from the pager notification. This may also be a limitation of a typical SMS message.

Designating a Fail-Safe Operator

A fail-safe operator can be designated if all notifications to designated operators fail. For example, let’s say you define several operators for pager notifications and not one of them receives notification. The fail-safe operator is then notified when the following takes place:

  • The operator(s) responsible for the alert cannot be paged. Reasons for this include incorrect pager addresses and off-duty operators.

  • The SQL Server Agent cannot access system tables in the msdb database. The sysnotifications system table specifies the operator responsibilities for alerts.

The fail-safe operator is a safety feature, so you cannot delete the operator assigned to fail-safe duty without reassigning fail-safe duty to another operator or deleting the fail-safe assignment.




Microsoft SQL Server 2005. The Complete Reference
Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features
ISBN: 0072261528
EAN: 2147483647
Year: 2006
Pages: 239

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net