Initializing a Merge Subscription Without a Snapshot

Switch View

Initializing a Merge Subscription W...

Community Content

In this section

Statistics Annotations (1)

Click here to expand

Collapse All

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (October 2009)

Initializing a Merge Subscription Without a Snapshot

Note:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

By default, a subscription to a merge publication is initialized with a snapshot, which is generated by the Snapshot Agent and applied by the Merge Agent. In some scenarios, you can initialize a subscription using another method, such as a backup. Merge replication supports initializing subscriptions without a snapshot for both filtered and unfiltered publications. The option is more commonly used with unfiltered publications and those that use static filters. Publications that use parameterized filters are typically initialized with a snapshot for a Subscriber's partition. For more information, see Snapshots for Merge Publications with Parameterized Filters.

When possible, initialize subscriptions with a snapshot. We recommend against initializing a subscription without a snapshot, especially under the following conditions:

When using column-level tracking, vertical filtering, or making schema changes at the Publisher. This can result in non-convergence when initializing a subscription without a snapshot.
When using Web synchronization, snapshot files are replicated to the Subscriber but are not used.
When you must reinitialize the subscription.
When you need the performance benefits provide by using precomputed partitions.

To initialize a merge subscription from a backup or using another method, use the following steps:

Add a uniqueidentifier column with the rowguid and not null column attributes to the tables that will be used in merge replication.
Perform a backup of the database that you will use to initialize the subscriber.
Create a publication and generate the snapshot for the publication. Although the schema and data from published tables in the snapshot will not be used, the snapshot is still required because it includes system objects and metadata required by replication. These objects and metadata are copied to the Subscriber during the initial synchronization.
Restore the backup at the Subscriber or copy data using another method. If you restore a backup, do not specify the KEEP_REPLICATION option. This option is designed to maintain replication settings during restore; the backup came from the Publisher, so you want to remove the Publisher settings from the database. The restored database will have the necessary Subscriber settings after the initial synchronization.

Create a subscription, specifying that the subscription will be initialized manually.

Note:
If a subscription is initialized without using a snapshot, the account under which the SQL Server service runs at the Publisher must have write permissions on the snapshot folder at the Distributor. For more information about permissions, see Replication Agent Security Model.

For more information, see:

SQL Server Management Studio: How to: Initialize a Subscription Manually (SQL Server Management Studio)
Transact-SQL: How to: Initialize a Subscription Manually (Replication Transact-SQL Programming)

Perform an initial synchronization with the Merge Agent to copy the objects and metadata required by replication. The Subscriber is now synchronized and ready to accept incremental changes from the Publisher.

Concepts

Initializing a Subscription
Reinitializing a Subscription

Help and Information

Getting SQL Server 2008 Assistance

Tags

: Add a tag

Community Content

Add new content

Annotations

Step 1 is Mandatory and if you forget it, you'll see Error 102 at initial sync time

Jason H - SQL | Edit | Show History

If you forget this first step ("1. Add a uniqueidentifier column with the rowguid and not null column attributes to the tables that will be used in merge replication.") , you will get a nasty error...

Error:
Message: {call sp_MSsetconflicttable (N'FirstTableinPublication', N'MSmerge_conflict_pub_pubname_fistTableinPublication', N'publisher_name', N'databasename', N'publication_name)}

Source:  SERVER\INSTANCE
Number:  102
Message: Incorrect syntax near 'FirstColumnInTable'.

Source:  Merge Replication Provider
Number:  -2147201001
Message: The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.

You have to add the rowguid column yourself as per Step 1, after the subscriber database is restored, on each table.

alter table <subscribertable> add [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL;

Helper script to write the TSQL which you can copy/paste into subscriber connection and run before initial sync

-- To find all tables and help the syntax, run this at the publisher:
select 'alter table [' + name +'] add [rowguid] uniqueidentifier rowguidcol default newsequentialid();' from sysmergearticles where type=10

-- Copy the results from the above query, and run that at the subscriber to make the rowguid columns ahead of time.
alter table <subscribertable1> add [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL;
alter table <subscribertable2> add [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL;
alter table <subscribertable3> add [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL;