Microsoft Sync Service Framework includes Sync Services for ADO.Net that allows you to "synchronize data from different sources over two-tier, N-tier, and service-based architectures. The Synchronization Services API provides a set of components to synchronize data between data services and a local store, instead of only replicating a database and its schema". I am currently working with Sync Services using SQL Server 3.5 as the client and SQL Server 2008 as the server side database to synchronize with. In this post I will detail out the implementation details on the server side, and how we can leverage the new Change tracking functionality of SQL Server 2008 for Synchronization.
Before we dig into how to do this with SQL Server 2008, lets take a look at how this was implemented in SQL Server 2005
SQL Server 2005 - using special columns and tables to track changes
When using SQL 2005 as the data store on the server side change tracking is typically done by using special columns on the tables to be synchronized that keep track of updates, inserts and deletes timestamps on the table. An additional _tombstone table is required to hold deleted rows. Sync Services ADO.Net provides support for this design with SqlSyncAdapterBuilder class which accepts these columns names as input parameters to build up the SqlSyncAdapter to track changes and apply inserts, updates and deletes to the server side. This design serves the needs of synchronization, but enforces the use of special columns and table just for synchronization and clutters the business abstraction in the DB with sync implementation details.
NEW SQL Server 2008 - Out of the box Change Tracking
The Nov CTP release of SQL Server, introduces an in built change tracking mechanism that is invaluable for synchronization scenarios. The documentation for this release is still sketchy and incomplete, but a good starting point is the MSDN reference.
When change tracking is enabled, the SQL Server engine maintains information about changes made to the table internally. The tracked data is made accessible by a bunch of system functions that allow you to query the information. However, remember that this does not provide full out data logging and only maintains the primary keys changes and type for change.
Database Configuration
Change tracking must be turned on explicitly on the database and on the tables that are to be tracked
ALTER DATABASE [Database_Name]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
ALTER TABLE [Table_Name]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
More...
Important: the tracking begins for data modified after these commands are fired. This means that data that is already present in the table is viewed as seed data and will not be available through the change tracking functions.
Accessing Tracked Data
When change tracking is enabled for the database a version number is maintained internally for the database and can be accessed by calling CHANGE_TRACKING_GET_CURRENT_VERSION(). On initialization, all the rows are assigned (virtually and internally) this version number.
Whenever a DML statement is executed on any of the tracked tables, the database version number is incremented by 1, and the new row is now assigned the new version number. The old rows are still marked with the old version number. The appeal of this scheme is in its simplicity.
The CHANGETABLE function retrieves the tracked information. There are 2 ways to use this function -
1. SELECT * FROM CHANGETABLE ( Changes <Table_Name>, last_sync_version)
this returns the all the rows that have changed for the table since "last_sync_version". Each rows corresponds to one changed row in the table and contains the primary key of the row and operation.
2. SELECT * FROM CHANGETABLE ( Version <Table_Name>, <primary_column_name>, <value>)
this is used to return the version information for a particular row
Integrating with Sync Services
Basically the Sync Service API requires that you configure a DbServerSyncProvider for your specific data store, and a SqlSyncAdapterBuilder per table to perform synchronization. A full implementation of this using SQL Server 2005 is described here A Synchronization Services Application (SQL Server 2005)
Shown below is how you would set this up using the change tracking feature of SQL 2008.We need to configure a bunch of the commands on these to enable synchronization.
DbServerSyncProvider.SelectNewAnchorCommand :contains the query to retrieve the new anchor value from the database. The anchor defines the upper bound for the set of changes to be synchronized during the current session. We will set this to the value returned by change_tracking_current_version()
DbServerSyncProvider _serverSyncProvider = new DbServerSyncProvider();
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
//SQL Server 2008 supports change_tracking_current_version() that returns
//the latest version number for the database
selectNewAnchorCommand.CommandText =
"SELECT " + newAnchorVariable + " = change_tracking_current_version()"
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Int);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
_serverSyncProvider.SelectNewAnchorCommand = selectNewAnchorCommand;
SyncAdapter.SelectIncrementalInsertsCommand: query that is used to retrieve inserts made in the server database since the last synchronization.
//select inserts from the server
SqlCommand incrementalInsertCommand = new SqlCommand();
incrementalInsertCommand.CommandText = String.Format(
@"SELECT {0} FROM {1} as originalTable
INNER JOIN ChangeTable(changes {1}, {2} ) ct
ON ct.{3} = originalTable.{3}
where ct.SYS_CHANGE_OPERATION = '{4}'
", columns, tableName, "@" + SyncSession.SyncLastReceivedAnchor, idColumnName, OPERATION_INSERT);
incrementalInsertCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
syncAdapter.SelectIncrementalInsertsCommand = incrementalInsertCommand;
SyncAdapter.SelectIncrementalUpdatesCommand: query that is used to retrieve updates made in the server database since the last synchronization.
//Select updates from the server.
SqlCommand incrementalUpdateCommand = new SqlCommand();
incrementalUpdateCommand.CommandText = String.Format(
@"SELECT {0} FROM {1} as originalTable
INNER JOIN ChangeTable(changes {1}, {2} ) ct
ON ct.{3} = originalTable.{3}
where ct.SYS_CHANGE_OPERATION = '{4}'
", columns, tableName, "@" + SyncSession.SyncLastReceivedAnchor, idColumnName, OPERATION_UPDATE);
incrementalUpdateCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
syncAdapter.SelectIncrementalUpdatesCommand = incrementalUpdateCommand;
SyncAdapter.SelectIncrementalDeletesCommand: query that is used to retrieve deletes made in the server database since the last synchronization
//Select deletes from the server.
SqlCommand incrementalDeleteCommand = new SqlCommand();
incrementalDeleteCommand.CommandText = String.Format(
@"SELECT originalTable.{0} FROM {1} as originalTable
INNER JOIN ChangeTable(changes {1}, {2} ) ct
ON ct.{0} = originalTable.{0}
where ct.SYS_CHANGE_OPERATION = '{3}'
", idColumnName, tableName, "@" + SyncSession.SyncLastReceivedAnchor, OPERATION_DELETE);
incrementalDeleteCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);
syncAdapter.SelectIncrementalDeletesCommand = incrementalDeleteCommand;
happy sync'ing
