Jobs and job management, so much to talk about. I've already published two
articles on the subject (Managing
Jobs - Part 1 and Managing
Jobs - Part 2), this week I'm going to cover some ideas about where, when
and how to run jobs.
As soon as you get a request for any task that will run unattended and/or a
scheduled basis, you need to think about not just how to solve the problem, but
how you'll execute and monitor it. Let's talk about the second part first. What
options do you have for scheduling?
- SQL Agent (hey, we are SQLServerCentral!)
- Windows Scheduler
- Write your own
- Buy 3rd party
In my experience you can solve most problems with the first two. SQLAgent has
a tremendously flexible scheduling/job engine. You can support multiple
schedules per job, has built in email notification, etc. The Win scheduler is
good, but doesn't have the range of SQL Agent. More importantly to me, it logs
the results to a text file and has no built in notification capability, so every
app would have to report it's own success/failure.
For monitoring, I think you want to centralize as many of these tasks as you
can. Ideally you want whatever runs the job to notify you if it fails and as I
discussed in the earlier articles, you want a way to easily see all the failed
jobs and to monitor the scheduler itself in case it fails (and so cannot notify
you that it failed!).
To me (and I may well be biased) that means 99% of the time I will use SQL
Agent for all jobs, not just stuff like backup, index rebuild, etc. Any screams
from the readers yet? Not running that stuff on your production server? Good
point. Once we've decided which scheduler to use, we need to decide where
to run it. Some factors to consider:
- Processing power and memory required for the task, disk space too
- Bandwidth - don't want to run network intensive apps across a dialup
connection, or even a slow frame link
- Access - if you want it to run on a production server you'll probably have
to go through an admin every time it changes
- Overall machine load at the time it needs to run
A good first question is "will I gain a significant advantage in either
process execution or administration by running directly on the db server?".
In many cases you will. Simple TSQL jobs, even jobs that run far more complex
stored procedures should go on the server. Even if you offload the scheduling
part, the work still gets done on the server, so you don't gain much. Most db
servers do peak workloads during the day, there is often a ton of availability
capacity you can use. It's also a sturdy, well maintained machine. You won't
have to worry about hardware failures, applying updates, virus protection, etc.
All that stuff is in place already.
Just because you've decided on SQL Agent as your scheduler doesn't mean you
have to use the one on your production server. Set up a reporting/task
workstation, install SQL personal edition, just use the scheduler. Don't make
the mistake of storing anything besides your jobs on the machine. It's limited
to the number of connections it accepts (and only people administering it should
be connected) and it's not running on a server quality machine. It still needs
to be maintained - sql backup, index rebuild, file system backup. If you put a
lot of stuff on this box and it breaks, trust me - you'll want to be prepared!
Eventually you'll run into a task that can't be (shouldn't be!) solved with
just TSQL. Sometimes VBScript will do the job. If it will, good. Remember you
have the 3200 character job step limit. More than that, you'll have to store
your code in a file and execute it as an OS task using wscript.exe. Or you have
purchased an executable that you just want to run. More likely, you'll have a
task that goes way beyond that, something that requires a lot of business logic
or faster execution than you get with compiled code. You probably also want to
keep a job that big under source control, a task most easily done from a
In the next installment I'm going to show you some ideas about how to build
your jobs as compiled VB/VB.Net code. Until then, I look forward to your
comments on what I've written so far.