Redigera

Dela via

Initialize a Subscription Manually

  • Artikel

Applies to: SQL Server Azure SQL Managed Instance

This topic describes how to initialize a subscription manually in SQL Server by using SQL Server Management Studio or Transact-SQL. While the initial snapshot is normally used to initialize a subscription, subscriptions to publications can be initialized without using a snapshot, provided that the schema and initial data are already present at the subscriber.

Before You Begin

Limitations and Restrictions

  • If there is activity on a database published using transactional replication between the time data and schema are copied to the Subscriber and the time at which the subscription is manually initialized, changes resulting from this activity might not be replicated to the Subscriber.

Using SQL Server Management Studio

Initialize a subscription to a publication manually by copying the schema (and typically data) to the subscription database. The schema and data should match the publication database. Then specify that the subscription does not require schema and data on the Initialize Subscriptions page of the New Subscription Wizard. For more information about accessing this wizard, see Initialize a Transactional Subscription Without a Snapshot and Create a Pull Subscription.

When you synchronize the subscription for the first time, the objects and metadata required by replication are copied to the subscription database.

To initialize a subscription to a publication manually

  1. Ensure that the schema and data are copied to the subscription database.

  2. Clear the Initialize check box on the Initialize Subscriptions page of the New Subscription Wizard. Do this for each subscription that requires only replication objects and metadata to be copied.

Using Transact-SQL

Subscriptions can be initialized manually using replication stored procedures.

To manually initialize a pull subscription to a transactional publication

  1. Ensure that the schema and data exist on the subscription database. For more information, see Initialize a Transactional Subscription Without a Snapshot.

  2. At the Publisher on the publication database, execute sp_addsubscription. Specify @publication, @subscriber, the name of the database at the Subscriber containing the published data for @destination_db, a value of pull for @subscription_type, and a value of replication support only for @sync_type. For more information, see Create a Pull Subscription.

  3. At the Subscriber, execute sp_addpullsubscription. For updating subscriptions, see Create an Updatable Subscription to a Transactional Publication.

  4. At the Subscriber, execute sp_addpullsubscription_agent. For more information, see Create a Pull Subscription.

  5. Start the Distribution Agent to transfer replication objects and download the latest changes from the Publisher. For more information, see Synchronize a Pull Subscription.

To manually initialize a push subscription to a transactional publication

  1. Ensure that the schema and data exist on the subscription database. For more information, see Initialize a Transactional Subscription Without a Snapshot.

  2. At the Publisher on the publication database, execute sp_addsubscription. Specify the name of the database at the Subscriber containing the published data for @destination_db, a value of push for @subscription_type, and a value of replication support only for @sync_type. For updating subscriptions, see Create an Updatable Subscription to a Transactional Publication.

  3. At the Publisher on the publication database, execute sp_addpushsubscription_agent. For more information, see Create a Push Subscription.

  4. Start the Distribution Agent to transfer replication objects and download the latest changes from the Publisher. For more information, see Synchronize a Push Subscription.

To manually initialize a pull subscription to a merge publication

  1. Ensure that the schema and data exist on the subscription database. This can be done by restoring a backup of the publication database at the Subscriber.

  2. At the Publisher, execute sp_addmergesubscription. Specify @publication, @subscriber, @subscriber_db, and a value of pull for @subscription_type. This registers the pull subscription.

  3. At the Subscriber on the database containing the published data, execute sp_addmergepullsubscription. Specify a value of none for @sync_type.

  4. At the Subscriber, execute sp_addmergepullsubscription_agent. For more information, see Create a Pull Subscription.

  5. Start the Merge Agent to transfer replication objects and download the latest changes from the Publisher. For more information, see Synchronize a Pull Subscription.

To manually initialize a push subscription to a merge publication

  1. Ensure that the schema and data exist on the subscription database. This can be done by restoring a backup of the publication database at the Subscriber.

  2. At the Publisher on the publication database, execute sp_addmergesubscription. Specify the name of the database at the Subscriber containing the published data for @subscriber_db, a value of push for @subscription_type, and a value of none for @sync_type.

  3. At the Publisher on the publication database, execute sp_addmergepushsubscription_agent. For more information, see Create a Push Subscription.

  4. Start the Merge Agent to transfer replication objects and download the latest changes from the Publisher. For more information, see Synchronize a Push Subscription.

Related content