One database per company for "security" reasons

Question

Possible Duplicate:
Is that a good idea to use one database for 50.000+ shops?

We are developing an internet application in C# with MVC 4. It's connected to a database which will contain a companies customers and their turnovers, also the products.

Naturally,these companies want their data secured, and also the data needs to be brought from the database to the web application as quickly as possible.

We are unsure what is the most secure and performance effective approach:

1. Have one smaller database for each company, containing their customer data. This way the only "weak" human link is to code the connection string correct depending on who's logging in.

2. Use one single database containing every companies data and all their customer data. This will naturally be a very much larger database, and possible(?) more room for error, leading to one employee seeing another companies turnovers. A critical situation.

What solution might be most secure, and the fastest in crunching numbers and processing it to the web application? is this a common "security" solution, to minimize human error by giving each company their own database?

Answer 1

Separate databases are preferable for many reasons. THese are the ones I gave when we had the same decision to make:

1. Separating client databases means there is no way someone can manipulate a querystring to view another client's data.
2. If client1 somehow nukes their database, Client2 through Client-n isn't affected and rollback only affects a single client.
3. Better performance because each database has it's own separate file with its own dedicated IO, cursors, etc.
4. Smaller tables are quicker to access. This may not be an issue now, but five years down the line when you have 10 10GB databases, you'll be glad you don't have only one 100GB database.
5. By separating the databases, you remove the need for one whole layer of management. You don't need to check whether a request for data is allowed based on the source client and the targer client's data. This speeds up the data access even more.
6. Backups are much more simple. What would you do if Client3 asks you to create a backup of their data? You'll need to write some serious SQL-fu if all the data is in one database. Having separate db's for each client means you can simply use SSMS to run a quick export. Onle a few clicks, a quick ZIPm and the backup is ready for delivery.

Answer 2

Your security requirements seem to trump your performance concerns (otherwise you wouldn't call this part "critical").

Some general comments (but please see more details here):

1. Keeping the data separate seems paramount. There is no weak human link with regard to connection strings. You store the client information in a central database, including what server/database to connect to, and the application code / middle tier builds the connection string depending on the client chosen (which can be tied to credentials, etc).
2. Performance wise it is going to be much more efficient to search a table for one customer's data than to search a massive table that contains everyone's data. You can also very easily split up 50,000 clients with their own databases across many servers instead of trying to do that all in one database in one instance on one server. Very difficult to scale out both read/write with one single monolithic database.
3. Separate databases afford you the ability to place different customers on different SLA (e.g. their database can be on faster storage) and HA/DR (full recovery vs. simple, less data to mirror / replicate / log ship etc., different frequency of log backups, etc). They also allow you to recover one client to a point in time without also rolling back everyone else to that same point.

However, I don't know where you're going with the .sdf approach. For 50,000 databases you should have proper instances of SQL Server with real databases attached to those instances, none of this file-based compact edition nonsense. IMHO.