Replication: Issue with setting up Transactional Replication with Updatable Subscribers and NFR Triggers

Problem:

I have set up a Transactional Replication with Updatable Subscribers on my SQL 2005 server and created user triggers on the Publisher database. These triggers have been configured with “not for replication”.

I update my subscriber and the changes are propagated onto the publisher. I then update my publisher and these changes are not sent to the subscriber even though no error message is flagged. If I attempt to alter the data on the subscriber side, I obtain the following error:

Server: Msg 20515, Level 16, State 1, Procedure sp_MSreplraiserror, Line 9

Updatable Subscribers: Rows do not match between Publisher and Subscriber. Refresh rows at Subscriber. Server:

Msg 20512, Level 16, State 1, Procedure sp_MSreplraiserror, Line 8

Updatable Subscribers: Rolling back transaction.

This issue is not reproducible in SQL 2000

This issue is reproducible in SQL 2008

Cause & Workaround:

Looking at SQL 2000’s behaviour:

There was an issue in SQL2000 in regards to cached plans for stored procedures with regard to repl agent connection: when a proc is first executed by repl agent, the plan contains information about repl agent connection property, this plan is cached, later when proc is executed by non-repl connection the same plan is used hence it will behave as if it was executed by repl agent. In this particular case sync procs on publisher side are created by replication snapshot agent hence the plan was created with repl agent connection flag, when subscriber trigger fires and invoke publisher side sync proc through RPC, it should have been treated as non-repl connection but sync proc on publisher was using the cached plan from before hence it was behaving as if coming from repl agent connection. If you manually drop and recreate the sync procs on publisher from QA window, subsequent subscriber side change will cause user NFR trigger on publisher to fire, this has been fixed in SQL2005 hence the behaviour difference.

Unfortunately the problem in SQL2000, as described above, was indeed a pretty serious one as well, because once sync procs on publishers are created by snapshot agent, it will always behave as if called by repl agent, which means one can in theory invoke them directly in publisher db and it would honour NFR trigger by not firing them. This is undesired and can be considered as a hole. Not to mention if user manually drop and recreate these sync procs for whatever reason, it will behave the same as SQL2005.

Having said these, we realize the inconsistent behaviour and we are taking steps to provide better and more consistent user experience:

1. the fix we made in SQL2005 was meant to plug the hole so NFR triggers are only honoured by repl agent connection
2. there is still the fundamental difference in how RPC and repl agent behave in this case, we hope to provide better support to updateable scenario by adding conflict detection capability into Peer-to-peer replication to in the near future we can consider deprecating 'immediate update' future which means by then all updateable subscription will be serviced by repl agent instead of RPC, which will help providing uniformly consistent behaviour.

Workaround for this issue:
1) consider using 'queued' as the update mode instead of 'failover' - if you leave queued agent running in continuous mode it will work near real-time (within a couple of second) and NFR trigger will always behave as you expected.
2) create publisher side trigger not as NFR and add context_info logic (see 'set context info ..' in BOL) into subscriber side so publisher trigger can figure out if a change was made on subscriber or publisher.

Example:

IF NOT EXISTS(SELECT * FROM master.dbo.sysprocesses
WHERE spid = @@SPID AND
CONVERT(tinyint, SUBSTRING(context_info, 1, 1)) & 0x80 = 0x80)
RETURN