How to: Switch Between Updating Modes for an Updating Transactional Subscription (Replication Transact-SQL Programming)

  • Article

When an updating subscription to a transactional publication supports failover from one updating mode to another, you can programmatically switch update modes to handle situations when connectivity changes for a short period of time. The update mode can be set programmatically and on demand using replication stored procedures. For more information, see Updatable Subscriptions for Transactional Replication.

Note

You can fail over from immediate to queued updating at any time. After you do, however, you cannot return to immediate updating until the Subscriber and Publisher are connected and the Queue Reader Agent has applied all pending messages in the queue to the Publisher.

To switch between update modes

  1. Verify that the subscription supports failover by executing sp_helppullsubscription for a pull subscription or sp_helpsubscription for a push subscription. If the value of update mode in the result set is 3 or 4, failover is supported.

  2. At the Subscriber on the subscription database, execute sp_setreplfailovermode. Specify @publisher, @publisher_db, @publication, and one of the following values for @failover_mode:

    • queued - fail over to queued updating when connectivity has been temporarily lost.
    • immediate - fail over to immediate updating when connectivity has been restored.