70-451 Prep–SQL Server Agent

Well, now that I’ve passed the 70-433, I’ve moved on to preparing for the 70-451 PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008. It’s basically a follow-up to the last exam. The downside to this follow up is there are no books or self-paced study guides to help you get ready for the exam.

So. I’ll be the bold one. I’m going to go through the skills being measured list, and try to include a write up on each of the topics they list. I know, the list can change over time. As it does, I hope to use that as an opportunity to expand this section. If you want to keep track of the topics covered in the 70-451, feel free to do a tag search on my site. http://shannonlowder.com/tag/70-451/

On to the content

The first bullet point listed on the skills covered is:

Identify which SQL Server components to use to support business requirements.
This objective may include but is not limited to: SQL Server Agent, DB mail, Service Broker, Full-Text Search, Distributed Transaction Coordinator (DTC), linked servers

I’ve discussed the Agent before. I’ve shown how to create Jobs, add steps and set schedules. I’m thinking I need to put together a guide to creating a job via the gui. For now, check out the BOL for the step-by-step guide. It’s not great, but it should hold you over until I can put together a more visual guide.

Agent jobs are great for automating tasks you need to complete against your database instances. You can even build Maintenance Plans, SSIS packages, T-SQL, even PowerShell scripts and schedule them to run. Via the Agent, anything you can do in SSMS (and some things outside of it) can be accomplished through the Agent.

Think of the SQL Server Agent as cron for SQL Server. You know about cron, right?

What they’re going to want you to be able to do for the 70-451 is identify when you would want to select the Agent rather than choosing one of the other options. If they ask you how would you do something at a certain time, or on a recurring schedule, it’s a safe bet you’re going to want to use the agent to accomplish that.

Also keep in mind that replication, while it can be scheduled to re-occur, that scheduling is actually using the SQL Agent to accomplish that timing.

Yes, there are other ways to schedule things, but in terms of just using SQL Server, Agent is your scheduler.

Shannon Lowder is the Database Engineer you've been looking for!
Look no further for expertise in:
Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.