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
As
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
Best Answer
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.