Hundreds of databases.

Question

SQL Server 2005 Standard 64x, with 300+ tiny databases currently (5 MB each), the user database adds databases as needed. You want to implement log delivery in warm standby mode, but not through the wizard, since it looks like it is adding 3 jobs (1 to primary, 2 to secondary) for each database sent by the log.

Am I trying to write my own or use something like Quest LiteSpeed? Or am I too dismissive of the fact that hundreds of SQL Server Agent jobs and all of them are leaving (or, even worse, I need to try to let them go)?

All tips are welcome.

Answer 1

Since you are providing a new database for each user, I would suggest creating an integrated Lo Shipping. With each newly created db, you will need to add a new log delivery session, jobs, and all that.

Given that sending the log is really trivial in this kernel (backup, copy, restore, repeat), I would instead consider an automatic solution from scratch, with fewer jobs (for example, one job cycles through backups, one copy, one or two apli are restored).

Answer 2

If you manage hundreds of databases, buy some product that automates all this and does it reliably. If this means Quest LiteSpeed ​​(I am not familiar with it), then so be it. You do NOT want to manage all the tasks that come with it, so just find something that automates it yourself. You will beat yourself in the ass later if you don't have one!

Answer 3

This is a very poor use of log shipping. 300 log files, each tiny one sent each, what, 15 minutes? If the log reservation interval is more than 15-30 minutes, then this is not critical, perhaps.

I would really consider database mirroring , which is “work less” or complete clustering. For low cost, database mirroring wins.

High Availability Overview (SQL Server 2008, but applies to 2005)

Late editing:

2 articles on mirroring and streams

• What to consider when setting up database mirroring in SQL Server
• Mirroring a large number of databases in a single instance of SQL Server

This may not be relevant, but good material to know.