locked
How to prevent Cleint echoing back to server (Change Tracking, SyncAdapterBuilder, SQL Express Provider) RRS feed

  • Question

  • Hi all,

    I believe I am seeing improper client echo of *previously* synced values (values that came from server during previous sync session).

    How to prevent this?

    I am using a modified version of the SQLExpressSyncProvider example.
    My client is SQL Server 2008 Express and my server is SQL Server 2008 Standard
    I am using native Change Tracking

    I also see the context ID of the Client database change between different sync sessions. Is that normal?

    Here is a trace that demonstrates the issues:

    I start with a "good" sync.
    I make a change on the client (I haven't synced yet).

    1. These are the sync-related values before that first "good" sync:
    (I will be showing the values for the changed row)
    CLIENT
    SELECT CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.aspnet_Users')):
    243
    SELECT * FROM CHANGETABLE(VERSION dbo.aspnet_Users, ([UserId]), ('39F2A224-9EED-4024-BCFC-7C095845A8F1')) CT
    SYS_CHANGE_VERSION      SYS_CHANGE_CONTEXT         UserId
    274                                     NULL                                      39F2A224-9EED-4024-BCFC-7C095845A8F1
    SyncMetaData on Client
    TableName         LastSentAnchor         LastReceivedAnchor
    aspnet_Users     271                             284

    SERVER
    SELECT CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.aspnet_Users'))
    78
    SELECT * FROM CHANGETABLE(VERSION dbo.aspnet_Users, ([UserId]), ('39F2A224-9EED-4024-BCFC-7C095845A8F1')) CT
    SYS_CHANGE_VERSION         SYS_CHANGE_CONTEXT           UserId
    284                                        NULL                                         39F2A224-9EED-4024-BCFC-7C095845A8F1

    2. After the sync:
    CLIENT
    SELECT CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.aspnet_Users')):
    243
    SELECT * FROM CHANGETABLE(VERSION dbo.aspnet_Users, ([UserId]), ('39F2A224-9EED-4024-BCFC-7C095845A8F1')) CT
    SYS_CHANGE_VERSION              SYS_CHANGE_CONTEXT             UserId
    274                                             NULL                                          39F2A224-9EED-4024-BCFC-7C095845A8F1
    SyncMetaData on Client
    TableName         LastSentAnchor           LastReceivedAnchor
    aspnet_Users     274                             285

    SERVER
    SELECT CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.aspnet_Users'))
    78
    SELECT * FROM CHANGETABLE(VERSION dbo.aspnet_Users, ([UserId]), ('39F2A224-9EED-4024-BCFC-7C095845A8F1')) CT
    SYS_CHANGE_VERSION      SYS_CHANGE_CONTEXT                                     UserId
    285                                     0x729705E243E8F64F907FDB64CCA8355F      39F2A224-9EED-4024-BCFC-7C095845A8F1

    3. Another change on the client (not synced yet):
    CLIENT
    SELECT CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.aspnet_Users')):
    243
    SELECT * FROM CHANGETABLE(VERSION dbo.aspnet_Users, ([UserId]), ('39F2A224-9EED-4024-BCFC-7C095845A8F1')) CT
    SYS_CHANGE_VERSION          SYS_CHANGE_CONTEXT                     UserId
    276                                         NULL                                                  39F2A224-9EED-4024-BCFC-7C095845A8F1
    SyncMetaData on Client
    TableName         LastSentAnchor         LastReceivedAnchor
    aspnet_Users     274                           285

    SERVER
    SELECT CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.aspnet_Users'))
    78
    SELECT * FROM CHANGETABLE(VERSION dbo.aspnet_Users, ([UserId]), ('39F2A224-9EED-4024-BCFC-7C095845A8F1')) CT
    SYS_CHANGE_VERSION              SYS_CHANGE_CONTEXT                                       UserId
    285                                             0x729705E243E8F64F907FDB64CCA8355F         39F2A224-9EED-4024-BCFC-7C095845A8F1

    4. Synced (this demonstrates that client changes are being flagged on the server with different client ID - Is that OK because the ID is on ly important within current sync session, to prevent echoing back to client?):
    CLIENT
    SELECT CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.aspnet_Users')):
    243
    SELECT * FROM CHANGETABLE(VERSION dbo.aspnet_Users, ([UserId]), ('39F2A224-9EED-4024-BCFC-7C095845A8F1')) CT
    SYS_CHANGE_VERSION                  SYS_CHANGE_CONTEXT            UserId
    276                                                 NULL                                         39F2A224-9EED-4024-BCFC-7C095845A8F1
    SyncMetaData on Client
    TableName         LastSentAnchor       LastReceivedAnchor
    aspnet_Users     276                         286
    SERVER
    SELECT CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.aspnet_Users'))
    78
    SELECT * FROM CHANGETABLE(VERSION dbo.aspnet_Users, ([UserId]), ('39F2A224-9EED-4024-BCFC-7C095845A8F1')) CT
    SYS_CHANGE_VERSION              SYS_CHANGE_CONTEXT                                     UserId
    286                                             0xEB82F6A7B69E2E44A1B33764B150C6DE      39F2A224-9EED-4024-BCFC-7C095845A8F1

    5. Now I'll show how client echoes server changes, while I think it's not supposed to. From synced state, I make a change on the server. These are the values before they are synced:
    CLIENT
    SELECT CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.aspnet_Users')):
    243
    SELECT * FROM CHANGETABLE(VERSION dbo.aspnet_Users, ([UserId]), ('39F2A224-9EED-4024-BCFC-7C095845A8F1')) CT
    SYS_CHANGE_VERSION                      SYS_CHANGE_CONTEXT                     UserId
    276                                                     NULL                                                  39F2A224-9EED-4024-BCFC-7C095845A8F1
    SyncMetaData on Client
    TableName          LastSentAnchor      LastReceivedAnchor
    aspnet_Users     276                         286
    SERVER
    SELECT CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.aspnet_Users'))
    78
    SELECT * FROM CHANGETABLE(VERSION dbo.aspnet_Users, ([UserId]), ('39F2A224-9EED-4024-BCFC-7C095845A8F1')) CT
    SYS_CHANGE_VERSION                      SYS_CHANGE_CONTEXT                         UserId
    288                                                     NULL                                                      39F2A224-9EED-4024-BCFC-7C095845A8F1
    6. Server change synced to client. Notice how server change was synced to the client but since LastSent was not updated (or the context ID will chage next sync), version 277 will be echoed to the server on next sync, which should not happen:
    CLIENT
    SELECT CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.aspnet_Users')):
    243
    SELECT * FROM CHANGETABLE(VERSION dbo.aspnet_Users, ([UserId]), ('39F2A224-9EED-4024-BCFC-7C095845A8F1')) CT
    SYS_CHANGE_VERSION           SYS_CHANGE_CONTEXT                                         UserId
    277                                         0xEF3C7FB5DDEC0B4CA23F65C00C1EE4C9          39F2A224-9EED-4024-BCFC-7C095845A8F1
    SyncMetaData on Client
    TableName                 LastSentAnchor         LastReceivedAnchor
    aspnet_Users             276                           288
    SERVER
    SELECT CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.aspnet_Users'))
    78
    SELECT * FROM CHANGETABLE(VERSION dbo.aspnet_Users, ([UserId]), ('39F2A224-9EED-4024-BCFC-7C095845A8F1')) CT
    SYS_CHANGE_VERSION         SYS_CHANGE_CONTEXT             UserId
    288                                       NULL                                           39F2A224-9EED-4024-BCFC-7C095845A8F1
    7. I will make a change on the server ONLY - these are the data before the sync and I am setup for a conflict, because the client will try to echo previous server change:
    CLIENT
    SELECT CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.aspnet_Users')):
    243
    SELECT * FROM CHANGETABLE(VERSION dbo.aspnet_Users, ([UserId]), ('39F2A224-9EED-4024-BCFC-7C095845A8F1')) CT
    SYS_CHANGE_VERSION           SYS_CHANGE_CONTEXT                                     UserId
    277                                          0xEF3C7FB5DDEC0B4CA23F65C00C1EE4C9     39F2A224-9EED-4024-BCFC-7C095845A8F1
    SyncMetaData on Client
    TableName              LastSentAnchor          LastReceivedAnchor
    aspnet_Users         276                             288
    SERVER
    SELECT CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.aspnet_Users'))
    78
    SELECT * FROM CHANGETABLE(VERSION dbo.aspnet_Users, ([UserId]), ('39F2A224-9EED-4024-BCFC-7C095845A8F1')) CT
    SYS_CHANGE_VERSION              SYS_CHANGE_CONTEXT             UserId
    290                                             NULL                                          39F2A224-9EED-4024-BCFC-7C095845A8F1
    8. I execute the sync and I get a conflict
    I get ServerApplyChangeFailed with Conflict=ClientUpdateServerUpdate, because the client echoed back version 277. This is client's SELECT changes query, captured with SQL Profiler. Notice that if LastSentAnchor was updated to 277 or server's Identificator was consistent, this wouldn't have happened:
    exec sp_executesql N'
    IF @sync_initialized > 0
    BEGIN 
        SELECT <...all columns...>
        FROM dbo.aspnet_Users 
            JOIN CHANGETABLE(CHANGES dbo.aspnet_Users, @sync_last_received_anchor) CT 
                ON CT.[UserId] = dbo.aspnet_Users.[UserId] 
        WHERE (CT.SYS_CHANGE_OPERATION = ''U'' 
            AND CT.SYS_CHANGE_VERSION <= @sync_new_received_anchor 
            AND (CT.SYS_CHANGE_CONTEXT IS NULL 
                    OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary))
    ; IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N''dbo.aspnet_Users'')) > @sync_last_received_anchor 
        RAISERROR (N''SQL Server Change Tracking has cleaned up tracking information for table ''''%s''''. 
                        To recover from this error, the client must reinitialize its local database and try again''
                        ,16,3,N''dbo.aspnet_Users'')
    END '
    ,N'@sync_initialized bit,@sync_last_received_anchor bigint,@sync_new_received_anchor bigint,@sync_client_id_binary varbinary(16)'
    ,@sync_initialized=1
    ,@sync_last_received_anchor=276
    ,@sync_new_received_anchor=277
    ,@sync_client_id_binary=0x1366C58313A12B4F86AF725AD2029FEB
    • Moved by Hengzhe Li Friday, April 22, 2011 5:31 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Wednesday, February 25, 2009 4:24 PM

