We currently run scheduled overnight jobs to sync heavily calculated data into flat tables for use in reports. These processes can take anywhere between 5mins to 2hrs per database, depending on the size. This has been working fine for a long time.

The need we now have is to try to keep the data as up-to-date as possible with our current setup.

I wrote a sync routine that can sync the specific users' data as it gets modified.

The short version is a trigger inserts the userids into a holding table when their records get modified, and there is a job that runs every 10 seconds that checks that table and if a userid is found it then fires the sync for that user and updates the flat table (and then flags the record as complete). This can take anywhere between instant and ~1min depending again on how much data it needs to calculate. Far better than the 24hrs it used to be.

Now onto the 'problem'.

We have upwards of 30 databases that all need to be checked.

Currently, our overnight jobs have a step for each database and run through it in turn.

The problem I can foresee is if customer 1 has a lot of users that are syncing then it'll wait to finish that before moving on to customer 2's database, etc.. by the time you get to customer 30 it could be a relatively long wait before their sync even begins; regardless of how quick the actual sync is. Ie. 2 customers enter data at the same time: As far as customer 1 is concerned, their sync happened in seconds whereas customer 30 took 30 mins before their data updated, even though the sync routine itself only took seconds to complete as it had to wait for 29 other databases to finish their work first.

I have had the idea of changing how we do our scheduled job here and create a job for each database/customer. That way the sync check will run synchronously across all database and no customer at the end of the queue will be waiting for other customers' sync to finish before theirs starts.

Is this a viable solution? Is it a had idea to have 30 jobs checking 30 databases every 10 seconds? Is there another, better option I haven't considered?

Our servers are running Microsoft SQL Server Web and Standard currently, though I believe we may be upgrading to enterprise at some point. (If the version makes a difference to my options here)