locked
Client must be reinitialized - Change Tracking RRS feed

  • Question

  • What is involved in reinitializing client's local database when using SQL Server Change Tracking and SyncServices for ADO.NET

    Server: Sql Server 2008 Standard using the out-of-the-box DbServerSyncProvider
    Client: SQL Server 2008 Express using custom SqlExpressClientSyncProvider (modified SQL Express Example to use Change Tracking)

    I tried disabling change tracking for all tables, then for the database, then re-enabling it, but I still get the built-in 
    SQL Server Change Tracking has cleaned up tracking information for table '[MyTable]'. To recover from this error, the client must reinitialize its local database and try again

    Friday, February 13, 2009 9:38 PM

Answers

  • Is SQL server change tracking a definite requirement in this environment? 
    a. If yes, then I would recommend setting the retention cleanup period on the client side (Express server) way longer then the one setup on the server side.
    b. If no, then I would recommend using the "traditional in-table tracking" as exposed in the sample for the client table (Express server) tracking.

    If you like to consider for the latest sync technology, please try the Peer-to-Peer Provider.  Please see "Collaboration Scenarios (Sync Services)" of Sync Services for ADO.NET 2.0, http://msdn.microsoft.com/en-us/library/bb726002.aspx.

    Please also refer to posting, from Sean Kelley, http://social.microsoft.com/Forums/en/uklaunch2007ado.net/thread/0072d8f3-2876-4e9c-9677-98d0fe28e774.

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Yunwen Bai Tuesday, October 6, 2009 12:46 AM
    Tuesday, September 29, 2009 6:44 PM
    Answerer

