Professional SQL Server™ 2005 Administration

Chapter 5. Automating SQL Server

Much of the work that a DBA does is repetitive: backing up databases, rebuilding indexes, and checking for file sizes and disk-space availability. Responding to events such as the transaction log being full or being out of disk space may also be part of daily life for some DBAs. The problem grows rapidly with the number of servers you must administer. Automating this work is more than a convenience; it is a requirement for enterprise systems.

SQL Server Agent comes to the rescue, allowing you to automate routinely scheduled tasks, respond to predefined events, and notify you of status.

Automation Components

There are four basic components for SQL Server Agent, each of which we discuss in the following sections:

• Jobs: Defines the work to be done

• Schedules: Defines when the job will be executed

• Alerts: Allows you to set up an automatic response or notification when an event occurs

• Operators: The people who can be notified regarding job status and alerts

By default, the SQL Server Agent Service is not running, and the service is set to manual after the install of SQL Server. If you are going to be using this in production, be sure to use SQL Server Configuration Manager to set the Start Mode of this service to Automatic.

Jobs

A basic reason to use SQL Agent is to schedule work to be done automatically, like backing up a database. A SQL Agent job contains the definition of work to be done. A job has a name, description, owner, and category. A job can be enabled ...