Queued Updating Components
New Information - SQL Server 2000 SP3.
Triggers, stored procedures, queues, and the Queue Reader Agent are the components used with queued updating.
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.
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.
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
Microsoft recommends using SQL Server 2000 queues, but if you are running Windows 2000 Server on the Distributor and Subscriber and you are on a network that is secured from malicious users, you can use Microsoft Message Queuing as the queuing mechanism at the Subscriber.
If Message Queuing is used, replication creates a queue on the Distributor. The login under which the SQL Server service runs on the Distributor is given read and write permissions for the queue; all other logins are restricted. The login under which the Queue Reader Agent runs on the Distributor requires read permissions for the queue, and the logins under which the SQL Server service runs on each Subscriber require write permissions for the queue. Permissions can be handled in one of two ways:
- Use the same login for the SQL Server Service at the Distributor and all Subscribers, and the SQL Agent service on the Distributor.
- Use different logins, and give to each login the appropriate permissions for the queue.
If you use different logins, the required permissions for the Queue Reader Agent are "Peek Message" and "Receive Message"; the required permission for each Subscriber is "Send Message". For more information on setting permissions for Message Queuing, see the topic "Access control for Message Queuing" in the Windows 2000 documentation.
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; it is 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).
To install Message Queuing on the Distributor and Subscribers
Queue Reader Agent
The Queue Reader Agent is a multithreaded agent that runs on the Distributor. It is responsible for taking messages from a queue and applying them to the appropriate publication.
The Queue Reader Agent reads messages from the SQL Server 2000 queue on each Subscriber and applies the transactions to the publication. When using Message Queuing, the Queue Reader Agent reads the messages stored in a centralized queue created at the Distributor.
The Queue Reader Agent uses the security context of SQL Server Agent by default. When configured manually, the agent supports integrated and standard login to the Publisher and Distributor. Unlike the Distribution Agent and the Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given Distributor.