none
Moving data from remote to central using SQL CE. RRS feed

  • Question

  •  

    Hi,

    We are developing one OCA which operates with SQL Server 2012 database at both central and client. Objective is to sync the remote applications with central appliation in a disconnected mode (via USB or file). We tried to push the data from Remote DB to some temporary SQL CE  and move this as a file using USB stick and try synchronizing the data to central database. This works great for the very first time in this operation.

    During subsequent sync from Remote to Central, we are facing some issue in identifying the incremental change set. Meaning, in the second sync, we don't want to move the data, already gone in the first sync. Will using change tracking option in sql server help in this context? or do we need to think / consider something else?

    Any suggestion on this would greatly help.

    Thanks

    Tuesday, August 21, 2012 5:12 AM

Answers

  • Hi JuneT,

    Appreciate you wonderful posts and the knowledge you have in MS Sync. Thanks for your inputs too.

    Just to summarize and give on the outlook of what we are doing for this completely disconnected architecture,

    We enable change tracking to identify the incremental change set in our Central and Remote DBs. We are tracking the row versions, export and move the data to a staging DB. From there, we use MS Sync to move the data to a CE file and ship it to destination. Then in the destination, we move the data from CE to a staging DB and use custom scripts to import the data to our application DBs. This is working just fine.

    Hope this concept will help others, who try similar task.

    • Marked as answer by The Elixir Tuesday, October 9, 2012 4:18 AM
    Monday, October 8, 2012 11:14 AM

All replies

  • which provider are you using?
    Tuesday, August 21, 2012 5:43 AM
    Moderator
  • For this trial, we have used SqlSyncProvider.

    Note: We planned to have Express Edition in Remote clients and Standard or Enterprise edition of SQL in Central base in production environment.

    Tuesday, August 21, 2012 5:52 AM
  • your solution should work.

    sync remote db to sql ce database

    ship sql ce database to central site

    sync sql ce with central database

    ship sq ce database back to remote site

    sync sql ce database to remote db

    you can't just ship the changesets. it has to be a replica that has been synched.

    Tuesday, August 21, 2012 6:02 AM
    Moderator
  • Yes.

    This approach works fine with full set of data and not with incremental change set. That is where, we got held. Since the data we are operating is bit volumnous, this is very critical.

    If some solution is provided to resolve that alone, it will help.

    Thanks.

    Tuesday, August 21, 2012 6:08 AM
  • what's the nature of changes being synched? inserts only? updates only?
    Tuesday, August 21, 2012 6:15 AM
    Moderator
  • Unfortunately all the DMLs (Insert, Update, Delete) can be performed at both Remote and Central bases. May need some resolution logics to resolve conflict during sync.

    Tuesday, August 21, 2012 6:28 AM
  • try this (disclaimer: havent have time to test the approach and there may be side effects).

    create a filtered scope template, set the filter clause to a date range on the  updatedate column of  the tracking table (e.g. updatedate => @startdate and updatedate <= @end date)

    every time you want to send changes up to central, create a scope based on the template passing a cutoff date for the changes you want to sync 

    sync the filtered scope to the sql ce client and do the same steps as above.

    caveat: everytime you need to send something from remote to central, you need to create a new scope based on the template passing on a different cutoff time based on the previous date range you specified 

    you can later on deprovision the previous date's scopes you created.

    Tuesday, August 21, 2012 6:44 AM
    Moderator
  • Will try the suggested approach.

    Is there any possiblity to update the client and sync info (preferably in the server) to facilitate the server to have some tracing information on which client has requested for sync and when it was requested. So that, on subsequent sync operations, server can use that info, even if the client db (SQL CE in this case) is not physically available?

    Tuesday, August 21, 2012 1:05 PM
  • if you want to store those information, you'll have to do it outside of sync framework.

    this part I don't get: "So that, on subsequent sync operations, server can use that info, even if the client db (SQL CE in this case) is not physically available?"

    how will the server used it? for what?

    Tuesday, August 21, 2012 1:44 PM
    Moderator
  • What i meant is, once the data is moved from Remote clinet to CE, this file need not be physically maintained in client places. Server can process that CE and the life time of it can end there. If we want to move the data from remote or central, we can use another CE, because we wanted to use it as a file, rather than a DB.
    Tuesday, August 21, 2012 2:04 PM
  • yes, you can delete the file since you're provisioning a new scope everytime you want to send a new batch of changes anyway
    Wednesday, August 22, 2012 1:19 AM
    Moderator
  • Thanks.

    We tried to move data from remote to CE and move the CE to central db for uploading the data. During this operation, we faced some issue, and we have dropped the additional columns (__sysChangeTxBsn, __sysInsertTxBsn and  __sysTrackingContext) from the underlying sync table and dropped values from __syncArticles table for the respective object. Then only, we were able to move the data from CE to central db. Is this the way to do or we are missing something?

    Wednesday, August 22, 2012 4:46 AM
  • is the SQL Ce db synched from remote first?

    the columns you just dropped are used for  change tracking and you shouldnt even have to deal with them.

    if you synched as CE db with remote and sync the CE db with central, there is nothing else extra that you need to do.

    Wednesday, August 22, 2012 5:56 AM
    Moderator
  • Yes, first SQL CE is synched from remote db. Then CE is used for upload to central. During this time only, we faced some issue and followed the column dropping approach. After dropping columns and data, CE data was successfully moved to central DB.
    Wednesday, August 22, 2012 7:00 AM
  • did you reprovision after dropping the columns?

    not sure if you have deletes metadata at that time and that would have been lost when you removed the tracking tables.

    Wednesday, August 22, 2012 7:19 AM
    Moderator
  • No. we haven't done any deprovisioning after synching data from remote to ce. Just moved the .sdf file to central for upload to central.
    Wednesday, August 22, 2012 7:47 AM
  • strange, am not sure how you got it to sync when you manually removed the change tracking mechanism and didnt reprovision.
    Wednesday, August 22, 2012 7:55 AM
    Moderator
  • we have used ChangeTracking feature of SQL server and able to get the incremental change set by keeping one copy of CE in remote client place permanently. Now that, we are able to use this CE (ofcourse copy only) in central and sync the data. But the rows deleted in remote are not synced in central through CE. Any clue?
    Friday, August 31, 2012 12:30 PM
  • so you now have a mix of SQL Change Tracking and the custom tracking used by SqlSyncProvider.

    you're now keeping change tracking metadata twice as well and write operations has to update both.

    Monday, September 3, 2012 1:18 AM
    Moderator
  • Hi JuneT,

    Appreciate you wonderful posts and the knowledge you have in MS Sync. Thanks for your inputs too.

    Just to summarize and give on the outlook of what we are doing for this completely disconnected architecture,

    We enable change tracking to identify the incremental change set in our Central and Remote DBs. We are tracking the row versions, export and move the data to a staging DB. From there, we use MS Sync to move the data to a CE file and ship it to destination. Then in the destination, we move the data from CE to a staging DB and use custom scripts to import the data to our application DBs. This is working just fine.

    Hope this concept will help others, who try similar task.

    • Marked as answer by The Elixir Tuesday, October 9, 2012 4:18 AM
    Monday, October 8, 2012 11:14 AM