Coupled Versus DeCoupled - Peer to Peer Sync RRS feed

  • Question

  • I'm curious what other people have found performance-wise for the different change tracking models. I don't know if it's better to have on-demand metadata in a decoupled change tracking table to keep the storage requirements down and improve performance, or if I should work with coupled change tracking.


    There are around 130 tables each in the SQL Server 2005/Express databases.


    I have set up a sync client to work with my database based on the decoupled peer to peer examples, syncing bidirectionally.


    Here is how I have strayed away from the demo so far:


    1. I use a single change-tracking table rather than an individual one for each table.

    2. I use a CLR assembly for my insert, update, delete triggers that records an additional column for the table name

    (still investigating if this is reliable)

    Code Snippet

    SqlCommand cmd = new SqlCommand("Select object_name(resource_associated_entity_id) " +

      " From sys.dm_tran_locks " +

      " Where request_session_id = @@spid " +

      " And resource_type = 'OBJECT'"

      , conn);


    3. I modified the change-tracking t-sql to check for an existing tracking entry to determine update/delete so that metadata does not need to be pre-existing.



    I really like the peer to peer sync. It seems to be much more elegant and seems easier to implement.



    ------ (Edit)


    I know this is unrelated to sync, but when changes are replicated, it will correctly find the table name for the first insert, then it will see the lock on the tracking table and insert my tracking table name instead. I just need to add some sql to the insert commands to update the table name after the insert fires.

    • Moved by Max Wang_1983 Friday, April 22, 2011 5:53 PM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    • Moved by Max Wang_1983 Friday, April 22, 2011 5:53 PM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, August 29, 2008 12:57 PM

All replies


    It's starting to look somewhat unrealistic to accomplish what I want to accomplish with decoupled metadata. Time to experiment with coupled changed tracking.


    If anyone has any ideas to make it work where there is no pre-existing metadata for a record using a single tracking table, let me know. I'm seeing that there doesn't seem to be a good way to handle the update versus insert, but maybe once I get it working successfully in a coupled schema, I might get some more ideas.

    Friday, August 29, 2008 4:38 PM
  • it would be hard to use signle tracking table for mutiple user tables that will be in syncrhonization. that will need a complex logic and will have big performance impact for both the synchronzation and the data change on the database.




    Friday, September 5, 2008 7:50 AM
  • What I was hoping to do was improve performance and reduce storage by only storing metadata for updates at the time they happen - basically forgetting about the change once it has propagated to the server or all peers, but I can see that doing it that way could become a nightmare for metadata cleanup to be able to maintain performance and storage goals.

    I have everything running now using coupled change-tracking and a single tombstone table. I'm still experimenting with the quickest way to introduce a new peer.

    As always, thanks for following up.
    Friday, September 5, 2008 10:38 AM