January 25, 2007 at 8:13 am
My background is in front-end programming rather than database architecture, so pardon me in advance if this is a no-brainer.
I am beginning design work for a couple of new applications. I will be housing the data in a SQL2000 database.
There is no current plan for the applications to share data between themselves.
Is it better, from a design standpoint, to create a single database and have it contain all of the objects for these two applications and any future applications; or would it be better to create separate databases for each application?
Thanks for any opinions.
Mike
January 25, 2007 at 8:30 am
If the applications are truely unrelated and have no need to share data, create separate databases for each application.
It makes them easier to manage. For example, in the future you might want to put them on different servers for performance reasons.
January 25, 2007 at 9:49 am
I agree with dclark.
It makes for easier management long term.
Also, with the databases seperate, if somewhere down the line you did need to query data from another database, then you can use a Linked Server to acheive this.
January 26, 2007 at 6:20 am
On the other hand, if the applications are even slightly related, there's an advantage to having a single, unified schema though...ease of deployment and maintenance, because you need to support and upgrade a single schema, rather than one db and all it's upgrades for each application. having a suite of 1000 tables that some application will never use has minimal impact, though it may be annoyingly large as far as number of objects...Installation footprint tends to be easier, as there is just one db, and so the user maintenance is easier.
What if you have a single client with two or three of your applications? for the clients ease of use, a single database is easier on their IT and SQL server guys when it comes to deployment and upgrades. also, standard lookup tables (contacts, states, zipcodes, counties, whatever) don't need to exist in mulitple places. reporting is also easier if financial data is invloved (app1 sales + app2's volume, etc), if the data needs to be reported together.
One of the cons I've encountered is when it comes to upgrading; application #1 might be ready with a new "version" and need to go right NOW, but another application needs another week or two, while the others are not ready at all. holding back a release to coincide with another may or may not be acceptable, while having weekly updates to support each/various app releases may also have a negative impact.
HTH
Lowell
January 26, 2007 at 9:22 am
You should also look at the type(s) of applications that they are. By this I mean OLTP, ETL, DSS, Reporting, web based, pure batch or a mixture. The different application attributes will tend to change your database recovery intervals and even the type of database recovery chosen not to mention different SLAs for each applciation potentially. Also, whilst having many databases as opposed to just one, upgrades will be easier as pointeed out, customized database maintenance will also be more granular and tunable for the applications as well. However, more databases tends to be more JOBs to do things.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
January 26, 2007 at 9:47 am
Even if the data is related, unless all of the applications are hitting the same data, setup seperate databases. You can simulate having the tables in multiple databases by setting up views in the other databases.
When you go to setup your backup and restore schedules you will be happy that you setup more, smaller databases.
I ran into your problem a couple of years ago, back in the 6.5 days. I put a couple of different applications into one database. This database got moved to 7.0 and is still a beast to manage. I have slowly been moving applications out of the database.
With views you can simulate having one table in another database. It makes it real nice for developers. With linked servers you can take this a step further.
Bottom line - if you can logically break them up into seperate databases, do it. If you feel that you have too many small databases, then group a couple together. One big database sounds good, but it's a pain to maintain.
January 26, 2007 at 9:58 am
Real issue here is what you are separating. Essentially, each database is its own file with an associated log. If you have different logging requirements then that's a pretty good reason for a separate database. If the applications are unrelated and have different administrative models--including different administrators--that's another good reason to consider separate databases. If one of the applications hits the database hard and the another accesses lightly or infrequently, may be a good idea to split. Size matters, too, but this is more of an art than a science since databases frequently get larger over time.
January 26, 2007 at 11:16 am
We overdid the separation a few years ago and tend to integrate databases back into larger ones (from 35 to 3) except data from foreign sources. The main reason is that the interaction is becoming greater between the inhouse applications.Relation integrity might help to keep the data clean and avoid some duplications. The main drawback is that a restore takes longer because the database is bigger in size.
January 26, 2007 at 2:31 pm
Using separate databases makes managing security easier as well. You don't have to pick and choose which objects each database should be able to access.
Of course, if this was SQL 2005, you could do the same using schemas.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply