SQL Server Compact supports predominantly two different sync technologies. “Merge replication” and “Sync Services”. Users might be using one or the other, and interested in knowing the differences between the two technologies, from a use-case point of view. Still, there might be others interested in knowing, which technology better suites their need, by comparing and choosing the right one. One simple rule of thumb here: Merge replication is designed keeping enterprises in mind, where Sync Services is a framework for developer community/social networking/hobbyist programmers etc… (everything else). “Merge replication” is a solution, which is suitable for Enterprises mostly, in preference to Sync services. However, Sync services is an open-ended, extensible framework on top of which, one can do almost everything that Merge replication does. This article concentrates mostly on the merge replication angle in terms of where it makese more sense and where it doesn't.
Where does "Merge" make more sense?
In the enterprise scenario. In my opinion, enterprises like things faster, better (without any issues), and easily do-able/repeatable. If a solution (like “Merge replication”) allows them to do things like “setting up a publication, subscribers etc…”, although with significant coding, people are not going to like it. So, tools and wizards for enterprises are mandatory (Also give them a way to re-do the setups, typically outputting scripts from these tools/wizards). To avoid the setup/upgrade glitches, provide an out-of-the-box integration story, with other often used components (like Windows, IIS, SQL Server, HTTP etc…). Also, tighter integration helps performance. Usually, in enterprise contexts, setting up a “data server and clients” is a long-term commitment, so, tighter integration is fine there. All these are basic requirements for the “Enterprise” oriented “merge replication” technology.
Note: There are many ways in which a tighter integration with other components helps deployments.
2. Automation possibility and tooling
3. Better end-2-end support/solution story for the customers
4. Ability to monitor and troubleshoot parts of the system
5. Essentially, you leave all the hardwork to us, and in the end, expect something that automatically works J (Means, reduced cost of development and testing for you)
Also, my take is that “Enterprises like rich (and relevant) features”. Give them something that can easily model/extend their business logic, and that could be of great value to them. Some of the features of “Merge replication”, in this category are,
1. Automatic partition management
2. Custom conflict resolution hooks
3. Business logic plugins to do custom processing, in the process of merge replication
a. Example is, if one wants to charge the client for synching
b. One wants to compute his share whenever a salesman syncs to the server
4. Retention cleanup, which can be used to (weakly) set a stringent policy about incremental syncs.
a. Like, every salesman must sync at least once in a week or every branch office, must sync at least once a day.
5. Incremental schema changes replication
6. Integration with SQL Server mirroring
7. Integration with SQL Server backup and restore.
Other valuable features are, “Automatic identity range management” etc. I hope now it is very clear as to, why “merge replication” technology is good for enterprise businesses. Enterprises do not mind, buying into a special architecture, if that provides value. Enterprises can setup a particular set (at the least) of windows machines, with IIS and SQL Server, and run merge replication. So, a tailored solution, like merge replication is best suited for them.
Where does Sync play better role compared to Merge?
Now, let’s examine another use-case. Incidentally, in this use-case, the features of merge replication, do not seem much relevant. You want to write a stock-alert application, and link it with a stock-tick source on a website. On closer examination, this is also a “data sync” scenario, so, merge replication could be used here. Trying to use merge replication here, elicits many incompatibilities/redundancies, that are not relevant to this use-case.
First of all, the nature of the data source is unknown, it can change while the app is running too. The web-site might use SQL backend now, and something else tomorrow. The transport is not known, it is over HTTP, but, can have any format (plain HTML/Json etc…). There is no need of rich data semantics here, as one is just comparing a single piece time-series data, and deciding to throw the alert or not. Also, one does not need a big database at client, all he does is read and discard (or read, alert and discard). Clearly, using merge replication here is an overkill, and unsuitable. So, you should use “Sync services”.
If you want to write a very quick sync app, and use it anywhere*, “Sync services” is the answer. Because Sync Services, is not tied to any particular, server architecture or transport mechanism, one can use it anywhere*. For quick development of sync apps, Sync Services is integrated with Visual Studio. But, this is only to develop the app quickly, not to deploy it. Visual Studio is not even needed for plain development of any sync app. It is just there to make it easy and fast.
Sync services is amazingly extensible, componentized, customizable. While merge replication answers enterprise use-case, which is a typical use-case for data synchronization, virtually all other use-cases are addressable by “Sync services”. If for some reason, people do not want to use a particular server/transport architecture, (or can’t use a single architecture), Sync services is the way to go. Besides, Sync services is a free platform to develop apps on. It is a great tool to enable non-(traditional)-enterprise related, businesses/users to realize the new models of interaction cropping up almost everywhere now. Sync services can be made to have feature-parity with “merge replication” solution through coding, although, I hope you would agree that it is not the intent of Sync services. I quoted that here, only to make you realize the potential of Sync services platform.
When an open-ended sync solution needs to be developed, Sync Services Framework is the one to use. Many times in usage, applications to sync data, need heterogeneous servers/transports etc. This is a very important capability, for example, for news generation and propagation web-site. News could be present in various web-sites, and also inside documents/databases of different formats. Also, the solution should be open-ended and extensible, so that, it can be easily tailored for a new data source. The importance of “Sync services” in enabling such scenarios, should not be underestimated. Sync Services, is coming out strong, and there is more to watch out in this field, going forward. With this, we conclude the explanations of the technologies, from the use-cases point of view.
The list of features of merge replication and Sync services are tabulated below:
Supported in merge replication
Supported in Sync Services
Type of the technology
Enterprises with DBAs
Developers/Hobbyists/Social networking Communities
Integration with DBA tools (SQL Server)
More tooling support
Pluggable business logic hooks
Conflict resolution support
Built-in + support for custom***
Built-in + extensible**
Schema propagation support
Initial + any changes
Limited, available by extension**
Available by extension
Server type (State-full or State-less)
Integration with Server mirroring (SQL Server)
Integration with Server backup/restore (SQL Server)
Identity ranges management
Developer platform support
Yes, Visual Studio integration provided
No (HTTP with IIS server only)
Yes (Web service model is possible, by extension)
No (Only SQL Server)
Any server is good (by extension).
Variable, 2-tier to N-tier
Supports web services model
Ability to work with other sync platforms/frameworks
Yes (by extension)
Exposed API surface for tracking****
Common functionality provided
Subscribed database deployment
Type of change tracking used
PK or ROWGUID
Sync directions allowed
Native & managed
Auto-management of dependent tables
Sync granularity enforced
Yes (Publication level)
No (table level)
Built-in conflict resolution strategies (also customizable)
Only some limited number of built-ins
* Well, almost everywhere.
** Whenever we use, “available by extension” or simply “by extension”, that means developer should write code to achieve the desired behavior.
*** (For this document,) the difference between customizable and extensible is the following: customization is a type of extension, where, the application architecture is not affected majorly. Like, register a COM dll, to do conflict resolution etc…
**** Tracking is a mechanism used to tag all changes. Changes could be Inserts/Updates/Deletes on tracked tables. This module is used to detect changes between successive syncs, so that the data can be forwarded to the other party. Exposing the tracking API (enable/disable tracking etc…), helps write applications like peer-peer sync, transaction notifications etc…
Contributor: Udaya Bhanu Goteti