November 17, 2009 at 3:28 pm
The topology at my company is 1 db per client (not my choice). We have over 800 clients meaning that we have 800 databases on a single sql server instance. I need to log ship these to a dr site. My understanding is that there will be 1 sql server agent job for each db on both the primary and secondary instances. Is there a limit to the number of jobs that sql server agent can handle? What type of performance issues am I going to run into? Any feedback and alternative solutions is greatly appreciated.
November 18, 2009 at 2:10 pm
I have not heard of a set limit for the number of jobs that an instance can have or run. I would imagine the any limit you hit will more likely be due to resource consumption of memory and possibly network connectivity due to jobs running concurrently.
Testing by adding jobs one by one and running perfmon counters should help you identify how many jobs you will be able to realistically run at once.
Joie Andrew
"Since 1982"
November 18, 2009 at 6:02 pm
I'd be seriously looking at consolidating those databases. You could have a single database with a separate schema for each user, which would make your life hugely simpler, plus have the benefit of making common (eg. reference) data shared and so more easily maintained.
The other alternative that springs to mind would be to write a custom job or jobs to manage the log backups, copies and restores as a single thread. There's a bit of development effort and testing involved, but that's the only other "easy" way I can think of to handle such a requirement.
Both the above could also handle new or departing users automatically, whereas separate jobs for every database is going to be a big management overhead when staff turnover is taken into account.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply