How many is too many databases on SQL Server?

Question

I am working with an application where we store our client data in separate SQL databases for each client. So far this has worked great, there was even a case where some bad code selected the wrong customer ids from the database and since the only data in the database belonged to that client, the damage was not as bad as it could have been. My concerns are about the number of databases you realistically have on an SQL Server.

Is there any additional overhead for each new database you create? We we eventually hit a wall where we have just to many databases on one server? The SQL Server specs say you can have something like 32000 databases but is that possible, does anyone have a large number of database on one server and what are the problems you encounter.

Thanks,

Frank

Answer 1

ISPs routinely have one database server that is shared by hundreds or thousands of databases.

Answer 2

The biggest problem with all the multiple databases is keeping them all in synch as you make schema changes. As far as realistic number of databases you can have and have the system work well, as usual it depends. It depends on how powerful the server is and how large the databases are. Likely you would want to have multiple servers at some point not just because it will be faster for your clients but because it will put fewer clients at risk at one time if something happens to the server. At what point that is, only your company can decide. Certainly if you start getting a lot of time-outs another server might be indicated (or fixing your poor queries might also do it). Big clients will often pay a premium to be on a separate server, so consider that in your pricing. We had one client so paranoid about their data we had to have a separate server that was not even co-located with the other servers. They paid big bucks for that as we had to rent extra space.

Answer 3

The upper limits are

• disk space
• memory
• maintenance

Examples:

• Rebuilding indexes for 32k databases? When?
• If 10% of 32k databases each has a active set of 100MB data in memory at one time, you're already at 320GB target server memory
• knowing what DB you're connected too
• ...

The effective limit depends on load, usage, database size etc.

Edit: And bandwidth as Wyatt Barnett mentioned.. I forgot about network, the bottleneck everyone forgets about...

Answer 4

What you are really asking about is Scalability; Though, ideally setting up 32,000 Databases on one Server is probably not advantageous it is possible (though, not recommended).

Read - http://www.sql-server-performance.com/articles/clustering/massive_scalability_p1.aspx

Answer 5

Architecturally, this is the right call in general. You've seen the first huge advantage--oftentimes, damage can be limited to a single client and you have near zero risk of a client getting into another client's data. But you are missing the other big advantage--you don't have to keep all the clients on the same database server. When you do get big enough that your server is suffering, you can offload clients onto another box entirely with minimal effort.

I'd also bet you'll run out of bandwidth to manage the databases before your server runs out of steam to handle more databases . . .