Debugging SQL Express Client Sync Provider
- Written by Boris Drajer
- Published in Synchronization Framework
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.
46 comments
-
I do not have the exact zip file that was there before, but here is a zipped up copy of my proof application. It has been a few years since I've looked at this stuff, so hopefully it explains itself sufficiently.
http://www.codeable.net/files/SQLChangeTrackingSync.zip -
Hi,
Thanks for such a great work. just wondering, the link http://files.codeable.net/Code/SqlExpressClientSyncProvider.zip is down... can I get a copy of this file if you still have it...
Thanks a lot... -
I am converting an app to Windows 8 with Express (from CE) using a sync component on the desktop. I do not want to switch to n-tier and can not believe that MS did not ever create a SQLExpressClientSyncProvider. I do not want to switch to peer-to-peer as it is overkill for what I need. (I would consider using Merge replication if I have to switch to p2p sync framework)
I was considering rewriting a provider using the ILspy to copy the CEprovidor but it seems like you guys got this working. However the link is broken. Can you repost or send me the current code ? -
Hi,
Indeed, it seems that the sample was removed from the MSDN site, for better or worse... If you need it you should probably post a question on Microsoft's sync framework forum. But if you want the debugged version, take a look at the comments above, there's a couple of links to download the updated versions with full source. -
Hi All,
Is the above "sql express provider source" link working? Thanks in advance. -
30) I was able to get the sample code working no problem. It has an enhanced version of the Express provider which doesn't use a guid table so my question earlier is likely yes it is not necessary when using change tracking.
It is curious that the SyncAdapters are created on the client instead of in the service as I have done withe my SQLCE implementation. I see how there could performance benefits when doing this if using an adapter builder because you don't have to make a remote call, but it also limits the functionality to have data driven filters and filter parameters. I'll play with it some more to see what I can work out.
Anyone else out there trying to use dynamic Adapter builders with an Express client? -
I would like to comment that I appreciate the collaboration that occurred on this site to get a working version the MS SqlExpressClientSyncProvider (sample) out to the public. I assume the "source" link in the blog has all the integrated changes. I've just started converting a SQL 2008 Server with CT to SQLCE product to use an Express client and let me tell you the process of getting there has sucked. I am using N-Tier and have been asked to do it without modifying schema or using triggers. I found your page from an answer to my question on Stack Overflow by JuneT. A combination of damon's sample and William M's client have me almost fully functional. My variations is that all syncadapters are created with the builder. I do however have a question. Neither the server or client DB script has a reference to the "guid" table. Is this not needed with CT or am I missing something?
Also, I would like to know if there is any new advice on this process since 9/2010 from William M. -
Any help with batching? I cannot see how or even if the batching of updates is possible... What objects do I need to use?
Thanks! -
Hi guys... Unfortunately, a couple of comments seem to have disappeared from the WordPress database - the site was moved to a new hosting service but I don't think that's what caused the problem. I'll see if I can reconstruct them somehow. Sorry about that.
-
This website was down for some time. It appears as if some comments were lost after it came back. I currently have no issues with any topic that have been uncovered here. My solution is now (again) available at that link above should anyone be interested.