SQL Compact database updates - keep client data RRS feed

  • Question


    I have a question regarding the Sync Services and the SQL Compact database.


    I have read that you can initialize the db in two ways:

    1) use sync services to download table schema and data during first sync.

    2) pre-create the database and allow the ClickOnce application to deploy the database


    Let’s say that I want to use #2, a pre-created database.

    The initial install seems straight forward, but deploying a database during install raises some questions when I have a new version of my application that needs to go out to a user that has existing data that needs to be synchronized.


    Is there a different database initialization that needs to happen for a new install verses a version update (especially to keep un-sync’d client data)?


    For example, I have a user who has version 1.0 and they will be updating to version 2.0 (new column on existing sync table). Will the new database be installed by the application and will the old database be overwritten?

    What happens to the user’s data on the client db? Does it go away? How do you keep the client data during version changes?


    I have also read about the Schema events, but I don’t know when those events would be used rather than having the application initialize the database. I have an event coded. It will alter the table and add the new column. When the application runs a second time it blows up because the column already exists.


    Thanks, Jason

    • Moved by Max Wang_1983 Friday, April 22, 2011 8:55 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Monday, January 28, 2008 9:11 PM


All replies


    Is this the right forum? Or should this be in the SQL Compact forum?


    I'm going to set up the scenario using a ClickOnce deployment with a pre-built database.

    I'll test to see if the schema changes are propogated to the client db.

    I'll also test to see if the unsynchronized client stays on the client. If not, it will just be a training issue for the users, that they must synchronize their application before we roll out a release.


    This brings up an issue for me. I'd like to know the workaround.

    I've read that you can use SQL Server Management Studio 2005 to pre-build a SQL Compact v3.1 database.

    I am using SQL Compact v3.5 edition db, but our central server is SQL Server 2005. This means that I have SQL Server Management Studio 2005. As we all know SQL Compact v3.5 doesn't work with SSMS 2005 (it doesn't recognize the 2008 SSCE file). How am I suppost to pre-build a Compact v3.5 database? Is SSMS 2008 available yet?


    Come on MVP's, don't be shy

    Thanks, Jason

    Wednesday, January 30, 2008 9:47 PM
  • Hi Jason,

    I recently did a presentation that talked just about these sorts of updates.  I actually covered the content for both my Compact and Express talks at Tech Ed EMEA:

    Presentations & Demos from Tech Ed Barcelona 07


    This sample is SQLce specific, but not as elaborate as the Express sample below.  Conceptually, they are the same, I just did the Express sample after the Compact sample and haven't gone back and updated the Compact one.

    SQL Server Compact Deployment

    Deploying Express with Scripts


    Hope this helps,


    Wednesday, January 30, 2008 10:53 PM
  • Thank you Mr. Lasker. I have been reading many articles written by you and watching Channel 9 videos about SQL Compact and Synchronization Services. I have enjoyed learning about this new technology and compliment you guys and gals at Microsoft.

    Yesterday, I implemented a test ClickOnce deployment by following along this tutorial.


    It worked great. Step by step.


    One question still remains for me and I believe it is a blocking problem.

    How do I pre-build a SQL Compact v3.5 db. I can't find a download for SQL Server Management Studio for 2008 yet.

    Currently I have SSMS for SQL 2005 but it won't recognize a SSCE 3.5 db. Can you pre-build using Visual Studio 2008?

    I do have a MSDN Subscriber Access Number, will that help?


    Yesterday, I found out the following things from my ClickOnce deployment:

    #1. For schema changes to a current database and new application versions, I will update the central server with the schema changes I want, then pre-build my compact db from the server. The new schema will get created automatically.

    I will add it to my application and have it deploy with ClickOnce.

    #2. When a new database is deployed (either initial install or version update) the new database overwrites the old database and it's data. So in there is data that is not synchronized, it will be lost. When future releases go out from development to production environments, I will instruct the users to synchronize first. 


    Thanks, Jason
    Thursday, January 31, 2008 5:20 PM