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

    • 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. 

        • 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

          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.

             1. QUEUED UPDATING

                1. 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. 

                   • 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.

• • • • • • • • 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.