SQL Server Agent is a component of Microsoft SQL Server that is responsible to execute & schedule tasks or jobs in SQL Server. It runs as a Windows service and starts automatically when the system boots. It basically performs the backup task, handles reporting services, log shipping, and user tasks like; T-SQL scheduling, etc. It also supports operators and alerts for administrator notification.
To store information, SQL Server Agent uses SQL Server to perform a Job and each Job contains one or more tasks to be performed which is assigned to every job step.
For example, backing up a database.
It runs a job on schedule according to a specific event and on demand. Let’s take an example, if you backup database of all servers and schedule the backup for specific time duration, then an error or notification may encounter while taking backup.
Components of SQL Server Agent:
The SQL Server agent uses the following types of components to perform its tasks.It defines the tasks that is to be performed and when to perform it.
Jobs
Job is performed on particular tasks that can be run multiple times. It can run on multiple remote servers and one local server.
A user can run jobs in different ways like; based on one or more schedules, according to one or more alerts and by executing sp_start_job stored procedure.
Job step is an action in a job. The Job step might be a Transact-SQL statement, Analysis Services server commands and executing SSIS package. Each step in a job runs on security context. For transact-SQL it uses EXECUTE AS statement and for others, it uses proxy accounts.
Schedules
It specifies when a job runs, more than one job can run on the same schedule and vice versa. It can be defined on a job if SQL Server Agent starts, specific date or time at one time, CPU utilization of computer at idle level and at the time of recurring scheduling.
Alerts
An alert is an automatic reaction to a specific event. An alert can occur during one of the following conditions:
• SQL Server performance conditions
• SQL Server events
• WMI (Microsoft Windows Management Instrumentation) events on the computer where SQL agent is running.
An alert can perform notification to one or more operators and it runs a job.
Operators
The operator defines contact information of individuals for the maintenance of single or multiple instances of SQL Server. It does not contain security information. SQL Server notifies operators for one of the following:
• Pager
• Email
• Net send
Proxies
The security context is managed by the SQL Server agent through proxies. It can be used in more than one job steps. Proxies can be created by sysadmin fixed server roles. The members of fixed server roles have unrestricted access to the proxies and only SQLAgentUserRole, SQLAgentOperatorRole and SQLAgentReaderRole can use proxies which are assigned to them.
Conclusion
The blog explains about SQL Server agent, their roles, components (Jobs, Schedules, Operators, Alerts, and Proxies, etc.) and how they are carried out in the SQL Server. The SQL Server Agent is the most important part of SQL Server, which specifies each and every task.