Need Advice on ADO.NET Sync Sevices Architecture RRS feed

  • Question

  • I have the following project needs and am looking at ADO.NET sync services as a possible solution and am hoping someone in the know can shed some insights as to whether sync serices is a good fit for my project and if there are any recommended or suggested aritectures or approaches that I should look at.

    My main project needs are as follows:

    1. I have a central SQL Server (called the Configuration Server) that includes several tables and sets of rows that are considered configuration *versions*. Think of these as a package of data rows that provide settings for a complex web application.

    2. Specific configuration versions need to be ported or synched to multiple other peer SQL Server databases.

    3. The synching will not necessarily be triggered via changes and standard change tracking, but rather by version number/identifier, i.e. if I am applying or synching a peer database with a specific configuration version - then all records of the configuration associated with that version must be synched to the peer database.

    4. The synching is one-way from the configuration server to the peer databases. Any peer may require any configuration version to be installed at any time.


    One potential solution that I was investigating is to have companion tables on the config server that are identical copies of the tables that contain my config data rows with an additional field to store a version number. I could then easily sync any peer with a desired configuration by touching/updating the timestamps of all rows for a specific config version, and synching with the peer databases. This seems simple and straightforward and I'm pretty sure this will work with little difficulty.

    However, it has been requested that I store the configuration records in the configuration database in XML format. So I am looking for an architecture/app framework that would support synching peer SQL Server databases from an XML data source. After reading the 'Architecture and Classes for Client and Server Synchronization' article, it looks as though the Service-Based Architecture could be a possible fit to support XML as a datasource of my config data. I'm hoping someone can provide some insights as to whether this is a good or bad idea to pursue further. I'm thinking that my XML configurations will not necessarily have timestamps updated that can be used with change tracking to enable the synching. In this case, I would really want to drive the synching by comparing a version number that could possibly be stored in a tracking table. If the version being pushed out differs from what's currently installed on the peer, then all config records would be synched.

    Looking at the diagram and descriptions for the service-based arhitecture, it looks complicated, and I don't want to waste time proving out an idea if it's a bad match for my project needs from the onset. Can anyone provide some insights as to whether this approach seems logical/doable and if there are any sample apps out there that do peer-to-peer synching with an XML data source on the server side synching a SQL Server peer? Also if anyone has any additional approaches that I might want to investigate that coiuld support synching from an XML data source, I'd appreciate hearing any and all ideas. I'm also interested in hearing from anyone who may have working with an application where synching of data rows was triggered by something other than timestamps, such as a version in my case.



    Tuesday, May 18, 2010 10:52 PM


  • Hi,

    In order to sync a XML storage, you need to write a custom sync provider (http://msdn.microsoft.com/en-us/library/bb902847(v=SQL.105).aspx) for it. Since your XML storage may not have timestamp for each record for change detection, you can either directly use Simple Custom Provider (http://msdn.microsoft.com/en-us/library/dd937537(v=SQL.105).aspx) or write a Standard Custom Provider by storing some record hash values in the metadata store for change detection.

    For records of different configuration versions, you may want to put them into different sync scope. When you need to sync records of a particular configuration version to a peer store, you can only sync this scope with the peer. Your current approach of touching timestamp for all records may not be a good solution because it more like a copy. With the sync scope, peer store will not get records for a given configuration version if it already has synced them before, only incremental changes will be synced.

    For peer SQL Server database, you may need to write a custom sync provider to match the one for the XML storage. The builtin database provider in Sync Framework may not be a good option for you before we release a sample for how to write match simple custom provider for the builtin database provider.

    There are multiple MSDN samples for how to write custom sync provider: http://code.msdn.microsoft.com/sync.





    This posting is provided AS IS with no warranties, and confers no rights.
    Wednesday, May 19, 2010 1:01 AM