Muokkaa

Jaa

Quiesce a Replication Topology (Replication Transact-SQL Programming)

  • Artikkeli

Applies to: SQL Server Azure SQL Managed Instance

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

  1. Stop activity on all published tables at the Publisher.

  2. At the Publisher on the publication database, execute sp_posttracertoken (Transact-SQL).

  3. At the Publisher on the publication database, execute sp_helptracertokenhistory.

  4. Ensure that each Subscriber has received the tracer token.

To quiesce a transactional replication topology with updatable subscriptions

  1. Stop activity on all published tables at the Publisher and all Subscribers.

  2. If any Subscribers use queued updating subscriptions:

    1. If the Queue Reader Agent is not running in continuous mode, run the agent. For more information about running agents, see Replication Agent Executables Concepts or Start and Stop a Replication Agent (SQL Server Management Studio).

    2. To verify that the queue is empty, execute sp_replqueuemonitor at each Subscriber.

  3. At the Publisher on the publication database, execute sp_posttracertoken.

  4. At the Publisher on the publication database, execute sp_helptracertokenhistory.

  5. Ensure that each Subscriber has received the tracer token.

To quiesce a peer-to-peer transactional replication topology

  1. Stop activity on all published tables at all nodes.

  2. Execute sp_requestpeerresponse on each publication database in the topology.

  3. 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 Replication Agent Executables Concepts or Start and Stop a Replication Agent (SQL Server Management Studio).

  4. Execute sp_helppeerresponses 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

  1. Execute sp_requestpeerresponse on the publication database at the node you are checking.

  2. 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 Replication Agent Executables Concepts or Start and Stop a Replication Agent (SQL Server Management Studio).

  3. Execute sp_helppeerresponses 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

  1. Stop activity on all published tables at the Publisher and at all Subscribers.

  2. 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 Replication Agent Executables Concepts or 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.

Related content