Initializing a Merge Subscription Without a Snapshot

Initializing a Merge Subscription W...

Community Content

In this section

Statistics Annotations (1)

Click here to expand

Collapse All

SQL Server 2005 Books Online (November 2008)

Initializing a Merge Subscription Without a Snapshot

Updated: 17 July 2006

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, we recommend initializing subscriptions with a snapshot. We recommend against initializing a subscription without a snapshot 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. Unused schema 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 you intend to initialize the subscriber with.
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:

Concepts

Initializing a Subscription
Reinitializing a Subscription

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 July 2006	Changed content: Added information about situations where initializing a merge subscription without a snapshot may not work as expected. Added a recommendation to not use this functionality.

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;