none
Problem handling Handling Initial Synchronization RRS feed

  • Question

  • I have a device app that use SQL Compact and uses sync framework to synchronize to a SQL Server 2008 instance.  In order to speed up the initial synchronization of my device app, I'm creating the database (.sdf) using the sync framework on the server based on the suggestion in this article:

    http://msdn.microsoft.com/en-us/library/dd938879.aspx#HandlingInitialSynchronizationandExpiredSubscriptions

    Then I get it via a WCF service down to the device app.  I'm able to download the db and it has data.  When I sync however, the database doesn't seem to think it is syncronized and seems to try to do a full synchronization which won't work because there is too much data.  Do I have to somehow tell the compact db that it is synced already and to just pick up where it left off?  Why isn't that already doing it.

    I know you can set the client id, but should i be setting something else?

     ((SqlCeClientSyncProvider)(syncAgent.LocalProvider)).ClientId =      Guid.NewGuid();

    Any suggestions are appreciated. 

    Thanks

     

    Tuesday, August 17, 2010 10:55 PM

Answers

  • Ok.  NOW I think I figured it out.  My project that generated the snapshots on the server was referencing these (Sync Framework v2)

    C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\v1.0\Runtime\x86\Microsoft.Synchronization.dll
    C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\v1.0\Runtime\ADO.NET\V2.0\x64\Microsoft.Synchronization.Data.dll
    C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\v1.0\Runtime\ADO.NET\V2.0\x64\Microsoft.Synchronization.Data.Server.dll
    C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\v1.0\Runtime\ADO.NET\V2.0\x64\Microsoft.Synchronization.Data.SqlServerCe.dll

    instead of these (Sync Services v1):

    C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\v1.0\Runtime\x86\Microsoft.Synchronization.dll
    C:\Program Files (x86)\Microsoft Synchronization Services\ADO.NET\v1.0\Microsoft.Synchronization.Data.dll
    C:\Program Files (x86)\Microsoft Synchronization Services\ADO.NET\v1.0\Microsoft.Synchronization.Data.Server.dll
    C:\Program Files (x86)\Microsoft Synchronization Services\ADO.NET\v1.0\Microsoft.Synchronization.Data.SqlServerCe.dll

    That seems to set it up properly for change tracking using the __sysSyncArticles and __sysSyncSubscriptions tables instead of the __SyncArticles and __SyncSubscriptions.

    Still need to test but this really looks like it was the cause.

    Friday, August 20, 2010 5:11 AM

All replies

  • Another way to state the problem is that on the server when I sync the db and use the code below I get an anchor back:

    FPASQLCeClientSyncProvider).GetTableReceivedAnchor("State")

     

    When I copy the database to the device and make the same call, I get nothing back.  So for some reason it has forgotten its anchors. WHY?

    Tuesday, August 17, 2010 11:56 PM
  • can you check if  __sysSyncArticles table on teh SqlCe copy if it the anchors are there?

    Wednesday, August 18, 2010 1:46 AM
    Moderator
  • I don't see __sysSyncArticles, but I do see __syncArticles and using Query Analyzer 3.5 on the device, i can run the following query:

    select * from __syncArticles

    and see a record for the one table in the test database that has the proper table name and seems to have the received anchor set to a some binary data and client id set to a guid.  Looks something like this with truncated values shown:

    TableName           SentAnchor  ReceivedAnchor     ClientId

    MyTableName       NULL            0001000000FFF...   {DF306...

    Is this what you expected?

     

    When toggling to show system tables I see the following tables (I'm not sure what they all are):

    __sysOCDDeletedRows

    __sysOCSTrackedObjects

    __sysSyncSubscriptions

    __sysTxCommitSequence

    __syncArticles

    __syncSubscriptions

    __syncTransactions

    Wednesday, August 18, 2010 2:23 AM
  • strange, that means the lastreceivedanchor is actually set. can you try forcing the ClientID to be the same as the ClientId you have on the table?

    e.g., ((SqlCeClientSyncProvider)(syncAgent.LocalProvider)).ClientId = '{DF306...'

    Wednesday, August 18, 2010 2:39 AM
    Moderator
  • That didn't help.  What I do notice now is that if I look at it is the __sysSyncArticles (not __syncArticles) has this row which implies that there is no anchor.  Sort of looks like the tracking is happening in one table on the desktop/server version and different tables on the device.

    TableName           SentAnchor  ReceivedAnchor     ClientId

    MyTableName       NULL            NULL                    5ca7f7a4-bd8b-4bf0-bf4a-4b23cda48d86

     

    Thoughts?

    This article is sort of related:

    http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/dd26698a-21e5-4df0-af8b-0b5f38a4c381

    Wednesday, August 18, 2010 3:53 PM
  • To add to that, when you create the database using the sync process on the server, the two tables __syssyncarticles and __syssyncsubscription don't even exist.  Seems like they're created on the device.

     

    Wednesday, August 18, 2010 3:59 PM
  • I also have this problem and those tables __syssyncarticles and __syssyncsubscription don't even exist on SQL CE database.
    Wednesday, August 18, 2010 4:19 PM
  • Another thought: It looks like I'm using version 1.0 of sync framework to create the sdf on the server (i.e. the code generated by the Local Database Cache wizard which uses SyncAgent and the project is referencing C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\v1.0\Runtime\x86\Microsoft.Synchronization.dll).  Does that matter?  Should I try with the 2.0 version?  I think I have 2.0 so installed, I'm not sure why the old version is being used.
    Wednesday, August 18, 2010 4:21 PM
  • The Local Database Cache wizard is still based on v1, to be more specific, Device support is only on v1.

    how did you create the database btw, is it using sync direction = Snapshot or is it Download?

    Wednesday, August 18, 2010 11:46 PM
    Moderator
  • To create the database, I customized the generated code for the local agent to set the sync table adapters I needed and set the directions per table as needed (some downloadonly, some bidirectional).  And to my set the remote provider as my customized sql server provider which works for syncing.  So syncing that db is essentially doing what I would do if I were syncing from the device, it is just being done on the server so that we don't have the slowness of transfer over the network.
    Thursday, August 19, 2010 1:54 AM
  • I just proved that calling GetTableReceivedAnchor("State") on the server after the first sync returns the anchor.  In other words, my prepared database appears to be synced.  However, when copying to the device, the same call does not work, returning null.  So the storage of the anchor seems to be handled differently on server vs device.  Any thoughts on fixing this?
    Thursday, August 19, 2010 12:30 PM
  • I also confirmed that the GUID that is in the ClientId column of the _syssyncarticles table is what I get from the SQLCeClientSyncProvider.ClientId property.  So this further confirms that on the device, it doesn't know that it has been synced by the desktop/server API. 

    I'm tempted to try to set the table received anchor based on the the anchors in the __syncArticles table, but I'm guessing that won't work and is a good idea because before the first sync, it hasn't set up sync for the db.

    How else can I prepare the sdf on the server and get it to look already synced on the device?

    Thursday, August 19, 2010 1:11 PM
  • Ok - I have the start of a solution.  Please let me know if you think this sounds ok.

    So I still create the .sdf on the server.  I copy it down to the device.  As we know, it is confused and doesn't think it is has been synced.  So I call the agent.synchronize which will go through my wcf proxy up to the server provider and do a getschema.  After this call has completed, it now has the __sysSyncArticles tables created.  It wants to continue on with the sync process now, but I don't want it to since I know the data is already local.  So in my proxy, I check for a syncparameter that I've set to indicate this is an reinitialization and I return an empty SyncContext object.  This prevents a trip to the server and prevents getting any data (since, again, I already have the data).  Then the sync process is over and I run a custom query against my local db to get the __syncArticles table which has the receiveanchors that were set when the server did the initial snapshot sync (the anchors that I wish were found on the device).  For each of these anchors, I update the anchor using the SQLCeClientSyncProvider.SetTableReceivedAnchor method.  So essentially I'm copying the anchors to the system tables that the device cares about.  Then I do a sync which would get any changes between snapshot creation and current time.

    I've tested just for one table so far and need to do more testing (bidirectional, multiple tables, etc.) but it seems promising.

    Does this sound good? 

    Thursday, August 19, 2010 4:16 PM
  • Ugh.  So that is actually only working because it is wiping out the tables because CreationOption was being set to DropExistingOrCreateNewTable.  So it wiped and recreated the table.  That defeats the purpose of bringing the table data down.  Setting CreationOption to UseExistingTableOrFail throws and exception for "A column ID occurred more than once in specification" which I'm assuming is where it is trying to turn on the change tracking columns.  Back to the drawing board...
    Thursday, August 19, 2010 6:30 PM
  • Ok.  NOW I think I figured it out.  My project that generated the snapshots on the server was referencing these (Sync Framework v2)

    C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\v1.0\Runtime\x86\Microsoft.Synchronization.dll
    C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\v1.0\Runtime\ADO.NET\V2.0\x64\Microsoft.Synchronization.Data.dll
    C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\v1.0\Runtime\ADO.NET\V2.0\x64\Microsoft.Synchronization.Data.Server.dll
    C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\v1.0\Runtime\ADO.NET\V2.0\x64\Microsoft.Synchronization.Data.SqlServerCe.dll

    instead of these (Sync Services v1):

    C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\v1.0\Runtime\x86\Microsoft.Synchronization.dll
    C:\Program Files (x86)\Microsoft Synchronization Services\ADO.NET\v1.0\Microsoft.Synchronization.Data.dll
    C:\Program Files (x86)\Microsoft Synchronization Services\ADO.NET\v1.0\Microsoft.Synchronization.Data.Server.dll
    C:\Program Files (x86)\Microsoft Synchronization Services\ADO.NET\v1.0\Microsoft.Synchronization.Data.SqlServerCe.dll

    That seems to set it up properly for change tracking using the __sysSyncArticles and __sysSyncSubscriptions tables instead of the __SyncArticles and __SyncSubscriptions.

    Still need to test but this really looks like it was the cause.

    Friday, August 20, 2010 5:11 AM
  • Did you manage to solve the problem with the other references?
    Friday, August 20, 2010 2:15 PM
  • Yes, this solved my issue.
    Friday, August 20, 2010 5:39 PM
  • good to hear you got it up and running.

    this is actually one of the confusing parts in Sync Fx support for SQL CE. The actual assemblies for Devices are the one's installed by Microsoft Synchronization Services for ADO.NET (Devices). Whereas the same assemblies are also available in the Sync Fx folders.

    Monday, August 23, 2010 8:16 AM
    Moderator