March 30, 2010 at 11:42 am
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
March 30, 2010 at 12:17 pm
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
April 5, 2010 at 7:14 am
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.January 13, 2011 at 7:50 am
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...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply