Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

8 Answers 8

up vote 6 down vote accepted

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.

share|improve this answer
1  
Good point about the backups/restore, thanks for the advice. –  Ricardo Jan 30 '10 at 0:27

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.

share|improve this answer
    
Thanks for your input. Wouldn't be just a matter of running the same scripts in all databases when a change in the schema is needed? I'm just guessing... I haven't have the need to do this yet but does not seem as a difficult task, but I could be completely wrong and that is the reason I am asking :) –  Ricardo Jan 30 '10 at 0:41
    
Yes, but, that can be a daunting task when the numbers get big. And when things get out of sync, it's a royal pain, scripts start failing. If your db is nice and stable though, it might not become an issue. –  SqlACID Jan 30 '10 at 2:09

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.

share|improve this answer
    
You are absolutely correct about moving data easier by having separate databases and having the ability to take one client's db and set it up in a different server if needed. –  Ricardo Jan 30 '10 at 0:30

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
If I end up with 100,000 clients, I am sure I'll have enough resources ($$) to throw at the problem then... :) I see your point, however I am inclined to believe that one db per customer is better for all the reasons posted here by others. I will also think that with that many databases I would probably spread them to multiple SQL server instances. Thanks for your input! –  Ricardo Jan 31 '10 at 15:56

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.