So I've got two servers, we'll call them Server A and Server B.

Server A has a table, Table 1, which gets replicated to Server B.

Server B has a table, Table 2, which gets replicated to Server A.

All replication is transactional and pushed from the publisher continuously.

On Server B, there is a trigger on Table 1 which will ultimately create a record in Table 2.

Other various processes also create additional records in Table 2. All of the records in Table 2 are supposed to get replicated back to Server A.

In short, the data from Server A, Table 1 is supposed to make a round trip back to Server A in table 2 along with other records. However only the 'other records' are coming back. None of the data which originated on Server A is coming through, even though it's been successfully inserted into Table 2 on Server B.

Any ideas?

Additional info:

When I generate a new snapshot all those records that didn't replicate are included in the snapshot and do get replicated over. My suspicions lie in what's happening behind the scenes when the trigger fired by replication inserts the data into Table 2 and perhaps something is preventing those records from replicating over.

The trigger looks something like:

Create Trigger [dbo].[trg_INS_TableA] 
For Insert

Declare @<Various Parameters>

Select @<Various Parameters> = <Record Data>
From inserted

Declare @RC Int
Exec @RC = [MyDB].[dbo].[up_INS_TableB] @<Various Parameters>

And up_INS_TableB transforms and inserts the Table A data into Table B

  • Any errors you are encountering in replication monitor or error logs ? – Kin Shah Aug 23 '13 at 13:03
  • No, I haven't seen any errors. Near as I can tell, replication is merrily going about its business. – user27401 Aug 23 '13 at 13:34
  • What does your trigger look like? – Brandon Williams Aug 23 '13 at 16:06
  • @BrandonWilliams, I've updated the OP with a block of code to show the structure of the trigger. – user27401 Aug 23 '13 at 18:14
  • @Superbeard Best option is to use Profiler as it will give you whats happening behind scenes. – Kin Shah Aug 23 '13 at 18:22

The reason the round trip is not occurring is because your subscription from Server B to Server A has its @loopback_detection property set to TRUE, which is the default setting.

As we can see from sp_addsubscription, @loopback_detection specifies if the Distribution Agent sends transactions that originated at the Subscriber back to the Subscriber. If set to true, the Distribution Agent does not send transactions that originated at the Subscriber back to the Subscriber. If set to false, the Distribution Agent sends transactions that originated at the Subscriber back to the Subscriber.

I'm aware that the documentation states that this property is used with bidirectional transactional replication, however, I have reproduced your scenario and it appears to be being enforced with plain vanilla transactional replication as well. Dropping the subscription and adding it back with @loopback_detection set to FALSE alleviated the problem and allowed for the round trips to occur.

Technically your topology can be considered Bidirectional Transactional Replication since both servers are publishing and subscribing to and from each other, even though the articles are different.

Keep in mind that you will need to drop and add the subscription back with @loopback_detection set to FALSE since sp_changesubscription does not allow you to change the @loopback_detection property on the fly.

I hope this helps.

  • I guess since it's less about the data being replicated and more about where the data came from. I appreciate it! – user27401 Aug 26 '13 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy