How to: Use SQL Server Change Tracking

  • Article

This topic provides an overview of SQL Server change tracking and describes a console application that performs bidirectional synchronization between a SQL Server database and a SQL Server Compact database. If the server is running SQL Server 2008, we recommend that you use SQL Server change tracking. If the server is running a different database, see How to: Use a Custom Change Tracking System.

Overview of SQL Server Change Tracking

In many of the examples in this documentation, change tracking is handled by a set of columns and triggers that are added to base tables, and additional tables to track delete operations. For more information, see Tracking Changes in the Server Database. This type of tracking is useful for databases other than SQL Server 2008 databases. However, it has the following disadvantages:

  • Schema changes are required in the server database. This might affect other applications, or might not be possible at all.

  • Triggers are fired for each change that has been made to a row. This has performance implications.

  • Logic for maintaining the correct row versions and deletions can become complex.

  • If a server database has long-running transactions, unless these transactions are correctly handled, data changes can be missed during synchronization. This can cause data inconsistencies.

SQL Server change tracking addresses these issues and provides a straightforward way to track changes. When change tracking is enabled on a table, the SQL Server Database Engine maintains information about the changes that were made to the tables. Applications then use change tracking functions to determine which rows have changed and to obtain information about the changes. The key benefits of SQL Server change tracking are as follows:

  • For offline synchronization scenarios that use Sync Framework, you do not have to create triggers, timestamp columns, other additional columns, or additional tables.

  • Changes are tracked at commit time instead of when the DML operations occur.

  • Functions return incremental changes to tables and version information. These functions provide reliable and easy-to-consume results, even when there are overlapping and uncommitted transactions.

  • The performance overhead is minimal.

  • Change tracking data can be cleaned up automatically.

The rest of this topic shows you how to use SQL Server change tracking in a Sync Framework application. For more information about change tracking, see SQL Server 2008 Books Online.

Using SQL Server Change Tracking with Sync Framework Offline Database Providers

This section of the topic describes how to enable change tracking and how change tracking queries are used to determine which data changes to download to a client. The information in this section describes how to use manually created commands to select changes from the server. For information about how to use the synchronization adapter builder to create commands for you, see Getting Started: Client and Server Synchronization.

Enabling SQL Server Change Tracking

Change tracking is enabled on the server database, and then for each table that requires tracking. The following code examples show the schema for the Sales.Customer table in one of the Sync Framework sample databases, and the code that is required to enable change tracking for that table. Each table must have a primary key. Primary keys must be unique across all nodes and must not be reused: If a row is deleted, the primary key of that row must not be used for another row. Identity columns are typically not an appropriate choice for distributed environments. For more information about primary keys, see Selecting an Appropriate Primary Key for a Distributed Environment.

The change tracking options that are specified by running the following code include how long to retain tracking metadata and whether to clean up that metadata automatically. For more information about tracking options, see the topics "Change Tracking," "ALTER DATABASE," and "ALTER TABLE" in SQL Server 2008 Books Online.

Note

We strongly recommend that you use snapshot transactions when you are querying for change information. This helps ensure the consistency of change information and avoids race conditions that are related to the background cleanup task. For more information about snapshot isolation, see "Isolation Levels in the Database Engine" in SQL Server 2008 Books Online.

Determining Which Data Changes to Download to a Client

After change tracking is enabled, Sync Framework applications use change tracking functions and anchors to determine which inserts, updates, and deletes to download. An anchor is just a point in time that is used to define a set of changes to synchronize. Consider the following queries:

  • The query that you specify for the SelectIncrementalInsertsCommand property. The following query selects incremental inserts from the Sales.Customer table at the server to apply to the client:

    IF @sync_initialized = 0
  SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] 
  FROM Sales.Customer LEFT OUTER JOIN 
  CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT
  ON CT.[CustomerId] = Sales.Customer.[CustomerId]
ELSE
BEGIN
  SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType]
  FROM Sales.Customer JOIN CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT
  ON CT.[CustomerId] = Sales.Customer.[CustomerId]
  WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION 
  <= @sync_new_received_anchor)
END

    If this is the first synchronization session for a client (@sync_initialized = 0), the schema and all rows are selected directly from the Sales.Customer base table. During later synchronizations, newly inserted rows are selected by performing an inner join between the base table and its change tracking table. Metadata in the change tracking table is exposed by the CHANGETABLE() function. This function takes as parameters the base table name and the change tracking version that was stored from the previous synchronization. The SYS_CHANGE_OPERATION column defines the type of change that is stored in a row of the change tracking table.

    Note

    Queries should also check whether any required changes have been cleaned up from tracking tables. For an example, see "Specifying a Command to Select Incremental Inserts from the Server to Apply to the Client" later in this topic.

  • The query that you specify for the SelectNewAnchorCommand property. This query retrieves a point-in-time value. The following query retrieves a new anchor value from the server by using the change_tracking_current_version() function. This built-in SQL Server function returns a version integer that is associated with the last committed transaction that was tracked by change tracking.

    SELECT @sync_new_received_anchor = change_tracking_current_version()

    The integer value is stored in the client database and is used by the commands that synchronize changes. During each synchronization session, the new anchor value and the last anchor value from the previous synchronization session are used: This represents the set of changes between these upper and lower bounds is synchronized.

