Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Switch Between Update Modes for an Updatable Transactional Subscription

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

This topic describes how to switch between update modes for an updatable transaction subscription in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL. Specify the mode for updatable subscriptions using the New Subscription Wizard. For information about setting the mode when using this wizard, see View and Modify Pull Subscription Properties.

In This Topic

Limitations and Restrictions

  • 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.


  • 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.

Arrow icon used with Back to Top link [Top]

Note Note

To change the update mode after the subscription is created, the update_mode property must be set to failover (which allows a switch from immediate updating to queued updating) or queued failover (which allows a switch from queued updating to immediate updating) when the subscription is created. These properties are set automatically in the New Subscription Wizard.

To set the updating mode for a push subscription

  1. Connect to the Subscriber in SQL Server Management Studio, and then expand the server node.

  2. Expand the Replication folder, and then expand the Local Subscriptions folder.

  3. Right-click the subscription for which you want to set the update mode, and then click Set Update Method.

  4. In the Set Update Method - <Subscriber>: <SubscriptionDatabase> dialog box, select Immediate updating or Queued updating.

  5. Click OK.

To set the updating mode for a pull subscription

  1. In the Subscription Properties - <Publisher>: <PublicationDatabase> dialog box, select a value of Immediately replicate changes or Queue changes for the Subscriber update method option.

  2. Click OK.

For more information about accessing the Subscription Properties - <Publisher>: <PublicationDatabase> dialog box, see View and Modify Pull Subscription Properties.

Arrow icon used with Back to Top link [Top]

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.

Arrow icon used with Back to Top link [Top]

Community Additions

© 2015 Microsoft