none
How does sync servces on device determine which changes to upload to server? RRS feed

  • Question

  • Hi, I am trying to troubleshoot an issue with a production application (Windows Mobile device using Sync Services and Sql Server Compact 3.5). Within the application, there is an UploadOnly sync group which intermittently fails to upload its changes to the server. There are no run-time errors on either the device, or on the server and the 'missing data' that is not being uploaded is causing a problem to users of the applcation.

    I am now wondering if the changes are being selected correctly on the client (and never reaching the server, hence no errors being raised), but I cannot find anywhere the documentation on how changes are selected on the client device by the sync framework.

    On the server side, there is a SelectIncrementalInserts command, but on the client I'm not sure how this works? My understanding is that there is metadata within the local database file that manages changed rows etc since the last sent/received anchors were set. Is there an article anywhere that explains how these work on the client side?

    TIA
    Saturday, January 30, 2010 4:59 PM

Answers

  • That is interesting.

    Could you try to narrow down on a patter the next time it happens or maybe track it to find a pattern?

    Also you should look at these and enable or use these to debug further.

    1. Tracing How to: Trace the Synchronization Process<!---->
    2. Events How to: Work with Events and Program Business Logic - specifically ChangesSelected on client, ChangesApplied on server, ApplyChangeFailed on server<!---->
    3. SQL profiler - run on the server when such an update goes missing to see if anything strage is going on on the server
    3. what does the log say - SyncStatistics. Does it have anything for this missing row?

    This posting is provided AS IS with no warranties, and confers no rights
    Thursday, February 11, 2010 7:29 AM

All replies

  • you should find tables and columns on the client database which are prefix with "_sys". The documentation of the members of the SQL CE provider doesn't list all the available members, but if you use Reflector, you'll actually find methods there for retrieving and setting the anchors.
    Saturday, January 30, 2010 6:58 PM
    Moderator
  • Sunhunter, what is this missing data? Is there a pattern as to when you encounter this missing data?
    Does this data come across on subsequent attempts to synchronize?
    This posting is provided AS IS with no warranties, and confers no rights
    Sunday, February 7, 2010 11:32 PM
  • Hi, thanks both for responding. No, there appears to be no pattern as to why the failure occurs. I have an upload only sync group which is supposed to upload input collected on the device from the end-user. The sync group contains four tables. Two of the other tables are also 'upload only' and one is 'bi-directional' (the four tables are related and cannot be uploaded separately as there are FK relationships). The other tables appear to synchronize correcftly but intermittently one of the tables does not upload its data.

    The sync completes successfully (no errors) but the data does not end up in the server database and on subsequent synchronizations, the data is not re-transmitted (it remains on the device). On the server, there is no trace of any of these records attempted to be processed duirng the server synchronization process although the records definitely exist in the client database. On other occasions, the process works perfectly. It is an intermittent issue.

    I originally thought perhaps that there could be an OOM issue on the device during the sync, or a network failure but the sync session should throw up an error if so? My final thought was therefore that the changes were not being (re-)selected on the device since there is no error anywhere during the sync.


    Tuesday, February 9, 2010 5:17 PM
  • That is interesting.

    Could you try to narrow down on a patter the next time it happens or maybe track it to find a pattern?

    Also you should look at these and enable or use these to debug further.

    1. Tracing How to: Trace the Synchronization Process<!---->
    2. Events How to: Work with Events and Program Business Logic - specifically ChangesSelected on client, ChangesApplied on server, ApplyChangeFailed on server<!---->
    3. SQL profiler - run on the server when such an update goes missing to see if anything strage is going on on the server
    3. what does the log say - SyncStatistics. Does it have anything for this missing row?

    This posting is provided AS IS with no warranties, and confers no rights
    Thursday, February 11, 2010 7:29 AM
  • Hi, thanks for your response. I now have a clearer idea of the underlying problem.

    In my application, synchronization occurs on a background thread to allow users to continue working while a sync is in progress.

    In my scenario, I have a real-estate monitoring application where a mobile user is moving around a building checking off items on a list (e.g. are the windows clean? is the floor clean? etc), when the user leaves a room they instigate a sync to upload their monitoring data and then continue to the next room where they add more data, often while the previous sync is still in progress (it is inpractical for the user to always wait for the sync to complete before continuing working as it would slow their work down too much).

    Under the above conditions (soemtimes adding new data while syncing), is it possible that sync services could sometimes not 'know' whether data has been sync'd or not and therefore sometimes not upload all of the data that has been captured? I originally thought it would be safe to add new data while a sync is in progress as surely there would be some type of flag set internally for each record to indicate whether it had been uploaded or not and therefore a subsequent synchronization would pick up any new records added after the previous synchronization had been initiated.

    In my original post, I said that I was not sure how local data was selected to be uploaded and I think this would be interesting to know so that I can program around this issue, or perhaps write the application slightly differently to take this into account.

    Regards

    Wednesday, March 3, 2010 9:23 AM
  • It seems the multi-threaded issue was a false alarm and has now been ruled out (due to extensive testing).

    The original problem remains in that not all data from an uploadonly sync table is arriving on the server and there are no error messages on the device or on the server to give me any clues as to why this is happening. Other sync tables within the same sync group are uploading correctly, but one table (and always the *same* table) is intermittently not uploading all of its contents to the server. This is quite a serious issue for our application and I'm still no nearer to understanding why this issue is occurring despite an enormous amount of effort in trying to get to the bottom of the problem.
     
    Monday, March 15, 2010 2:05 PM
  • Hi,

    Have you enabled tracing as suggested by Mahesh above and set the tracing to 4 - Verbose? How about running SQL Profiler to check the uploaded rows and verify which rows reached the server and which rows were skipped in the client Select changes to catch any specific pattern.

    Monday, March 15, 2010 2:34 PM
    Moderator
  • Apologies for the delay in responding. During this time, I've released an update to the application to collect some more diagnostics based on the SyncStatistics class. In answer to Mahesh, all errors on both server and client are logged locally and there is nothing anywhere to indicate a problem (no exceptions raised and no problems in ChangesApplied or ApplyChangeFailed on server). SyncStatistics on the client says zero UploadChangesFailed for all sync sessions (during a period where it is known that there was a problem with missing data). Is there a setting on the client where you can set logging to verbose or does this just work on the server? (sorry, yes, I've already traced on the server)

    I still suspect that the 'missing' rows are not being selected for upload on the client. I'm not sure what I can do on the device to test this assertion? Maybe loop through the client dataset and log details of each row on every sync to find where/when the 'missing' row disappears during the sync process and then wait for the problem to re-occur and retrospectively trawl through the log file to see if there are any clues. This still wouldn't tell me why the rows were not selected but at least I'd have a better idea of where the problem is.

    Monday, April 26, 2010 9:15 PM
  •  

    Hi, I'm continuing to get issues with missing data. I've noticed in the database on the client, the tracking columns __sysInsertTxBsn and __sysChangeTxBsn.

    Here's what happened when I performed my own verification/observations of the client's problem:

    1. After a sync, rows are downloaded successfully, all of the values in the tracking columns are set to null

    2. I add some new rows on the device via the application (which end up in four different tables), sync again and the new rows do *not* upload to server. No errors anywhere on device or server.

    3. I check the database on the device. The new rows in all four tables definitely exist, the change tracking columns are still null.

    4. I then add some *more* rows on the device, sync again and the sync uploads all of the rows from all four tables in my second input session. It also uploaded the rows from *one* of the tables in my first input session (but missed out the rows from three other tables that had been added during the first input session!). Again, no errors, and the rows are now 'missing' from these tables on the server (but still locally on the device).

    5. The change tracking columns in the 'problem' tables after the second sync still show null for the rows added in the first input session and contain values for the rows uploaded during the second sync. The table that uploaded correctly shows values in the rows that were uploaded.

    6. The missing rows from the initial input session are never re-sent to the server during subsequent syncs, it's almost as if the client thinks it's already uploaded them ok, so there's no need to re-select them (or maybe the client thinks these are not new rows so don't select them).

    7. The table that uploaded correctly was a bi-directional sync table (the three other sync tables are upload only - not sure if that should make a difference, but thought it worth mentioning). Could the mix of upload only and bi-directional tables within a single sync group cause an issue?

    8. One of the 'upload only' tables contains binary data (photos) and is 'upload only' because we want to keep the sync time as low as possible (and also some mobile networks charge for each byte sent/received). The other 'upload only' sync table just contains small amounts of text.

    9. The four sync tables are arranged into two sync groups. There are three tables in one sync group (1 bi-directional, 2 upload-only). In the other sync group, there is just one table (upload only). The sync group containing three tables uploads before the sync group with just one table. The table that uploaded correctly was SyncTable1 which is part of a sync group containing three tables (below is a high level sync schema).

     

    Sync Group 1

    SyncTable1 (base table) (bi-directional) (*no missing data)

    SyncTable2 (FK to base table) (upload only) (*missing data)

    SyncTable3 (FK to base table) (upload only) (*missing data)

    When this sync group is uploaded, the server ChangesApplied event-handler calls an external assembly which generates an email to notify users of a business issue (using details from the three tables). There are no errors raised by this routine (but again thought it worth mentioning).

     

    Sync Group 2

    SyncTable4  (FK to base table) (upload only) (*missing data)

     

    10. Having mentioned this detail about sync groups, I can still smell a client-side issue as there is nothing on the server to indicate a problem. It just seems that the client is not re-submitting changed rows to the server even though they are not on the server. If the sync was uploading these rows and they were failing, I'd see evidence in server trace and server event handlers and if the sync was bombing out part-way through the first sync group, there should be an error somewhere.

     

    I'm still not sure why rows are not being uploaded - is there anything else I can check?  (I've been trying to resolve this issue for months) :(

     

    Sunday, May 2, 2010 8:25 AM
  • hi sunhunter,

    just a quick question, how did you provision the client database? is it via the Local Database Cache Designer?

    not sure if you're case is similar to this: http://social.microsoft.com/Forums/en-SG/syncdevdiscussions/thread/b93aef3c-7f8d-42d3-b6b1-23b56c53f56d (which i blogged about here too http://jtabadero.spaces.live.com/blog/cns!BF49A449953D0591!1207.entry)  except that your case is on inserts instead of updates.

    Sunday, May 2, 2010 10:38 AM
    Moderator
  • Thanks very much June, that sounds very interesting. No, I've not seen that information before... I'm going to give it a try. Thanks again and I'll post back if it solves the problem.
    Sunday, May 2, 2010 12:01 PM