We have two situations:
My question is: Since this data is read-only for the consuming applications, could we use replication to solve this problem? Could we replicate the one table of job titles from the source to the destination server / database, and could we do the same for the tables in the "common" database (replicate them to any application database that needs them)? I think this would eliminate the above problems, but would it be a wise course of action or would it cause more issues than it solves?
It will certainly cause more maintenance work, but it will fix the problem of the application breaking when the first server is taken down for maintenance.
However, at least in a straight forward implementation of replication (in that the replicated tables are in their own database, probably with the same name as the source database) it would not permit you to referential integrity. Remember that foreign keys can only be created within a single database. If you want referential integrity you have some options:
I would recommend number 2, it just needs to be documented and monitored carefully.
answered Feb 05 '10 at 07:43 PM