July 15, 2001 at 10:53 am
Hi all
We have three core applications (2 others out of our control).. all in seperate databases and share some commonality as far as using similar data sets and more importantly, share keys, ie. pkey of one table in app A and other pkey in appB will form an intersection table between the two applications.
The aim is to significantly improve the current "commonality" situation we are in. The key aims is to (ideally) have DBMS defined FK's between schemas to ensure complete integration between application schemas.
I am after some recommendations and/or similar senarios others have come across and the solution chosen.
Solution in privy to at present:
I am planning on a single "corporate database" that encapsulates the three core application shemas (owned by different users, ie. APPA, APPB, APPC).. then use various roles and other privs to control access between the applications. With appropriate data cleansing, we will have the FK relationships enabled between schemas and shared "common" tables. Key application Business objects will strictly apply business logic rules against specific tables, therefore, APPB wants to insert a new "code" into a tables "managed-by" APPA, then it must use the COM+ object of APPA in all cases (no code duplication)... in a control and secured environment as defined by the analysts.
The only real problem with this method is...
a) Single DB = Single Failover (not that problematic in a controlled and managed env)
b) Cant really "move" schemas to other locations.
c) Backup and Restores affect all app's
Other possible senarios have other problems..
a) Cant FK link between databases in the same sql*server instance
b) Cant FK link between "instances" using linked servers
Replication is another option, but places a level of complexity that I believe is unnecessary. Increasing maintenance and management costs in the short and long term.
Triggers are also an option, but what happens when a DB is down and a trigger attempts to fire and insert data over the link server to the other APP? this again requires further error trapping etc.
Anyhow, im not really sure what answers to expect?! but worth blurting it out either way.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 15, 2001 at 1:35 pm
If you're going to require apps to access other DB's via a COM object only, I dont see any advantage to consolidating them just to get fkeys working. Having one of the three db's offline is going to cause a lot of headaches anyway, your COM+ object would have to have the ability to queue actions (MSMQ maybe). Replication is actually a pretty simple way to do this, and if you do transactional replication with immediate updating/queued updating subscribers, you can still work if one is offline and not worry about losing transactions.
I think the one DB thing is a quest for the grail - usually much better to have one DB per app - in my opinion anyway!
Andy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply