September 11, 2003 at 10:36 pm
Hi all,
We have a dozen or so applications using SQL Server databases. Our practice has always been to have a database for each application.
A new contractor started with us and said why not have one database with everything in it, and use diagrams to manage the separate applications. This would allow referential integrity across databases and make querying the data faster.
Would appreciate any comments.
Cheers,
Steve
September 11, 2003 at 10:51 pm
Hi
I think the developer has asked the right questions here..
a) do you require cross db table integrity? how else will you manage it if you do and the tables are in seperate db's?
b) siloed apps but same data model? (data duplication/redundency issues here?)
c) do you require different backup/recovery regiems for the db's?
d) apps will move around the organisation?
e) apps require specific instance settings? probably not the case in your example but some may support 7, others 2000 (for example)
f) how is the seperate db vs single db paradigm going to work for you in DEV and TEST, any issues here?
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
September 12, 2003 at 5:57 am
Hi,
just one more thing in addition to what has already been said:
Do you have any processes in any of the databases that could cause extensive locks or use large proportion of system resources when run?
In general, if the data are related, one database is better than several - but it isn't a strict rule, you simply have to analyze your DBs and test... and test. Maybe you'll find that it would be to your advantage to have 2 or 3 databases instead of one, depending on what data are stored in them and how they are processed. As an example, we have one main production database (~90GB) and a few small (~300MB) databases. One of the small ones is used for application that checks in returned parcels (from mail-order business), does a little pre-processing and then during night sends everything to production DB for final processing. Works nice... much better than if this would be done in production directly.
Good luck!
Vladan
September 12, 2003 at 6:04 am
Point #1 made by Chris is superb - if you need cross db integrity (think about doing a restore of a single db) you'll do a LOT of work to make it happen. In practice where I work we do much the same thing, new applications that don't use existing data get their own db. If they are add-ons or whatever for existing apps, then we take a harder look to see what is "right".
Andy
September 15, 2003 at 12:25 am
You can keep the apps in separate dbs, with their own specific tables, views, procs, et al. Meanwhile any common data tables are accessed from one main DB.
That's what I've usually done. When I start combining apps in one db, I run into too many problems keeping it organized. But maybe I'm not knowledgeable/clever enough make it work in one db.
The only drawbacks I can think of with this approach is with permissions, and with db backups not containing all data required for the app. Neither has been a real problem.
I'm curious now on what others think.
Data: Easy to spill, hard to clean up!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply