70-457 Reviewer #10 Managing SQL Server Agent

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

Database Mail

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

Configure Error Log

Managing Alerts

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.

  1. Creating an alert name.
  2. Specifying the alert trigger.
  3. 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

New Job Alert

Managing Jobs

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;

  1. Define the general information on the General page.
  2. Configure the job steps
  3. Configure the job schedule
  4. Configure the job alerts
  5. Configure the notification
  6. 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.

New Job Step

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.

New Job Schedule

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.

New Job Alert

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;

New Job Notification

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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.


Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment

%d bloggers like this: