none
BIZARRE sync behavior in peer-to-peer sync using DbSyncProviders RRS feed

  • Question

  • I have put together a 1-way (download) peer-to-peer sync app between 2 SQL Server databases that uses custom DbSyncProviders and custom adapters. The app is straightforard with one exception - I am passing a custom parameter to my UpdateMetaData command that represents a type of versioning; the param is guid that I use basically as a signature to detect "version differences" between peers.

     My tracking tabl looks like:

     CREATE TABLE Sync.Customer_Tracking

    (

        CustomerId uniqueidentifier NOT NULL PRIMARY KEY   

        , update_scope_local_id int NULL

        , scope_update_peer_key int

        , scope_update_peer_timestamp bigint

        , local_update_peer_key int

        , local_update_peer_timestamp timestamp

        , create_scope_local_id int NULL

        , scope_create_peer_key int

        , scope_create_peer_timestamp bigint

        , local_create_peer_key int

        , local_create_peer_timestamp bigint

        , sync_row_is_tombstone int

        , restore_timestamp bigint

        , last_change_datetime datetime default NULL

        , [signature] uniqueidentifier

    )

     

     My UpdateMetaData stored proc is straightforward and simpy updates my tracking table normally with the exception of also updating my [Signature] field which is also on the tracking table on the source peer.

    MY MAIN CONFUSION is HOW the value of [Signature] gets passed to this stored proc? It works and the [Signature] value gets updated in the tracking table on my destination peer when records are synched from the source peer. I am *not* passing the value via my app code; the call to the proc is coded in my custom adapter like:

    adapterCmd = new SqlCommand();

    adapterCmd.CommandType = CommandType.StoredProcedure;

    adapterCmd.CommandText = "Sync.Customer_insertmetadata";

    adapterCmd.Parameters.Add( "@CustomerId", SqlDbType.BigInt );

    adapterCmd.Parameters.Add( "@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int );

    adapterCmd.Parameters.Add( "@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int );

    adapterCmd.Parameters.Add( "@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int );

    adapterCmd.Parameters.Add( "@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt );

    adapterCmd.Parameters.Add( "@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int );

    adapterCmd.Parameters.Add( "@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt );

    adapterCmd.Parameters.Add( "@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int );

    adapterCmd.Parameters.Add( "@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt );

    adapterCmd.Parameters.Add( "@signature", SqlDbType.UniqueIdentifier, 16 );

    adapterCmd.Parameters.Add( "@" + DbSyncSession.SyncRowCount, SqlDbType.Int ).Direction = ParameterDirection.Output;

    In the SelectChanges command on my source peer, I do include in my Select list the [Signature] value from the source tracking table, although I can't seem to confirm that the value comes from here when synched on the destination peer. Can someone tell me precisely how the value for a *custom* parameter to an UpdateMetaData command is passed? Although the sample I set up works, I'm not sure how the value is being passed to my custom [signature] paramter, and that makes me feel a little uneasy about my sopution.

     

    Now this is where things get really wierd - in my sample app used above, my base table uses a uniqueidentifier field as the PK on the table. IF I CHANGE this field to be a BigInt and an *IDENTITY* field, then the updating of my [signature] field in my UpdateMetaData proc on my destination peer fails and for some odd reason I can't explain, my @signature parameter is now passed in a NULL value. What was working in the previous example now fails by passing NULL for my [Signature] parameter when I move to using an IDENTITY field as my PK field. In my Insert comand, I turn IDENTITY_INSERT ON and OFF like:

    SET IDENTITY_INSERT [dbo].[Customer] ON

     

    INSERT INTO Customer

    (

          CustomerId

          , CustomerName

          , SalesPerson

          , CustomerType

    )

    VALUES

    (

          @CustomerId

          , @CustomerName

          , @SalesPerson

          , @CustomerType

    )

     

    SET IDENTITY_INSERT [dbo].[Customer] OFF

    And now the @Signature param to the UpdateMetaData proc on my destination peer is always passed a value of NULL.

    Can anyone shed light on this bizarre behavior? What I basically need to know is:

    1. How are custom parameters passed values to the UpdateMetaData command/proc?  Funny thing is that I pass a value (in my app code) to a filter parameter to my SelectChanges proc on my Source peer and it is passed the value fine. If I pass a value for my UpdateMetaData parm on my destination peer in exactly the same way via app code, it consistently is passed NULL and seemingly ignore the value I set. Very wierd and highly confusing.

    2. Are there any caveats or wierdness that is introduced to the sync process simply by having an IDENTITY field as a PK on the base table, requiring setting IDENTITY_INSERT on and off for inserts? This seems to be the only change in my app that breaks everything and I can't explain it.

     

    Someone please help me understand how this works. It is *very* unintuitive and inconsistent to say the least.

     

    Thanks,

    Glenn

     

    Tuesday, June 22, 2010 9:34 PM