none
SQL Server peer-to-peer synchronize between table and data stored in XML RRS feed

  • Question

  • Does anyone know how to set up a SQL Server peer-to-peer synchronization with an XML data source on the source end of the sync and normal SQL Server table(s) as the destination end? I may look into using the newer XML column data type, but we have older code with XML stored as VARCHAR field types that I would need to use OPENXML, etc to access the data records that I would want ti sync with peer table(s) in the destination SQL Server peer.

     

    If anyone knows how to set up such a scenario, I would appreciate some advice or pointers to samples, code, etc. that would show how to accomplish this.

     

    Thanks,

    Glenn

    Monday, June 7, 2010 3:30 PM

Answers

  • If both ends can be Microsoft SQL Server, you could even use SqlSyncProvider instead of DbSyncProvider on both endpoints for simplicity. In that case, all the table adapters will be built for you by the Sync framework code. For the XML data, as long as it's in the synced table('s VARCHAR column), it will be available on the SQL Server after sync.

    More information on SqlSyncProvider can be found at http://msdn.microsoft.com/en-us/library/dd918848(v=SQL.105).aspx and complete samples can be found at C:\Program Files\Microsoft SDKs\Microsoft Sync Framework\2.0\Samples\SharingAppDemo-CEProviderEndToEnd after you install Sync framework 2.0.

    Monday, June 7, 2010 7:22 PM
    Answerer

All replies

  • We don't have a separate XML sync provider out of box.

    For your scenario, I think it's still helpful to use normal database sync provider. You can sync the whole XML data stored in the VARCHAR column to/from the server and extract/Construct the data you need from the XML data at either end (especially the SQL Server end). Basically, use your application to transform the data a little bit instead of depending the Sync framework to do that, either with T-SQL or .Net code.

    Monday, June 7, 2010 6:23 PM
    Answerer
  • That sounds fine, but what kind of adapter would I use in that scenario? I have been looking at the DBSyncAdapter and testing a little with a sample SQL Server peer-to-peer app that syncs tables on each end, but it looks like the DBSyncAdapter binds only to SQL tables. How would I construct an adapter or what type of adapter should I use to work against the XML stored in a varchar table column?

     

    Glenn

     

    Monday, June 7, 2010 7:07 PM
  • If both ends can be Microsoft SQL Server, you could even use SqlSyncProvider instead of DbSyncProvider on both endpoints for simplicity. In that case, all the table adapters will be built for you by the Sync framework code. For the XML data, as long as it's in the synced table('s VARCHAR column), it will be available on the SQL Server after sync.

    More information on SqlSyncProvider can be found at http://msdn.microsoft.com/en-us/library/dd918848(v=SQL.105).aspx and complete samples can be found at C:\Program Files\Microsoft SDKs\Microsoft Sync Framework\2.0\Samples\SharingAppDemo-CEProviderEndToEnd after you install Sync framework 2.0.

    Monday, June 7, 2010 7:22 PM
    Answerer
  • I don't think you're getting what it is I am needing to do .. let me try to explain a little better:

     

    I need to sync 2 SQL Server databases with a 1-way sync, 1 configuration server that will sync many peer databases

    The config server that will do the synching has the data that needs to be synched stored in an XML field of a table

    The XML field might look like the following (simplified example):

    <data>

      <roles>

        <role [attributes] >[role elements]</role>

      </roles>

      <permissions>

        ...

      </permissions>

    </data>

     

    The roles, permissions, etc. entities stored in the XML on the config (synching) server map to tables that exist on the peer servers

    that are to be synched. This is not a table-to-table synch. It's XML data (containing many row entities for many tables) on the synching

    endpoint that needs to sync the XML data against tables and rows that are on the receiving endpoint of the sync process.

     

    I'm not seeing how DBSyncProviders with DBSyncAdapters can handle this scenario. It seems that the DBSyncProvider should work,

    but possibly with a custom adapter of some sort that would communicate with the XML stored in a field on the synching endpoint. DBSyncAdapter

    specifically wants to bind to a specific database table, which makes it look like an unsuitable adapter type for my need. Also I definitely don't

    want to use SqlSyncAdapter as I must have control over the Sql due to special change tracking needs that differ from typical timestamp-

    oriented change tracking.

     

    I'm sure this can be done since the framework doc touts being able to sync anything to anything. Here I want to sync 2 SQL Server endpoints,

    however the data storage on both ends differs vastly from table-to-table.

     

    Does it makes sense what I am trying to do? 

     

    Glenn

     

    Monday, June 7, 2010 9:29 PM
  • It just occurred to me that maybe you did understand what I was doing ...

     

    If I have tableA in the synching endpoint (I have a 1-way synch scenario) and Table1, Table2 and Table3 on the peers to be synched, and my

    XML stored in TableA contains the row updates for Table1, Table2 and Table3 in the peer databases -- are you saying that I could create a custom

    provider with 3 separate sync adapters as:

     

    TableA (XML field) --> Table1

    TableA (XML field) --> Table2

    TableA (XML field) --> Table3

     

    and within the custon SQL for each adapter, I coulkd tailor the OPENXML() to extract just the recs I need for each matching table on the

    peer endpoint and sync no differently than with a table-to-table sync (other than the custom command SQL)?

     

    This sounds like it would work. If this is what you were describing, I'm not sure I understand how SqlSyncProviders could be used to do

    this. The simple provider is only for straight table-to-table syncs, isn't it? How would SqlSyncProvider be used to "extract" from the XML

    only the records to be synched for a specific table on the peer endpoint? It generates all the code and adapters for you just based on

    the selected tables, doesn't it? At least that's what it seems to be doing in the sample/demo apps I played with.

     

    Glenn

     

    Monday, June 7, 2010 9:46 PM
  • I can understand your scenario now. I am afraid either DbSyncProvider or SqlSyncProvider is not designed for this type of scenarios. They are built for a model to do database table to table sync without much tranforming of the data when doing the Sync.

    If you want to sync between a XML store (even it may be stored in a table column) to SQL database tables, you will need to build a sync provider to handle this, either standard sync provider or simple sync provider. Information can be found http://msdn.microsoft.com/en-us/library/bb902844(v=SQL.105).aspx and other related links. Some samples for standard sync provider or Simple sync provider can be found in the Sync 101 section at http://code.msdn.microsoft.com/sync 

    You could also take a look at this post for the similar idea:

    http://social.microsoft.com/Forums/en-US/syncdevdiscussions/thread/4a7584d0-c5c7-4b6b-b1c5-e23f4d5776fd

     

     

    Tuesday, June 8, 2010 5:24 PM
    Answerer