sql server replication: We decided to replicate a database post-design and deployment and have had a
few struggles. The first one was with the numerous foreign key constraints
that were not marked NOT FOR REPLICATION. We altered them and are now not
getting duplicate data errors. I understand that this is b/c certain updates
are replicated as deletes followed by inserts (clustered indexes, keys,
etc.). However, we have been having a number of possibly unrelated problems
with this server (SQL2000, sp3, Windows 2003) and have an open case with MS.
The most recent issues are the sql server service terminating unexpectedly
(no errors or access violations) and after it restarts, the agent service
just keeps terminating unexpectedly and restarting. MS has asked that we
look into any triggers that we have in our replicated database and change
them to NOT FOR REPLICATION. I have done this in development but before
running the script in production, I'm trying to find out what the results of
this will be. Will the data in the tables being altered by the triggers
still be altered with the replication if I mark the triggers NOT FOR
REPLICATION? We would like to rule this out as the cause of our problems but
I need to understand what to expect in terms of the data in this publication
if I change these triggers.

I have posed the question to our MS Support person but have yet to hear a
response. I will give them a call but would also appreciate another expert's
opinion of this.

Michelle

Thanks. That's pretty much what MS said. We already had articles for the
audit tables and they are therefore being kept in synch that way. We have
altered the triggers so that they are NOT FOR REPLICATION.

[quoted text, click to view]

Michelle,
altering your triggers and setting them to NOT FOR REPLICATION will mean
that they won't fire when the data change originates from the
synchronization process. However sometimes you want the data to be
consistent between 2 tables eg perhaps you have TableA which has an audit
trigger to update TableB. In this case the standard way round this is to set
the trigger to NFR and replicate both tables. If there is a PK-FK
relationship at work, you might need to set the FKs to NFR also, because the
parent might be processed after the child record.
HTH,
Paul Ibison

(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)