Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Mixing tables from different databases/applications into one database Expand / Collapse
Posted Tuesday, March 30, 2010 11:42 AM


Group: General Forum Members
Last Login: Friday, November 1, 2013 4:39 PM
Points: 108, 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.



Post #893107
Posted Tuesday, March 30, 2010 12:17 PM



Group: General Forum Members
Last Login: Monday, August 29, 2016 1:09 PM
Points: 13,999, Visits: 9,728
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.

Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #893152
Posted Monday, April 5, 2010 7:13 AM

Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #896679
Posted Thursday, January 13, 2011 7:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:04 AM
Points: 9, 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...
Post #1047297
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse