Immediate Updating Components

Immediate Updating Components

Immediate updating is supported using:

• Triggers
  
  
• Stored procedures
  
  
• Microsoft Distributed Transaction Coordinator (MS DTC)
  
  
• Conflict detection
  
  
• Loopback detection

Triggers

An update trigger at the Publisher updates the MSrepl_tran_version column for the updated rows when needed.

Triggers at the Subscriber capture transactions and submit them to the Publisher using a remote stored procedure call within a 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:

• Extract values from inserted or deleted tables at the Subscriber.
  
  
• Call the BEGIN DISTRIBUTED TRANSACTION statement.
  
  
• Execute a remote procedure to call the relevant stored procedure at the Publisher, passing values from inserted or deleted tables.
  
  
• Manage identity and timestamp values at the Subscriber. In the case of immediate updating subscriptions, the new values generated at the Publisher for these types of columns are propagated to the Subscriber as part of the 2PC transaction.
  
  
• If the remote stored procedure call succeeds, commit the transaction, reflecting exactly the same changes at both the Subscriber and the Publisher.

  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 and use the immediate updating option, but 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.

  When creating synchronization triggers for immediate updating or queued updating subscriptions, additional calls to the sp_settriggerorder system stored procedure are made to specify the firing order for the INSERT, UPDATE, and DELETE triggers so that these triggers fire first during synchronization. If there is already a trigger set to fire first, an error will be returned and the subscription will be marked inactive. If you receive this error, you should either remove the existing trigger or set the firing order to none. Restart the Distribution Agent so that the initial snapshot and triggers are applied at the Subscriber.

Stored Procedures

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 subscription stored procedure at the Publisher is:

• Insert procedure

  Attempt to insert rows. Check @@ROWCOUNT and @@ERROR, and return success or failure to calling trigger. May also return an identity value to the Subscriber if required.

• Delete procedure

  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.

• Update procedure. Attempt to update row, with a WHERE clause that qualifies the unique index and uniqueidentifier column in current row, with unique index and uniqueidentifier value from deleted table. Check @@ROWCOUNT and @@ERROR, and return success or failure to the calling trigger. May also return an identity value to the Subscriber if required.

Note  A transaction that affects multiple rows must have all rows reflected at both sites to succeed.

Microsoft Distributed Transaction Coordinator

Microsoft Distributed Transaction Coordinator (MS DTC) manages the two-phase commit operation between a Subscriber and Publisher inside a Microsoft® SQL Server™ 2000 remote stored procedure call using the BEGIN DISTRIBUTED TRANSACTION statement in Transact-SQL.

Conflict Detection

The Publisher stored procedure uses the uniqueidentifier 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 uniqueidentifier value (generated at the Subscriber) to the Publisher, along with all other columns in the row. Within the Publisher's stored procedure, this value is compared to the current uniqueidentifier 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. If a conflict is detected, the transaction is rejected, and the application should treat it like any transaction rollback. This usually means that the Subscriber needs to synchronize with the latest data changes at the Publisher before attempting to update the same data locally.

Loopback Detection

If a transaction is applied successfully 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 2000 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 Subscriber with immediate updating subscriptions or tables that reside in different databases across Subscribers with immediate updating subscriptions should not be updated in the same transaction.

Warning  Using the same transaction to update tables that reside in different databases at the Subscriber or to update tables that reside in different databases across Subscribers that have immediate updating subscriptions will delete the information necessary to control loopback detection and may cause replication to fail. Loopback detection is tracked at the transaction level. If the transaction involves more than one subscription database, SQL Server will attempt to mark the transaction with the Subscriber server name and database name multiple times. The last entry will overwrite all previous entries.