Synchronizing Data with change tracking

SQL Server 2008 introduces change tracking, a lightweight solution that provides an efficient change-tracking mechanism for applications. Typically, to enable an application to query for changes to data in the database and to access information related to those changes, the application developer must implement a custom change tracking mechanism. Creating these mechanisms usually involves a number of tasks and often involves using triggers, timestamp columns, and new table combinations to store trace information, as well as the use of custom cleanup procedures.

With change tracking, you can easily write the application of synchronized data, and here is an example of using change tracking to implement one-way data synchronization.

1 . setting up an example environment

-- ====================================================

--Test the database

Use master;

Go

CREATE DATABASE db_test;

Go

--Enable change tracking

ALTER DATABASE db_test SET

Change_tracking = On (

Auto_cleanup = ON,--turn on automatic cleanup options

Change_retention = 1 HOURS--Data retention is 1 o'clock

);

ALTER DATABASE db_test SET

Allow_snapshot_isolation on; --Allow the SNAPSHOT transaction isolation level to be used in the test database

Go

-- ====================================================

--Test the table

Use Db_test;

Go

--A. Synchronized source table

CREATE TABLE Dbo.tb_source (

pk_id int IDENTITY

PRIMARY KEY,

col1 int,

col2 varchar (10),

Col3 nvarchar (max),

COL4 XML

);

Go

--Enable change tracking

ALTER TABLE Dbo.tb_source

ENABLE change_tracking

With (

track_columns_updated = on--Log Update column information

);