This review topic is part of Managing Data lesson which covers 20% of the certification exam.
SQL Server Agent enables you to execute scheduled or event-driven administrative jobs, automating processes you would otherwise have to execute manually. Each SQL Server instance you setup has a separate SQL Server Agent.
During the installation of SQL Server 2012, the SQL Server Agent is disabled by default. You will need to start this manually. You can configure the restart process and startup type however, in the SQL Server Configuration Manager.
Configuring the SQL Server Agent Account
Some of the best practice when setting up a service account for your SQL Server Agent are;
- The service account should not be a member of the local administrators group on the host computer.
- The service account should be member of the sysadmin fixed server role.
- It is best to use a domain-based security account.
Setting SQL Server Agent Security
To allow a user to use the SQL Server Agent, and he is not a member of sysadmin fixed server role, then he/she must a member in one ore more of the following fixed database roles in the msdb database.
SQLAgentUserRole – This is the role with the least privilege among the 3 roles. Members of this role have only permission on the local jobs and the job schedules they own. This role does not allow use of multi-server jobs.
SQLAgentReaderRole – Members of this role are also granted permissions the same as SQLAgentUserRole. Additionally members of this role can view the properties and history of all available jobs and schedules and multi-server jobs.
SQLAgentOperatorRole – Members of this role are also granted permissions for SQLAgentReaderRole and SQLAgentUserRole. Additionally, members of this role can execute, start and stop and delete job history for any local job. Members can also enable and disable all local jobs and schedules.
SQL Server Agent Mail Profile
SQL Server Agent uses Database Mail in sending out alerts as email messages. You will need to set up the alerts in the SQL Server Agent properties. On the Alert System page, check the Enable mail profile checkbox and select an existing mail profile. After clicking OK, you need to restart your SQL Server Agent service.
In your SSMS, right click on the SQL Server Agent and go to properties. This should open a dialog window similar below
SQL Server Agent Error Log
The SQL Server Agent error log stores warnings and error messages from the SQL Server Agent. Other information events by choosing ‘Information’ in the Agent log-level category in the error log properties. You can maintain up to maximum of 9 error logs, but you can cycle these logs by choosing ‘Recycle’ from the Error Logs node under the SQL Server Agent. In this case, however, the oldest log will be deleted.
In your SSMS, right click on the SQL Server Agent and go to Error Logs. This should open a dialog window similar below
Alerts can be triggered by event logs, WMI events or performance conditions. You can configure SQL Server Agent to look for a specific event or performance condition; when SQL Server Agent detects that event or performance condition, it triggers an alert.
Creating an alert involves the following steps.
- Creating an alert name.
- Specifying the alert trigger.
- Specifying the alert action.
To create a new alert, from the SQL Server Agent, right click on Alerts and select New Alert. You will need to fill the all the details. On the Response page, you can choose an existing job or create a new one. You can select to notify operator from this page. On the Options page, you can choose to include alert text in an email, pager or net send message.
If the alert you are creating is based on an SQL Server event, similar to the one below, you must choose an alert based on
- a specific error number
- a specific error severity
- message text
Database administrators use jobs to automate routine task such as health checks, maintenance tasks or application required tasks.
The steps in creating a new job are;
- Define the general information on the General page.
- Configure the job steps
- Configure the job schedule
- Configure the job alerts
- Configure the notification
- Configure the targets for multi-server administration
When configuring a job step, you define what happens if the step is completed successfully or if it fails. You can do this on the Advanced page of the job step dialog window. See below screen shot.
After setting up the steps for your jobs, you need to configure when the job will execute. You configure the schedule for the job on the Schedules page. See below screen shot.
From the above dialog window, you can also configure your job to run only once or recurring and or with a trigger, for example whenever the CPU falls below the idle threshold value.
Next step, which is optional when creating a job is configuring the Alerts associated with your job. The Alerts window is similar below.
Another optional step you can configure when creating your job, is setting up the notifications. You can do this in the Notifications page. See below window;
To monitor all your jobs and view job history, you can go to Job Activity Monitor window. You can also use this Job Activity Monitor to determine which jobs are scheduled to run in the future and wether the job’s last execution was successful.
Monitoring Multi-Server Environments
When properly configure, you can use SQL Server Agent to manage jobs across multiple servers. This is where you need to setup master and target servers. Before enlisting a target server in your master server, the target server must be already configured successfully. A target server can only be enlisted to a single master server. You must use a windows domain account for the SQL Server Agent service account.
You can use the sp_msx_enlist stored procedure on the target server to enlist it to a master server. If you must switch master servers for a target server, you need to defect the target server from its current master server. If you need to change the name of your target server, you must first defect it also from its current master server, change the name then enlist it again. You also need to do this defection for all your target servers if you need to remove an existing multi-server configuration.
I have written an article on scaling out your maintenance plans if you have a master and target server configured in your environment. You may read the article here http://coffeeandsql.com/2013/07/30/using-msx-and-tsx-to-scale-out-your-maintenance-plans/
One exam tip on this lesson is you should be able to remember the fixed database role to which you add a principal to if the principal needs to be able to start, stop or execute any job.
Credit for featured image to V&A Steamworks.