June 25, 2010 at 6:10 pm
We have several applications each with separate databases that need to be accessed, backed up and restored seperately. The problem arises from having SOA environment, where the pkeys from these lookup tables are often passed as parameters to service method calls. We absolutely need RI constraints defined in each database, which kills the ideal solution which would be to place all tables in a central database and add views to each application database with the same name of the lookup tables. All databases, at least for now, will be on the same sql server instance. Triggers are out, as is trying to enforce RI via the Business layer of applications or repository tiers. The best we have been able to come up with is using single master transactional replication to transfer the specified subset of common tables needed by each app to it's database. This allows us to do backups and restores of the database at a logically consistent point in time, rather than have to worry about restoring multiple databases to a single point in time. I don't like this solution but is the best we have been able to come up with. Consolidating all databases into one is not an option for a variety of reasons. These tables will be fairly small and change rarely. Any suggestions?
June 26, 2010 at 1:23 am
an idea:
funnel all inserts and updates to the lookup tables via stored procedures
and use a transaction to update tables across all databases
this would give you all the benefits of the transactional replication without the hassle and ongoing db management issues
plus now you can setup RI local within each db
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply