Fog Creek Software
Discussion Board

Best way to run long running batch jobs

If you have a series of jobs lasting half a day (for very large datasets and batch jobs) on SQL Server in a client server environment, which connection model is best?

I have had trouble doing straight VB accessing SQL Server using ADO. Mostly due to connection problems. When we are on the road or in our office, our net connection is not reliable enough for the long tasks to complete gracefully.

I am starting to think about using SQLDMO to set up/tear down immediate SQL Server Agent-based jobs and then poll on their status using ADO. Keeping the connections as short as possible. It's a lot of work but might just do it for us.

Another question: What's the best way to assign rights to run privilaged commands like sp_oa* and setting up SQL Server Agent jobs to normal database accounts without creating security holes left and right?

Any tips on how you do it at your company?

Anonymous coward
Tuesday, May 13, 2003

cron and perl. Oh wait, that's for a real computing platform....


Seriously, we run the task locally on the server and generate an output file. This file is then transfered across the net for processing. This can be done a number of methods, using a number of transfer mechanisms, depending upon how your network is set up.

There is NEVER any reason to have direct db connections from a db client from outside the firewall.

Wayne Earl
Tuesday, May 13, 2003

We do it very similarly. All "long running" jobs (for sufficient values of "long") are set up as jobs in the SQL Agent (job scheduler). They run from there, on the server, not on the client process. We then periodically log the job "checkpoints" to a BatchTracking table. Don't confuse the terminology, this is a job-related "checkpoint" (defined by the particulars of the individual job) and should not be confused with an SQL Checkpoint, where all dirty data/index pages are flushed to disk.

The "checkpoint" gives us a couple of things: (1) we can poll the BatchTracking table to give the user a quick status on where the job is and what it's doing. (2) I think this is more important, the "checkpoints" in the BatchTracking table allow us to restart a job (assuming it's restartable in the first place) without having to do a lot of redundant processing. If a job needs to process, say, 10,000,000 records, we'll break that up into "chunks" of , say, 1000 records and log the progress in the BatchTracking table at each "checkpoint" of 1000 records. This way, if the job fails at record number 9,999,999 -- we don't have to start over and reprocess the eniter ten million records. The BatchTracking table tells us we only have to re-process the 1000 records since the previous "checkpoint" that was entered in the log.

It works very well for us and has been running in production for over 5 years -- including SQL 6.5, 7.0 and 2000 with no problems.

As to the security question -- I can't answer this. Our jobs are predefined and pre-scheduled. End-users don't submit the jobs. They submit a request that a batch be scheduled for after-hours. The jobs and schedules are all run through the DBA, and approved by the DBA, and everything runs through the administrative service accounts for SQLServer and the SQLAgent. We don't run any of these jobs "on demand" by the end-users. It's just not done at our shop.

Sargent Sausage
Tuesday, May 13, 2003

"There is NEVER any reason to have direct db connections from a db client from outside the firewall."


I think an even better black & white rule would be "There is NEVER any reason to make arbitrary architectural limitations without knowing all of the factors involved". In this case he was specifically talking about a client/server enironment (for some reason they opted for CS over n-Tier - See the prior rule, though there are those who would foolishly proclaim "There is NEVER any reason to not have an n-tier architecture...") so obviously a direct DB connection is needed. Whether it is via a heavily secured VPN or some other route, it seems that the suggestion was that it was over an unreliable connection.

I really dislike arbitrary black and white rules in software development. They scream out, at least in my opinion, "I heard a rule so I propagate it". I've had too many arguments, err debates, regarding where business logic exists, normalization/denormalization, etc, with people who simply see the world as black and whites rather than a myriad of grays that are the palette of our architectures.

Dennis Forbes
Tuesday, May 13, 2003

Well client server does not mean the server can't do the work, in fact it means the server does the majority of the work and the client presents it.

But in a batch job, if the server can do it, use the server.  If you need to check the progress of the batch then do as suggested and use some intermediate checkpoint table.

Simon Lucy
Wednesday, May 14, 2003

Maybe I can place the bulk of the VB program logic into separate executables, placed to run the same servers as the SQL Server. And get it going using technologies like XML-RPC. Again, keeping the connections mostly disconnected.

Anonymous Coward
Thursday, May 15, 2003

*  Recent Topics

*  Fog Creek Home