locked
How to handle schema changes in sqlserver/sqlce synced databases? RRS feed

  • Question

  • Hi:

    I have created a Windows Mobile 6 application that uses a WCF service library, hosted on IIS, to keep a local data cache (in the form of a SqlCE database) synced with a SQLServer 2008 database.  It is working beautifully. 

    However, I am wanting to add new features to the application, so the database schema needs to be modified (new tables and new columns in existing tables added).  The synchronization was set up originally using the 'Configure Data Synchronization' wizard that appears when adding a 'Local database cache' item to the WCF project. 

    Now, I don't know how to go about ensuring the server and client schema changes get incorporated into the sync setup; whatever new tables/columns get added need to participate in the syncing just like the existing tables.  The WinMo apps are out in the field, so the update, however it works, needs to happen remotely, and the remote databases cannot simply be replaced.  One other detail: the data are going one way only, from server to devices; no upload sync is necessary.

    I'm new to the Sync framework, so I guess I'm a bit confused about what the wizard did for me.  If I understand correctly, it looks like a reprovisioning would do the trick; is this right?  And that gets done to the server and the client, and the client database needs to get replaced?  If someone can enlighten me on this I'd appreciate it!

    Thank you!


    m j klassen
    • Edited by MJKlassen Wednesday, December 8, 2010 1:27 PM to clarify the question
    Tuesday, December 7, 2010 8:55 PM

Answers

  • if you used the Local Database Cache wizard, then you can refresh the code generated by re-running the wizard. however, since you mentioned you cant replace the client databases, you will have to find a way to replace the existing table definitions in the client and add the new tables as well.

    if the data being downloaded is not that big, you can simply put a flag that tracks if the schema and sync group tables has changed. if it has changed, simply change the TableCreationOption to DropExistingOrCreateNewTable so that it will create the table if it doesnt exists (for newaly added tables) or drop it if it exists ( for same table, but modified columns)

    rather than using the Local Database Cache Designer, you may just want to hand-code the sync so you can easily modify the schema and the tables involved, see How to: Configure N-Tier Synchronization in the documentation.

    • Proposed as answer by Ganeshan Thursday, December 9, 2010 5:48 PM
    • Marked as answer by MJKlassen Thursday, December 9, 2010 6:09 PM
    Wednesday, December 8, 2010 3:12 PM

All replies

  • if you used the Local Database Cache wizard, then you can refresh the code generated by re-running the wizard. however, since you mentioned you cant replace the client databases, you will have to find a way to replace the existing table definitions in the client and add the new tables as well.

    if the data being downloaded is not that big, you can simply put a flag that tracks if the schema and sync group tables has changed. if it has changed, simply change the TableCreationOption to DropExistingOrCreateNewTable so that it will create the table if it doesnt exists (for newaly added tables) or drop it if it exists ( for same table, but modified columns)

    rather than using the Local Database Cache Designer, you may just want to hand-code the sync so you can easily modify the schema and the tables involved, see How to: Configure N-Tier Synchronization in the documentation.

    • Proposed as answer by Ganeshan Thursday, December 9, 2010 5:48 PM
    • Marked as answer by MJKlassen Thursday, December 9, 2010 6:09 PM
    Wednesday, December 8, 2010 3:12 PM
  • Thanks JuneT.  Since writing this post, I've learnt that Syncronization Services 1.0 SP1 was used.  Would it be straightforward and/or beneficial to upgrade to v2.1?  This would obviously require new client databases, but I'm wondering if it'd be significantly faster.  Our sync takes between 1 and 2 minutes to perform on the device.
    m j klassen
    Wednesday, December 8, 2010 4:00 PM
  • unfortunately, device support hasnt improved from v1 to v2.0 or v2.1... it still uses the Synchronization Service v1 sp1 provider... a more robust device support will come in v4
    Wednesday, December 8, 2010 11:37 PM