Does MSSQL 2005 Scale to a Large Number of Databases?

Question

If I add 3-400 databases to a single MSSQL server instance will I encounter scaling issues introduced by the large number of databases.

Answer 1

This is one of those questions best answered by: Why are you trying to do this in the first place? What is the concurrency against those databases? Are you generating databases when you could have normalized tables to do the same functionality?

That said, yes MSSQL 2005 will handle that level of database per installation. It will more or less be what you are doing with the databases which will seriously impede your performance (incoming connections, CPU usage, etc.)

Answer 2

According to Joel Spolsky in the SO podcast # 11 you will in any version up to 2005, however this is supposedly fixed in SQL Server 2005.

You can see the transcript from the podcast here.

Answer 3

I have never tried this in 2005. But a company I used to work for tried this on 7.0 and it failed miserably. With 2000 things got a lot better but querying across databases was still painfully slow and took too many system resources. I can only imagine things improved again in 2005.

Are you querying across the databases or just hosting them on the same server? If you are querying across the databases, I think you need to take another look at your data architecture and find other ways to separate the data. If it's just a hosting issue, you can always try it out and move off databases to other servers as capacity is reached.

Sorry, I don't have a definite answer here.