Use Change Tracking for Data Synchronization

In daily applications, there is often a need for data changes in Audit, or data that needs to be tracked for data synchronization. (The most common applications, such as data synchronization in a data warehouse, need to synchronize data changes to a data warehouse because of the huge data volume. Such synchronization does not require real-time synchronization ).

Generally, a custom application uses triggers, timestamp columns, and a new table combination to store tracing information. colleagues also need a custom purge program to clear outdated data. After SQL Server 2008, a Change Tracking function is provided ). this lightweight solution provides an effective change tracking mechanism for applications (with high performance compared to custom programs.

Note: If the user program needs information about all the changes and the median of the changed data, it may be applicable to Change Data Capture rather than change tracking.

Working principle: after a change tracking is configured for the table, any DML statements that affect the rows in the table will record the change tracking information for each modified row. The change information is recorded in the SQL Server internal table. You can use sys. internal_tables to query the internal table and use the CHANGETABLE function to obtain the data change information.

The following is a script to start Change Tracking and obtain the changed data:

-- Create a test database and table

Create databasetest

Go

Create table [dbo]. [A] (

[MAXID] [int] not null,

[Name] [varchar] (20) NULL,

CONSTRAINT [PK_A] PRIMARY KEYCLUSTERED

(

[MAXID] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

-- Start change tracking in the database and table:

ALTER DATABASEtest

SET CHANGE_TRACKING = ON

(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

-- CHANGE_RETENTION: Specifies the shortest time for retaining change trace information in the database. Data is deleted only when the AUTO_CLEANUP value is ON. Retention_period is an integer used to specify the value of the retention period. The default retention period is 2 days. The shortest retention period is 1 minute.

-- AUTO_CLEANUP = ON will automatically delete the change tracking information after the specified retention period.

ALTERTABLEA

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = ON)

If the table does not have a primary key to start the change tracking, the following error occurs:

Msg 4997, Level16, State 1, Line 1

Cannot enablechange tracking on table 'A'. Change tracking requires A primary key on thetable. Create a primary key on the table before enabling change tracking.

Cause: the primary key column value is the unique information from the table that is tracked and records the change information. These values are used to identify the modified rows. To obtain the latest data of these rows, the application can use the primary key column value to join the source table and the tracked table.

(You can also enable change tracking in SSMS)

 

 

 

-- Query database and table change tracking information

SELECT * FROMsys. change_tracking_databases

SELECT * FROMsys. change_tracking_tables

SELECT * FROMsys. internal_tablesWHEREparent_object_id = OBJECT_ID ('A ')

 

-- Returns the version associated with the last transaction.

SELECT CHANGE_TRACKING_CURRENT_VERSION ()

-- Obtain the minimum version of the change tracking information in the specified table

SELECT CHANGE_TRACKING_MIN_VALID_VERSION (OBJECT_ID ('A '))

-- CHANGETABLE: The trace information is changed after Table A version 0 is returned (because no data changes exist, the returned data is 0)

SELECT * FROMCHANGETABLE (CHANGESA, 0) asCT

 

-- Insert test data and query the latest version number of the trace information

Insert into A values (1, 'kevin ')

Insert into A values (2, 'kevin ')

Go

SELECT CHANGE_TRACKING_CURRENT_VERSION ()

Go

Update A set name = 'kevinmodify' wheremaxid = 1

Go

SELECT CHANGE_TRACKING_CURRENT_VERSION ()

 

-- CHANGETABLE: The trace information is changed after Table A version 0 is returned.

SELECT * FROMCHANGETABLE (CHANGESA, 0) asCT

-- CHANGETABLE: change tracking information after Table A Version 2 (less than the data returned above)

SELECT * FROMCHANGETABLE (CHANGESA, 2) asCT

 

-- Associate the change tracking information with the original table information to obtain the latest data (these data are the data we need to synchronize)

DECLARE @ previusversion bigint

SET @ previusversion = 2

SELECT * from changetable (CHANGESA, @ previusversion) ASCTTable

Left outerjoinaon a. MAXID = CTTable. maxid

GO

 

 

 

--- Check whether the field NAME has been updated

DECLARE @ previusversion bigint

SET @ previusversion = 0

SELECT *, [NameChanged?] =

CHANGE_TRACKING_IS_COLUMN_IN_MASK (COLUMNPROPERTY (OBJECT_ID ('A'), 'name', 'columnid'), SYS_CHANGE_COLUMNS)

From changetable (CHANGESA, @ PreviousVersion) AS CTTable

Left outerjoin a on a. MAXID = CTTable. MAXID

WHERE CTTable. SYS_CHANGE_OPERATION = 'U'

GO

 

CHANGETABLE return value:

• SYS_CHANGE_VERSION is a bigint field, indicating the latest version number of the current row of data.
• SYS_CHANGE_CREATION_VERSION indicates the update version of the data table inserted to the current data row.
• SYS_CHANGE_OPERATION is a nchar (1) field. I represents Insert, U represents Update, and D represents Delete (the most appropriate processing value for Data Synchronization Based on this value ).
• SYS_CHANGE_COLUMNS indicates which data columns are affected by the update operation. The result of this field is varbinary (4100 ). This field returns a valid value only when the TRACK_COLUMN_UPDATED option is set to ON when Change Tracking is activated ON the table, and is NULL for the INSERT and DELETE operations, because DELETE and INSERT actually affect all data columns, only the UPDATE operation will return the value. The value of this field can be parsed using the CHANGE_TRACKING_IS_COLUMN_IN_MASK () function.
• SYS_CHANGE_CONTEXT is a varbinary (128) field that records the context information of data updates. However, the context information must be explicitly provided by the WITH CHANGE_TRACK_CONTEXT statement when a DML statement is submitted.
• ID is the primary key field of the TestCT table. Therefore, if the primary key of TestCT is a combination primary key whose content is (ID, Name, CHANGETABLE will return an additional Name field in the result set.

Note:

• If the truncate table or Cleanup process clears the Change Tracking record, you can use the CHANGE_TRACKING_MIN_VALID_VERSION function to find the minimum update version. If the minimum version number is higher than the maximum version number of the last successful copy recorded on the target end, it means that the source database has lost a copy of the record that has not been copied, this means that the target system needs to be reinitialized.
• For an UPDATE statement, if the Track_Column_Updated option is set to ON when the Change Tracking function of the table is enabled, Change Tracking records the UPDATE statement that affects the field information. This information can be parsed using the CHANGE_TRACKING_IS_COLUMN_IN_MASK function, for example, you can use CHANGE_TRACKING_IS_COLUMN_IN_MASK (COLUMNPROPERTY (OBJECT_ID ('testct '), 'name' to check whether the Name field of the TestCT table is affected in version 2 UPDATE ', 'columnid'), SYS_CHANGE_COLUMNS) function.

 

For more information, see MSDN: change tracking Overview