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