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

development environment.

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.