The Immediate-updating Subscribers option is supported using the following components:
Triggers at the Subscriber capture transactions and submit them to the Publisher using a remote stored procedure call within a two-phase commit protocol (2PC) that is controlled by MS DTC. The triggers are created using the NOT FOR REPLICATION parameter of the CREATE TRIGGER statement so that changes applied by the Distribution Agent do not themselves cause the trigger to fire. The logic of the INSERT, UPDATE, and DELETE triggers is:
The Publisher then ensures that the changes are propagated to all other Subscribers. Otherwise, roll back the transaction and return an error to the user.
If you subscribe to a transactional publication, use the immediate-updating Subscribers option, and choose not to initialize the subscription, the immediate-updating triggers are not automatically applied to the Subscriber. Instead, you must create the triggers manually at the Subscriber using sp_addsynctrigger. You can use sp_script_synctran_commands to script out the immediate-updating trigger commands at the Publisher and then use those commands when running sp_addsynctrigger at the Subscriber.
Stored procedures at the Publisher apply transactions only if they do not conflict with changes made at the Publisher after the Subscriber last received its copy of the changes. If a conflict is detected, the transaction is rejected and rolled back at both sites. INSERT, UPDATE, and DELETE procedures are created for each article. The logic of the Immediate-updating Subscribers stored procedure at the Publisher is:
Attempt to insert rows. Check @@ROWCOUNT / @@ERROR, and return success or failure to calling trigger.
Attempt to delete rows, with a WHERE clause that qualifies the current row with values from deleted table. Check @@ROWCOUNT and @@ERROR, and return success or failure to the calling trigger.
Attempt to update row, with a WHERE clause that qualifies the unique index and timestamp column in current row, with unique index and timestamp value from deleted table. Check @@ROWCOUNT and @@ERROR, and return success or failure to the calling trigger. If success is returned, also return a new timestamp value in an output parameter.
Note A transaction that affects multiple rows must have all rows reflected at both sites to succeed.
Microsoft Distributed Transaction Coordinator (MS DTC) manages the two-phase commit operation between a Subscriber and Publisher inside a Microsoft® SQL Server™ remote stored procedure call using the BEGIN DISTRIBUTED TRANSACTION statement in Transact-SQL.
The Publisher stored procedure uses the timestamp column to detect whether a row has changed after it was replicated to the Subscriber. When the Subscriber requests an immediate-update transaction, it passes the timestamp value to the Publisher, along with all other columns in the row. Within the Publisher’s stored procedure, this value is compared to the current timestamp value for the row in question. If the values are the same, the row has not been modified after it was replicated to the Subscriber, and so the transaction is accepted.
Note timestamp is a monotonically, automatically increasing value unique within a database. It has nothing to do with chronological time.
If a transaction is successfully applied to a Subscriber and Publisher, it is unnecessary to propagate the change back to the originating Subscriber using the standard asynchronous transaction replication mechanisms. SQL Server replication has a loopback detection mechanism to handle this situation.
The information used to perform loopback detection is stored on a transaction by transaction basis. Consequently, tables that reside in different databases at the immediate-updating Subscriber or tables that reside in different databases across immediate-updating Subscribers should not be updated in the same transaction.
Warning Using the same transaction to update tables that reside in different databases at the immediate-updating Subscriber or to update tables that reside in different databases across immediate-updating Subscribers will delete the information necessary to control loopback detection and may cause replication to fail.