Debugging SQL Express Client Sync Provider

How to finally get the SQL Express Client Sync Provider to work correctly? It’s been almost a year since it was released, and still it has documented bugs. One was detected by Microsoft more than a month after release and documented on the forum, but the fix was never included in the released version. We could analyze this kind of shameless negligence in the context of Microsoft's overall quality policies, but it’s a broad (and also well documented) topic, so we’ll leave it at that. It wouldn’t be such a problem if there were no people interested in using it, but there are, very much so. So, what else is there to do than to try to fix what we can ourselves… You can find the source for the class here. To use it, you may also want to download (if you don’t already have it) the original sql express provider source which has the solution and project files which I didn’t include. (UPDATE: the original source seems to be removed from the MSDN site, and my code was updated - see the comments for this post to download the latest version). The first (and solved, albeit only on the forum) problem was that the provider was reversing the sync direction. This happens because the client provider basically simulates client behavior by internally using a server provider. In hub-and-spoke replication, the distinction between client and server is important since only the client databases keep track of synchronization anchors (that is, remember what was replicated and when). I also incorporated support for datetime anchors I proposed in the mentioned forum post, which wasn’t present in the original source. But that is not all that’s wrong with the provider: it seems that it also swaps client and server anchors, and that is a very serious blunder because it’s very hard to detect. It effectively uses client time/timestamps to detect changes on the server and vice versa. I tested it using datetime anchors, and this is the most dangerous situation because if the server clocks aren’t perfectly synchronized, data can be lost. (It might behave differently with timestamps, but it doubt it). The obvious solution for anchors is to also swap them before and after running synchronization. This can be done by modifying the ApplyChanges method like this:

foreach (SyncTableMetadata metaTable in groupMetadata.TablesMetadata)
    SyncAnchor temp = metaTable.LastReceivedAnchor;
    metaTable.LastReceivedAnchor = metaTable.LastSentAnchor;
    metaTable.LastSentAnchor = temp;

// this is the original line
SyncContext syncContext = _dbSyncProvider.ApplyChanges(groupMetadata, dataSet, syncSession); 

foreach (SyncTableMetadata metaTable in groupMetadata.TablesMetadata)
    SyncAnchor temp = metaTable.LastReceivedAnchor;
    metaTable.LastReceivedAnchor = metaTable.LastSentAnchor;
    metaTable.LastSentAnchor = temp;

This seems to correct the anchor confusion but for some reason the @sync_new_received_anchor parameter still receives an invalid value in the update/insert/delete stage, so it shouldn’t be used. The reason for this could be that both the client and server use the same sync metadata and that the server sync provider posing as client probably doesn’t think it is required to leave valid anchor values after it’s finished. I promise to post in the future some more information I gathered poking around the sync framework innards. Note that this version is by no means fully tested nor without issues, but its basic functionality seems correct. You have to be careful to use @sync_new_anchor only in queries that select changes (either that or modify the provider further to correct this behaviour: I think this can be done by storing and restoring anchors in the metadata during ApplyChanges, but I’m not sure whether this is compatible with the provider internal logic). Another minor issue I found was that the trace log reports both client and server providers as servers. If you find and/or fix another issue with the provider, please post a comment here so that we can one day have a fully functional provider.


  • William M

    This post was a life saver when I was doing research on synchronizing SQL STD to SQL Express. I have since refactored/updated the class so that it is more consistent with the style of the sync framework: It now uses command properties like the other classes and did some general rework on some of the methods. It no longer has implementation, and every method is virtual in case of future overriding need. I have the zip of the new class and a sample class that consumes it here:

    posted by William M subota, 13 mart 2010 03:18 Comment Link
  • bdrajer

    Yes, this is the "special" provider for hub-and-spoke and it should be significantly simpler to use than the peer-to-peer version. It was unofficial (a "sample" as Microsoft dubbed it) until a few weeks ago. The Microsoft Sync Framework 2.0 CTP 2 contains the release version of the provider that I would expect to be stable enough for normal use. Now, since the sample version was patched-up (using a server provider to work as a client etc.), the new version is probably fundamentally different. This means that the behaviour of my application could change if I switch to the new version. Because of this, I want to be careful about it - but for anyone who hasn't used the previous version of SQL Express sync provider, I think it would be smart to start with CTP 2 and never look back :).

    UPDATE: it seems I was misled by the doublespeak of Microsoft's annoucement. There is still no official Sql Express provider. More details" rel="nofollow">here.

    posted by bdrajer četvrtak, 06 avgust 2009 13:52 Comment Link
  • peter cli

    I also am very interested in learning about the SQL Express Client Sync Provider for n-tier. I am using the june 08 custom class, which does implement important features.

    Has the official versin been released yet ?

    posted by peter cli sreda, 05 avgust 2009 01:46 Comment Link
  • Johan


    Are you referring to the sqlsyncprovider?
    Because that one is based on peer to peer conection.

    Only needing a server n-client architecture; a sqlexpressclientprovider for the older syncagent would be fine.
    Because otherwise I need to make a peer to peer configuration for getting sqlexpress support. When I only need hub and spoke :->

    posted by Johan četvrtak, 23 jul 2009 12:18 Comment Link
  • bdrajer

    Hi, sorry for the delay.

    Since there's a new CTP of the .Net Sync Framework out that contains an official version of the SQL Express Client Sync Provider, all of this debugging info may be obsolete. On the other hand, I do intend to submit it to thorough examination once I have some spare time (the previous version is built into a live application so we have to be careful not to break something). Stay tuned :).

    posted by bdrajer sreda, 22 jul 2009 14:55 Comment Link
  • KonstantinMiller

    How soon will you update your blog? I'm interested in reading some more information on this issue.

    posted by KonstantinMiller ponedeljak, 06 jul 2009 21:22 Comment Link

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

Na vrh