Merge replication vs. Sync Services for Compact

 

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?  

I

n 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.

1. Efficiency/performance

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:

 

Feature	Supported in mergereplication	Supported in Sync Services
Enterprise-centric
Type of the technology	Solution	Framework
Target users	Enterprises with DBAs	Developers/Hobbyists/Social networking Communities
Integration with DBA tools (SQL Server)	Yes	No
More tooling support	Yes	No
Pluggable business logic hooks	Yes	No
Conflict resolution support	Built-in + support for custom***	Built-in + extensible**
Schema propagation support	Initial + any changes	Limited, available by extension**
Partitioning support	Built-in	Available by extension
Server type (State-full or State-less)	State-full	State-less
Integration with Server mirroring (SQL Server)	Yes	No
Integration with Server backup/restore (SQL Server)	Yes	No
Identity ranges management	Yes	No
Developer centric
Type of the technology	Solution	Framework
Target users	Enterprises with DBAs	Developers/Hobbyists/Social networking Communities
Developer platform support	No	Yes, Visual Studio integration provided
Pluggable transport	No (HTTP with IIS server only)	Yes (Web service model is possible, by extension)
Heterogeneous server	No (Only SQL Server)	Any server is good (by extension).
Network architectures	Fixed, 3-tier	Variable, 2-tier to N-tier
Supports web services model	No	Yes
Ability to work with other sync platforms/frameworks	No	Yes (by extension)
Exposed API surface for tracking****	No	Yes
Common functionality provided
Subscribed database deployment	Yes	Yes
Type of change tracking used	ROWGUID	PK or ROWGUID
Sync directions allowed	All	All
Programmability layer	Native & managed	Managed
Semantics provided
Auto-management of dependent tables	Yes	No
Sync granularity enforced	Yes (Publication level)	No (table level)
Schema propagation	Yes	Limited extent
Built-in conflict resolution strategies (also customizable)	Yes	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…

 

Wrap-up:

We have looked at the use-cases where merge replication technology is suitable and where it is not. There are scenarios where “Sync services” comes out as a candidate for solution, and there are scenarios for merge replication too. When considering the right technology for using, there is no silver bullet; there is no panacea that works for all needs. The requirements of your use determine the “right” technology for you. Merge replication provides a solution, but, there are many scenarios that it can’t help you with. Sync services can be made to work in “any” scenario (including the one that merge replication provides out-of-the-box J), but, are you fine with building such a (mammoth) architecture yourself, and incurring the various costs involved? In merge replication, we take the pains and give you a solution, although, for a special, tailored need, is also happens to be the most common and justified one. In sync services, you are on your own, but, it gives many benefits that you can’t do without in many situations.

 

Contributor: Udaya Bhanu Goteti