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.
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"