We have 15 different clients that we work for. Currently they are in separate databases, but all fields are standard. Is there a benefit to consolidate them all into one master database? The clients are contract based, so we will add and remove clients on a regular basis (6 months). Currently we have separate analysts that work with each of their databases to do updates and maintenance. If we consolidate, I'm worried about performance, size, and having to lock up all 15 projects if we need to make a change to the database structure. And if the master database gets corrupted, I will lose all 15 projects until I could restore backups. The reason for consolidation is to run office wide reports showing all projects, but I'm sure that could be done even if they are on separate databases. Back to the question (I know this is limited information) - Should I consolidate or keep it separate? Any benefits one way or the other? Appreciate any advice you could provide. Thanks, John.
Answer by Grant Fritchey ·
Based on what you describe, no, I wouldn't consolidate. Right now, if any one of the projects grows bigger or gets a bigger load or what have you, you can easily cycle it off to another server. If you cram them all into the same database, even doing it carefully, seperate file groups, seperate schema's, you're still looking at a pretty severe loss in flexibility. Unless you're going to cram them all into a single set of tables, anything else you do will just decrease your flexibility without providing a lot of benefit. If you do put them all into a single schema, then you gain in your ability to have a single, coordinated deployment process that will work better than trying to get 15 different databases all scripted the same way at the same time. You also gain in that, if you have a single structure, you also have a single set of procedures and code, so any tuning you apply is immediately available to everyone. Those are about the only benefits that come to mind immediately.
I'd stick with the multiple databases.