Combining ALL databases into one big one?
-
We have a request floating around my company to combine ALL databases into one.
One server -> ONE database with all tables/stored procs etc. that support ALL applications now (about 10) and in the future.
Can anyone tell me some pros and cons of this suggestion?
I believe this is a bad idea but do not want to jump to conclusions until I get some expert advice.
Thanks in advance!
-Kristov
-
The first cons I can think of is one database if it gets corrupt or something else happens to it, then your company will stand still.
Best of luck.
Holger
-
Ideally they would be more than "combined", they would be designed to be one database. Eliminating "stovepipe application" is (or can be) a good thing; but if you are going to combine ten application databases into one, and still have stovepipes within the database, why do it? If the data are integrated and used across applications it makes sense for one database. Having one may simplify security; but not necessarily, it actually may complicate it depending on your requirements. There is something to be said for keeping a database up and in cache, and it may make memory management easier with all available memory being used for one database, vs. split up for many. A lot of the controversy against is pride, we create a piece that is important and we like to maintain control, i.e., ownership, of it. Combining into one larger database makes some of us feel like we're losing it. standalone databases, stovepipes, are much like data marts, we have authority and freedom to design and use them as we please without conforming to "standards"; but, again, if there are no standards, what's the sense?
Perhaps someone in your organization has a reason, and it might be smart to ask what it is? It could be a good thing. Maybe there will be money and tasks to do the integration, perhaps someone really wants a data warehouse, or perhaps a normalized DB... then again, maybe someone is just trying to grab control
It could be fun though.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
My thoughts on why having all of the apps using a single database would be bad - especially if the apps arent truly related to each other:
1) scalability
2) all of your eggs are in one basket
3) dependenace on each other - making a change to a production app requires we address all production apps for conflicts. we cant just focus on the app being modified.
4) what if we have/need to use a trigger on one table, but that data is shared elsehwere - all of this has to be looked at
5) say someone breaches the database - now they have the whole kindgom - individually each is its own castle
some thoughts?
-
And the list can go on...
1. Now all the processes will compete between each other for server resorces.
2. Backup will much longer.
3. If you need to restore it, all the rest users will have to wait until you're done.
4. If your maintenence process will require shutdown - it will affect all other applications.
-
Anyone have any additional thoughts on this - I am putting together a PROS and CONS list and would appreciate advice in either direction.
Thanks for all the help!
-
There aren't any PROS to this.
Whats the business driver for this work?
What do your applications do, what resources do they need?
What availability is required by the business?
The answers to these questions will only provide more reasons not to do this.
However, it is much more feasible in 2005 as they've implemented user-schema seperation so you can segregate data well enough within a database to hold entirely different applications.
-
The PROS the managers associated with this concept have identified:
1) easier to maintain because we just have 1 database
2) easier to manage permissions because we just have 1 database
3) no redundant data (but in this case redundant data amounts to login data and thats about it)
I think thats about all. I personally don't see much a point to it but given the lackluster outrage on this forum maybe there is merit to it.
Frankly I was appalled at the idea.
-
I can see the merit in keeping all databases on the same instance - you don't need to use linked servers etc.
The only pro as far as I can see is declarative referential integrity being possible only in the same db.
All apps in the same database, though, will create lots more difficulties than it will solve. Especially for code promotions. Also you'll have to come up with messy naming conventions to differentiate the objects in each app.
We have a mixture of real time apps (updated during the day via a UI) and reporting type apps (updated usually only by a file during the night.) In the case of a problem, we can rollback the latter, without having to worry about losing data in the former. (As they're in separate databases.)
Other comments regarding the unnecessary dependencies are of course very pertinent.
...and I don't buy the arguments put forward by your management (none of the three.)
good luck.
-
i don't know about the easier to manage permissions part
where i work we have a few sql logins with the same permissions across all servers. R/O servers only have the R/O login and the R/W servers have the R/W login as well.
i think that for some people it's a personal issue of scaling up vs scaling out. some people like one super server to host everything for whatever personal issues they have like thinking it's cool to have a huge server in their datacenter that cost more than their salary and that can be used to heat their house in the winter.
other people like scaling out with more smaller servers
i'm in the scaling out camp because i think that if something happens it's better to affect only part of production instead of everything
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply