SQL Server Agent is a Windows service that execute scheduled administrative tasks (Jobs) and notify operators when a specific SQL Server event occurs (Alerts).
Jobs: - 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.
Alerts: - 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.
Notifications: - 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