Answers

  • The client id should always be the same.  Did you make any modification to the SQL Express Sync Provider?

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by G.Stoynev Thursday, February 26, 2009 4:26 PM
    Wednesday, February 25, 2009 6:36 PM
    Answerer
  •  

    Thank you for pointing me to the Client ID issue. There was a problem that was causing a different Client ID to be issued on every sync.
    Correcting it records persistent Client ID context, which prevents subsequent echoing of previously synced records.

    • Marked as answer by G.Stoynev Thursday, February 26, 2009 4:27 PM
    Thursday, February 26, 2009 4:26 PM

All replies

  • The client id should always be the same.  Did you make any modification to the SQL Express Sync Provider?

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by G.Stoynev Thursday, February 26, 2009 4:26 PM
    Wednesday, February 25, 2009 6:36 PM
    Answerer
  • My I see your command for Update command to the Server and Update command to hte Client (Express)?

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, February 25, 2009 6:47 PM
    Answerer
  • Extracted from the Adapter, before the sync:
     CLIENT Update Command:
    ";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary)
    UPDATE dbo.aspnet_Users
    SET [ApplicationId] = @ApplicationId
        , [UserName] = @UserName, [LoweredUserName] = @LoweredUserName, [MobileAlias] = @MobileAlias, [IsAnonymous] = @IsAnonymous
        , [LastActivityDate] = @LastActivityDate, [FirstName] = @FirstName, [LastName] = @LastName, [HealthGroup] = @HealthGroup
        , [PhoneNumber] = @PhoneNumber, [CellNumber] = @CellNumber, [PagerNumber] = @PagerNumber
    FROM dbo.aspnet_Users  
        JOIN CHANGETABLE(VERSION dbo.aspnet_Users, ([UserId]), (@UserId)) CT  
            ON CT.[UserId] = dbo.aspnet_Users.[UserId]
    WHERE (@sync_force_write = 1 
                OR CT.SYS_CHANGE_VERSION IS NULL 
                OR CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor 
                OR (CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary))
    SET @sync_row_count = @@rowcount
    ; IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.aspnet_Users')) > @sync_last_received_anchor 
        RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. 
                        To recover from this error, the client must reinitialize its local database and try again'
                        ,16,3,N'dbo.aspnet_Users') "

    SERVER Update Command:
    ";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary)
    UPDATE dbo.aspnet_Users
    SET [ApplicationId] = @ApplicationId, [UserName] = @UserName, [LoweredUserName] = @LoweredUserName, [MobileAlias] = @MobileAlias
        , [IsAnonymous] = @IsAnonymous, [LastActivityDate] = @LastActivityDate, [FirstName] = @FirstName, [LastName] = @LastName
        , [HealthGroup] = @HealthGroup, [PhoneNumber] = @PhoneNumber, [CellNumber] = @CellNumber, [PagerNumber] = @PagerNumber
    FROM dbo.aspnet_Users 
        JOIN CHANGETABLE(VERSION dbo.aspnet_Users, ([UserId]), (@UserId)) CT  
            ON CT.[UserId] = dbo.aspnet_Users.[UserId]
    WHERE (@sync_force_write = 1 
                OR CT.SYS_CHANGE_VERSION IS NULL 
                OR CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor 
                OR (CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary))
    SET @sync_row_count = @@rowcount;
    IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.aspnet_Users')) > @sync_last_received_anchor 
        RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. 
                            To recover from this error, the client must reinitialize its local database and try again'
                            ,16,3,N'dbo.aspnet_Users') "
    Thursday, February 26, 2009 3:06 PM
  •  

    Thank you for pointing me to the Client ID issue. There was a problem that was causing a different Client ID to be issued on every sync.
    Correcting it records persistent Client ID context, which prevents subsequent echoing of previously synced records.

    • Marked as answer by G.Stoynev Thursday, February 26, 2009 4:27 PM
    Thursday, February 26, 2009 4:26 PM