April 15, 2002 at 4:04 pm
I have a replication situation that I must resolve soon - your input will be most welcome.
Scenario:
SQL2000 SP2, transactional replication. Publishing database has one table, table_a, with one column, a_col int not null, which is PK.
I can publish and replicate this table via push subscription into a subscribing database on same server. I then create a FK relationship from another table in the subscriber (table_b) into table_a, such that a record must exist in table_a in order to insert a record into table_b. I now have records in table_b that depend on the key value being in table_a.
The problem that I must resolve:
I can delete a record in table_a in the publishing database that has dependent records in table_b in the subscriber. As soon as the record is successfully removed from table_a in the publisher, the Distribution Agent Monitor shows an error and the record I deleted in table_a of the publisher is still in the subscriber. The record could not be deleted from the subscriber table due to the FK constraint. Now the tables are out of sync and replication is stopped until I resolve the problem.
Has anyone experienced this situation? If so, what are you doing to work around this problem?
Thanks very much for your time,
Steve Armistead,
Database Administration
Panther Systems Northwest, Inc.
Vancouver, WA
Steve
April 15, 2002 at 4:28 pm
Yes it is never advised to use any kind of relations on a subscribers tables but you can try a few things. (I have not tried this myself sorry, but the principal should be right) On the subscriber you may be able to set cascade updates and cascade deletes on table_a for table_b thus a delete on the publisher should be handled correctly on the subscriber. Or you can do thru triggers on the subscriber and not use the FK but this is a bit tricker to maintain data consistancy. Or finally you could just write a DTS package that runs periodically and drop replication. You can build all kinds of logic in the package, but then this is trickest of all to ensure consistancy.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 15, 2002 at 8:28 pm
I agree that changing things on the subscriber requires extra care, not to be done lightly. Cascading delete seems like a good suggestion for this case. If you enforce the fk on the publisher, why worry about the subscriber?
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply