Consolidate Databases or Not

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.

more ▼

asked Apr 02, 2010 at 09:34 AM in Default

avatar image

John 3 1
164 8 8 13

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Apr 02, 2010 at 09:50 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 02, 2010 at 09:34 AM

Seen: 1150 times

Last Updated: Apr 05, 2010 at 10:01 AM

Copyright 2018 Redgate Software. Privacy Policy