SQL Server Agent is a Windows service that execute scheduled administrative tasks (Jobs) and notify operators when a specific SQL Server event occurs (Alerts).
- SQL Server Agent uses SQL Server to store job information.
- Jobs contain one or more job steps; job steps can define which specific job step should be executed when the current step succeeds / fails.
- Job steps can do different things, like
- launch a console application
- backing up a database using T-SQL.
- run PowerShell script.
- initiate data transfer task using SQL Server Integration Services.
- running an MDX query on Analysis server.
- Results of job execution are stored in a history table for later analysis.
- Jobs can be defined to start on schedule or started ad-hoc using SSMS UI / T-SQL API / SMO managed object model / PowerShell.
- Jobs can also be started when a specific SQL Server event occurs. Example: Start a backup job whenever a new database was created / attached.
- Alert definitions are stored in MSDB database.
- Alert can be defined on a specific SQL Server error / information event.
- Alert can be triggered when a specific SQL Server performance counter exceeds a threshold value.
- SQL Agent can notify an operator when a job succeeds / fails.
- When an Alert condition is triggered, operator can be notified by sending emails using Database Mail.
Jobs, Alerts, operators can be defined using SSMS UI / T-SQL API / SMO managed object model / PowerShell.
More details in MSDN: http://msdn.microsoft.com/en-us/library/ms189237.aspx