During recent course of Disaster Recovery and Performance Tuning, I had very interesting conversation with students regarding Initializing a Merge Subscription Without a Snapshot and Initializing a Transactional Subscription Without a Snapshot. After the discussion when we were looking at MSDN pages one thing caught my notice was the note on the top of the MSDN page regarding future support of the feature for Initializing a Merge Subscription Without a Snapshot.
In the book on line on the subject Initializing a Merge Subscription Without a Snapshot it suggests that this feature will be deprecated in future, whereas there is no such note on the topic for Transactional Subscription. Well, I want to share this with everybody suggesting that there may be issue with future version if you are not initiating your replication with snapshot in the case of Merge Replication.
I would like to know if you are currently using Merge Replication and initializing it without snapshot. If you are – I would like to know why?
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 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
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 ??
Hi Pinal,
We’ve merge replication configured in our environment(4 SQL servers 2008R2). As we know merge replication is bidirectional, its working fine in case of inserts i.e, if a row is inserted at publisher it’s getting updated at subscriber and vice-versa. The Issue is, if row gets deleted at publisher its getting deleted at subscriber but, if a row gets deleted in any of 3 subscribers its not getting deleted at publisher. I’ve checked delete_tracking option in sp_helpmergearticle and its set to ‘1’.
Regards,
Nadeem
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.