Chapter 14. Automating management with SQL Server Agent
We’ve covered backups, index maintenance, and a number of other important maintenance tasks so far. You can perform all of those tasks manually, but wouldn’t it be nicer if you could set them up once, and have them run on a regular basis? Of course! That’s where SQL Server Agent becomes your very best friend.
SQL Server Agent, Agent for short, is a separate service that installs along with SQL Server. It runs under its own service account, and it stores its configuration information in the built-in MSDB database. You can think of Agent as a kind of super-duper task scheduler: it runs tasks on whatever schedule you set. It can also do a lot to help automate certain aspects of SQL Server monitoring, and it can even provide a basic framework for automating certain kinds of reports.
Agent consists of three main elements: jobs, operators, and alerts. We’ll cover each of those in this chapter, along with supporting mechanisms like Database Mail, Agent security, and Database Maintenance Plans. I’ll even give you some ideas for putting Agent straight to use in your environment.
Note
I won’t be covering 100% of what Agent can do, but I’ll try to touch on the stuff that’s most immediately usable in most environments. Still, it’s worth spending some time later reading up on what else Agent can do for you. SQL Server Books Online is the place to start.