Merge subscription initialization without a snapshot needs precise version and method context. Check the chosen initialization method and its restrictions before preparing the subscriber.
Skipping the snapshot still needs a plan
I use manual initialization when the subscriber already has the schema and the initial data. It works for merge pull and push subscriptions. It saves sending the initial snapshot. It does not remove the need to set up the replication objects and metadata.
For a pull subscription, I register the subscription at the Publisher first. The call to sp_addmergesubscription passes @sync_type = N’none’ there. Configuring the subscriber and its Merge Agent is a separate step. A push subscription has its own Publisher-side setup. This page describes the approach. It is not a tested configuration script.
One caution belongs to transactional replication only: changes made at the source between copying the data and starting the manual initialization can be missed. Keep that caution with transactional replication. Do not borrow a transactional procedure for merge just because both skip the snapshot.
When Manual Initialization Can Help
Unreliable connections and databases requiring changes in both directions can make another full snapshot costly. Delivery databases and disaster recovery sites may also need to preserve an existing data copy. Avoiding the snapshot still requires a valid replication setup.
Subscribers receiving frequent sensor data may have local changes that must survive initialization. Matching application rows alone does not demonstrate equivalent replication metadata or a safe failover path. Preserve unsynchronized local changes and validate the actual topology before choosing a recovery procedure.
Keep separate problems separate
If subscriber deletes fail to reach the Publisher, investigate the actual publication, agents, filters and synchronization errors. The initial-snapshot choice alone does not explain that failure.
Manually reconciling offline changes after replication failure or expiry requires a topology-specific recovery procedure and validation. It is not a universal recipe for every expired subscription. Preserve the affected changes before attempting destructive reinitialization.
I write down the release, the topology, how I prepared the existing data and how local changes are treated. That note helps the next person who touches this setup.
Skipping the snapshot saves a transfer, and the preparation work stays with you.
A skipped snapshot is not skipped setup, it is a subscriber you prepared yourself.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
5 Comments. Leave new
Hi Pinal,
We are using the Merge Subscription without Snapshot and we strongly need it.
Because we don’t a reliable medium (DSL/Dialup) between publisher and subscriber.
There is no other ways to avoid sending the snapshot
also we need the Merge Replication because we need to communication two ways.
Regards
Zafar Iqbal
That is interesting point Zafar!
We have a number of DB “Delivery” servers that support a Farm of IIS Web servers.
We feed new content to the DB servers via merge replication from a “Publishing” DB Server. This SQL server has a “Twin” Master in the DR site, which is also kept up to date via Replication.
If the main server dies, we want to establish the replication with the “twin” in the DR site. As everything is already in sync, we don’t need a snapshot. (and we can’t afford the downtime & bandwidth to resync)
(It is a little more complex, ie: the servers are clustered & delivery is in multiple sites, but that is the core logic)
Interesting issue, we need to initialize Merge Replication without a snapshot because our clients running SQL Server Express are pulling data from sensors in almost real time fashion (every few seconds) and we cannot afford to lose valuable data while the snapshot is applied.
Is there any other way of satisfying this requirement other than initializing without a snapshot ??
I often do merge without initialisation when we have had a replication failure (including expiry) at a customers deployment which has offline data that needs to sync so we can’t blow it away initialising. So you set it up without initialisation, and then manually synch the data using a variety of techniques, until you can get the subscription validate with checksum.