One Database vs. Multiple Databases

Question

I need to design a system which represents multiple "projects", one per client in SQL Server , something similar to StackExchange... same data model, different sites (one per customer). Each project has the same data model, but is independent of all others. My inclination is to use one database to store all projects. What is your recommendation?

Answer 1

I would make separate databases, because otherwise, if each client is using a similar schema, you're going to either have to combine tables or use lots of prefixes, or have link tables containing client identifying information. In addition, it will be much easier to manage backups/restores of client data if they have their own DBs.

There's really no good reason not to use separate DBs, IMO.

Answer 2

One downside of seperate db's is rolling out schema changes; if you have a couple hundred db's, be prepared to find a clever way to push new tables, stored procedures, indexes for upgrades to them. Another downside is mirroring becomes less attractive for a DR solution as the number of db's grows.

Answer 3

I would say one database per customer sounds attractive in order to achieve a higher degree of content isolation from the perspective of security.

On the other hand I think if you want to handle more customers this is going to cost you, because you will need to supply additional infrastructure to handle any sort of db upgrades which can of course be done, but someone has to actually sit down and think of a way and in most of the cases it end up being done half manual. With multiple customers you see more quickly the drawbacks of badly scaled code and that's a good thing, because you don't carry it over for years.

it will also make things easier when you migrate from a monolith to a microservice architecture as you just need to think of the domain of the service without setting up additional instances or environments per client. You should always keep in mind there are not unlimited devs and the more there are the more additional people you need to hire. Sorry for the sort of economic answer as well, but I think it's necessary for this kind of question.

Also I'd like to mention security yes, but not as the only thing because we should also strive for performance which is ultimately, also more environmentally friendly -> less hardware -> less electricity -> power consumption. Just having a reference per client is nit going to cost you that much especially with SQL. The relational DBs are made for joins.

Answer 4

Unless there is pressing need to keep the data in one place, I suggest separating them. It will make moving the data between servers easier (if you want to split them over multiple database servers once load grows to the point of being an issue, for instance, or if a client wants to pay to take the app in-house), it can make backups and subsequent restores more convenient (dependant, or course, on the backup method(s) you use), and it reduces the risk of code bugs allowing clients (accidentally or through intentional hacking around) to see each others data.

Answer 5

There's no right answer here. The purist-dba route would be all in one DB. If you're good at the design/config/sql you'd be amazed what you can get out of one modern day ~$8K server. The cheap and dirty web-scaling route would be 1:1 customer:db. That way if a customer gets wildly more popular than others you can split them out and scale their setup. The purist web developer route would be "one" db but built in a bigtable/dynamo kind of way to scale wide.

It really becomes less of a tech question and more of a business one. How many devs and admins do you have now, how talented are they, what do they already know, what kind of traffic do you expect in the very near term, what kind of revenue would it take to buy/lease 10x the hardware it takes for that, etc.

Overall thats why most of the older startup hands will say "just get it working and focus".

edit: also, there's no way this is a sysadmin asking the question. devs always like to think of designs that 10x the server config complexity "just in case".

Answer 6

As cagenut says, it depends. However...

One database per client sounds like it might be making things harder for yourself in the long run. What happens if you end up with 100,000 clients? Will SQL server cope with that many databases? Will you end up with 100,000 database backups? How will you manage that? If this is going to make more work for you, why take that option now if it could be changed later?

It might be an idea to use a single database now, but think about how you could partition the data if you did end up with very many clients.

Answer 7

I don't use MS SQL but as a principle, one DB per customer. Much easier to deal with. Plus, if one database gets screwed, and it does happen, it won't affect your other customers. Any maintenance procedures can be scripted for multiple databases as easily as one.

Answer 8

I have been using one database per customer and as mentioned abv updating schema and backups will get tricky.

But not that much.

You need to log all your database separately (in a different database atleast if not a different machine), have a table called schema_update_log or something and write a script that updates schema for every database and logs success in this. So if you have 100,000 dbs, even across many physical server you just query each one and log success, if for any reason it fails, try the failed databases again.

So it would go something like this:

master_databases: (record of every database per client, and its connection string) db_id database ...

schema_update_log pk query_id db_id status(pending, success, failed) timestamp error(just in case)

schema_query query_id(referenced in schema_update_log) query

then write a script to loop through each dataasem query and update log, keeping doing it till for every query_id, every database returns a success in schema_update_log.

this will take time, but will be more reliable than adhoc updates.

Answer 9

Separate DBs. In addition to other reasons given:

1. Your queries would otherwise need to all include a " AND project_id = $project_id"
2. Managing confidentiality, storing records of individual customers etc is much easier (you may want to periodically archive certain customers' db's)
3. If you ever do succomb to adding features for individual customers, you can add the schema changes just for that customer (or at least you have the option)
4. No danger of data leaking from one customer to another

Basically, it's much cleaner or more correct: if the rows in a table have absolutely nothing to do with each other, they shouldn't be in a table together. (Ok, I just made that rule up.)