An all too familiar challenge of today’s applications is to support the mobile user.  A mobile user is not always guaranteed to have a connection to the home office.  They need an application that can support both offline and online connection states.  An application that is occasionally connected is often referred to as an “Occasionally Connected Application” (OCA).  OCAs will often work offline and when brought online will execute synchronization processes.  In this blog, I’ll briefly introduce some nice features included with SQL Server 2008 Change Tracking.

SQL Server has had replication features for quite some time now.  I’m by no means an expert when it comes to SQL Server Transactional replication, but personally I’ve found it difficult to setup, troubleshoot, customize, and unable to easily communicate across N-Tier application environments.  In the past, the next option from replication was to create our own SQL synchronization engines utilizing triggers, timestamp columns, tombstone tables, etc to track database changes on the client and server and then synchronize up the differences.  There are some problems with this solution.  First glaring issue to me is the use of triggers.  Triggers cause transactions to take longer to commit and cause blocking issues.  So basically performance and storage issues result from this solution prior to SQL 2008 Change Tracking.  SQL 2008 now provides a new feature which is available in all versions: SQL Change Tracking. 

SQL Server Change Tracking Advantages/Features:

1)  Easy to Setup.  It does not require timestamp columns, tombstone tables, triggers, etc.  You can simply script or use SQL Management Studio to turn on SQL Change Tracking for a database and then what tables you want to track changes on.

2)  Better Performance.  Changes are tracked at commit time rather than when DML operations occur.  What this basically means is transactions run quicker and this also helps with blocking issues.

3)  Minimal Disk Space Costs.  Change Tracking stores changes in SQL system tables and the disk space cost is minimal.

4)  Integrates with the .NET Sync Framework.  If you are using the .NET Sync Framework to develop your OCA, then .NET/Visual Studio has some nice features that are at the developer’s disposal.

5)  Synchronize with other DB Platforms.  SQL Change Tracking runs independently from other databases.  So if your server is running an Oracle DB and you want to run SQL Compact on your client with Change Tracking enabled, it will support that just fine.  Changes on the client are tracked independently of the server and obviously vice versa.

6)  Packaged Functions.  SQL Server comes with packaged functions that are used to query the SQL Change Tracking system tables to acquire incremental changes.

7)  Auto Clean Up.  When SQL Change Tracking is enabled for a database, you can specify when change history will be purged automatically for you.

8)  Column or Entire Row.  You can enable change tracking to record that an entire row/record had something changed in it or you can even track down to what column was changed to limit the amount of data changes returned when querying for incremental changes.

Example 1 - Turn on SQL Change Tracking for a Database:

   1:  ALTER DATABASE [AdventureWorks2008] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 365 DAYS, AUTO_CLEANUP = ON);

   2:  GO

Example 2 – Turn on SQL Change Tracking for a Table:

   1:  ALTER TABLE HumanResources.[Department] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);

   2:  GO

Example 3 – Return Incremental Table Inserts:

   1:  DECLARE @sync_last_received_anchor BIGINT, @sync_new_received_anchor BIGINT;

   2:   

   3:  SELECT @sync_last_received_anchor = CHANGE_TRACKING_CURRENT_VERSION();

   4:   

   5:  INSERT INTO [HumanResources].[Department] (Name, GroupName, ModifiedDate) VALUES ('Test1', 'My Group', GETDATE())

   6:  INSERT INTO [HumanResources].[Department] (Name, GroupName, ModifiedDate) VALUES ('Test2', 'My Group', GETDATE())

   7:  INSERT INTO [HumanResources].[Department] (Name, GroupName, ModifiedDate) VALUES ('Test3', 'My Group', GETDATE())

   8:   

   9:  SELECT @sync_new_received_anchor = CHANGE_TRACKING_CURRENT_VERSION();

  10:   

  11:  --return inserts

  12:  SELECT dept.*

  13:  FROM [HumanResources].[Department] AS dept 

  14:      INNER JOIN CHANGETABLE(CHANGES [HumanResources].[Department], @sync_last_received_anchor) CT ON CT.[DepartmentID] = dept.[DepartmentID]

  15:  WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor)