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.

Multiple Client on the same ReportServer

Hi !

Once again i have a question . We have a Web application and lately we have install ReportBuilder to allow some of our Clients to create their own reports. I was wondering how to "hide" reports made by a Client from another client ? For exemple if a Client 'A' create a report and save it on his folder. When the client 'B' open reportBuilder and select the option OPEN, currently the Client 'B' can see the folder of the Client 'A'. So the Client 'B' could modify a report that belong to another client.

I was looking in the Report Manager but i couldn't find how to 'hide' a folder from user using the security option. Maybe we haven't configuer ReportServer propely. How can we solve this problem ? Is there a good website that explain how to do this ?I've forgot to ask something else. Currently we have made over 600 reports. All our Clients can use thoses reports but we don't want to allow them to modify them. Is it possible to hide a project from all our Client ?|||Anyone ? I really need to find a solution because we can let a Client have access to reports of another client.

Thanks ! Sorry about my bad English ^_^

Multiple Client Database Model

Does anyone have a sample of what a database would look like that would support multiple clients in a single ASP.net application?

Thank you,

Why would this be any different from any other database?
A database is meant to be shared amongst multiple clients. That'sone of the main reasons for using databases instead of storing data inflat files or other application-based structures. Databasessupport transactions and locking that keep clients from destroying eachothers' work. Perhaps I'm misunderstanding your question,though? Are you looking for an example of a specific type ofdatabase?

|||

Really what I was hoping was an example of a database that would be used on a web host that could maintain more than one client using it. For example two or more companies using the same hosted database. What is done to the data tables to allow for the separation of the companies and their clients, etc...

I hope this explains it better.

Thank you,

|||Okay, that makes a lot more sense
This can be achieved using what's known as "row-based security". See this article:
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
Note that there are some serious caveats in a row-based securityscheme, and if you really need heavy security it's probably better todo a seperate database per client. Steve Kass (SQL Server MVP)has posted some interesting ways he's found to hack row-based securityschemes -- unfortunately, the SQL Server engine doesn't realize you'redoing row-based security so it doesn't know not to show certain data inerrors/etc if you give it the right inputs.

|||Thank you, I will check it out.