locked
Sync Framework & client DB data migration RRS feed

  • Question

  • Hello!

    I have a task to set up bidirectional synchronization of MS SQL Server 2008 database with multiple MS SQL CE 3.5 SP1 client databases by using Sync Framework 2.0 (Sync Services for ADO.NET) through WCF service. But I have a problem with migration client data (I mean data migration, that executes only on the client side) during updating client database schema (update to the next application version). Database schema changes can be arbitrary: adding new tables, adding foreign keys to the existing tables, deleting table fields, division one table to two or more tables... And I have a constraint to save and migrate all client data changes to new DB schema during the first synchronization with updated to new version application server side.

    As far as I know standard practice in Sync Framework (WCF method GetSchema()) allows only add new tables and new columns to existing synchronizing tables, not all arbitrary schema changes. So we decided to download to the client .SDF file with new DB schema and current data, and somehow migrate client non-synchronized data to this new database. But I have a doubt about system SyncFramework-generated columns with "__sys" prefix in all synchronizing tables and system tables with "__sys" prefix. I suppose that this is a SQL CE change tracking infrastructure. And I am afraid that if we migrate only data to the new database and stay this system tables/fields empty - we broke synchronization process.

    Please advise, perhaps exist some common methods/practices for migration client data (client DB updating)?
    Perhaps this task could be done by using some methods of Sync Framework classes (SyncAgent, SyncProvider...)

    Thank you.

    Tuesday, May 25, 2010 10:39 PM

Answers

  • is the new SDF file you download provisioned for synchronization already? or is it just an SDF that has never been in a sync operation before?

    if its a non-synced SDF, then you can migrate the existing data to it and when you first synchronize, the metadata will be populated appropriately. however, if you're doing a bidirectional sync or upload, there is a greater chance everything you have on the client side will be sent up to the server.

    moving to the collaboration providers maybe a better option. you can create an SDF peer from the server or maybe even a snapshot, send the SDF to the client then initiate sync to introduce the snapshot as a new peer. you can then do the data migration to this SDF and when you next sync, only the newly migrated data need to be sent.

    • Marked as answer by _Case Thursday, May 27, 2010 11:36 AM
    Wednesday, May 26, 2010 10:42 AM
  • can you check your table creation option settings to confirm that the first sync is not wiping out your client data with data downloaded from the server?
    • Marked as answer by _Case Thursday, May 27, 2010 11:37 AM
    Wednesday, May 26, 2010 8:07 PM

All replies

  • is the new SDF file you download provisioned for synchronization already? or is it just an SDF that has never been in a sync operation before?

    if its a non-synced SDF, then you can migrate the existing data to it and when you first synchronize, the metadata will be populated appropriately. however, if you're doing a bidirectional sync or upload, there is a greater chance everything you have on the client side will be sent up to the server.

    moving to the collaboration providers maybe a better option. you can create an SDF peer from the server or maybe even a snapshot, send the SDF to the client then initiate sync to introduce the snapshot as a new peer. you can then do the data migration to this SDF and when you next sync, only the newly migrated data need to be sent.

    • Marked as answer by _Case Thursday, May 27, 2010 11:36 AM
    Wednesday, May 26, 2010 10:42 AM
  • JuneT, thank you for your response!

    Today I tried to download to the client new .SDF file (this file contains only schema and current data, but doesn't contains "__sys" tables and fields, i.e. non-synced SDF) and replace existing old SDF with this one, migrate data to this new file (execute T-SQL migration script) and then execute synchronization. As you wrote, after synchronization all metadata has been generated, but unfortunately client changes (migrated data) has been disappeared from client and doesn't appered on server. That is similar to full refresh of client database with new server schema and current server data, and this lead (in my test sample) to the lost of "unapplied" (merged) client data... :(

    Perhaps I'am doing something wrong in these steps...  But I assume that this approach is more applicable than applying arbitrary change schema/migration scripts on existing .SDF file (not replacing SDF file with new one), due to existing metadata.

    I will glad any advice!  Thank you.

    Wednesday, May 26, 2010 7:16 PM
  • can you check your table creation option settings to confirm that the first sync is not wiping out your client data with data downloaded from the server?
    • Marked as answer by _Case Thursday, May 27, 2010 11:37 AM
    Wednesday, May 26, 2010 8:07 PM
  • JuneT, thank you! 
    Changing table creation option from "DropExistingOrCreateNewTable" to "UseExistingTableOrFail" helps me! Now during synchronization, client data saved successfully.
    Thursday, May 27, 2010 11:36 AM