One Database vs. Multiple Databases [closed]

Solution 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.

Solution 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.

Solution 3:

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.

Solution 4:

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".

Solution 5:

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.