Export (0) Print
Expand All

How to: Specify the Order and Batch Size of Changes

This topic describes how to specify the order and batch size of changes that are downloaded to a client database by using Sync Framework. The examples in this topic focus on the following Sync Framework types and events:

For information about how to run sample code, see "Example Applications in the How to Topics" in Programming Common Client and Server Synchronization Tasks.

Understanding Synchronization Order and Batching

For each table that is synchronized, changes are selected from the server database in the order of inserts, updates, and then deletes. Changes are applied to the client database in the order of deletes, inserts, and then updates. When several tables are synchronized, the order in which each table is processed depends on the order in which its SyncTable object was added to the collection of tables for the synchronization agent. For example, if Customer and OrderHeader tables are added in that order, Customer table inserts are selected first, followed by updates and deletes. Then the changes to the OrderHeader table are selected. All Customer table changes are applied to the client database in a single transaction (if batching is not used), followed by OrderHeader changes that are applied in a second transaction. If the Customer and OrderHeader tables are assigned to the same SyncGroup object, the inserts, updates and deletes for both tables are selected once. All changes are applied to the client database in a single transaction (again, if batching is not used).

By default, Sync Framework does not divide changes into batches. Changes are downloaded to and uploaded from the client database as a unit. For many applications, it makes sense to divide changes into smaller batches. For example, if a synchronization session is interrupted, synchronization can restart from the last batch instead of resending all the changes. There can also be a performance benefit, because the client can manage a smaller batch of changes at a time. Because of these advantages, Sync Framework enables applications to download batches of changes to the client (batching is not supported on upload).

Batching is enabled by specifying a value for the BatchSize property, and creating a command for the SelectNewAnchorCommand property that can return anchor values for each batch of changes. Without batching, applications use new anchor and last anchor values to define the upper and lower bounds for the whole set of changes to download. For more information, see Getting Started: Client and Server Synchronization. With batching, the maximum received anchor value defines the upper bound for the whole set of changes, and the new anchor value and last anchor value define the upper and lower bounds for each batch of changes. The SessionProgress event provides a convenient way to monitor overall synchronization progress, and the BatchProgress property provides access to progress information at the batch level.

Example

The following code examples show how to synchronize the Customer and OrderHeader tables in the Sync Framework sample database. The changes for these tables are downloaded in batches, with 50 changes per batch. The initial synchronization downloads 10 rows. All rows are downloaded in a single batch and applied in a single transaction. The subsequent synchronization downloads 92 rows in two batches. Each batch contains changes from the Customer table and the OrderHeader table, and each batch is applied in a single transaction.

Key Parts of the API

This section provides code examples that point out the key parts of the API to use when you are ordering and batching changes. The following code example is from a class that derives from SyncAgent. The code creates a SyncGroup object for the Customer and OrderHeader tables.

//Create a SyncGroup so that changes to Customer
//and OrderHeader are made in one transaction.
SyncGroup customerOrderSyncGroup = new SyncGroup("CustomerOrder");

//Add each table: specify a synchronization direction of
//DownloadOnly.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
customerSyncTable.SyncGroup = customerOrderSyncGroup;
this.Configuration.SyncTables.Add(customerSyncTable);

SyncTable orderHeaderSyncTable = new SyncTable("OrderHeader");
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly;
orderHeaderSyncTable.SyncGroup = customerOrderSyncGroup;
this.Configuration.SyncTables.Add(orderHeaderSyncTable);

The following code example is from a class that derives from DbServerSyncProvider. The code creates an anchor command for an application that does not use batching. It returns a new anchor value once for the whole set of changes to be synchronized. This example is included to make it easy to see the difference between a command that uses batching and one that does not.

SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;

The following code example creates an anchor command that can be used if changes are delivered in batches. Instead of returning a new anchor value once for the whole set of changes, it returns a new anchor value for each batch of changes. It uses the BatchSize property to specify how many changes should be in each batch, and session variables to pass anchor values back and forth between a stored procedure and the synchronization runtime. If you write synchronization adapter commands manually, you still use the @sync_new_received_anchor and @sync_last_received_anchor session variables; the @sync_max_received_anchor session variable is used only by the new anchor command.

SqlCommand selectNewAnchorCommand = new SqlCommand();
selectNewAnchorCommand.Connection = serverConn;
selectNewAnchorCommand.CommandText = "usp_GetNewBatchAnchor";
selectNewAnchorCommand.CommandType = CommandType.StoredProcedure;            
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp, 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;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
this.BatchSize = 50;

