January 20, 2005 at 4:53 pm
This is a question about which solution is better in terms of performance and maintainability.
I have many applications that I need to implement(legacy apps that need to be redone), each one of them are using its own database, but some of them access tables from the other databases becaure there are things they share and have in common. So my dilema is whether I should keep the same 'way of things' or if it is better to just use one database for ALL applications (using prefixs on table names and stored proc names to differentiate from one application's objects from another), considering that these databases are really big.
I guess my whole point is to know what is better: to have different databases that can communicate with eachother(through stored procs and/or views) or just one huge database.
Thanks in advance
January 20, 2005 at 5:04 pm
I believe the question to ask is where the workload is and plan according to that instead. I am assuming this is one server - so what is the filegroup config? If it is all the same filegroup and diskdrive - I dont see a performance gain either way. If anything you would improve performance by examining your filegroups and distributing your workload appropriately. Thats a bigger subject so i would encourage doing some reading.
The only reason i can see to consolidate all the db's into one on a server is for organization.
January 20, 2005 at 11:09 pm
Having a single db for multiple disparate applications sounds like a bit of a maintenance nightmare to me. I would keep them separate, but perhaps create an extra database called 'common' (or whatever) that contains the shared tables.
If you go down the single-database route, think about what will happen when upgrades/mods are made to an application's schema - conceivably all of the other apps are affected and therefore need to be tested.
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 25, 2005 at 10:52 am
If the legacy applications are fairly tightly connected in function and purpose, it would make sense to integrate them into a single database. This would make DB Maintenance and Backup much easier to create and control (1 set of jobs instead of N sets).
I unraveled a similar legacy system at my company. We had Access DBs that linked to other DBs, running two versions of Office (97 and 2K). It was a huge mess to work with, and always needed tweaking.
Now, we have a single SQL database, with about 3 middle-ware applications to import and export data for special purposes. The payback in time saved has been enormous. I rarely get a call from the users that something is broken -- usually they want some enhancement or other.
Good luck to you!
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
January 27, 2005 at 9:38 am
I'm definitely in the camp of 'Keep them Seperate'.
Over time, applications become legacy, are discontinued, or simply rewritten with new objectives. Sometimes, things simply go wrong with a database.
With the monolithic model, an error with any section of the DB (file error, user error causing restore etc) will impact the whole of the install base.
With a partitioned strategy, it becomes possible to simply restore one section of a DB (impacting one segment of the install base of all your apps, which actually uses that DB).
Application support becomes more logical (having all in one database feels much like looking at a sprawl of information) by containment, rather than naming schemes.
If, in future, these applications grow to require their own server, it's a sight easier to split off an already compartmentalised DB and use linking than it is to split out tables from the monolithic DB, then do all the above too.
I'm sure there are arguments to the contrary, but I've always found retaining flexibility, and not putting 'all the eggs in one basket' to be a more robust solution.
January 31, 2005 at 2:45 pm
Another argument for separate databases for separate applications, is in the off-chance you need to restore data from one, you're not obligated to restore data from all (or worrying about a workaround).
David W. Clary
MCSD
Data Miner 49er
Sr. Database Administrator, Ceiva Logic
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply