Database platforms
SQL Server Change Tracking is a capability that allows you to track all the changes made to a Microsoft SQL Server database. This feature records all insert, update, or delete operations made to the database schema over a specific time period, and the information received is used for the SQL Server replication process. SQL Change Tracking reads the transaction log to find changes made to the data and then records these changes. This article will briefly discuss how to track SQL Server changes in the database schema using Change Tracking.
Change Tracking (CT) is a lightweight mechanism for capturing incremental changes happening to the schema during a particular window. You can enable Change Tracking for both databases and tables. However, CT can only be configured for tables that have a primary key. CT won't track individual changes made, but it displays the primary key of the rows for which values changed. All intermediate operations or how many times the row is modified in the table cannot be viewed using CT.
The primary function of CT is efficient data storage and retrieval, which helps in the SQL Server replication process in cases where end-to-end replication fails to work and requires a custom solution. For example, a case where data must be synchronized with data stores that aren't SQL Server databases or have substantially different store formats.
In a table with Change Tracking enabled, each row is tracked internally for a change. A version ID is attached for every new insert, update, or delete change in the database.
If Change Tracking is enabled at the database level, each table needs a primary key as its row-level identifier to track data manipulation language (DML) operations. While enabling Change Tracking at the table level, you need to track all the columns to get a clear idea of what has been changed.
For instance, to enable Change Tracking in the database:
ALTER DATABASE Zoho
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 5 DAYS, AUTO_CLEANUP = ON)
The three parameters involved here are:
This is the retention period for keeping the Change Tracking information in the database. Records older than X days will be automatically removed if AUTO_CLEANUP is set to ON. The default value is two days.
This removes the tracking information from the database.
This option exists only in SQL Server Management Studio and allows you to set days, hours, or minutes. The minimum retention period value is one minute, and there is no maximum retention period. These parameters are changed after Change Tracking is enabled.
Ensure the database table with Change Tracking enabled has a primary key.
Change Tracking should be enabled for each table that you want to track. Execute the commands below to enable CT.
ALTER DATABASE database name
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON)
USE <database name>
GO
ALTER TABLE table name
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
Use the sys.change_tracking_tables catalog to view the tables that have Change Tracking enabled. Then, pick the tables or database for which you want to disable CT and execute the commands below.
ALTER DATABASE database name
SET CHANGE_TRACKING = OFF
ALTER TABLE table column name
DISABLE CHANGE_TRACKING;
In SQL Server auditing, tracking data changes is a crucial process that CT does with ease. SQL Server Change Tracking helps track all the Data Definition Language and Data Manipulation Language changes performed in SQL Server database tables. Analyze the transaction log file changes to identify all the DML operations and commits made on the server.
It's not possible to use the Change Tracking feature without a primary key in a table. Moreover, Change Tracking is a limited SQL Server database audit solution that tracks only the DML changes that have occurred. Change Tracking identifies the rows that have changed but does not provide information about the values that have been modified. Essentially, CT retrieves only the last change performed on the modified record with no version history of any other intermediate update or delete operations.
Overall, Change Tracking is a useful feature that can effectively monitor changes in a server containing a large volume of data.
Interested in a
log management
solution?
Manage logs, comply with IT regulations, and mitigate security threats.
Our support technicians will get back to you at the earliest.
Zoho Corporation Pvt. Ltd. All rights reserved.