How Queued Updating Works

SQL Replication


How Queued Updating Works

When you create a publication and enable it for queued updating, data modifications can be made at the Subscriber and then held in a queue until they can be applied to the Publisher and then propagated to other Subscribers. The queue is implemented as a Microsoft® SQL Server™ 2000 table but on Microsoft Windows® 2000, it can optionally be implemented using Microsoft Message Queuing. For more information, see Queued Updating Components.

The following illustration shows how triggers, queues, and the Queue Reader Agent work together to complete this process.

  1. Updates made at the Subscriber are captured by triggers on the subscribing tables. The triggers store these updates in a queue, which by default is a SQL Server queue. The triggers are created automatically when the subscription is created.

  2. If you are using SQL Server queues, updates will be stored in a table designated as the queue (called MSreplication_queue), which is created automatically when the subscription is configured. If you are using Message Queuing version 2.0, the updates will be stored in a message queue at the Distributor. If the Subscriber is disconnected from the network, it can continue to generate messages destined for other computers. Message Queuing stores the messages locally, and automatically sends them to the queue at the Distributor when network connection is restored.

  3. The Queue Reader Agent applies queued transactions to the appropriate publication. When using SQL Server 2000 queues, the queued transactions are read directly from the queue stored on the Subscriber. When using Message Queuing, the queued transactions are read from a queue stored at the Distributor.

  4. 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, but they are sent only to the Subscriber that caused the conflict.

  5. Any changes made at the Publisher are propagated to all other Subscribers according to the Distribution Agent schedule.