My friend Brent Ozar recently said that “Schedulers belong in applications, not databases.” He has a perfectly valid point. I’d like to offer another side to that opinion.

My previous employment had me working as a database administrator in a small environment. We had 700 users running 7 key business applications, all run on a two-node cluster. Compared to most other DBAs, this was is pretty small, and it was perfect for cutting my teeth.

None of those key applications had internal schedulers, meaning we had to accomplish most automated tasks with SQL Agent jobs. Looking back, when done properly, using the SQL Agent for these jobs gave us central control, flexibility, visibility, and credibility. Allow me to explain

Since the company didn’t have an anal-retentive DBA prior to me, the SQL jobs were initially a bit of a mess. The schedules may have made sense at one point, but we got to a point where we had close 40 scheduled jobs with undocumented reasons for their schedules. This meant we had jobs blocking each other, jobs fighting with backups, and jobs fighting with users. Did I mention that it was a mess? We went back to the drawing board.

When we were done, we had about five jobs, and they were defined by their frequencies, every 15 minutes, hourly, at noon, or overnight. What were once jobs became steps.  This meant that jobs weren’t fighting with each other. And by working with our backup administrator, we were able to make sure the overnight jobs didn’t fight with the backups. As far as jobs fighting with users, by keeping as many items out of peak usage time, we minimized that more than ever before.

This central control was great for moving things around, and I used one tool without having to use multiple application schedulers. This worked great in our environment because we only had one primary database server. I will concede that if you have multiple servers, this will not scale as well.

We quickly found great flexibility in this model. If we had something going on, I could very easily hold all of my jobs until the smoke cleared. This meant I didn’t have to log into seven different systems to reschedule jobs. If we were at the end of a quarter when backups ran long, I could reschedule my overnight jobs to start later.

What my developers and managers  liked is that I could periodically provide them a report that showed all of our jobs, the schedules, and the steps on each job. None of this was difficult. The data is right in the MSDB database. That visibility is something we never had before. Gone were the days of sifting through 40 jobs to find the proverbial needle in a haystack.

Ultimately, the best thing I got by doing this was credibility. A simple report from the MSDB database gave me a review of what my servers did in the past 24 hours. When something did go bump in the night, someone would always ask what the database server was doing at that time. I could pull my report (automatically generated by our Crystal Reports Server) that showed jobs, tasks, and execution times. It gave me credibility in that not only did I know what happened when, but I could prove it. If we relied on application schedulers, I would have been pointing fingers at applications. It’s much more fun playing offense than playing defense.