Microsoft Change Tracking Sync data from One server to two or more offline - Sql Server Part -1

What is change Tracking in Sql Server ?

Change Tracking is the light weight application, helping the developers to track the data changes takes place in database.Most commonly change track is used to sync the data between the two server applications.

That means synchronization the data from server to the two or more offline store applications.

In Microsoft they release a Change Tracking system in Sql Server 2008 , Which have following features.

1. Efficient and fast
2. Easy to track the data.
3. Minimum disk space
4. Regular clean up of additional table data.

To work with change tracking , we have to  Enable the change Tracking in Database and table .In which we have to track the data .

/* Functions used in change tracking */
CHANGETABLE()   
CHANGE_TRACKING_CURRENT_VERSION()   
CHANGE_TRACKING_MIN_VALID_VERSION()
CHANGE_TRACKING_IS_COLUMN_IN_MASK()   
WITH CHANGE_TRACKING_CONTEXT()

/* Change Tracking database */
Use Master;
Go
Create Database CT
Go

/* Create the Change Tracking Table */
USE CT
CREATE TABLE [SALESTRACKING]
(
SALEID INT PRIMARY KEY,
PRODUCTNAME VARCHAR(40),
MODEL VARCHAR(40)
)

/* INSERT 4 RECORDS */
INSERT INTO [SALESTRACKING] VALUES(1,'LG','AC45')
INSERT INTO [SALESTRACKING] VALUES(2,'SAMSUNG','S453')
INSERT INTO [SALESTRACKING] VALUES(3,'WHIRLPOOL','WL87')
INSERT INTO [SALESTRACKING] VALUES(4,'APPLE','IP342')
GO

SELECT * FROM SALESTRACKING

Check the Properties of database about Change Tracking by default it is disabled false

1. Right click the database
2. Select properties
3. Select the change tracking page
4. Now you can see the change tracking status



USE master
GO
ALTER DATABASE CT
SET CHANGE_TRACKING = ON

Now Change tracking is enabled your database .We have addtional two optional parameters (CHANGE_RETENTION,AUTO_CLEANUP). 
CHANGE_RETENTION         : MAINTAIN THE DATA OF HOW MANY DAYS
AUTO_CLEANUP                    : CLEAN THE OLD DATA AUTOMATICALLY IF IT IS ON

USE master
GO
ALTER DATABASE CT 
SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 100 DAYS,AUTO_CLEANUP = ON)
GO

ENABLE THE CHANGE TRACKING FOR TABLE TO TRACK THE DATA
1. Right click the table
2. Select the properties
3. Select the change tracking page
4. Now you can see the change tracking status.


USE CT
GO
ALTER TABLE [dbo].[SALESTRACKING]
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF)

TRACK_COLUMNS_UPDATED: This parameter is used to indicate the columns which are changed by UPDATE operation and also indicates that row has changed. By default, it is OFF. 

Now delete two row and insert one row

DELETE FROM [SALESTRACKING] WHERE SALEID = 1
INSERT INTO [SALESTRACKING] VALUES(8,'LG','AC45')

Now we get the change tracked data by executing the following query

SELECT SYS_CHANGE_VERSION,SYS_CHANGE_OPERATION,SALEID
FROM CHANGETABLE(CHANGES [dbo].[SALESTRACKING],0)AS SI
ORDER BY SYS_CHANGE_VERSION

(OR EXECUTE THIS )

SELECT * FROM CHANGETABLE(CHANGES SALESTRACKING,0)AS SI
ORDER BY SYS_CHANGE_VERSION 

From this article we can learn how to enable the change tracking in Database and tables to track.Above image will show that what kind of change that data goes