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

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!

Answer 2

This is a very poor use of log shipping. 300 log files, each tiny being shipped every, what, 15 minutes? If the log backup interval is greater then 15-30 mins then it isn't critical, arguably.

I really would consider database mirroring which is "job less" or full clustering. For cheapness, database mirroring wins.

High availability overview (SQL Server 2008 but applies to '2005 too)

Late edit:

2 articles on mirroring and threads

• Things to consider when setting up database mirroring in SQL Server
• Mirroring a Large Number of Databases in a Single SQL Server Instance

This might not be relevant, but good stuff to know anyway.

Answer 3

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).