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
-
If you have googletalk, add me: codeable.
-
Damon is correct everyone! Be very careful using ANY version of this provider that exists before now. I added a solution to my version and have updated the zip in the link above. I didn't notice Damon's solution till after I went to test his database again. Our solutions are similar, except mine only retrieves the sync anchor of changes applied by the server. (this ensures no changes between transactions are lost)
Changes:
1) Added support for "ServerID" so applied changes will properly track their origin. [sql: WITH CHANGE_TRACKING_CONTEXT(ServerID)]
2) -You can provide the ServerID on the SyncAgent like:
this.Configuration.SyncParameters.Add(SqlExpressClientSyncProvider.PARAMETER_SERVER_ID, serverID);
-If you do not provide a serverID, it will generate a GUID and use that for the duration of the session.
-You can override all new methods as they are virtual.
3) There is now a property : (bool)SqlChangeTracking. If the client is using sql change tracking set it to true to properly update the anchors.
Let me know if anyone finds any more bugs! -
I am able to see that bug now. And was able to replicate it within my test environment. I wasn't paying close enough attention to your steps. (sorry)
4. Updating the same row in the client
We will have to solve this problem before we go live! I'll be investigating and working on this tomorrow.
BTW: I like your solution set up. -
I just downloaded your code, I'll attempt to get it runnin on my system and update shortly.
-
I am using the anchor scripts from your code.
Just to be sure the steps to reproduce the bug are...
1. On the server: insert a new row
2. Do a sync
3. On the client: Update a non-key column in the new row.
6. Do a sync...
Violation of primary key constraint - Its trying to do an insert on the server instead of an update
If you do an extra sync after step 2 you will not get the error.
Here's a link to my demo code. It includes a workaround for this problem which i've commented out in the SqlExpressClientSyncProvider class.
http://cid-99de5ac895b36056.skydrive.live.com/self.aspx/.Public/SyncDemo.zip -
I set up a test environment using the following specs, and was unable to see the conflict you mention. Maybe there is a flaw with your anchor script?
I am using the code I linked to above. [which includes the comment similar to Eisen's note]
Server (Remote)
Config: SQL2008 w/change tracking
Class: DbServerSyncProvider
Client (Local)
Config: SQL2008 Express w/change tracking
Class: SqlExpressClientSyncProvider (extended similar to the sample in the zip) -
I also noticed that the ApplyChanged method needs to be fixed as well if you plan on doing a SyncDirection other than BiDirectional. Here is the fix:
public override SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)
{
// Need to flip download and upload SyncDirections
// since this is from the server perspective
foreach (SyncTableMetadata metaTable in groupMetadata.TablesMetadata)
{
switch (metaTable.SyncDirection)
{
case SyncDirection.DownloadOnly:
{
metaTable.SyncDirection = SyncDirection.UploadOnly;
break;
}
case SyncDirection.UploadOnly:
{
metaTable.SyncDirection = SyncDirection.DownloadOnly;
break;
}
}
}
SyncContext syncContext = _remoteSqlProvider.ApplyChanges(groupMetadata, dataSet, syncSession);
// Swap them back for consistency
foreach (SyncTableMetadata metaTable in groupMetadata.TablesMetadata)
{
switch (metaTable.SyncDirection)
{
case SyncDirection.DownloadOnly:
{
metaTable.SyncDirection = SyncDirection.UploadOnly;
break;
}
case SyncDirection.UploadOnly:
{
metaTable.SyncDirection = SyncDirection.DownloadOnly;
break;
}
}
}
// Set table rec'd anchors
foreach (SyncTableMetadata table in groupMetadata.TablesMetadata)
{
SetTableReceivedAnchor(table.TableName, groupMetadata.NewAnchor);
}
return syncContext;
} -
The problem is with the last sent anchor. If you insert some records on the server and then do a sync it updates the last sent anchor before it applies the changes on the client. Then the next time you do a sync the server gets the changes from the last sent anchor which means it gets the inserts that were made on the client and tries to apply them back on the server. So it's echoing the changes.
-
I just ran that test with SQL STD to SQL STD and did not have that issue. I will set up another sql express environment to make sure. If there are bugs, I wanna know about them!
-
I'm having the following problem using this sql express provider with sql server change tracking...
1. Inserting a new row in Server
2. Synchronizing between server and client
3. Inserted row is added to client
4. Updating the same row in the client
5. Synching back to the server
6. Update is not reflected in the server, instead we are getting a conflict message of duplicate row inserting.
Am i the only one getting this?