Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Managing Jobs Part 3

By Andy Warren,

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.

Total article views: 7110 | Views in the last 30 days: 5
 
Related Articles
FORUM

scheduling and executing the dts packages

scheduling and executing the dts packages

FORUM

SSIS package scheduled as job fails after about 20-40 minutes executing

SSIS Package fails after about 20-40 minutes executing

FORUM

Process Task Not Executing when Scheduled

SSIS scheduled package will not execute

ARTICLE

Executing SQL Server Jobs From An External Scheduler

SQL Server 2000 has a great scheduler in SQL Agent, but it isn't integrated with anything else. And ...

FORUM

Sql server job scheduler question

Sql server job scheduler

Tags
.net    
administration    
miscellaneous    
programming    
sql server 7    
strategies    
t-sql    
visual basic 6    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones