Configuring Local Database Cache generated sync for UploadOnly

The Local Database Cache item in Visual Studio by default generates code for Snapshot or Incremental downloads only.

To change the sync to upload only, it is not enough that the SyncDirection is set to UploadOnly.  When you simply set SyncDirection=SynDirection.UploadOnly, you will run into an issue where subsequent updates on rows in the client side are not uploaded to the server.

This is because by default, the designer generated code compares timestamp column (or whatever column you choose for comparing updates) to the last_received_anchor.  If you inspect the generated UpdateCommand, the update statement Where clause looks similar to this:

WHERE ([Id] = @Id) AND (@sync_force_write = 1 OR ([TimeStamp] <= @sync_last_received_anchor)

The condition:  ([TimeStamp] <= @sync_last_received_anchor) is there to check that the server row being updated by the update coming from the client has not been updated in the server side since the last synchronization. A client update on a row that has been updated also in the server is treated as a conflict.

The last_received_anchor is a timestamp anchor stored on the client side to mark the high watermark when changes were last retrieved from the server. This anchor is then used as the low watermark on the next sync.

i.e., WHERE ([TimeStamp] > @sync_last_received_anchor AND [TimeStamp] <= @sync_new_received_anchor)

The last received anchor is first/last set by the designer when you configure the sync.  By configuring the sync direction to UploadOnly, no new anchor value is downloaded and saved on the last_received_anchor on the client side since Sync framework won’t bother to retrieve and store an anchor value from the server side that marks the high watermark timestamp on the server during the sync.

Here’s how to reproduce the scenario:

1.  Assuming two rows in the server with timestamp 0x0000000000000FA1 and 0x0000000000000FA5

2. Use Local Database Cache Designer, using timestamp as update column, new and incremental updates (there is no option for bidirectional or upload only in the designer)

3. Rows get downloaded to the SDF, last received anchor is set to 0x0000000000000FA5

4. Change sync direction to UploadOnly

5. Do an insert on the SDF and sync, SQL Profiler shows last received anchor as 0x0000000000000FA5, this works since inserts on server doesn’t check for timestamp

6. Newly inserted/uploaded row in server gets a timestamp of 0x0000000000000FA6

7. Update the row on client again then sync,  SQL profiler shows last received anchor still as 0x0000000000000FA5 (we only upload so last received never changes), however timestamp of the same row which was previously uploaded and inserted to server in step 5/6 is  0x0000000000000FA6.

8. Where condition is 0x0000000000000FA6 <= 0x0000000000000FA5, so it fails and no update happens.

To work around this issue, you can remove the condition ([TimeStamp] <= @sync_last_received_anchor). But this will result to the client overwriting  the server changes with its own changes.

A more elegant solution is to simply configure the sync direction to BiDirectional but configuring sync as well not to select and download changes from the server side. This would allow sync to retrieve a new anchor value for the last_received_anchor without retrieving any rows from the server.

Here’s how to the code looks like:

Code Snippet
  1. LocalDataCache1SyncAgent syncAgent = new LocalDataCache1SyncAgent();
  3. LocalDataCache1ServerSyncProvider remoteProvider = (LocalDataCache1ServerSyncProvider)syncAgent.RemoteProvider;
  4. remoteProvider.TestSyncAdapter.SelectIncrementalInsertsCommand = null;
  5. remoteProvider.TestSyncAdapter.SelectIncrementalUpdatesCommand = null;
  6. remoteProvider.TestSyncAdapter.SelectIncrementalDeletesCommand = null;
  8. syncAgent.Test.SyncDirection = Microsoft.Synchronization.Data.SyncDirection.Bidirectional;
  10. Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();


I hope this helps.



  1. Hi June, I like the look of your more elegant solution and I\’d like to implement this in my own project. However, line 3 in your example, I\’m not sure where the LocalDataCache1ServerSyncProvider object would come from in the client project? Is there a way of referencing this? (I\’ve tried setting this code block in the server project instead and got a SchemaException during sync: "Make sure that you have specified a value for at least one of the following SyncAdapter properties: SelectIncrementalInsertsCommand or SelectIncrementalUpdatesCommand.")RegardsJonAKA SunHunter (on MSDN forums)

  2. if you used the Local Database Cache designer, LocalDataCache1ServerSyncProvider is the default name for the server sync provider class it generates. You mentioned you have a server project, am assuming you\’re having an n-tier setup with WCF, is that right?

  3. Hi, yes, this is n-tier. I just couldn\’t find a way of resolving the server provider class within my client project (I guess there is another trick I\’m missing…) The client is a Windows Mobile device speaking to WCF hosted in IIS. I have implemented instead the less elegant fix for UploadOnly updates (so thanks for the useful blog post) but it didn\’t resolve my problem with missing UploadOnly inserts (which was my original issue). I\’ve got a bit further narrowing down the issue but a solution still eludes me…

  4. Hi Jon,Yes, you\’re right. You will not get a reference to it on your client because all the client has is a proxy to the provider thats on your server and the adapter commands are not part of the proxy.Thanks!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: