Share via

How Updatable Subscriptions Work

  • Article

Updatable subscriptions for transactional replication allow Subscribers to replicate changes to the Publisher. Triggers are added to the published tables in the subscription database, and when a change is made at the Subscriber, the trigger fires:

  • For immediate updating subscriptions, the change is propagated directly to the Publisher and applied using Microsoft Distributed Transaction Coordinator (MSDTC).
  • For queued updating subscriptions, the change is first propagated to a queue and then applied to the Publisher by the Queue Reader Agent.

Changes made at the Publisher are replicated to Subscribers in the same way as transactional publications with read-only Subscribers. For more information, see How Transactional Replication Works.

Immediate Updating

Immediate updating subscriptions use the following components:

  • Tracking column for each published table
    When a table is published in a publication that allows updatable subscriptions, the column msrepl_tran_version is added to the table. This column is used for change tracking and conflict detection. Conflicts in immediate updating occur if the Subscriber updates an outdated copy of the data.
  • MSDTC
    For each change made at a Subscriber, MSDTC manages the two-phase commit operation between the Publisher and the Subscriber committing the change. This approach does not have the availability limitations of using two-phase commit with all participating sites because only the Publisher needs to be available. After the change is made at the Publisher using two-phase commit, it is replicated to other Subscribers by the Distribution Agent.
  • Triggers on tables in the subscription database
    Insert, update, and delete triggers are added to each published table in the subscription database. The triggers are created using the NOT FOR REPLICATION modifier of the CREATE TRIGGER statement so that the changes applied by the Distribution Agent do not cause the triggers to fire. For more information, see Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION.
    For immediate updating subscriptions, the triggers also manage values for identity and timestamp columns at the Subscriber. Values are generated at the Publisher for these types of columns and propagated to the Subscriber as part of the two-phase commit operation.
  • Stored procedures
    When you create a publication and enable it for immediate updating subscriptions, insert, update, and delete procedures are created for each published table in the publication database. When a change occurs at the Subscriber, a replication trigger issues a remote procedure call through MSDTC to the appropriate stored procedure at the Publisher, which then applies the change.
    The stored procedures at the Publisher apply changes only if they do not conflict with changes made at the Publisher after the Subscriber last received its copy of the changed rows. If a conflict is detected, the transaction is rejected and rolled back at the Publisher and the Subscriber.

The following illustration shows the main components used in a topology that includes immediate updating subscriptions.

Immediate updating components and data flow

  1. A change made at the Subscriber is captured by a trigger on the subscribing table.
  2. The trigger calls through MSDTC to the appropriate stored procedure at the Publisher.
  3. The stored procedure performs the insert, update, or delete unless there is a conflict. If there is a conflict, the change is rolled back at the Publisher and the Subscriber.
  4. Changes made at the Publisher as a result of changes replicated from a Subscriber are propagated to all other Subscribers according to the Distribution Agent schedule.

Queued Updating

Queued updating subscriptions use the following components:

  • Tracking column for each published table
    When a table is published in a publication that allows updatable subscriptions, the column msrepl_tran_version is added to the table. This column is used for change tracking and conflict detection.
  • Triggers on tables in the subscription database
    Insert, update, and delete triggers are added to each published table in the subscription database. The triggers are created using the NOT FOR REPLICATION modifier of the CREATE TRIGGER statement so that the changes applied by the Distribution Agent do not cause the triggers to fire. For more information, see Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION.
  • Stored procedures
    When you create a publication and enable it for queued updating subscriptions, insert, update, and delete procedures are created for each published table in the publication database.
    The stored procedures are called by the Queue Reader Agent to apply transactions at the Publisher, detect conflicts, and if needed, generate compensating commands, which are posted to the distribution database and then delivered to the Subscriber.
    A stored procedure for logging conflict information at the Publisher, and optionally sending conflict information to relevant Subscribers, is also created at the Publisher. This is invoked by the Queue Reader Agent if a conflict is detected.
  • Microsoft SQL Server queue
    Each subscription database contains the system table MSreplication_queue, which stores changes from the Subscriber.
  • SQL Server Queue Reader Agent
    The Queue Reader Agent reads changes from the MSreplication_queue and applies them to the Publisher. For more information, see Replication Queue Reader Agent.

The following illustration shows the main components used in a topology that includes queued updating subscriptions.

Queued updating components and data flow

  1. Updates made at the Subscriber are captured by triggers on the subscribing tables. The triggers store these updates in MSreplication_queue.
  2. The Queue Reader Agent reads from MSreplication_queue, and then applies queued transactions to the appropriate publication using replication stored procedures.
  3. While applying the queued transactions, conflicts (if any) are detected and resolved according to a conflict resolution policy that is set when the publication is created. As a result, compensating commands may be generated to rollback a transaction to a Subscriber using the standard transactional replication distribution process (they are sent only to the Subscriber that caused the conflict). For more information, see Queued Updating Conflict Detection and Resolution.
  4. Changes made at the Publisher as a result of changes replicated from a Subscriber are propagated to all other Subscribers according to the Distribution Agent schedule.

See Also

Concepts

How Transactional Replication Works
Updatable Subscriptions for Transactional Replication

Help and Information

Getting SQL Server 2005 Assistance