All replies

  • Anybody, please

    How to "reinitialize" the client?
    Tuesday, February 17, 2009 3:06 PM
  • Here is some more details about the issue.

    Hub-and-spoke bidirectional synchronization between SQL 2008 client and servers using native Change Tracking, where the client provider is a modified version of the SqlExpressSyncProvider example, but with SqlSyncAdapterBuilder and Change Tracking and the improvements mentioned in the example's thread.

    Synchronization seems to be working fine in a test environment with two (parent-child) tables.

    After certain amount of clean-cut sync sessions, I  decided to simulate a conflict. Due to the setting Action.Continue, the conflict went undetected. After a few more these I handled ApplyChangeFailed events, setting the action to error and abort synchronization until the conflict is resolved. Then I needed to reset the client (and the server?) and that's when I started thinking about a way to manage such occurrences in a life, production environment.

    Deleting the anchor metadata on the Client doesn't do any good with Change Tracking enabled (which meanst that it's probably not a good idea with custom tracking as well), because the built-in check kicks in:

    IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'[myTable]')) > @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'[myTable]')

    What exactly did Microsoft mean by the implied solution: "To recover from this error, the client must reinitialize its local database and try again". I could not find any guidance on *reinitialyzing* the client.

    I tried disabling and re-enabling Change Tracking on the client (all tables and the database), but that didn't reset internal change tracing data, as far as I can tell.

    I had the idea of modifying the internal change tracking data (like min_valid_version), but that data are not readily accessible and I'd like to get a sanity check on that, before I go ahead.

    I don't rule out that I am on the wrong tangent or that the imaginary situation is very unlikely and it has nothing to do with sync and Change Tracking (completely up to developers to re-sync the databases before normal synchronization continues).

    Thank you.


    Wednesday, February 18, 2009 9:35 PM
  • What is the value returned from CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'[myTable]') on the SQL 2008 Server (the Server Side)?

    Thanks.

    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Sunday, February 22, 2009 5:36 PM
    Answerer
  • I have exactly same problem:
    And can be easy simulate.
    So for example do on server for a table that has enabled Change Tracking some modifications then do Syn with Client then enable/disable ChnageTracking on clinet and on Server.So after those modifications we  assuming that on DB Retantion Days is 2 days after 2 days if u test you can have following data on Server DB result of query:

    select * from sys.change_tracking_tables

    object_Id              is_track_Columns_update_on             min_valid_Version                 begin_version            cleanup_version
    1307203757                     1                                                103                                      103                               102


    On client DB for same query I have this:
    object_Id              is_track_Columns_update_on             min_valid_Version                 begin_version            cleanup_version
    997578592                          1                                                  3                                     3                                  0

    When I try to Sync I have the nice error:SQL Server Change Tracking has cleaned up tracking information for table '[MyTable]'. To recover from this error, the client must reinitialize its local database and try again

    so question: how can I change min_valid_Version for client???? if this is possible then it solve my/our problem isn't it? If not how can we solve this. We are stuck now with this problem in the middle of a demo project and is quite urgent for us!
    thx!

    Cristi


    Cristi
    Monday, March 30, 2009 2:33 PM
  • G.Stoynev -

    This is a little off the topic but would you be willing to share your sample with the integrated change tracking? I know there are many users out there looking for a SQL Express 2008 to SQL 2008 Standard sync provider with the integrated change tracking feature.

    Thanks
    Friday, April 3, 2009 11:50 PM
  • pfew, another strange situation....
    I created again both Databases on client and on server and then, I delete and recreate tables on Client a few times and after that I have following situation:
    on server DB the query:

    select * from sys.change_tracking_tables
    
    

    return this:

    object_id         is_track_columns_updated_on        min_valid_version    begin_version     cleanup_version
    133575514  1  14  14  13
    357576312  1  14  14  13
    2105058535  1  13  13  13
    2137058649  1  13  13  13
    On client DB same query return this:
    object_id         is_track_columns_updated_on        min_valid_version    begin_version     cleanup_version
    1330103779 1  23  23  18
    1394104007 1  23  23  18
    1458104235  1  23  23  18
    1522104463  1  23  23 18

    Now gues what happen when I Sync first time, because version on Client is bigger then on server it tries to Insert back all records on Server DB because System things is newer...
    So how this can be solved???

    Is really no way to reset those min_valid_version to be the same like on Server DB when Client DB is configured first time????



    Cristi
    Tuesday, April 7, 2009 7:40 AM
  • I will consider posting our code, but I am not able to do it right now.
    Saturday, April 18, 2009 3:19 PM
  • Please can someone post code example of ClientSyncProvider implementation which work with sql 2008 change tracking?
    Tuesday, May 12, 2009 4:55 PM
  • In case you haven't already discovered it the version numbers for the client and server are independant of one another, in the anchor table you track what (client) version you have sent up until and what (server) version you have received up until.

    That said, because the SqlExpressClientSyncProvider is internally using a DbServerSyncProvider it needs to swap those anchors around before doing any calls (since it's tricking it into thinking it's the server, so the real server's anchors get swapped into the internal providers "client" anchors and vice-versa). Unfortunately that anchor swapping hasn't been done in the ApplyChanges method, my teams updated ApplyChanges method (with swapping method we refactored out) is as follows:
            public override SyncContext ApplyChanges(
                SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession )
            {
                SwapAnchors( groupMetadata );
    
                //_log.Debug( "ApplyChanges" );
                //Map SyncDirection from client POV to our internal server POV
                foreach( SyncTableMetadata tableMetadata in groupMetadata.TablesMetadata )
                {
                    if( tableMetadata.SyncDirection == SyncDirection.DownloadOnly ||
                        tableMetadata.SyncDirection == SyncDirection.Snapshot )
                    {
                        //This SyncDirection DownloadOnly/Snapshot is from a Client point of view. But our client is inturn a Server provider.   Hence switch this to UploadOnly
                        tableMetadata.SyncDirection = SyncDirection.UploadOnly;
                    }
                    else if( tableMetadata.SyncDirection == SyncDirection.UploadOnly )
                    {
                        //This SyncDirection UploadOnly is from Client POV. But our client is inturn a Server provider. Hence switch this to DownloadOnly
                        tableMetadata.SyncDirection = SyncDirection.DownloadOnly;
                    }
                }
                SyncContext syncContext = _dbSyncProvider.ApplyChanges( groupMetadata, dataSet, syncSession );
    
                SwapAnchors( groupMetadata );
                
                foreach ( SyncTableMetadata table in groupMetadata.TablesMetadata )
                    SetTableReceivedAnchor( table.TableName, groupMetadata.NewAnchor );
    
                return syncContext;
            }
    
            private static void SwapAnchors( SyncGroupMetadata groupMetadata )
            {
                // neet to set the LastReceivedAnchor as the LastSentAnchor since 
                // DbServerSyncProvider operates from the server's perspective, so
                // we swap the two fields temporarily. 
                foreach ( SyncTableMetadata metaTable in groupMetadata.TablesMetadata )
                {
                    SyncAnchor temp = metaTable.LastReceivedAnchor;
                    metaTable.LastReceivedAnchor = metaTable.LastSentAnchor;
                    metaTable.LastSentAnchor = temp;
                }
            }
    Once that bug is sored out (which will prevent all sorts of bizarre sync issues like the one described) your real issue is then your anchors. If your anchor table in your Express database says you're up to version 0 but the server has a min-valid-version higher than that then you will get the exception. Take a look at your Anchor table in your client DB, what values do you have there?

    Hope this helps!
    Thursday, May 14, 2009 4:49 AM
  • yes.. I know about swaping anchors... but my problem is implementation of GetTableReceivedAnchor and GetTableSentAnchor... Do I need to store these values in anchor table or  values are provided by sql 2008 itself (change tracking)? Can you post complete client provider code?

    thanx
    Thursday, May 14, 2009 7:52 AM
  • I'm working in the same way.

    First i have found a lot of problems with change tracking in the two databases. Until now i can syncronize new rows between the databases but i can't perform mods or deletes.

    For Salk: until i know, anchor table is needed but i don't know if is posible without this table. I'm using it for now.

    If someone  can post complete client provider code....

    Thanks
    Desarrollador de software
    Tuesday, June 30, 2009 9:50 AM
  • I'm not sure if you have an answer for your question. But I thought I'd add my two cents.

    I have developed a solution very similar to yours.  I did two things to Avoid the problem:

    1. I set the retension for 21 days.  This should be our worste case senerio.
    2. I strip out the code that checks for the initialized code.  I use the BuildSyncAdapter function to build the queries for syncing.

      'removes SQL code to check if the Change Tracking info is valid (generated bogus results if used)
        Private Shared Function stripMinValidVersion(ByVal CommandString As String) As String
            Try
                Dim NewCommandString As String
    
                If CommandString Is Nothing Then Return Nothing
                'Debug.Print("0:" & CommandString)
                Const START_TAG As String = "IF CHANGE_TRACKING_MIN_VALID_VERSION"
                Dim startPos As Integer
                Try
                    startPos = CommandString.IndexOf(START_TAG)
                Catch
                    'discovered problem with IndexOf() "invalid property" ????
                    startPos = -1
                End Try
                If startPos > 0 Then
                    If CommandString.Contains(" END ") Then
                        NewCommandString = CommandString.Substring(0, startPos - 1) & " END "
                        'Debug.Print("1:" & NewCommandString)
                        Return NewCommandString
                    Else
                        NewCommandString = CommandString.Substring(0, startPos - 1)
                        'Debug.Print("2:" & NewCommandString)
                        Return NewCommandString
                    End If
                Else
                    ' Debug.Print("3:" & CommandString)
                    Return CommandString
                End If
            Catch ex As Exception
                xException.Handle(ex, SbcException.Policies.WarningContinue)
                'Debug.Print("Exception: " & ex.Message)
                Return CommandString
            End Try
        End Function
    
        'cleanup sync adapaters use: stripMinValidVersion on each command
        Private Shared Function fixSyncAdapter(ByVal SyncAdapter As SyncAdapter, ByVal Timeout As Integer) As SyncAdapter
            Try
                'Debug.Print("T:" & SyncAdapter.TableName)
                If SyncAdapter Is Nothing Then Return Nothing
                
                If SyncAdapter.InsertCommand IsNot Nothing Then
                    SyncAdapter.InsertCommand.CommandText = stripMinValidVersion(SyncAdapter.InsertCommand.CommandText)
                    SyncAdapter.InsertCommand.CommandTimeout = Timeout
                End If
                If SyncAdapter.SelectConflictDeletedRowsCommand IsNot Nothing Then
                    SyncAdapter.SelectConflictDeletedRowsCommand.CommandText = stripMinValidVersion(SyncAdapter.SelectConflictDeletedRowsCommand.CommandText)
                    SyncAdapter.SelectConflictDeletedRowsCommand.CommandTimeout = Timeout
                End If
                If SyncAdapter.SelectConflictUpdatedRowsCommand IsNot Nothing Then
                    SyncAdapter.SelectConflictUpdatedRowsCommand.CommandText = stripMinValidVersion(SyncAdapter.SelectConflictUpdatedRowsCommand.CommandText)
                    SyncAdapter.SelectConflictUpdatedRowsCommand.CommandTimeout = Timeout
                End If
                If SyncAdapter.SelectIncrementalDeletesCommand IsNot Nothing Then
                    SyncAdapter.SelectIncrementalDeletesCommand.CommandText = stripMinValidVersion(SyncAdapter.SelectIncrementalDeletesCommand.CommandText)
                    SyncAdapter.SelectIncrementalDeletesCommand.CommandTimeout = Timeout
                End If
                If SyncAdapter.SelectIncrementalInsertsCommand IsNot Nothing Then
                    SyncAdapter.SelectIncrementalInsertsCommand.CommandText = stripMinValidVersion(SyncAdapter.SelectIncrementalInsertsCommand.CommandText)
                    SyncAdapter.SelectIncrementalInsertsCommand.CommandTimeout = Timeout
                End If
                If SyncAdapter.SelectIncrementalUpdatesCommand IsNot Nothing Then
                    SyncAdapter.SelectIncrementalUpdatesCommand.CommandText = stripMinValidVersion(SyncAdapter.SelectIncrementalUpdatesCommand.CommandText)
                    SyncAdapter.SelectIncrementalUpdatesCommand.CommandTimeout = Timeout
                End If
                If SyncAdapter.UpdateCommand IsNot Nothing Then
                    SyncAdapter.UpdateCommand.CommandText = stripMinValidVersion(SyncAdapter.UpdateCommand.CommandText)
                    SyncAdapter.UpdateCommand.CommandTimeout = Timeout
                End If
                Return SyncAdapter
            Catch ex As Exception
                xException.Handle(ex, SbcException.Policies.WarningContinue)
                Return SyncAdapter
            End Try
        End Function
    
        'create a sync adapter based on the source database
        Public Shared Function BuildSyncTableAdapter(ByVal SqlConnection As SqlConnection, ByVal TableName As String, ByVal SyncDirection As Microsoft.Synchronization.Data.SyncDirection, ByVal FilterClause As String, ByVal Timeout As Integer) As SyncAdapter
            Dim SqlSyncAdapterBuilder As SqlSyncAdapterBuilder = New SqlSyncAdapterBuilder()
            Dim TimeoutInSeconds = 60
            If Timeout > 0 Then
                TimeoutInSeconds = Timeout * 60
            End If
            With SqlSyncAdapterBuilder
                .TableName = TableName
                .Connection = SqlConnection
                .ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking
                .SyncDirection = SyncDirection
                .FilterClause = FilterClause
                'Debug.Print(TableName & ":" & SyncDirection.ToString & ":" & FilterClause)
            End With
            Try
                Return fixSyncAdapter(SqlSyncAdapterBuilder.ToSyncAdapter(), TimeoutInSeconds, isPushed)
            Catch ex As Exception
                'automatically add to change tracking
                If ex.Message.Contains("SQL Server Change Tracking is not enabled for table") Then
                    Dim SqlCommand As New SqlCommand("ALTER TABLE " & TableName & " ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED=OFF)", SqlConnection)
                    If SqlConnection.State = ConnectionState.Closed Then
                        SqlConnection.Open()
                        SqlCommand.ExecuteNonQuery()
                        SqlConnection.Close()
                    Else
                        SqlCommand.ExecuteNonQuery()
                    End If
                    Return fixSyncAdapter(SqlSyncAdapterBuilder.ToSyncAdapter(), TimeoutInSeconds)
                End If
            End Try
            Return fixSyncAdapter(SqlSyncAdapterBuilder.ToSyncAdapter(), TimeoutInSeconds)
        End Function
    I am open to other solutions.

    Roger
    Monday, September 28, 2009 10:46 PM
  • Is SQL server change tracking a definite requirement in this environment? 
    a. If yes, then I would recommend setting the retention cleanup period on the client side (Express server) way longer then the one setup on the server side.
    b. If no, then I would recommend using the "traditional in-table tracking" as exposed in the sample for the client table (Express server) tracking.

    If you like to consider for the latest sync technology, please try the Peer-to-Peer Provider.  Please see "Collaboration Scenarios (Sync Services)" of Sync Services for ADO.NET 2.0, http://msdn.microsoft.com/en-us/library/bb726002.aspx.

    Please also refer to posting, from Sean Kelley, http://social.microsoft.com/Forums/en/uklaunch2007ado.net/thread/0072d8f3-2876-4e9c-9677-98d0fe28e774.

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Yunwen Bai Tuesday, October 6, 2009 12:46 AM
    Tuesday, September 29, 2009 6:44 PM
    Answerer
  • Lots of Questions!

    I have been experimenting with code  brought from the Code Gallery, but have faced several issues. First, I am using Windows 7 and the x64 bit version of the Microsoft.Synchronization.*, since at the beginning I had mixed different versions. 

     

     

    Now, I started the prototype using the Project from SqlExpress Synchronization. My goal is to have a sync working between two full sql server 2008 databases, one acting as a client and the other as a server.

     

    http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=1200

     

    Although it compiles it doesn’t work as expected. I have found in several forums that this sample needs to be tweaked to actually work.

     

    1.      1.  It  comes with reversed logic, because the SqlExpress is a server by itself, and the library is designed for clients.

     

    2.       2. How to build the Anchors table is not described anywhere. Should I build it by myself or is it available as soon as I enable change tracking in SqlServer 2008? I have SqlServer in both sides, the server and the client.

     

    Since I didn’t find it I created the table similar to the following:

     

    Table Anchors

    TableName string

    SentArchive datetime

    ReceivedArchive datetime

     

    3. 3.       Then a conversion from datetime to byte[] would throw an error. I had to recreate the table with the Sent and Received Archive as varbinary(64) and then it stoped giving this error.

     

    4. 4.      Now, to get data from the server to the client, with the Download only option: I started by having an empty table in the client. Although the function GetChanges receives a dataset with the table and the correct number of rows, it does not actually insert any row in the table, but does not report any error. If I immediately try to sync again, the dataset would report zero rows, as if nothing was needed to be brought. This is correct, but I still got an empty table in the client.

     

    5.    5.   I then tried the snapshot option, which didn’t seem to work either. In fact, it seems that the only option that does something is the Bidirectional.  With this option, I  finally got the client to write the received record in the table, but then another problem appeared. Although it receives the records and actually inserts these record in the client table, it reports ApplyFailedChanges, but the records are there in the table.  (I learned it is because of the nice error ' SQL Server Change Tracking has cleaned up tracking information for table '[MyTable]'. To recover from this error, the client must reinitialize its local database and try again ' In this case, if I try to sync again, it seems it will try to transmit all of those client records back to the server. Since there are more than 1k records, I am getting a Bad request (400) from the server. I haven’t found a way to solve this either. I guess that i if didnt fail at WCF than the DB would complain in the server.

     

     

    So, at this point, after long hours, I am giving up. I want to avoid by all means having to make custom logic. I would appreciate if you can point me to how to solve these issues, mainly: How to sync the database for the first time (snapshot), and hot to sync it from there on, bidirectionally.

     

    Note: Already applied code patches found in this forum to re-reverse the logic to be client-server, instead server-server. Did not apply yet the patch for the ignoring of the "SqlServer has cleaned.." yet...

     

     

    Thanks a lot

     



    C# Devlpr, new to Biztalk 2006 R2.
    Friday, October 16, 2009 9:53 PM
  • Regarding the original question of this thread, I came across the same problem and solved it as follows:

    1. Drop client database
    2. Recreate database
    3. enable change tracking
    4. create table
    4. enable change tracking for the table

    It may work with just

    disable change tracking for table
    drop table
    recreate table
    enable change tracking.

    but I have not tested this latter procedure.

    Good luck.


    C# Devlpr, new to Biztalk 2006 R2.
    Friday, October 23, 2009 6:27 PM
  • 2. How to build the Anchors table is not described anywhere. Should I build it by myself or is it available as soon as I enable change tracking in SqlServer 2008? I have SqlServer in both sides, the server and the client.

    I'm stuck here too !!!!

    did you find an answer ?
    Thursday, January 7, 2010 12:31 PM
  • Sunday, January 10, 2010 11:00 AM