Multiple databases for each client of one big master DB?

We are designing a new data model, and have many clients. Currently they each have their own database in SQL.

We are contemplating the pro's and con's of migrating to a Single Master Database.

I would like to hear from others on what your thoughts are on the pros and cons. Also, if you have experience doing something similar, please speak up.

Thanks in advance,
ScAndal2 of the biggest cons I see are restore database ability and the ability to customize things on a per client basis.|||I was at this "fork in the road" at the beginning of this month. I have a system that is runs within an Application Sevice Model over the web. There are approx. 26 tables and 275 stored procedures for the system. database Our initial design was to have a differenct database for each client. There was one main controlling database which would administer the clients and their respective databases. The model was working fine and I even had the administration of creating new clients automatic. But the harsh reality of a maintenance nightmere was coming true. We have the potential of having 60,000 clients, which is equivilent to 60,000 databases and then aother 60,000 databases on another server on another coast that is used for replication. That is a total of 120,000 SQL Databases!

Though the reality of having that many clients will most likely never happen, it did bring up a very good point: what happens if you have to make one filed change a two stored procedure changes? What a nightmere!

We made the decision to modify the model to oeprate from one database which uses administration tables to administer the clients through the database. This model is more cost efficient an safe from corruption. It will allow easy replication and backup of logs. Though the data is now squashed into one Data Structure it makes sense to let the database do what it was desinged for: manage data. A good portion of your decision should also be based upon your possible client base. Determine the minimum number and maximum number of clients that you could potentialy have and go from there.

HTH|||Wes you bring up some great points. Currently our client base is < 20. I forsee us never taking on more than 100.

ScAndal|||One of the pros of the multi database approach is data security and integrity between client databases. This is why I initially designed the model using multi-databases. Each client login would utilize a different SQL Server login account that was specific to their database. Therefore if one of my clients network was penetrated by a hacker who then gained login access to the system and decided to distroy and corrupt the database, I would only have to explain to 1 client what had happend, and at worst lose 1 client--even though the hacker came through their network!. Whereas in the single database model I would potentially have to explain to 200 other clients, and possibly lose 200 clients, why their data is corrupt and the system is offline for thirty minutes while I restore from backups on the other coast (given the hacker was not able to jump to those servers). That is my strongest argument for the multe-database model.

Another aspect to look at is Administration of the Databases after development and deployment. Is your time limited and the databases will need to run without much intervention or will you be fully dedicated to these databases where you might get bored with them?

I would love to hear about experiences from others on this same issue.