Queued Updating Components
Triggers, stored procedures, queues, and the Queue Reader Agent are the components used with queued updating.
Triggers
When immediate updating, queued updating, or immediate updating with queued updating as a failover is enabled, triggers are attached to the replicated table at the Subscriber. With queued updating, the triggers capture transactions initiated at the Subscriber, and then package the transactions into messages and place them in a queue. This occurs within the same transaction to ensure that the update to the local database and the queuing of the update is atomic.
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 trigger to fire.
If you subscribe to a transactional publication and use the queued updating option but do not initialize the subscription; the queued updating triggers are not applied to the Subscriber automatically. Instead, you must create the triggers manually at the Subscriber using sp_addsynctrigger.Manual initial synchronization of a queued updating subscription is discussed later in this topic.
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
When you create a publication and enable it for queued updating by default, stored procedures to insert, update, and delete data in the published table are created automatically on 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. INSERT, UPDATE, and DELETE stored procedures are created for each article.
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.
Storing Messages in a Queue
Subscribers with the queued updating option can use either a Microsoft® SQL Server™ 2000 queue or Microsoft Message Queuing version 2.0 on Microsoft Windows® 2000 Server as the queuing mechanism. When selecting queued updating, the default is a SQL Server 2000 queue, which is available to all instances of SQL Server. After creating the publication, you can change the queue to Message Queuing using the publication properties dialog box. This must be done before activating any subscriptions to the publication.
To see which rows have changes that are pending in a queue, execute sp_getqueuedrows in the subscription database at the Subscriber.
SQL Server Queue
When using SQL Server 2000 queue, each Subscriber has its own queue in the form of a SQL Server 2000 table (MSreplication_queue) in the subscription database. The triggers store all messages in the SQL Server 2000 queue until the Subscriber reconnects to the network after updating published data. The Subscriber and the Publisher must be connected and available for the updates to occur.
The Subscriber is dependent on the Queue Reader Agent to read and empty the queue. The Queue Reader Agent reads messages on a Subscriber, finds modifications, and propagates the changes to the Publisher. It then repeats this process at each Subscriber.
Using SQL Server 2000 queues requires that all three servers (Subscriber, Distributor, and Publisher) are connected and available when queued updates need to be applied at the Publisher. Updates made at the Subscriber can be queued without the Subscriber, Distributor and Publisher being connected. SQL Server 2000 queues at the Subscriber can be monitored using the sp_replqueuemonitor stored procedure.
SQL Server queues:
- Work with all SQL Server platforms (Windows 98, Windows NT® 4.0, and Windows 2000).
- Do not have any additional components that need to be installed.
- Are faster for updates made at the Subscriber to queue.
The sp_getqueuedrows stored procedure returns a result set consisting of rows in the user table that have pending updates in the queue not yet picked up by the Queue Reader Agent. This procedure can be used to identify the rows that can be considered tentative.
Microsoft Message Queuing
If you are running Windows 2000 Server on the Distributor and Subscriber, you have the option to use Microsoft Message Queuing as the queuing mechanism at the Subscriber. Message Queuing provides additional routing, centralized monitoring, and administrative capabilities beyond what is available with SQL Server 2000 queues.
When using Message Queuing as the queuing mechanism, the update is packaged as a message and is placed in a queue on the Distributor under a two-phase commit protocol (2PC) transaction managed by Microsoft Distributed Transaction Coordinator (MS DTC).
When the Subscriber is disconnected from the network, Message Queuing stores transactions as messages in a cache on the Subscriber until they can be sent to a corresponding queue on the Distributor. You must enable Message Queuing on both the Subscriber and the Distributor. The Queue Reader Agent, which runs at the Distributor, reads the queued messages asynchronously and applies them as transactions to the appropriate publication.
Using Message Queuing provides some advantages over SQL Server 2000 queues. In addition to routing capabilities, it offers centralized queue administration and monitoring. This is not possible with SQL Server 2000 queues because the queues are distributed at each Subscriber instead of consolidated at the Distributor.
Message Queuing provides better offline capabilities including propagating offline changes to the queue at the Distributor without SQL Server running on the Subscriber. In addition, Message Queuing does not require availability of the Publisher when the Subscriber reconnects to the network after updating published data. Message Queuing propagates messages automatically when the Subscriber comes online without relying on the Queue Reader Agent to read and empty the queue. It will also be a better choice if there are many Subscribers.
You will need to install Message Queuing on each Subscriber and the Distributor. Queued updating works with Message Queuing installed in workgroup mode on Windows 2000. This eliminates the need to install Message Queuing on a Windows 2000 domain controller and should be the preferred installation method unless you have other Message Queuing requirements that preclude using workgroup mode (for example, Message Queuing in workgroup mode does not allow public queues and cannot use Message Queuing authentication or encryption).
For Message Queuing installed in workgroup mode, install Message Queuing on the Distributor and on the Subscribers. For Message Queuing not installed in workgroup mode, install Message Queuing server on the domain controller and Message Queuing independent client on the Distributor and on the Subscribers.
To install Message Queuing on the Distributor and Subscribers