SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mixing tables from different databases/applications into one database


Mixing tables from different databases/applications into one database

Author
Message
Aaron Prohaska
Aaron Prohaska
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 138
Hello All,

I'm working with a client who has recently purchased a new database server. They are setting it up now and one of the decisions that they have made is to take the tables from a number of different databases/applications and merge the tables into one large database, thus mixing the different applications tables into one database.

I think this is a bad idea and am trying to compile is list of reasons why this is a bad idea. Can anyone help me out by giving me reasons not to do this.

Thanks,

Aaron



GSquared
GSquared
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24531 Visits: 9730
The reasons I can think of are:

It can make scaling the database out more difficult. If one application eventually ends up needing its own server, due to size and/or demand, it will take more work to split it back up again.

If the different applications either have or end up having different maintenance needs, it can create a problem there. If, for example, one application has significant overnight downtime, and another does not, having them in a single database can complicate backup scheduling. If one database needs 99.999% uptime, and others don't, then you'll have to use the strictest possible controls on data that may not really need it, because it's all in one place.

In the case of an application error that results in the need to roll back one application's data to a prior point in time, you now have to roll back all of them, then roll the other ones forward somehow. That's significantly more complicated.

Security becomes more difficult to manage if different applications have different rules.

Counter Arguments:

Since you only have one database, if you back that up and test the backup, you're done. You don't need to run potentially dozens of backups and test them all.

If you need to scale up, to a more powerful server, it's easy, because you just have to migrate one database.

If you manage schemas (for applications) and files (for schemas) correctly, you can manage backups that way, and it gives you most of the advantages of multiple databases, without the complications.

If all the applications have the same security rules, you only have to implement them once, and you don't end up forgetting to apply a rule in one that should have been.

If applications share certain data, you only have to store it once, without having the performance penalties of cross-database queries.

Those are the things I can think of off the top of my head, both for and against.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5589 Visits: 4639
Three reasons not to do it...
1- Security administration,
2- Backup/Recovery strategy,
3- Escalability

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
craigbroadman
craigbroadman
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 86
We have done something similar, using Schemas to separate/group tables and Stored Procedures etc for each application.

This has been quite effective, especially as most of the applications share common data, hence we do not need to repeat the data in loads of different databases...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search