Database design for sync services RRS feed

  • Question

  • I'm just beginning to think about the design of an occasionally connected application using sync services, and I've got a couple of basic questions about how you guys imagine a database will be designed in this scenario:

    1. Do you normally have a common schema between the server database and the offline client?

    2. Can you use a common data access layer for server and client apps?

    My first thought was that it would be nice to share the schema and DAL code between the two, but the more I read the more I wonder whether I'm thinking about this the wrong way.

    I'd be very grateful if you could offer some advice!


    • Moved by Max Wang_1983 Friday, April 22, 2011 8:33 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Sunday, March 2, 2008 10:36 AM

All replies

  • Hi Jon,


    those are very generic design issues and could var from case to case. for the schema issue, the sync service was designed to let the sync service application to build in the logic to handle such situations. so what would be your most typical scenarios ? will they requires schema difference between client and the server ?


    for sharing DAL, are you using Ntier configuration or 2 tier configuration ? technically, there is nothing blocks you from using the same DAL to access the server db. but you probably will consider things such as accessibility, security, identiry user etc to make your decisions.






    Sunday, March 2, 2008 6:17 PM
  • Hi Yunwen,

    They are fairly generic questions, but I wondered if there were general patterns that people follow.

    Up till know I've been planning a web app with a data access strategy based on an existing DAL-generator I have, which creates stored procedures and a data class for each table. These data classes are part of an n-tier architecture.

    The reason I'm rethinking the design is that I'm hoping to build an occasionally connected client at some point in the future, and I was hoping to re-use as much as possible of the code.

    I'd like to use SQL CE as the client's offline data store, which obviously rules out views and SPs. I have no experience of building a data access layer without views or SPs, but if I need to build one for the offline client then I wondered if the best practice is to use the same DAL for both online and occasionally connected apps?

    The scenarios I can see are probably pretty standard:
    - users creating/modifying/deleting records in a limited number of tables (20?) and also requiring reference data in perhaps a further 10 tables.
    - users only cache their own records locally, so a small proportion of the overall data volume.
    - updates can be made online or offline, synchronised when re-connected.

    Apart from the additional timestamp cols, tombstone tables etc required for sync, I'd prefer to keep the schemas the same, but is this the best idea?

    Regarding the DAL, my generator uses datasets. As I understand it, I could use datasets with SQL CE, but SqlCeResultSets may be a better bet? If the DAL is going to be different for this reason then maybe the server should have a more standard view and SP based DAL?

    I'm guessing these are the same question all designers ask when they first think about a 2-way sync services app but it isn't immediately obvious from what I've read (Steve Lasker, Rafik Robeal) what the recommended way forward is.

    Thanks for your help!


    Sunday, March 2, 2008 8:59 PM