13

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?

SqlRyan
  • 1,206
  • 2
  • 17
  • 24

3 Answers3

9

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.

Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
7

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.

Bacon Bits
  • 1,894
  • 14
  • 16
0

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.

Gator
  • 301
  • 1
  • 5