locked
Need Help in setting up custom DBSyncProvider that uses Change Tracking mechanism OTHER THAN TIMESTAMPS RRS feed

  • Question

  • I'm hoping someone on the Sync team can help me to understand exactly how the sync orchestration works so that I can get a sync app working that does NOT use the traditional timestamps as the mechanism to detect "changes" to sync from one SQL Server peer to another ...

    I have created a peer-to-peer sync app that successfully syncs 2 SQL Servers using the change tracking stored procs and supporting code as is generated by the Sql Server simple provider. I created my custom app using custom DBSyncProviders and custom adapters and just plugged in the SQL that was generated by the simple provider app using the same tables. Everything works fine.

    NOW - I have changed the code to try and get to the point where I am really heading - which is to eliminate change tracking BASED ON TIMESTAMPS. I basically am trying to get the sync process working simply by detecting if a Source version field is different than the corresponding field on the Destination peer. I am using Uniqueidentifier columns to represent my "versions".

    Without enumerating all of my stored procs here (hoping that won't be necessary), in the SelectIncrementalChanges Command, I simple select all records for a specific Version. I have created some audit tables that I write into into from the change tracking triggers and the sync stored procs for my adapter commands, and I verified when I call my sync() method, the SelectIncrementalChanges command does indeed fire and select the rows I want. I DOESN'T, however, insert the rows on the destination peer. My Insert Command does exactly what the generated stored proc does for the simple provider app - it simply inserts the new record if it doesn't already exist in the tracking table for the base table.

     

    MY REAL QUESTION IS --> what exactly happens within the sync orchestration that communicates to the sync agent that the records that exist on my Source peer and to be inserted on my Destination peer?

     

    There is obviously something I am missing here. My SelectIncrementalChanges command basically selects all records on my Source and the Insert stored proc on the Destination peer simply inserts the record if not already n the tracking table (which it isn't). Why doesn't it sync these new records?

     

    I delete and recreate my scope record before each test run; I delete all of the base table data and tracking table data on my Source peer. But when I test, it just won't sync. I did get an error about not having the 'sync_update_peer_key' missing from the SelectIncrementalChangesCommand's result set.   My guess is that this has something to do with why the records are not inserted on my Destination peer.

    I tried adding these columns to the result set, such as:

     

    declare @ts timestamp
    set @ts = @@DBTS+1
       
    SELECT
        base.CustomerID
        , base.CustomerName
        , base.SalesPerson
        , base.CustomerType
       
        , 0 as sync_row_is_tombstone
        , @ts as sync_row_timestamp
        , @ts as sync_update_peer_timestamp
        , null as sync_update_peer_key
        , @ts as sync_create_peer_timestamp
        , null as sync_create_peer_key

     

    I also tried returning all NULLs as well for these fields, but I'm guessing the vakues are important and are being compared somewhere, but I don't know the logic. I admit I am very confused by all fo the timestamps and key columns and haven't taken the time to fully understand it since my goal is to use a version key instead of timestamps.

     

    Can someone please help me figure out what logic is actually happening within the sync orchestration to actually apply the new inserts and updates based on the result sets from the commands (or stored procs)?  I need to know how it works (what is compared) so that I can successfully compare row versions instead of timestamps to determine if a row should be synched with my peer.

     

    Thanks,

    Glenn

     

    ps - any timely help GREATLY appreciated ... I am getting desperate!

     

     

    Monday, June 14, 2010 10:27 PM

All replies

  • you may want to check out Enumerating Changes in the documentation or visit http://msdn.microsoft.com/en-us/library/bb902832(v=SQL.100).aspx

    The SelectIncrementalChangesCommand simply enumerates what has changed since the last sync between the parties but the results is not necessarily sent to the client. Instead, this is compared to the destination's "knowledge" if it already knows about these changes as it may have received the same changes already from another peer.

    e.g., A and B syncs row X1 from C

    C updates X1

    A syncs with C and gets the updated X1

    B syncs with A and gets the updated X1 of C via A

    B syncs with C and X1 is detected as changed since A and C last synched, but B already knows about the change since it got it already from A so the change is not sent to B

    also, afaik, the peer keys identifies the peer that created/updated the row.

    hth

     

    Tuesday, June 15, 2010 4:06 PM