x

Replication to maintain data integrity

We have two situations:

  1. 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.

  2. 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?

more ▼

asked Feb 05, 2010 at 04:22 PM in Default

NYSystemsAnalyst gravatar image

NYSystemsAnalyst
312 5 5 6

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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:

  1. 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.

  2. 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.

more ▼

answered Feb 05, 2010 at 07:43 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 21 23 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x306
x108

asked: Feb 05, 2010 at 04:22 PM

Seen: 1280 times

Last Updated: Feb 06, 2010 at 01:05 AM