Optimizing performance across thousands of SQL Server databases

Solution 1:

I'd throw it all on one server. Maintaining the several cheap sql express servers will be a pain. You can spread databases, logs and the temp database across different RAID disk arrays. You should consider moving your temp database to it's own array as it may be in use by all the databases at once.

Check out the 2008 resource governor to make sure no one user will bring the server to a crawl. It is only in the enterprise version though.

Solution 2:

If you offer hosting for multi-tenant client access you also have the option of volume licensing as a Service Provider, which can turn out to be significantly cheaper than buying per-CPU Standard license, and the cost can effectively pass on onto your service per-month pricing.

Deploying multiple SQL Express instances for hosting is actually against the recommendations.

Although is a bit outdated with regard to SQL 2008 and R2, I recommend reading the white paper SQL Server 2005 Deployment Guidance for Web Hosting Environments, the guidance applies to other hosting and multi-tenant environments, not only to Web hosting.

Solution 3:

Jess,

Without knowing more in regards to your environment or usage patterns, I will say that you may see improved performance with each client having their own database (so thousands of smaller databases vs. 1 large database). You could potentially reduce the chances of table and row locking, as clients will only be hitting their own unique set of tables, versus sharing a set of tables. Disk I/O would still be a limiting factor however.

Also, security would be clearer, as each database would have its own unique set of permissions for each client. As you stated, backups and restores would be a lot faster with the smaller databases, but the setup and maintenance of these backup jobs would be extremely complex (but it sounds like you have already accounted for that).

If you have the hardware, I would highly recommend setting up different RAID arrays for your Data, Logs, and TempDB (as Sam has suggested). If you are using some kind of Direct Attach Storage or SAN, and you can afford extra arrays, you may even consider splitting up the actual files for your databases onto different arrays.

HTH, Dan

Solution 4:

Your biggest challenge will probably involve backups; if you go SQL Express, you have no Agent to run them, you'll need to rely on Windows Scheduled Tasks and some fancy scripting.

If you use SQL Std/Ent Edition, and you try to use the built-in maintenance plans to backup all databases, it will do them one at a time and could take a while. Same for log backups.

Don't even think about using mirroring with that many databases on a server.

I would lean towards more servers, with a well thought out failover strategy.