Replication to maintain data integrity

We have two situations:

We have a database server that contains tables of job titles. Those job titles need to be accessed by a database on another server. We have established a linked server connection and everything works well. Problems: There can be no foreign key referential integrity because the tables are physically stored on another server. Also, whenever the first server is taken down for maintenance, it breaks the applications on the second server because they are dependent on it for the linked server connection and data.
On another database server, we have a database that is used to hold common data elements. For example, there is a table of US states and territories, a table of zip codes, and various code tables used throughout our applications. Problem: Just like above, there is no referential integrity abilities. In addition, maintaining the security and ensuring that the users who have access to the application database have the necessary access to this "common" database is tedious and time consuming.

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?

asked Feb 05 '10 at 04:22 PM in Default

NYSystemsAnalyst
312 ● 5 ● 5 ● 6

(comments are locked)

1 answer:

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:

Enforce it with custom written triggers. This can be done accross databases if you use replication or even across servers if you do not want to use replication.
Use a non-straight forward implementation so that you are putting the replicated tables into the same database with the tables that will refer to them. This will allow you to create a foreign key relationship. But it can cause problems if someone forgets that those tables are replicated and tries to change them directly.

I would recommend number 2, it just needs to be documented and monitored carefully.

answered Feb 05 '10 at 07:43 PM

TimothyAWiseman
14.3k ● 16 ● 20 ● 29