Fundamentación

SQL Server Agent is an active, intelligent agent that plays an integral role in the management of the SQL Server environment. It provides a full-function scheduling engine that supports regular tasks for the management and administration of SQL Server, and it allows you to schedule your own jobs and programs.

SQL Server Agent is a Windows NT/2000-based service that can be started when the operating system starts. You can also control and configure it from within SQL Server Enterprise Manager or SQL Server Service Manager. SQL

Server Agent is entirely driven by entries in SQL Server tables that act as its control block. Clients never directly communicate with or connect to SQL Server Agent to add or modify scheduled jobs. Instead, they simply make the appropriate entries in the SQL Server tables (although this typically occurs through SQL Server Enterprise Manager via a simple dialog box that's similar to a typical calendar program). At startup, SQL Server Agent connects to an installation of SQL Server that contains its job table and then loads the list of jobs, the steps that make up each job, and the possible alerts that can be fired.

SQL Server Agent, like the SQL Server engine, is a single multithreaded process. It runs in its own process space and manages the creation of operating system threads to execute scheduled jobs. Its discrete managed subsystems (for replication, task management, and event alerting) are responsible for all aspects of processing specific jobs. When jobs are completed (successfully or not), the subsystem returns a result status (with optional messages) to SQL Server Agent. SQL Server Agent then records the completion status in both the operating system's application log and the job history table in SQL Server and optionally sends an e-mail report of the job status to the designated operator.

In SQL Server 2000, scheduled jobs can be made up of multiple steps; each step defines the action to take if it succeeds and the action to take if it fails. Thus, you can control the execution of subsequent steps when a step fails. SQL Server 2000 allows you to define a server as a master server for the purpose of scheduling jobs that will be carried out by one or more target servers. These target servers periodically poll their master server to determine whether any new jobs have been defined. If so, the master server downloads the job definitions to the target server. After the jobs are run on the target server, the success or failure status is reported back to the master server. The master server keeps the complete job history for all its target servers.

The event/alert subsystem gives SQL Server its ability to support proactive management. The primary role of the event/alert subsystem is to respond to events by raising alerts and invoking responses. As triggering activities (or user-defined activities) occur in the system, an event is posted to the operating system application log. SQL Server Agent monitors the application log to detect when an event has occurred.

SQL Server Agent determines whether any alerts have been defined for this event by examining the event's error number, severity, database of origin, and message text. If an alert has been defined (in the alert table), the operator(s) can be alerted via e-mail or pager or with a net send message. Alternatively, a SQL Server Agent job can be invoked when the alert is fired and can take corrective action. (For example, SQL Server Agent might automatically expand a full database.)

If no alerts have been defined locally, the event can be forwarded to another server for processing. This feature allows groups of servers to be monitored centrally so that alerts and operators can be defined once and then applied to multiple servers. Beyond the database environment, Microsoft Systems Management Server (SMS)—a

BackOffice component—is available to provide key services to manage the overall software configuration of all the desktops and servers in the environment.

Alerts can also be fired in response to System Monitor (or Performance Monitor) thresholds being crossed. Normally, the crossing of a threshold isn't considered an error, and no event is written to the application log.

However, the alert system in SQL Server 2000 is integrated with the performance counters available through System Monitor (discussed later in this chapter), and SQL Server can take action when a counter rises above or falls below a specified value. The kinds of actions possible are the same actions described previously: the operator(s) can be alerted via e-mail or pager or with a net send message. Alternatively, a SQL Server Agent job can be invoked to take corrective action when the alert is fired.

For example, SQL Server Agent might automatically expand a database that's almost full. Note that being almost full is technically not an error: no error number or severity level is associated with this situation. However, because

Performance Monitor can detect the condition of a database file reaching a certain percentage of its capacity, an alert can be raised. Figure 2-4 shows the definition of an alert based on a Performance Monitor threshold. This alert is fired when the log file for the Northwind database exceeds 80 percent of its capacity.

3 4

SQL Server Utilities and