Pros/Cons Using multiple databases vs using single database

Question

I need to design a windows application which represents multiple "customers" in SQL Server. Each customer has the same data model, but it's independent.

what will be the Pros/Cons Using multiple databases vs using single database.

which one is the best way to do this work. if going for an single database, what will the steps to do for that.

Edited:

One thing is database will be hosted in cloud(rackspace) account.

Subclinical

Pros: Security. Cons: Usability. – Diannediannne

By "multiple customers", do you mean that you have something like a netshop and need to store customer information? Or do you host databases for an application for customers, like a CRM system? – Lucier

host databases for an application for customers,like a CRM system. – Subclinical

If it's much of the same infrastructure/database layout, I would make sure to automate much of it with configuration management tools (Chef, Puppet, SaltStack, Ansible -- pick one, save yourself time in the long run). – Hirza

Here is an argument nobody made: Different databases according to the type of data being stored. I could imagine a case where I store my clients in a SQL database, their chat logs in a NoSQL DB and if I need complex relationships between them store them in a Graph Database. Sure, this sounds like an extreme use case, but sounds valid to me – Noll

Answer 1

Do not store data from multiple customers in the same database -- I have known companies that had to spend a lot of time/effort/money fixing this mistake. I have even known clients to balk at sharing a database computer even though the databases are separate - on the plus side, these clients are generally willing to pay for the extra hardware.

1. The problems with security alone should prevent you from ever doing this. You will lose large customers because of this.

2. If you have some customers that are unwilling to upgrade their software, it can be very difficult if you share a single database. Separate databases allow customers to continue using the old database structure until they are ready to upgrade.

3. You are artificially limiting a natural data partition that could provide significant scalability to your solution. Multiple small customers can still share a database server, they just see their own databases/catalogs, or they can run on separate database servers / instances.

4. You are complicating your database design because you will have to distinguish customer data that would otherwise be naturally separated, i.e., having to supply CustomerID on each where clause.

5. You are making your database slower by having more rows in all tables. You will use up database memory more rapidly because CustomerID is now part of every index, and fewer records can be stored in each index node. Your database is also slower due to the loss of the inherent advantage of locality of reference.

6. Data rollback for 1 customer can be very difficult, maybe even essentially impossible as the database grows - you will need custom procedures to do this that are much slower and resource intensive than a simple and standard restore from backup.

7. Large databases can be very difficult to backup / restore in a timely manner, possibly requiring additional spending on hardware to make it fast enough.

8. Your application(s) that use the database will be harder to maintain and test.

9. Any mistakes can be much more destructive as you can mess up all of your clients by a single mistake.

10. You prevent the possible performance enhancement of low-latency by forcing your database to a single location. E.g., overseas customer will be using slow, high-latency networks all the time.

11. You will be known as the stupid DBA, or the unemployed DBA, or maybe both.

There are some advantages to a shared database design though.

1. Common table schemas, code tables, stored procs, etc. need only be maintained and stored in 1 location.

2. Licensing costs may be reduced in some cases.

3. Some maintenance is easier, although almost certainly worse overall using a combined approach.

4. If all/most of your clients are very small, you can have a low resource utilization by not combining servers (i.e., a relatively high cost). You can mitigate the high cost by combining clients with their permission and explicit understanding, but still use separate databases for larger clients. You definitely need to be explicit and up-front with your clients in this situation.

Except for the server cost sharing, this is a very bad idea still - but cost can be a very important aspect too. This is really the only justification for this approach - avoid this if at all reasonable though. Maybe you would be better off to change a little more for you product, or just not be able to support tiny customers for a cheap price.

---

Reading an analysis of the recent Atlassian outage reveals that this mistake is precisely why they are having such trouble recovering.

There is a problem, though:

Atlassian can, indeed, restore all data to a checkpoint in a matter of hours.

However, if they did this, while the impacted ~400 companies would get back all their data, everyone else would lose all data committed since that point

So now each customer’s data needs to be selectively restored. Atlassian has no tools to do this in bulk.

The article also makes it clear that some customers are already migrating away from Atlassian for their OpsGenie product, and will certainly lose future business too. At a minimum, this will be a large problem for their business.

They also messed up big-time by ignoring the customer during this outage.

Choreographer

But whenever a new customer signs up, I will have to programmatically create new database and tables. Also it adds some complexity on how to choose the correct database for a user when he logs in. – Feasible

Answer 2

I'm assuming that by multiple customers you're not just storing customer information, you're hosting databases for an application for the customers, like CRM systems.

If so, then I would absolutely not store everything in the same database.

Reasons:

• Backup, when one customer calls and says that he needs to restore a backup because an intern managed to clean out the production database and not the test database, you do not want to have to deal with all the other customers at the same time
• Security, even with a bug in the application it won't be able to reach data for other customers. Also, consider if one customer is a bit too relaxed in their own security considerations and leaks passwords or whatnot to the system, if hackers discovers a way into that customers database, consider the fallout if that also includes all other customers you're hosting for.
• Politics, some customers will not allow mixing their data with other customers even if you can 100% guarantee that access to their data won't be (accidentally) given to other customers

So bottom line: separate databases.

Lucier

Answer 3

Some of the advantages to each approach to be considered are:

Single Database

• Relating data from different services can be bound together by foreign key constraints
• Analytic extracts are simpler to write and faster to execute
• In the event of a disaster, restoring the platform to a consistent state is easier
• For data that is referenced by multiple services, data cached by one service is likely to be used soon after by another service
• Administration and monitoring is simpler and cheaper up front

Multiple Databases

• Maintenance work, hardware problems, security breaches and so forth do not necessarily impact the whole platform
• Assuming each database is on separate hardware, scaling up multiple machines yields more performance benefits than scaling up one big one

Source

Allegorical

Id also say that implementing a security model for separate databases may be more straightforward (application wise) – Tabret

Keeping multi customer info together in the same database - disaster in the making, to say the least – Carleton

Answer 4

One day your developer will screw up something and one customer will access info of another customer. You will lose your customers as result. This alone should tell you that multiple customers can't be in one data base. no one will want to be your customer if they know this.

Do I have to really go over all issues that will eventually happen if this is the case? The answer is simple here - NO. You don't want to have information of multiple customers in the same database.

Only time that this happens is if you have multiplexer database to keep track of customer logons, sessions, etc. But data used and stored by customers should be in the dedicated database.

Carleton