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.
Jobs
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.
Important
SQL Server Agent jobs that are running at the time of a failover event on a SQL Server failover cluster instance do not resume after failover to another failover cluster node. SQL Server Agent jobs that are running at the time a Hyper-V node is paused do not resume if the pause causes a failover to another node. Jobs that begin but fail to complete because of a failover event are logged as started, but do not show additional log entries for completion or failure. SQL Server Agent jobs in these scenarios appear to have never ended.
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.
Schedules
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 and Attaching Schedules to Jobs.
Alerts
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.
Operators
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:
E-mail
Pager (through e-mail)
net send
Note
To send notifications by using net send, the Windows Messenger service must be started on the computer where SQL Server Agent resides.
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.
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.