Sync Services: Implementing Batching to Reduce Data Transfer Volumes

Synchronization Services supports a limited batching capability which performs multiple transfers of the changed data in smaller batches from server to client, rather than all the changed rows in one go. In order to understand how batching works, you need to understand how SQL Server 2008 change tracking tracks changes, and how Synchronization Services uses that information.

How Synchronization Services Uses Change Tracking Version Numbers

When you enable change tracking on a database server, SQL Server 2008 starts to maintain a change version number which increments from zero. Also it maintains a special internal ‘changetable’ for each table for which you have enabled change tracking. Whenever a row in the table is updated, a record is written to the internal changetable containing the primary key of the changed record and the associated change version number.

When a client synchronizes, the server returns to the client the current value of its server-wide change version number, which is available by calling the SQL Server 2008 CHANGE_TRACKING_CURRENT_VERSION function. You define the T-SQL command to use in your application code by setting the SelectNewAnchorCommand property of a key object in the server side code of your sync solution, a Microsoft.Synchronization.Data.Server.DbServerSyncProvider instance. The standard way of defining this, and similar to the code generated by the OCS Designer, is to write code in the DbServerSyncProvider class initialization logic as follows:

  // selectNewAnchorCmd command.
  SqlCommand selectNewAnchorCommand = new SqlCommand();
  selectNewAnchorCommand = new System.Data.SqlClient.SqlCommand();
  selectNewAnchorCommand.CommandText =
"Select @sync_new_received_anchor = CHANGE_TRACKING_CURRENT_VERSION()";
  selectNewAnchorCommand.CommandType = System.Data.CommandType.Text;
  System.Data.SqlClient.SqlParameter selectnewanchorParameter =
    new System.Data.SqlClient.SqlParameter(
          "@sync_new_received_anchor",
          System.Data.SqlDbType.BigInt);

  selectnewanchorParameter.Direction =
    System.Data.ParameterDirection.Output;
  selectNewAnchorCommand.Parameters.Add(selectnewanchorParameter);

  // Set to the DbServerSyncProvider
  this.SelectNewAnchorCommand = selectNewAnchorCommand;

The client also sends to the server the change tracking version it received from the server the previous time it synchronized (or 0 if this is the first synchronization), so now all the server side code has to do is select all changed rows with a change version greater than the change tracking version the client received at the previous synchronization, and less than or equal to the databases current change version number. This actual logic required is more complex than this, as I have not described how the logic to track inserts, updates and deletions separately is implemented, nor how the ID of the client that made the last update is tracked, but I have simplified this so that it is easier to understand the general process. Refer to SQL Server 2008 documentation for full details.

How to Implement Batching In Synchronization Services

The batch mechanism in Synchronization Services allows you to limit the range of change versions to be sent to the client in each response to a client synchronization request. For example, if the client previously synchronized to version 50, the servers’ change tracking version is currently 120, and you define a batch size of 30, then the changed rows will be sent in three separate batches: 51-80 in the first transfer, 81-110 in the second and 111-120 in the third.

One way of enabling this behavior is to define a stored procedure such as the following. This procedure takes five parameters which are required by Synchronization Services:

·        @sync_last_received_anchor which is the change version associated with the clients’ previous synchronization

·        @sync_batch_size which is the requested range of change version numbers requested for each batch

·        @sync_max_received_anchor which is an ‘out’ parameter which returns the current change version on the server

·        @sync_new_received_anchor which returns the actual maximum change version number of the set of records selected for the current batch

·        @sync_batch_count which returns the number of batches that are required to complete the synchronization

The logic of the stored procedure is described in the comments included in the following script:

USE [Northwind]
GO

/**** Object:  StoredProcedure [dbo].[usp_GetNewBatchAnchor] ****/
CREATE 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)       
            IF CHANGE_TRACKING_MIN_VALID_VERSION(
               object_id(N'dbo.Customers')) >
                   @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.Customers')

            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

GO

In order to activate batch support, create the stored procedure in the database, and then modify the SelectNewAnchorCommand property of the Microsoft.Synchronization.Data.Server.DbServerSyncProvider instance to call it, specifying the requested batch size:

       SqlCommand selectNewAnchorCommand = new SqlCommand();
    selectNewAnchorCommand.CommandText = "usp_GetNewBatchAnchor";
    selectNewAnchorCommand.CommandType = CommandType.StoredProcedure;
    selectNewAnchorCommand.Parameters.Add("@" +
      SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt, 8);
    selectNewAnchorCommand.Parameters.Add("@" +
      SyncSession.SyncMaxReceivedAnchor, SqlDbType.BigInt, 8);
    selectNewAnchorCommand.Parameters.Add("@" +
      SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt, 8);
    selectNewAnchorCommand.Parameters.Add("@" +
      SyncSession.SyncBatchSize, SqlDbType.Int, 4);
    selectNewAnchorCommand.Parameters.Add("@" +
      SyncSession.SyncBatchCount, SqlDbType.Int, 4);
    selectNewAnchorCommand.Parameters["@" +
      SyncSession.SyncMaxReceivedAnchor].Direction =
        ParameterDirection.Output;
    selectNewAnchorCommand.Parameters["@" +
      SyncSession.SyncNewReceivedAnchor].Direction =
        ParameterDirection.Output;
    selectNewAnchorCommand.Parameters["@" +
      SyncSession.SyncBatchCount].Direction =
        ParameterDirection.InputOutput;

    // Set to the DbServerSyncProvider
    this.SelectNewAnchorCommand = selectNewAnchorCommand;
    // Set the requested batch size
    this.BatchSize = 10;

Batching for Client Incremental Changes

There is no support in Synchronization Services for sending changes from a client up to the server in smaller batches. You should ensure that your client code synchronizes with the server sufficiently often that the volume of changes cached on the client does not grow to a large number. Large numbers of changes cached on the client could lead to out of memory exceptions when attempting to serialize the DataSet to be sent to the server, and to difficulties in transmitting large amounts of data over slow connections such as cell data networks.

Why Batching May Not Work After Enabling SQL Server 2008 Change Tracking on Populated Tables

There is one particular problem that may occur with batching. If you enable change tracking on a database table before any data is inserted in that table, then as each row is added to the source table, the change will be recorded in the internal changetable and will be assigned a new, unique change version number. In this case, batching will be able to limit the number of rows that are sent to the client on each communication.

However, if you enable change tracking on a table that already has many rows of data, then the initial state of the table is recorded by adding records to the changetable for all rows with the same initial change version number. As a result, even with batching implemented, when a client synchronizes for the first time, the Synchronization Services server-side code will attempt to return all the rows in the requested table.

In this circumstance, you must initialize the client in a different way, perhaps by creating a desktop app to perform the initial sync and then deploy the resulting database to the device.