Bewerken

Share via

Switch Between Update Modes for an Updatable Transactional Subscription

  • Artikel

Applies to: SQL Server

This topic describes how to switch between update modes for an updatable transaction subscription in SQL Server 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

Before You Begin

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.

Recommendations

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

Using SQL Server Management Studio

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. Select 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. Select OK.

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

Using Transact-SQL

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.

Related content