In some cases, an application requires only a subset of the data at each client. You can include additional conditions in the WHERE clause to filter data. For more information, see How to: Filter Rows and Columns. The section "Filters Based on Non-Key Columns" includes important information about filtering with SQL Server change tracking.

Queries That Are Executed During the Synchronization Process

When the Sales.Customer table is first synchronized, the following process occurs:

  1. The new anchor command is executed. The command returns an integer value, such as 372. This value is stored in the client database. The table has never been synchronized. Therefore, there is no anchor value that was stored in the client database from a previous synchronization. In this case, Sync Framework uses a value of 0. The query that Sync Framework executes is as follows:

    exec sp_executesql N'IF @sync_initialized = 0 SELECT 
Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], 
[CustomerType] FROM Sales.Customer LEFT OUTER JOIN 
CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT 
ON CT.[CustomerId] = Sales.Customer.[CustomerId] ELSE  BEGIN SELECT 
Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], 
[CustomerType] FROM Sales.Customer JOIN CHANGETABLE(CHANGES 
Sales.Customer, @sync_last_received_anchor) CT ON CT.[CustomerId] = 
Sales.Customer.[CustomerId] WHERE (CT.SYS_CHANGE_OPERATION = ''I'' 
AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor) 
END', N'@sync_initialized int, @sync_last_received_anchor bigint, 
@sync_new_received_anchor bigint', @sync_initialized=0, 
@sync_last_received_anchor=0, @sync_new_received_anchor=372

  2. During the second synchronization session, the new anchor command is executed. Rows have been inserted since the last session. Therefore, the command returns the value 375. The table has been synchronized before. Therefore, Sync Framework can retrieve the anchor value of 372 that was stored in the client database from the previous synchronization. The query that is executed is as follows. The query downloads only those rows from the table that were inserted between the two anchor values.

    exec sp_executesql N'IF @sync_initialized = 0 SELECT 
Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], 
[CustomerType] FROM Sales.Customer LEFT OUTER JOIN 
CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT 
ON CT.[CustomerId] = Sales.Customer.[CustomerId] ELSE  BEGIN SELECT 
Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], 
[CustomerType] FROM Sales.Customer JOIN CHANGETABLE(CHANGES 
Sales.Customer, @sync_last_received_anchor) CT ON CT.[CustomerId] = 
Sales.Customer.[CustomerId] WHERE (CT.SYS_CHANGE_OPERATION = ''I'' 
AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor) 
END', N'@sync_initialized int, @sync_last_received_anchor bigint, 
@sync_new_received_anchor bigint', @sync_initialized=1, 
@sync_last_received_anchor=372, @sync_new_received_anchor=375

For examples of update and delete commands, see the complete code example later in this topic.

Identifying Which Client Made a Data Change

There are two main reasons to identify which client made a data change:

  • To support conflict detection and resolution in upload-only and bidirectional synchronization.

    If the server and client or more than one client can change a given row, you might want to identify who made the change. This information lets you write code, for example, that prioritizes one change over another. Without this information, the last change that is made to the row is persisted.

  • To prevent echoing of changes back to the client during bidirectional synchronization.

    Sync Framework first uploads changes to the server, and then downloads changes to the client. If you do not track the identity of the client that made a change, the change will be uploaded to the server and then downloaded back to the client during the same synchronization session. In some cases, this echoing of changes is required, but in other cases it is not.

Change tracking provides a mechanism to store application data along with change information when rows are changed. This application data can be used to identify the client that is making a change. The identity of the client that made a change can then be returned when you query for changes.

The SYS_CHANGE_CONTEXT column can be used with the ClientId property to determine which client made each insert, update, or delete. The first time any table is synchronized by using a method other than snapshot synchronization, Sync Framework stores a GUID value on the client that identifies that client. This ID is passed to the DbServerSyncProvider so that it can be used by the commands for each SyncAdapter object. The ID value is available through the ClientId property, and the @sync\_client\_id and @sync\_client\_id\_binary session variables. Consider the following Transact-SQL query:

IF @sync_initialized = 0
  SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] 
  FROM Sales.Customer LEFT OUTER JOIN 
  CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT
  ON CT.[CustomerId] = Sales.Customer.[CustomerId]
  WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)
