May 13, 2015 at 3:22 am
Hello! I have a replication issue with an instance of sql server 2008 r2 configured as publisher and an instance of sql server 2014 configured as subscriber.
The publisher has two transactional publications, one updatable and one not updatable. Previously I had another sql server 2008 r2 instance acting as subscriber for these two publications and all was fine. I changed the server with a sql server 2014 instance and I registered this new server as subscriber for the publications.
Everything works fine except the thing I'm going to describe you:
Table A is part of the updatable subscription , Table B of the non-updatable.
A website acting on the sql server 2014 instance writes in Table A on the Web server, replication "copies" the inserted row in Table A on the local server.
Inserting in this table throws a trigger that updates a text field in Table B (the id field is the same in both Table A and B) that is part of the publication not-updatable. The updated field should be "copied" from the replication service in table B on the web server.
Everything works except this last step and I don't understand why ... If I open table B on publisher server with management studio after the trigger fired, I see the updated field but the update is not transferred to the web server. If I make a manual update of this field with the same query executed by the trigger, the change is correctly replicated online on the subscriber ...
If I make a manual insert in Table A on the local publisher server (instead of an entry made by replication after an insert done on the web server- subscriber) the trigger fires and makes the same update on Table B as in the first case but this time the change on the table B on the subscriber is replicated.
It seems that the update made by the trigger is not "visible" to replication and so is not replicated, instead the same update made manually is correctly replicated.
Anyone has an idea on which could be the problem?
May 13, 2015 at 8:39 am
This is from https://technet.microsoft.com/en-us/library/ms151718%28v=sql.105%29.aspx
User-Defined Triggers
If the application requires triggers at the Subscriber, the triggers should be defined with the NOT FOR REPLICATION option at the Publisher and Subscriber. For more information about this option, see Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION. This ensures that triggers fire only for the original data change, but not when that change is replicated.
Ensure that the user-defined trigger does not fire when the replication trigger updates the table. This is accomplished by calling the procedure sp_check_for_sync_trigger in the body of the user-defined trigger. For more information, see sp_check_for_sync_trigger (Transact-SQL).
Immediate Updating
For immediate updating subscriptions, changes at the Subscriber are propagated to the Publisher and applied using Microsoft Distributed Transaction Coordinator (MS DTC). Ensure that MS DTC is installed and configured at the Publisher and Subscriber. For more information, see the Windows documentation.
The triggers used by immediate updating subscriptions require a connection to the Publisher to replicate changes. For information about securing this connection, see Security Considerations for Updating Subscriptions.
If the publication allows immediate updating subscriptions and an article in the publication has a column filter, you cannot filter out non-nullable columns without defaults.
May 13, 2015 at 8:59 am
Hello! Thank you for the answer. In my scenario I don't have any trigger on the subscriber. The trigger is in the Publisher and simply updates a text field. The update il done in the table at the publisher, but this update is not replicated. Otherwise il I manually update the text field in the table, is correctly replicated
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply