Skip to content

Latest commit

 

History

History
239 lines (138 loc) · 20.1 KB

reinitialize-a-subscription.md

File metadata and controls

239 lines (138 loc) · 20.1 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords monikerRange
Reinitialize a Subscription
Learn how to reinitialize a subscription in SQL Server by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects.
MashaMSFT
mathoma
09/25/2024
sql
replication
ui-reference
updatefrequency5
initializing subscriptions [SQL Server replication], reinitializing
subscriptions [SQL Server replication], reinitializing
reinitializing subscriptions
=azuresqldb-current||>=sql-server-2016

Reinitialize a Subscription

[!INCLUDEsql-asdb] This topic describes how to reinitialize a subscription in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull], [!INCLUDEtsql], or Replication Management Objects (RMO). Individual subscriptions can be marked for reinitialization so that a new snapshot is applied during the next synchronization.

[!INCLUDEazure-sql-db-replication-supportability-note]

Using SQL Server Management Studio

Reinitializing a subscription is a two-part process:

  1. A single subscription or all subscriptions to a publication are marked for reinitialization. Mark subscriptions for reinitialization in the Reinitialize Subscription(s) dialog box, which is available from the Local Publications folder and the Local Subscriptions folder in [!INCLUDEmsCoName] [!INCLUDEssManStudioFull]. You can also mark subscriptions from the All Subscriptions tab and the publications node in Replication Monitor. For information about starting Replication Monitor, see Start the Replication Monitor. When you mark a subscription for reinitialization, you have the following options:

    Use the current snapshot
    Select to apply the current snapshot to the Subscriber the next time the Distribution Agent or Merge Agent runs. If there is no valid snapshot available, this option cannot be selected.

    Use a new snapshot
    Select to reinitialize the subscription with a new snapshot. The snapshot can be applied to the Subscriber only after it has been generated by the Snapshot Agent. If the Snapshot Agent is set to run on a schedule, the subscription is not reinitialized until after the next scheduled Snapshot Agent run. Select Generate the new snapshot now to start the Snapshot Agent immediately.

    Upload unsynchronized changes before reinitialization
    Merge replication only. Select to upload any pending changes from the subscription database before the data at the Subscriber is overwritten with a snapshot.

    If you add, drop, or change a parameterized filter, pending changes at the Subscriber cannot be uploaded to the Publisher during reinitialization. If you want to upload pending changes, synchronize all subscriptions before changing the filter.

  2. A subscription is reinitialized the next time it is synchronized: the Distribution Agent (for transactional replication) or Merge Agent (for merge replication) applies the most recent snapshot to each Subscriber that has a subscription marked for reinitialization. For more information about synchronizing subscriptions, see Synchronize a Push Subscription and Synchronize a Pull Subscription.

To mark a single push or pull subscription for reinitialization in Management Studio (at the Publisher)

  1. Connect to the Publisher in [!INCLUDEssManStudio], and then expand the server node.

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

  3. Expand the publication that has the subscription you want to reinitialize.

  4. Right-click the subscription, and then click Reinitialize.

  5. In the Reinitialize Subscription(s) dialog box, select options, and then click Mark for Reinitialization.

To mark a single pull subscription for reinitialization in Management Studio (at the Subscriber)

  1. Connect to the Subscriber in [!INCLUDEssManStudio], and then expand the server node.

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

  3. Right-click the subscription, and then click Reinitialize.

  4. In the confirmation dialog box that is displayed, click Yes.

To mark all subscriptions for reinitialization in Management Studio

  1. Connect to the Publisher in [!INCLUDEssManStudio], and then expand the server node.

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

  3. Right-click the publication with subscriptions you want to reinitialize, and then click Reinitialize All Subscriptions.

  4. In the Reinitialize Subscription(s) dialog box, select options, and then click Mark for Reinitialization.

To mark a single push or pull subscription for reinitialization in Replication Monitor

  1. In Replication Monitor, expand a Publisher group in the left pane, expand a Publisher, and then click a publication.

  2. Click the All Subscriptions tab.

  3. Right-click the subscription you want to reinitialize, and then click Reinitialize Subscription.

  4. In the Reinitialize Subscription(s) dialog box, select options, and then click Mark for Reinitialization.

To mark all subscriptions for reinitialization in Replication Monitor

  1. In Replication Monitor, expand a Publisher group in the left pane, and then expand a Publisher.

  2. Right-click the publication with subscriptions you want to reinitialize, and then click Reinitialize All Subscriptions.

  3. In the Reinitialize Subscription(s) dialog box, select options, and then click Mark for Reinitialization.

Using Transact-SQL

Subscriptions can be reinitialized programmatically using replication stored procedures. The stored procedure that is used depends on the type of subscription (push or pull) and the type of publication to which the subscription belongs.

To reinitialize a pull subscription to a transactional publication

  1. At the Subscriber on the subscription database, execute sp_reinitpullsubscription (Transact-SQL). Specify @publisher, @publisher_db, and @publication. This marks the subscription for reinitialization the next time the Distribution Agent runs.

  2. (Optional) Start the Distribution Agent at the Subscriber to synchronize the subscription. For more information, see Synchronize a Pull Subscription.

To reinitialize a push subscription to a transactional publication

  1. At the Publisher, execute sp_reinitsubscription (Transact-SQL). Specify @publication, @subscriber, and @destination_db. This marks the subscription for reinitialization the next time the Distribution Agent runs.

  2. (Optional) Start the Distribution Agent at the Distributor to synchronize the subscription. For more information, see Synchronize a Push Subscription.

To reinitialize a pull subscription to a merge publication

  1. At the Subscriber on the subscription database, execute sp_reinitmergepullsubscription (Transact-SQL). Specify @publisher, @publisher_db, and @publication. To upload changes from the Subscriber before reinitialization occurs, specify a value of true for @upload_first. This marks the subscription for reinitialization the next time the Merge Agent runs.

    [!IMPORTANT]
    If you add, drop, or change a parameterized filter, pending changes at the Subscriber cannot be uploaded to the Publisher during reinitialization. If you want to upload pending changes, synchronize all subscriptions before changing the filter.

  2. (Optional) Start the Merge Agent at the Subscriber to synchronize the subscription. For more information, see Synchronize a Pull Subscription.

To reinitialize a push subscription to a merge publication

  1. At the Publisher, execute sp_reinitmergesubscription (Transact-SQL). Specify @publication, @subscriber, and @subscriber_db. To upload changes from the Subscriber before reinitialization occurs, specify a value of true for @upload_first. This marks the subscription for reinitialization the next time the Distribution Agent runs.

    [!IMPORTANT]
    If you add, drop, or change a parameterized filter, pending changes at the Subscriber cannot be uploaded to the Publisher during reinitialization. If you want to upload pending changes, synchronize all subscriptions before changing the filter.

  2. (Optional) Start the Merge Agent at the Distributor to synchronize the subscription. For more information, see Synchronize a Push Subscription.

To set the reinitialization policy when creating a new merge publication

  1. At the Publisher on the publication database, execute sp_addmergepublication, specifying one of the following values for @automatic_reinitialization_policy:

    • 1 - changes are uploaded from the Subscriber before a subscription is automatically reinitialized as required by a change to the publication.

    • 0 - changes at the Subscriber are discarded when a subscription is automatically reinitialized as required by a change to the publication.

    [!IMPORTANT]
    If you add, drop, or change a parameterized filter, pending changes at the Subscriber cannot be uploaded to the Publisher during reinitialization. If you want to upload pending changes, synchronize all subscriptions before changing the filter.

    For more information, see Create a Publication.

To change the reinitialization policy for an existing merge publication

  1. At the Publisher on the publication database, execute sp_changemergepublication, specifying automatic_reinitialization_policy for @property and one of the following values for @value:

    • 1 - changes are uploaded from the Subscriber before a subscription is automatically reinitialized as required by a change to the publication.

    • 0 - changes at the Subscriber are discarded when a subscription is automatically reinitialized as required by a change to the publication.

    [!IMPORTANT]
    If you add, drop, or change a parameterized filter, pending changes at the Subscriber cannot be uploaded to the Publisher during reinitialization. If you want to upload pending changes, synchronize all subscriptions before changing the filter.

    For more information, see View and Modify Publication Properties.

Using Replication Management Objects (RMO)

Individual subscriptions can be marked for reinitialization so that during the next synchronization, a new snapshot is applied. Subscriptions can be reinitialized programmatically by using Replication Management Objects (RMO). The classes you use depend on the type of publication to which the subscription belongs and the type of subscription (that is, a push or pull subscription).

To reinitialize a pull subscription to a transactional publication

  1. Create a connection to the Subscriber by using the xref:Microsoft.SqlServer.Management.Common.ServerConnection class.

  2. Create an instance of the xref:Microsoft.SqlServer.Replication.TransPullSubscription class, and set xref:Microsoft.SqlServer.Replication.PullSubscription.PublicationName%2A, xref:Microsoft.SqlServer.Replication.PullSubscription.DatabaseName%2A, xref:Microsoft.SqlServer.Replication.PullSubscription.PublisherName%2A, xref:Microsoft.SqlServer.Replication.PullSubscription.PublicationDBName%2A, and the connection from step 1 for xref:Microsoft.SqlServer.Replication.ReplicationObject.ConnectionContext%2A.

  3. Call the xref:Microsoft.SqlServer.Replication.ReplicationObject.LoadProperties%2A method to get the properties of the object.

    [!NOTE]
    If this method returns false, either the subscription properties in step 2 were defined incorrectly or the pull subscription does not exist.

  4. Call the xref:Microsoft.SqlServer.Replication.TransPullSubscription.Reinitialize%2A method. This method marks the subscription for reinitialization.

  5. Synchronize the pull subscription. For more information, see Synchronize a Pull Subscription.

To reinitialize a push subscription to a transactional publication

  1. Create a connection to the Publisher by using the xref:Microsoft.SqlServer.Management.Common.ServerConnection class.

  2. Create an instance of the xref:Microsoft.SqlServer.Replication.TransSubscription class, and set xref:Microsoft.SqlServer.Replication.Subscription.PublicationName%2A, xref:Microsoft.SqlServer.Replication.Subscription.DatabaseName%2A, xref:Microsoft.SqlServer.Replication.Subscription.SubscriberName%2A, xref:Microsoft.SqlServer.Replication.Subscription.SubscriptionDBName%2A, and the connection from step 1 for xref:Microsoft.SqlServer.Replication.ReplicationObject.ConnectionContext%2A.

  3. Call the xref:Microsoft.SqlServer.Replication.ReplicationObject.LoadProperties%2A method to get the properties of the object.

    [!NOTE]
    If this method returns false, either the subscription properties in step 2 were defined incorrectly or the push subscription does not exist.

  4. Call the xref:Microsoft.SqlServer.Replication.TransSubscription.Reinitialize%2A method. This method marks the subscription for reinitialization.

  5. Synchronize the push subscription. For more information, see Synchronize a Push Subscription.

To reinitialize a pull subscription to a merge publication

  1. Create a connection to the Subscriber by using the xref:Microsoft.SqlServer.Management.Common.ServerConnection class.

  2. Create an instance of the xref:Microsoft.SqlServer.Replication.MergePullSubscription class, and set xref:Microsoft.SqlServer.Replication.PullSubscription.PublicationName%2A, xref:Microsoft.SqlServer.Replication.PullSubscription.DatabaseName%2A, xref:Microsoft.SqlServer.Replication.PullSubscription.PublisherName%2A, xref:Microsoft.SqlServer.Replication.PullSubscription.PublicationDBName%2A, and the connection from step 1 for xref:Microsoft.SqlServer.Replication.ReplicationObject.ConnectionContext%2A.

  3. Call the xref:Microsoft.SqlServer.Replication.ReplicationObject.LoadProperties%2A method to get the properties of the object.

    [!NOTE]
    If this method returns false, either the subscription properties in step 2 were defined incorrectly or the pull subscription does not exist.

  4. Call the xref:Microsoft.SqlServer.Replication.MergePullSubscription.Reinitialize%2A method. Pass a value of true to upload changes at the Subscriber before reinitialization or a value of false to reinitialize and lose any pending changes at the Subscriber. This method marks the subscription for reinitialization.

    [!NOTE]
    Changes cannot be uploaded if the subscription is expired. For more information, see Set the Expiration Period for Subscriptions.

  5. Synchronize the pull subscription. For more information, see Synchronize a Pull Subscription.

To reinitialize a push subscription to a merge publication

  1. Create a connection to the Publisher by using the xref:Microsoft.SqlServer.Management.Common.ServerConnection class.

  2. Create an instance of the xref:Microsoft.SqlServer.Replication.MergeSubscription class, and set xref:Microsoft.SqlServer.Replication.Subscription.PublicationName%2A, xref:Microsoft.SqlServer.Replication.Subscription.DatabaseName%2A, xref:Microsoft.SqlServer.Replication.Subscription.SubscriberName%2A, xref:Microsoft.SqlServer.Replication.Subscription.SubscriptionDBName%2A, and the connection from step 1 for xref:Microsoft.SqlServer.Replication.ReplicationObject.ConnectionContext%2A.

  3. Call the xref:Microsoft.SqlServer.Replication.ReplicationObject.LoadProperties%2A method to get the properties of the object.

    [!NOTE]
    If this method returns false, either the subscription properties in step 2 were defined incorrectly or the push subscription does not exist.

  4. Call the xref:Microsoft.SqlServer.Replication.MergeSubscription.Reinitialize%2A method. Pass a value of true to upload changes at the Subscriber before reinitialization or a value of false to reinitialize and lose any pending changes at the Subscriber. This method marks the subscription for reinitialization.

    [!NOTE]
    Changes cannot be uploaded if the subscription is expired. For more information, see Set the Expiration Period for Subscriptions.

  5. Synchronize the push subscription. For more information, see Synchronize a Push Subscription.

Examples (RMO)

This example reinitializes a pull subscription to a transactional publication.

[!code-csHowTo#rmo_ReinitTranPullSub]

[!code-vbHowTo#rmo_vb_ReinitTranPullSub]

This example reinitializes a pull subscription to a merge publication after first uploading pending changes at the Subscriber.

[!code-csHowTo#rmo_ReinitMergePullSub_WithUpload]

[!code-vbHowTo#rmo_vb_ReinitMergePullSub_WithUpload]

Related content