SQL Server Performance

Transactional Replication and Triggers

Discussion in 'SQL Server 2008 Replication' started by jwchoc, Mar 7, 2010.

  1. jwchoc New Member

    I am using bidirectional transactional replication between SQL Server 2008 databases. Publisher A replicates an insert to the subsciber table on publisher B. Publisher B also has an INSERT/UPDATE trigger on this table. B's trigger gathers data and puts it in tables which are replicated back to A. The trigger is firing and putting the data into the tables for replication from B to A and the log reader agent is copying the transaction into the distribution database. The triggered data is never replicated from B to A. Though the log reader knows it is a transaction on replicated tables, the distrubution agent somehow thinks this triggered transaction shouldn't go back to A. If I look at the 'Undistributed Commands' tab of the replication monitor I see that there are 0 commands waiting to replicate back to A. B's publication is unfiltered, so anything put in the publication article tables should replicate back to A which is a subscriber.
    When the insert replicaties from A to B the triggered data does not replicate back from B to A. When I perform a manual insert of the same data in B's table, the triggered data replicates to A successfully. I have verified that the log reader agent account belongs to the schema that these tables belong to so I don't think this is the problem. The trigger is not marked as 'NOT FOR REPLICATION'; I have verified this in sys.triggers and by looking at the trigger source. The biggest difference between A's publication and B's publication is that A is filtered and B is not.
    This replication setup worked in SQL Server 2000 but not in SQL Server 2008. Can anyone think of a reason for this?
    Thank You
  2. FrankKalis Moderator

    Have you found an explanation in the meantime?
  3. jwchoc New Member

    I haven't found an explanation yet. I have created a simpler test trigger than the one I run in production and it has the same problems in SQL Server 2008. My next step will be to create new databases natively in SQL Server 2008 with a replication/trigger scheme to represent the one I am working with now. The databases I am working with now were upgraded from SQL Server 2000.
    I can't believe this is intentional behavior.
  4. satya Moderator

    By chance is that database left on backward compatibility?
  5. jwchoc New Member

    The problem is the same no matter what compatibility mode the databases are set to. I have tried 80 and 100 with the same results.
  6. MohammedU New Member

    I think it is because part of the same trasaction initiated at the publisher A so that it is not considering it as new trasaction.
    Why don't you try using linked server call update a row instead of replicating and firing the trigger and replicating back the updated data?
  7. jwchoc New Member

  8. jwchoc New Member

    SQL Server 2008 has a different definition of loopback detection. In SS2000, the same table would need to be replicated bidirectionally in order to be
    protected from a replicated loopback. In SS2008, the result of a replicated transaction cannot replicate back to the originating publisher no matter what
    tables are affected by the result.
    What MahammedU said is correct. If part of a replicated transaction started at publisher A, any side effects of that replicated transaction on B will not replicate from B back to A. This problem is solved.
  9. jwchoc New Member

    To get around this problem I have to explicitly set @loopback_detection = 'false' in the call to sp_addsubscription. Keep in mind that @loopback_detection = 'true' is the default argument in SS2008.
    Thank you everyone for your help.

Share This Page