Managing SQL Server Agent
- The SQL Server Agent service account must be a member of the sysadmin fixed server role.
- Operators can be notified by email, net send, or pager; the fail-safe operator is contacted if the configured operator cannot be contacted.
- A job is a collection of job steps, which are independent tasks and can include command-line commands, Transact-SQL statements, and Windows PowerShell scripts.
- We can configure schedules for jobs.
- Jobs can be triggered by performance conditions
- Job Activity Monitor is used to monitor job status and history
Setting SQL Server Agent Security
Users who are not a member of the sysadmin fixed server role, they must be one or more of the following fixed database roles in the msdb database to use SQL Server Agent:
- SQLAgentUserRole - This is the least privileged SQL Server Agent role. Role members have permissions only on the local jobs and job schedules they own. This role does not allow use of multi-server jobs.
- SQLAgentReaderRole - This role includes all the permissions assigned to the SQLAgentUserRole. In addition, members of this role can view the properties and history of all available jobs and job schedules, including multi-server jobs.
- SQLAgentOperatorRole - This role includes all the permissions assigned to the SQLAgentReaderRole. In addition, members of this role can execute, stop, or start all local jobs and delete job history for any local job. Members can also enable and disable all local jobs and schedules.
Creating a job
You can execute the following stored procedures from the msdb databases to create jobs:
- sp_add_job Enables you to create jobs
- sp_add_jobstep Adds a job step to an existing job
- sp_add_schedule Creates a schedule that can be used by any job
- sp_attach_schedule Attaches an existing schedule to an existing job
- sp_add_jobserver Adds a job to a server
Monitoring Multi-Server Environments
- When properly configured, you can use SQL Server Agent to manage jobs across multiple servers.
- Multi-server environments use master servers and target servers. Target servers report to master servers, and a target server can report to only a single master server.
- You should use a domain-based account when choosing the SQL Server Agent service account for a multi-server environment.
- You can use local accounts for the SQL Server Agent service in multi-server configuration only if all instances are hosted on the same computer.