SQL Server Agent

SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. Each step contains its own task, for example, backing up a database.

SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand. For example, if you want to back up all the company servers every weekday after hours, you can automate this task

By default, the SQL Server Agent service is disabled when SQL Server 2012 is installed unless the user explicitly chooses to auto start the service.

SQLServer Agent Components

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.

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.

 Note: 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 fail over 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.

We 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.

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.

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

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

To send notifications by using net send, the Windows Messenger service must be started on the computer where SQL Server Agent resides.

The Pager and net send options will be removed from SQL Server Agent in a future version of SQL Server. Avoid using these features in new development work, and plan to modify applications that currently use these features.