Fog Creek Software
Discussion Board




Schedule a process in Windows/SQL Server 2000

The application I am developing needs to perform certain functions on a regular basis – send queued emails, optimize database (move data between different tables), exchange data with other systems. All these tasks need to be executed on a daily basis, or every 5 minutes, or every hour … depends.
The tasks are implemented as:

a) Stored procedures
b) VBScript scripts
c) Components (a certain method of the component is called)

Also, the application is web based (IIS / old ASP) using SQL Server 2000. It must be able to run with multiple web servers (load balanced in a server farm), and multiple SQL servers (federated servers, or cluster).
Up to now my best choice is to use the windows scheduler on one of the web server machines (or a machine where all components and scripts are installed, at least). However, I won’t have any failover in case of cluster machine failure – as far as I understand Microsoft’s cluster server will not transfer the windows scheduler's settings. Another alternative is to have the processes scheduled on more than one machine (more maintenance headaches!) but then I need to synchronize them somehow so that one task is not executed twice.
Please let me know, what is your opinion about the best way to execute schedules tasks in the above environment?

Alexander Chalucov (www.alexlechuck.com)
Thursday, July 17, 2003

SQL Server has scheduled tasks, and SQL jobs can run
-Stored Procedures
-VB Scripts
-Object calls

Philo

Philo
Thursday, July 17, 2003

How about the job scheduler in SQL Server 2000 itself (SQL Agent)??

It can run stored procdures, DTS jobs, scripts, programs - all of what you want.

DJ
Thursday, July 17, 2003

I was not aware that I can start scripts (vb) using the SQL Server scheduler. It seems it perfectly fits my requirements. Thank you!

Alexander Chalucov (www.alexlechuck.com)
Thursday, July 17, 2003

I love SQL Server's scheduler.

You can make multiple step jobs and tell each step what to do if it fails or succeeds.  You can easily change the order or skip steps in jobs.  I wish Windows had a scheduler this cool

Mike
Thursday, July 17, 2003

My favorite use for it - I set up monitoring scripts to email me summary status every morning. I'm generally the first to be aware of failed transactions or bugs.

Philo

Philo
Thursday, July 17, 2003

Philo,

  As a matter of fact the monitoring scripts will be the second (well, not the first) thing to be scheduled. I am using the windows scheduler for some of them right now, but I will move everything to the SQL Server scheduler because of the cluster failover.

Alexander Chalucov (www.alexlechuck.com)
Thursday, July 17, 2003

*  Recent Topics

*  Fog Creek Home