Problem having lots of databases?

We have built a hosted web application, that is developed in a way that each customer is connected to an individual database. This application is a web plattform/publishing system, and has been working very well so far with this design. We also have a main database.

Now we are about to make some changes in our pricing model, which would introduce free accounts. This should (hopefully) generate a lot of more accounts.

Is there a problem having a lot of databases, say many thousands (currently about twenty)? There are lots of advantages: security by separation, scalability, easily extract custo开发者_如何学运维mer specific data (part of scalability too), etc.

Two problems that I can see:

Maintenance - it's going to be a pain to make changes to the database (change table structure, modify SPs, etc.) if you have thousands of database. Sure you can script the changes but that many databases is that many more chances for something to go wrong. What are you going to do if your script fails halfway through and your left with some databases that have the changes and some that don't? Also, as another poster has mentioned, what about maintenance of things like connection strings, logins/passwords, etc.?

Resources - I'm not sure what resources each instance of a database uses but there has got to be some overhead to running that many databases. If you split it over several machines you again run into the Maintenance problem above.

I can understand the arguments about peace of mind and scalability, even though there may no hard "evidence" to support it. Maintenance tasks like table changes could be easily automated. Database errors or break-ins would be contained to one customer, and moving a database to a different server could be done in a breeze.

On the other hand, the critics of the model may have a point.

Why not simply try it? Set up a separate credentials system, replicate your database 10.000 times, and see whether overhead and performance are still o.k.

I agree with TLiebe, in the future maintenace is going to be very very hard to do with hundreds, if not thousands of databases.

A better solution might be by partitioning your databases by purpose, rather than by user. You could have profile, content, site, etc...as your logical partitions and then limit the number of users on each partition to a certain number. You would then go forward with profile1, profile2 until you run out of users. This way you would have your data spread out over a number of different machines, but stil have them related by function.

MySpace has used this concept for their database setup which seems to be pretty successful so far. Article about MySpace's Architecture .

Good luck and hope this helps.

I think there is a problem in your design. Instead of having a set of tables in one database, Why are you using a database per customer? As far I see having the advantages you mentioned about I can see a big overhead using many databases.. + Connection strings + separate credentials.

As mentioned, database maintenance (including security) can be scripted (how depends on your database server).

You actually gain scalability with this scenario too. The reason is that when load becomes too great, you can add another server and move a fraction of your databases to the new server. No need to re-design the application.