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 Services 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 Services 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 Services 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.
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.
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.
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.
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.