The following code example creates a stored procedure that outputs new anchor values, a maximum anchor value, and the current batch count for inserts and updates. The procedure enables the server synchronization provider to select batches of changes from the server database. The logic in this stored procedure is an example, but any logic can be used as long as it provides the output values shown here. One shortcoming of the example code is that empty batches can occur if a row is changed more than 50 times between synchronizations. You could add logic to handle this case.

CREATE PROCEDURE usp_GetNewBatchAnchor (
	@sync_last_received_anchor timestamp, 
	@sync_batch_size bigint,
	@sync_max_received_anchor timestamp out,
	@sync_new_received_anchor timestamp 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, 
	   -- Sync Framework 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 = MIN_ACTIVE_ROWVERSION() - 1
       
       -- If this is the first synchronization session for a database,
       -- get the lowest timestamp value from the tables. By default,
       -- Sync Framework 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
       -- timestamp value is reached.
       IF @sync_last_received_anchor IS NULL OR @sync_last_received_anchor = 0
       BEGIN
                
		SELECT @sync_last_received_anchor = MIN(TimestampCol) FROM (
		  SELECT MIN(UpdateTimestamp) AS TimestampCol FROM Sales.Customer
		  UNION
		  SELECT MIN(InsertTimestamp) AS TimestampCol FROM Sales.Customer
		  UNION
		  SELECT MIN(UpdateTimestamp) AS TimestampCol FROM Sales.OrderHeader
		  UNION
		  SELECT MIN(InsertTimestamp) AS TimestampCol FROM Sales.OrderHeader
		) MinTimestamp	
       
		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

Complete Code Example

The following complete code example includes the code examples that are described earlier and additional code to perform synchronization. The example requires the Utility class that is available in Utility Class for Database Provider How-to Topics.

using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
using Microsoft.Synchronization.Data.SqlServerCe;

namespace Microsoft.Samples.Synchronization
{
    class Program
    {
        static void Main(string[] args)
        {

            //The SampleStats class handles information from the 
            //SyncStatistics object that the Synchronize method returns and
            //from SyncAgent events.
            SampleStats sampleStats = new SampleStats();

            //Request a password for the client database, and delete
            //and re-create the database. The client synchronization
            //provider also enables you to create the client database 
            //if it does not exist.
            Utility.SetPassword_SqlCeClientSync();
            Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, true);
            
            //Initial synchronization. Instantiate the SyncAgent
            //and call Synchronize.
            SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
            SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
            sampleStats.DisplayStats(syncStatistics, "initial");

            //Make changes on the server.
            Utility.MakeDataChangesOnServer("CustomerAndOrderHeader");

            //Subsequent synchronization.
            syncStatistics = sampleSyncAgent.Synchronize();
            sampleStats.DisplayStats(syncStatistics, "subsequent");

            //Return server data back to its original state.
            Utility.CleanUpServer();

            //Exit.
            Console.Write("\nPress Enter to close the window.");
            Console.ReadLine();
        }
    }

    //Create a class that is derived from 
    //Microsoft.Synchronization.SyncAgent.
    public class SampleSyncAgent : SyncAgent
    {
        public SampleSyncAgent()
        {
            //Instantiate a client synchronization provider and specify it
            //as the local provider for this synchronization agent.
            this.LocalProvider = new SampleClientSyncProvider();

            //Instantiate a server synchronization provider and specify it
            //as the remote provider for this synchronization agent.
            this.RemoteProvider = new SampleServerSyncProvider();

            //Create a SyncGroup so that changes to Customer
            //and OrderHeader are made in one transaction.
            SyncGroup customerOrderSyncGroup = new SyncGroup("CustomerOrder");

            //Add each table: specify a synchronization direction of
            //DownloadOnly.
            SyncTable customerSyncTable = new SyncTable("Customer");
            customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
            customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
            customerSyncTable.SyncGroup = customerOrderSyncGroup;
            this.Configuration.SyncTables.Add(customerSyncTable);

            SyncTable orderHeaderSyncTable = new SyncTable("OrderHeader");
            orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
            orderHeaderSyncTable.SyncDirection = SyncDirection.DownloadOnly;
            orderHeaderSyncTable.SyncGroup = customerOrderSyncGroup;
            this.Configuration.SyncTables.Add(orderHeaderSyncTable);
        }
    }

    //Create a class that is derived from 
    //Microsoft.Synchronization.Server.DbServerSyncProvider.
    public class SampleServerSyncProvider : DbServerSyncProvider
    {
        public SampleServerSyncProvider()
        {
            //Create a connection to the sample server database.
            Utility util = new Utility();
            SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync);
            this.Connection = serverConn;

            //Create a command to retrieve a new anchor value from
            //the server. In this case, we call a stored procedure
            //that returns an anchor that can be used with batches
            //of changes.
            SqlCommand selectNewAnchorCommand = new SqlCommand();
            selectNewAnchorCommand.Connection = serverConn;
            selectNewAnchorCommand.CommandText = "usp_GetNewBatchAnchor";
            selectNewAnchorCommand.CommandType = CommandType.StoredProcedure;            
            selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp, 8);
            selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp, 8);
            selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp, 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;
            this.SelectNewAnchorCommand = selectNewAnchorCommand;
            this.BatchSize = 50;
            
            //Create SyncAdapters for each table by using the SqlSyncAdapterBuilder:
            //  * Specify the base table and tombstone table names.
            //  * Specify the columns that are used to track when
            //    and where changes are made.
            //  * Specify download only synchronization.
            //  * Call ToSyncAdapter to create the SyncAdapter.
            //  * Specify a name for the SyncAdapter that matches the
            //    the name specified for the corresponding SyncTable.
            //    Do not include the schema names (Sales in this case).

            //Customer table
            SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);

            customerBuilder.TableName = "Sales.Customer";
            customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
            customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
            customerBuilder.CreationTrackingColumn = "InsertTimestamp";
            customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
            customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
            
            SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
            customerSyncAdapter.TableName = "Customer";
            this.SyncAdapters.Add(customerSyncAdapter);


            //OrderHeader table.
            SqlSyncAdapterBuilder orderHeaderBuilder = new SqlSyncAdapterBuilder(serverConn);

            orderHeaderBuilder.TableName = "Sales.OrderHeader";
            orderHeaderBuilder.TombstoneTableName = orderHeaderBuilder.TableName + "_Tombstone";
            orderHeaderBuilder.SyncDirection = SyncDirection.DownloadOnly;
            orderHeaderBuilder.CreationTrackingColumn = "InsertTimestamp";
            orderHeaderBuilder.UpdateTrackingColumn = "UpdateTimestamp";
            orderHeaderBuilder.DeletionTrackingColumn = "DeleteTimestamp";

            SyncAdapter orderHeaderSyncAdapter = orderHeaderBuilder.ToSyncAdapter();
            orderHeaderSyncAdapter.TableName = "OrderHeader";
            this.SyncAdapters.Add(orderHeaderSyncAdapter);

            //Handle the ChangesSelected event, and display
            //information to the console.
            this.ChangesSelected += new EventHandler<ChangesSelectedEventArgs>(SampleServerSyncProvider_ChangesSelected);
        }

        public void SampleServerSyncProvider_ChangesSelected(object sender, ChangesSelectedEventArgs e)
        {
            Console.WriteLine("Total number of batches: " + e.Context.BatchCount);
            Console.WriteLine("Changes applied for group " + e.GroupMetadata.GroupName);
            Console.WriteLine("Inserts applied for group: " + e.Context.GroupProgress.TotalInserts.ToString());
            Console.WriteLine("Updates applied for group: " + e.Context.GroupProgress.TotalUpdates.ToString());
            Console.WriteLine("Deletes applied for group: " + e.Context.GroupProgress.TotalDeletes.ToString());
        }
    }

    //Create a class that is derived from 
    //Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
    public class SampleClientSyncProvider : SqlCeClientSyncProvider
    {

        public SampleClientSyncProvider()
        {
            //Specify a connection string for the sample client database.
            Utility util = new Utility();
            this.ConnectionString = Utility.ConnStr_SqlCeClientSync;         
        }
    }

    //Handle the statistics that are returned by the SyncAgent.
    public class SampleStats
    {
        public void DisplayStats(SyncStatistics syncStatistics, string syncType)
        {
            Console.WriteLine(String.Empty);
            if (syncType == "initial")
            {
                Console.WriteLine("****** Initial Synchronization Stats ******");
            }
            else if (syncType == "subsequent")
            {
                Console.WriteLine("***** Subsequent Synchronization Stats ****");
            }

            Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
            Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
            Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
            Console.WriteLine(String.Empty);
        }
    }         
}

See Also

Show:
© 2014 Microsoft