hundreds of databases sql server log shipping

Question

SQL Server 2005 Standard 64x, with 300+ tiny databases currently (5MB each), user base adds databases as needed. Want to implement log shipping for warm standby, but not via the wizard, since that looks like it adds 3 jobs (1 on primary, 2 on secondary) for each log-shipped database.

Do I try to write my own or use something like Quest's LiteSpeed? Or am I being too squeamish about having hundreds of SQL Server Agent jobs and all of them firing off (or worse, would I have to try to time them)?

All advice welcome.

Answer 1

Since you provision a new database per user, I would advice against the built in Lo Shipping. With each newly provisioned db you'd have to add a new Log Shipping session, jobs and all that.

Given that Log Shipping is really trivial at it's core (backup, copy, restore, repeat) I would consider instead an automated solution build from scratch, with fewer jobs (eg. One job cycles through backups, one copyes , one or two aplly restores).

Answer 2

If you're managing hundreds of databases, buy some product that automates it all and does it reliably well. If that means Quest's LiteSpeed (I'm unfamiliar with it), then so be it. You do NOT want to manage all of the jobs that would come with this, so just find something that automates it by itself. You'll be kicking yourself in the butt later if you don't have it automated!