locked
Batching in Synchronization Services RRS feed

  • Question

  • Hi,

    I have a question referring to the article Programming Microsoft Synchronization Services for ADO.NET (Devices) (http://msdn.microsoft.com/en-us/library/dd938879.aspx )

    The method i was using is the following:

                this.SelectNewAnchorCommand = new System.Data.SqlClient.
    SqlCommand();
                this.SelectNewAnchorCommand.CommandText = "Select @sync_new_received_anchor = CHANGE_TRACKING_CURRENT_VERSION()";
                this.SelectNewAnchorCommand.CommandType = System.Data.CommandType.Text;
    System.Data.SqlClient.SqlParameter selectnewanchorcommand_sync_new_received_anchorParameter = new System.Data.SqlClient.SqlParameter("@sync_new_received_anchor", System.Data.SqlDbType.BigInt);
                selectnewanchorcommand_sync_new_received_anchorParameter.Direction = System.Data.ParameterDirection.Output;
                this.SelectNewAnchorCommand.Parameters.Add(selectnewanchorcommand_sync_new_received_anchorParameter);

    And was working fine except for big synchronizations.

    Then, i found your article and tried to implement batching.
    During the initial synchronization the process keeps looping the GetChanges for each table until i cancel the process.
    After i start the synchronization it continues to the next table.

    Is there a way i can do this without having to apply the snapshot database suggested in the article?
    Why if i'm not using batch i can initialize the database and when using batching i get this strange loop?

    Thanks for you time.
    Wednesday, August 11, 2010 1:36 PM

Answers

  • As an work around, plesae dummy update each row on individual transaction on the server side and see if batching works better.

    Enumerate changes using SQL Server Change Tracking depends on the server transaction number.  But we could have multiple changes commited on a single transaction, more or less.  Therefore, changes may not evenly distributed per transaction.  So on each batch, you may get very different changes in terms of rows.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, September 2, 2010 5:24 PM
    Answerer

All replies

  • would you mind posting the query you used for the getting the new batch anchor?

    Wednesday, August 18, 2010 1:58 AM
  • Sure JuneT, thanks for taking the time to reply my post.

    I'm using the stored procedure from the article referenced in my post  :

    ALTER PROCEDURE [dbo].[usp_GetNewBatchAnchor] (
       @sync_last_received_anchor bigint,
       @sync_batch_size int,
       @sync_max_received_anchor bigint out,
       @sync_new_received_anchor bigint out,      
       @sync_batch_count int output)      
    AS      
        -- Set a default batch size if a valid one is not passed in.
        IF @sync_batch_size IS NULL OR @sync_batch_size <= 0
          SET @sync_batch_size = 1000  
    
         -- Before selecting the first batch of changes,
         -- set the maximum anchor value for this synchronization      
         -- session. After the first time that this procedure is
         -- called, Synchronization Services passes a value for
         -- @sync_max_received_anchor to the procedure. Batches of
         -- changes are synchronized until this value is reached.
        IF @sync_max_received_anchor IS NULL
         SELECT @sync_max_received_anchor =
              change_tracking_current_version()
       
        -- If this is the first synchronization session for a database,
        -- get the lowest change version value from the tables. By
        -- default, Synchronization Services uses a value of 0 for
        -- sync_last_received_anchor on the first synchronization. If    
        -- you do not set @sync_last_received_anchor,
        -- this can cause empty batches to be downloaded until the
        -- lowest change version value is reached.
        IF @sync_last_received_anchor IS NULL OR
           @sync_last_received_anchor = 0
        BEGIN
          SELECT @sync_last_received_anchor = 0
    
        -- Changes are only retained in the change table for a limited
        -- period of time set by the CHANGE_RETENTION parameter
        -- (on ALTER DATABASE).
        -- Check that we haven't had changes cleaned up on this table
        -- (i.e. CHANGE_TRACKING_MIN_VALID_VERSION returns > 0)    
    
    
          SET @sync_new_received_anchor =
            @sync_last_received_anchor + @sync_batch_size
    
          -- Determine how many batches are required during the
          -- initial synchronization.
          IF @sync_batch_count <= 0
           SET @sync_batch_count = (
             (@sync_max_received_anchor / @sync_batch_size) -
             (@sync_last_received_anchor / @sync_batch_size)
           )
        END
       ELSE
        BEGIN
         SET @sync_new_received_anchor =
          @sync_last_received_anchor + @sync_batch_size
    
         -- Determine how many batches are required during subsequent
         -- synchronizations.
          IF @sync_batch_count <= 0
           SET @sync_batch_count = (
             (@sync_max_received_anchor / @sync_batch_size) -
             (@sync_new_received_anchor / @sync_batch_size)) + 1
     
        END
    
       -- Check whether this is the last batch.   
       IF @sync_new_received_anchor >= @sync_max_received_anchor
       BEGIN
        SET @sync_new_received_anchor = @sync_max_received_anchor    
        IF @sync_batch_count <= 0
         SET @sync_batch_count = 1
       END
    

     

     

    Thursday, August 19, 2010 8:27 AM
  • what's the batch size you specified, how many rows are there and what's the configured change retention for Change Tracking?
    Thursday, August 19, 2010 8:55 AM
  • Sorry for the delay but for some reason I'm not being alerted when i get an answer on this post.

     

    batch is 1000

    rows on the server side depends on the table being synchronize. It goes from 100s to some 1000s

    retention period is 2 days

    My problem is during the initial synchronization . The  process keeps looping the GetChanges for each table until i cancel it.

     

    Thanks for your help.

     

    Monday, August 30, 2010 1:16 PM
  • you might want to run Sync Fx tracing or Sql Profiler and check if the looping you are observing are actually empty batches.
    Monday, August 30, 2010 2:37 PM
  • As an work around, plesae dummy update each row on individual transaction on the server side and see if batching works better.

    Enumerate changes using SQL Server Change Tracking depends on the server transaction number.  But we could have multiple changes commited on a single transaction, more or less.  Therefore, changes may not evenly distributed per transaction.  So on each batch, you may get very different changes in terms of rows.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, September 2, 2010 5:24 PM
    Answerer