Re: efficiently accessing many databases on one server

scott_sancetta_at_skillsoft.com (scott sancetta) wrote in message news:<356b404d.0301140730.34b14378_at_posting.google.com>...
> I have a DB Server running 500 databases, each with same schema (but
> data for different customer sites). There are several jobs I need to
> perform periodically on all the databases. For example, there's a
> table that needs to have old records flushed every few hours.
> Looking for an efficient solution, I can:
> 1) add one scheduled task (e.g. SqlServer Agent job) that opens each
> DB and performs the flush;
> 2) add one scheduled task per database;
> 3) kick off one (or one-per-database) scripts from an external
> always-running java program;
> 4) ???
> Another requirement is to allow programmatic change of the interval at
> which these jobs run. I need a solution for both SQL Server 2000 and
> Oracle 8i/9i. Suggestions?

For Oracle. Look at usage of DBMS_JOB package to run jobs internally in Oracle. This is better because it is portable and safer then external cron jobs or Windows Scheduler + you can fullfill your last requirement as the jobs interval can be managed programatically.

As for as performance goes... you can spread job intervals at different times.

Regards
/Rauf Sarwar

Regards Received on Tue Jan 14 2003 - 21:57:57 CET