Components of Automatic Administration
SQL Server Agent uses the following components to define the tasks to be performed, when to perform the tasks, and how to report the success or failure of the tasks. SQL Server Agent also provides security for automatic administration. For more information about SQL Server Agent Security, see Security for SQL Server Agent Administration.
A job is a specified series of actions that SQL Server Agent performs. Use jobs to define an administrative task that can be run one or more times and monitored for success or failure. A job can run on one local server or on multiple remote servers. You can run jobs in several ways:
- According to one or more schedules.
- In response to one or more alerts.
- By executing the sp_start_job stored procedure.
Each action in a job is a job step. For example, a job step might consist of running a Transact-SQL statement, executing an SSIS package, or issuing a command to an Analysis Services server. Job steps are managed as part of a job.
Each job step runs in a specific security context. For job steps that use Transact-SQL, use the EXECUTE AS statement to set the security context for the job step. For other types of job steps, use a proxy account to set the security context for the job step. For more information about setting the security context for a job step, see Security for SQL Server Agent Administration
For more information on jobs, see Creating Jobs.
A schedule specifies when a job runs. More than one job can run on the same schedule, and more than one schedule can apply to the same job. A schedule can define the following conditions for the time when a job runs:
- Whenever SQL Server Agent starts.
- Whenever CPU utilization of the computer is at a level you have defined as idle.
- One time, at a specific date and time.
- On a recurring schedule.
For more information, see Creating Schedules.
An alert is an automatic response to a specific event. For example, an event can be a job that starts or system resources that reach a specific threshold. You define the conditions under which an alert occurs.
An alert can respond to one of the following conditions:
- SQL Server events
- SQL Server performance conditions
- Microsoft Windows Management Instrumentation (WMI) events on the computer where SQL Server Agent is running
An alert can perform the following actions:
- Notify one or more operators
- Run a job
For more information, see Defining Alerts.
An operator defines contact information for an individual responsible for the maintenance of one or more instances of SQL Server. In some enterprises, operator responsibilities are assigned to one individual. In enterprises with multiple servers, many individuals can share operator responsibilities. An operator does not contain security information, and does not define a security principal.
SQL Server can notify operators of alerts through one or more of the following:
- Pager (through e-mail)
- net send
To send notifications by using netsend, the Windows Messenger service must be started on the computer where SQL Server Agent resides.
To send notifications to operators by using e-mail or pagers, you must configure SQL Server Agent to use Database Mail or SQL Mail. For more information, see Database Mail.
You can define an operator as the alias for a group of individuals. In this way, all members of that alias are notified at the same time. For more information, see Defining Operators.