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
-
Further update: We ran into several more critical issues with Sql CT(Offline providers), and after consulting with JuneT we switched to Sync 2.1/(PeerProviders). This is working well in production, although it has taken an immense amount of development time to get here. PeerProviders also perform better than Sql CT because of several factors including batching, bulk changes, etc.
-
Update for any people working with Sql CT in the future who didn't run away yet.
Just realized when I got multiple SyncDirections working my 3 sync groups were actually all based off the same string. Using multiple SyncGroups fails because of Foreign Key constraints between tables in different groups. The multi direction sync works fine with a single SyncGroup though.
To deal with the "SQL Server change tracking has cleaned up tracking information for table *" see my reply as HoldFast Stark in http://social.microsoft.com/Forums/en-US/0e2dd016-5d14-4cf9-aebc-93b762b42ec7/sql-server-change-tracking-clean-up-tracking-information?forum=syncdevdiscussions -
To solve the Server Insert Client Update bug rather than go through the nightmare of multiple anchors and unions (and performance impact of that)
I just changed the server ApplyChangesFailed to overwrite with Client Version on ClientInsertServerInsert conflict, as each Primary Key is device specific, a client can't ever insert with a PK that the server would use. -
The Server Insert Client Update bug still exists in the available version of SqlChangeTrackingSync.sln
-
Also for anyone else fighting with this in future, you can't have a SyncGroup with multiple SyncDirections in it.
-
Got mulitple SyncDirections working. I had to set SqlSyncAdapterBuilder.SyncDirection = SyncDirection.Bidirectional to generate all the commands. Then I can set for each SyncTable.SyncDirection to UploadOnly/DownloadOnly.
-
With the SQlChangeTrackingSync example I can't get any SyncDirection besides BiDirectional working. It says sync completed but doesn't sync anything.
On my own solution based on both examples when set to DownloadOnly I get exception System.ArgumentNullException: Buffer cannot be null.
Parameter name: buffer
at System.IO.MemoryStream..ctor(Byte[] buffer, Boolean writable)
at System.IO.MemoryStream..ctor(Byte[] buffer)
at SqlExpressClientSyncProvider.DeserializeAnchorValue(Byte[] anchor)
When set to UploadOnly sync completes but no changes are propagated from client to server. Bidirectional works fine. -
Ok so looks like I need to add back in the client ID tracking for multiple clients. See "Identifying Which Client Made a Data Change" section http://msdn.microsoft.com/en-us/library/cc305322.aspx
-
Does all the ClientID, ServerID stuff matter? I removed it and have code working fine here. But just noticed if multiple clients they will sync to the server fine, but if Client A makes a change then Client B won't get that new change. Server is making the change but must not register it as a change to send to Client B
-
Of the 2 examples that downloads still work
SyncDemo is local and simpler
SQLChangeTrackingSync uses a ProxyProvider over WCFService and is more complex.
Both are similar but code differs quite a bit, I used both as reference for a large sync application.