Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Since I'm a DBA (and in many cases, the de-facto sysadmin), SQL Server is installed on pretty much every server I have to work with regularly. I realized recently that I've been using the SQL Agent as the job scheduler in pretty much every case, rather than the native Windows Task Scheduler.

From my perspective, the SQL Agent has a number of advantages over the native Windows Task Scheduler:

  • Remote (from my workstation) start/stop/monitoring of tasks
  • Shared schedules (rather than each task on its own)
  • Multiple steps and control flow
  • Different types of tasks
  • Alerts on failure/completion
  • Can be configured to act as different users
  • (Moderately) descriptive error messages, rather than just an error code

However, I can't escape the feeling that this is bad practice - the SQL Agent should be reserved for just database-related tasks, and I should leave OS level tasks running in the Windows Task Scheduler, despite my dislike of its usability.

Is it okay to rely on the SQL Agent in this way? If not, should I consider a third-party Windows task scheduler to get some of the functionality I'm looking for?

share|improve this question
    
If this belongs on SF instead of here, let me know and I'll move it there, but I figured it belonged here since I'm using a database tool, and I'm interested to see if other DBA/SA admins are doing the same thing. –  SqlRyan Dec 21 '12 at 22:53
add comment

3 Answers

up vote 3 down vote accepted

Personally I think the biggest caveat would be the difficulty in keeping the list of jobs organized. As far as I'm aware you can't create folders to organize the jobs, so a large number would be cumbersome. I'm not 100% sure of this, though, since none of my servers has more than a dozen or so jobs. Server 2008 and later's Task Scheduler allows for much easier organization, IMO, and in general has much better functionality than previous versions. I'm sure third party apps do an even better job. I would cry if I had to use Server 2003's task scheduler or at.exe.

The second caveat I can think of would be potentially putting too much load on the SQL server. The Agent is a small program, but running a long or complex task could easily consume a lot of resources. Those resources would not be available for the SQL engine. Since the SQL engine is programmed to take about 80% of available system memory, this could be a problem.

Third, backup may be an issue. You will not only need to backup the filesystem, but also the msdb database to allow for recovery of the jobs (or use something to script the tasks to a text file). This adds a layer of complexity to disaster recovery.

Finally, you wouldn't want to be in a position where you're paying for an SQL Server license just to run SQL Server Agent. If the database gets decommissioned, you'll need to develop a plan for migrating off the SQL Server Agent.

share|improve this answer
    
All solid points - thanks for the caveats. I'd be concerned about number 3, except I'm not sure how you'd back up the list of scheduled tasks from the native Window scheduler, so I'd (at the moment) be prepared to lose that list completely, though I'm sure there's some way to keep a copy. Organization might be the biggest concern - none of my servers are at that point yet, but I could see them getting there if I didn't have a good system in place. –  SqlRyan Dec 23 '12 at 5:35
add comment

At my previous job I did exactly this, mostly because the jobs were all run from our central, primary cluster, which was the most visible server. I could see all of our scheduled tasks in one place instead of having to go check command line stuff on a bunch of servers.

While this is largely subjective (and it's going to be hard to derive a "correct" answer in this format), I don't think there's anything inherently wrong with this approach other than it becomes a single point of failure.

That might not be relevant if all of the tasks interact with, or depend on, the database server being up and the SQL Server services running (in our case, it did).

Oh, and you need to add in error handling for cases where the server where the task tries to run is not up - something you wouldn't have to do if the task were set up on that server.

share|improve this answer
    
I appreciate the feedback - I guess the question is pretty subjective, as either method gets the job done - but I'm looking for either some validation that "yeah, Windows tasks leave a lot to be desired" or "No, that's a terrible idea, here's why..." We'll see what bubbles up! –  SqlRyan Dec 21 '12 at 23:09
    
I'm sure people will pimp PowerShell jobs before you take too many more breaths. Personally I find those and Windows tasks a little more tedious to manage, but mileage will vary. –  Aaron Bertrand Dec 21 '12 at 23:11
add comment

I would ~likely~ take SQL Agent over windows task manager... obviously for database related tasks.

If you can code at all, or have people that can code, you can do quite a bit with console apps and wrapping them into a service creator like TopShelf (http://topshelf-project.com/ ) This can be a cheap/easy hack to get a little decoupling from SQL Agent for everything and start to also give you a layer for queueing if you are at that point.

Personally, you seem to care enough about this as well as knowing enough of your gotchas that I think you'll be fine. It is the people that don't care/know that I really worry about. I have no doubt that you'll evaluate your solutions on reasonable intervals and act accordingly.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.