ELSE
BEGIN
  SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType]
  FROM Sales.Customer JOIN CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT
  ON CT.[CustomerId] = Sales.Customer.[CustomerId]
  WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION 
<= @sync_new_received_anchor 
  AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary));

This query is similar to the earlier query that tracks inserts that are made on the server. The additional statement in each WHERE clause ensures that the only inserts that are downloaded are those inserts that were not made by the client that is currently synchronizing. Sync Framework also enables applications to identify clients by using an integer at the server instead of a GUID value. For more information, see How to: Use Session Variables.

To track which client made a data change that is applied at the server, use the WITH CHANGE_TRACKING_CONTEXT clause. Before executing an INSERT, UPDATE, or DELETE statement, set the CHANGE_TRACKING_CONTEXT to the value of the @sync\_client\_id or @sync\_client\_id\_binary session variable. This information is stored in the change tracking table so that applications can track the context under which a change was made. For Sync Framework, this is typically the client ID. However, you can store any value that fits in a varbinary(128) column.

WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary)
INSERT INTO Sales.Customer (CustomerId, CustomerName, SalesPerson,
 CustomerType)
VALUES (@CustomerId, @CustomerName, @SalesPerson, @CustomerType)
SET @sync_row_count = @@rowcount

Understanding and Running the Sample Application

This section of the topic includes the application code that is required to configure and perform synchronization. You can learn by just reading through the example code. However, it is more instructive to run it and to see it in action. Before you run the code, make sure that you have the following installed:

  • Sync Framework

    The application requires references to Microsoft.Synchronization.Data.dll, Microsoft.Synchronization.dll, Microsoft.Synchronization.Data.Server.dll, and Microsoft.Synchronization.Data.SqlServerCe.dll.

  • SQL Server 2008

    The example code uses localhost in connection strings. To use a remote server, change localhost to the appropriate server name.

  • The Sync Framework sample databases. For more information, see Setup Scripts for Database Provider How-to Topics.

If you have read the topic Architecture and Classes for Client and Server Synchronization, you should have an understanding of the main classes that are used in the application. The application is made up of the following classes.

  • SampleSyncAgent. This class is derived from SyncAgent.

  • SampleServerSyncProvider. This class is derived from DbServerSyncProvider and contains the SyncAdapter and a set of commands that query change tracking tables.

  • SampleClientSyncProvider. This class is derived from SqlCeClientSyncProvider and contains a SyncTable.

  • SampleStats. This class uses the statistics that are returned by SyncAgent.

  • Program. This class sets up synchronization and calls methods from the Utility class.

  • Utility. This class handles all functionality that is not directly related to synchronization, such as holding connection string information and making changes to the server and client databases For more information, see Utility Class for Database Provider How-to Topics.

Key Parts of the API

Before you look at the complete code example, we recommend that you review the following examples. These examples illustrate several key sections of the API that are used in this application. All of the example code that is shown is contained in the SampleServerSyncProvider class. In addition to the commands that are shown in this section, the complete code example also contains a command to apply inserts to the server and commands to select and apply deletes.

The first example applies directly to the DbServerSyncProvider property SelectNewAnchorCommand. The other examples apply to the SyncAdapter object for the Sales.Customer table.

Retrieving a New Anchor Value from the Server

The following code example specifies the command to retrieve a new anchor value from the server. The SyncSession class contains several string constants that can be used in synchronization commands. SyncNewReceivedAnchor is one of these constants. You could also use the literal @sync\_new\_received\_anchor directly in your queries.

Specifying a Command to Select Incremental Inserts from the Server to Apply to the Client

The following code example specifies a command to select incremental inserts from the server to apply to the client. All the queries for incremental changes check whether required changes have been cleaned up from the change tracking table. This check starts with the following clause and raises an error if changes have been cleaned up:

IF CHANGE_TRACKING_MIN_VALID_VERSION (object_id (@sync_table_name)) > @sync\_last\_received\_anchor

Specifying a Command to Select Incremental Updates from the Server to Apply to the Client

The following code example specifies a command to select incremental updates from the server to apply to the client.

Specifying a Command to Apply Incremental Updates from the Client to the Server

In the following code example, the UPDATE statement updates the base table and returns a count of rows that are affected. If the row count is 0, an error or conflict occurred. For more information, see How to: Handle Data Conflicts and Errors.

Selecting Conflicting Rows

The following command selects conflicting rows from the server database if the rows still exist in the base table.

The following command selects conflicting rows from the server database if the rows have been deleted from the base table.

For more information about how to handle data conflicts, see How to: Handle Data Conflicts and Errors.

Complete Code Example

The following complete code example includes the code examples that are described earlier and additional code to perform synchronization.

See Also

Concepts

Tracking Changes in the Server Database