How to: Quiesce a Replication Topology (Replication Transact-SQL Programming)
Quiescing a system involves stopping activity on published tables at all nodes and ensuring that each node has received all changes from all other nodes. This topic explains how to quiesce a replication topology, which is required for a number of administrative tasks, and how to ensure that a node has received all changes from other nodes.
To quiesce a transactional replication topology with read-only subscriptions
Stop activity on all published tables at the Publisher.
At the Publisher on the publication database, execute sp_posttracertoken (Transact-SQL).
At the Publisher on the publication database, execute sp_helptracertokenhistory (Transact-SQL).
Ensure that each Subscriber has received the tracer token.
To quiesce a transactional replication topology with updatable subscriptions
Stop activity on all published tables at the Publisher and all Subscribers.
If any Subscribers use queued updating subscriptions:
- If the Queue Reader Agent is not running in continuous mode, run the agent. For more information about running agents, see Programming Replication Agent Executables or How to: Start and Stop a Replication Agent (SQL Server Management Studio).
- To verify that the queue is empty, execute sp_replqueuemonitor (Transact-SQL) at each Subscriber.
At the Publisher on the publication database, execute sp_posttracertoken (Transact-SQL).
At the Publisher on the publication database, execute sp_helptracertokenhistory (Transact-SQL).
Ensure that each Subscriber has received the tracer token.
To quiesce a peer-to-peer transactional replication topology
Stop activity on all published tables at all nodes.
Execute sp_requestpeerresponse (Transact-SQL) on each publication database in the topology.
If the Log Reader Agent or Distribution Agent is not running in continuous mode, run the agent. The Log Reader Agent must be started before the Distribution Agent. For more information about running agents, see Programming Replication Agent Executables or How to: Start and Stop a Replication Agent (SQL Server Management Studio).
Execute sp_helppeerresponses (Transact-SQL) on each publication database in the topology. Ensure that the result set contains responses from each of the other nodes.
To ensure a peer-to-peer node has received all prior changes
Execute sp_requestpeerresponse (Transact-SQL) on the publication database at the node you are checking.
If the Log Reader Agent or Distribution Agent is not running in continuous mode, run the agent. The Log Reader Agent must be started before the Distribution Agent. For more information about running agents, see Programming Replication Agent Executables or How to: Start and Stop a Replication Agent (SQL Server Management Studio).
Execute sp_helppeerresponses (Transact-SQL) on the publication database at the node you are checking. Ensure that the result set contains responses from each of the other nodes.
To quiesce a merge replication topology
Stop activity on all published tables at the Publisher and at all Subscribers.
Run the Merge Agent for each subscription two times: synchronize all subscriptions once and then synchronize each subscription a second time. This ensures that all changes are replicated to all nodes. For more information about running agents, see Programming Replication Agent Executables or How to: Start and Stop a Replication Agent (SQL Server Management Studio).
Note
If conflicts occur during synchronization, it is possible that changes required by conflict resolution will not be propagated to all nodes after running the Merge Agent two times.
See Also
Tasks
How to: Administer a Peer-to-Peer Topology (Replication Transact-SQL Programming)
How to: Measure Latency and Validate Connections for Transactional Replication (Replication Transact-SQL Programming)
Other Resources
Measuring Latency and Validating Connections for Transactional Replication