Sync Framework application design question RRS feed

  • Question

  • Hi, 

    I would like to get some advice about how to design my application for the following requirements.

    - I have 1 central SQL Server 2008 Express database with 50 tables
    - I have 100+ Windows Mobile 5 devices that need a local copy of that database in SQL Server Compact 3.5 SP1 format
    - 43 of the 50 tables are "one-time" "download only" from the server.  What I mean is that changes on the server in those tables are very rare (only a few times a year).  If there are changes in the database, there will also be a file-and-folder structure that needs to be updated, and a user will have to "commit" the changes by clicking a button somewhere in the application that they use to edit the contents of the database.
    - 3 of the 50 tables are "often" "download only" from the server.  This means several times a day the tables on the devices should be updated with changes on the server. (There is WIFI connection at the desk where the devices are stored, but not all the time while they are used).  These tables are filled from a website by users that will use the mobile devices probably a few days later and they will need their personal data then.  It should be available on all the mobile devices because we don't know in advance which device will be given to that user.
    - 4 of the 50 tables are "often" "upload only" from the devices to the server.  This means data is inserted on the device, and should be transfered to the server when the user returns his mobile device at the desk.  He will consult the data from a website later that day or the day after.  There is no need to get this data on the other mobile devices.

    I thought that the Sync Framework would be a great tool to do this, so I started experimenting a little, but got stuck. 

    What I had in mind was for the rare major update (a few times a year), to recreate the .sdf compact database from scratch, by performing a "one-time" sync.  I was able to do this quite easily using the sample code from the MSDN documentation.   However, since these updates are rare, I would like to delete the "scope" on both the SQL Server database and the SQL Compact database, because it won't be needed anymore and can be recreated easily for the next update.  I haven't found how I can delete a scope that was created earlier.
    Since there will always be other files and folders that will need to be synced (one-way from server to devices), I thought that I could perform a one-way "file synchronisation" (including the .sdf database file) from the server to each of the devices.  I don't know yet how to do this.  

    But how do I do the sync for the 3 download-tables that are changed multiple times a day and for the 4 upload-tables that are changed on the devices?
    Do I need an application on the server that tries to synchronize maybe every hour for all the devices that can be reached by the WIFI connection?  Do I perform a database-sync?  Or do I create a seperate .sdf file for the download tables and use a file sync to get that file to the devices?
    Do I create a seperate (empty, schema only) .sdf file for the upload tables and first use a file sync to get that file on the server, then set up a one-time one-way database sync to get the data in the central SQL Server?

    I would appreciate your comments/advice/tips.

    Thanks in advance,

    Wednesday, January 6, 2010 8:08 PM


  • Joris,

    You have some great questions in this post and it probably makes sense to break them down individually:

    1.  You refer to the notion of scope a handful of times.  Scope is a new concept we have introduced in SyncFx v2 which has not been made available on devices yet.  That said, we do have a devices release that fits your scenario and more info can be found here: http://www.microsoft.com/downloads/details.aspx?familyid=75FEF59F-1B5E-49BC-A21A-9EF4F34DE6FC&displaylang=en to download the bits and http://msdn.microsoft.com/en-us/library/bb734662(SQL.90).aspx for the docs.

    2.  You are using SQL Express in somewhat of a non-traditional way.  Specifically, you are using it as a server for 50 clients.  We typically look at Express as a client-side store although there are certainly developers that use it as a server as well.  That said, given that you are syncing 50 clients you should think about the limitations of Express (i.e. 1 proc, 4 GB memory, 4 GB database size) as they may adversely affect the performance of sync.

    3.  How you approach major updates is going to be dependent upon the amount of new data that needs to be synced.  If the tables are small-medium sized (i.e. <1000 rows) you could just use table level snapshots which are described here: http://msdn.microsoft.com/en-us/library/bb726043(SQL.90).aspx.  If the amount of new data is large, you may want to just generate a database level snapshot close to the server and expose a web service to stream the file down.  Typically developers will perform full initialization of a new client on their app tier where the network latency is low and then just expose a way to directly stream that .sdf file down.  This improves perf in these major update scenarios significantly as performing full initialization over a high latency network connection can be slow.

    4.  For the remaining tables (download only and upload only multiple times per day) you should initiate sync from the client.  Typically these is performed on a scheduled basis (i.e.) every hour and run as a separate thread in the background.  A download only example can be found here: http://msdn.microsoft.com/en-us/library/bb726021(SQL.90).aspx, upload only example can be found here: http://msdn.microsoft.com/en-us/library/bb726033(SQL.90).aspx and a bi-directional example can be found here: http://msdn.microsoft.com/en-us/library/bb726007(SQL.90).aspx.

    Hope this helps,

    Sean Kelley
    Senior Program Manager
    • Marked as answer by Sean_Kelley Wednesday, January 13, 2010 7:02 AM
    Friday, January 8, 2010 7:44 PM