What is Change Tracking in SQL Server? A Comprehensive Guide 101

Organizations demand real-time data to fastrack their decision-making process based on the latest, most relevant data. Most businesses use 10+ applications and need to continuously update the data from their local data store to their data warehouses or databases like SQL Server. Change Tracking is a feature that allows you to identify the rows that were modified in a given period of time and query them according to your business needs. 

SQL Change Tracking feature was introduced in 2008 and became a legacy auditing solution that allows your applications to retrieve only the changes that have been made to the user tables, along with the information about those changes. 

In this article, you learn about the SQL Server Change Tracking feature and how you can enable and disable it for your databases and tables. 

What is SQL Server Change Tracking?

Change tracking is an efficient lightweight tracking approach with effective change tracking mechanisms for applications such as MySQL Server Database. For instance, SQL Change Tracking reads the transaction logs to identify any incremental changes such as inserts, updates, or deletes made to any of the tables in a certain timeframe.   

What are One-way and Two-way Synchronization Applications?

You might have applications that need a real-time sync with an instance of the SQL Server Database Engine so that the latest data is available for querying. You can employ Change Tracking effectively, establishing both One-way & Two-way Synchronisation: 

One-way Synchronization Applications

One-way synchronization pushes changes from the source to the destination only.

You can build caching applications such as clients and middle tiers that use one-way synchronization applications. Caching applications need to store data in the database engine and cache it in other data stores, as shown in the following figure. The application must be able to keep the cache up-to-date with the modifications made to database tables. You do not need to transfer your changes back to the database engine.

Two-way Synchronization Applications

Two-way synchronization, as the name suggests, involves pushing updates both from the source to the destination as well as vice-versa.

You can also build a two-way sync application that employs change tracking. Here, the data in the database engine instance needs to be synchronized with one or multiple data stores. The data in these stores is updatable and changes must be synchronized back to the database engine.

To understand it more easily, consider an example of a two-way synchronization application is an occasionally connected application. Here, the client application sends queries and enters the updates in a local data store. Once the connection between the client & the server is live, the application will try to get synchronized with the server, & the modified data flows both ways.

Another essential feature of this two-way synchronization change tracking mechanism is the ability to detect and resolve any data mismatches or conflicts. A conflict may arise when the same data was modified in both data stores during the time between synchronizations. The ability to effectively handle such situations ensures that no data is lost.  

How Does Change Tracking Work?

Change tracking is a feature in databases that records changes made to data over time. It is a way to keep track of what has changed in a database and when these changes happened. When change tracking is enabled, the database system maintains a record of all modifications made to the data, such as insertions, deletions, and updates. This information can then be used to synchronize the changes made to one database with another database, or to revert back to previous versions of the data if necessary.

Change tracking works by keeping a record of both the original data and the changes made to it. The specific implementation of change tracking can vary depending on the database management system being used, but the basic idea remains the same: to keep track of changes made to data over time.

SQL Server Change Tracking vs Change Data Capture

• Change Tracking (CT):
  • Basic Tracking: Identifies whether a row has changed since the last query but does not provide details on how many times or how it changed.
  • Minimal Data: Only provides information on which rows have changed and the type of DML operation (INSERT, UPDATE, DELETE).
  • Joins Required: Requires joining the source table to retrieve the changed data.
• Change Data Capture (CDC):
  • Detailed Tracking: This captures complete details of every DML operation (INSERT, UPDATE, DELETE) with before-and-after data.
  • Change Tables: Create separate change tables that mirror the original source table and additional metadata columns.
  • Comprehensive Data: CDC adds one or two rows to the change table for each operation, providing an extensive history of changes.

How to Enable and Disable SQL Server Change Tracking?

Understanding the Steps to Enable SQL Change Tracking

Before enabling SQL Change Tracking, you need to ensure the following:

• Your database compatibility level must be configured to 90 or greater.
• The tables you want to track must have a primary key properly defined.
• Permission to modify a database.
• As the best practice, it is also suggested to enable snapshot isolation to ensure change tracking information consistency.

-- Enable snapshot isolation
ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT ON
GO

ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

Enabling Change Tracking in a Database

To enable change tracking you can either type the T-SQL commands or use the GUI(Graphical User Interface) of the SQL Server Management Studio.

To enable change tracking in a database via T-SQL, use the following commands:

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 5 DAYS, AUTO_CLEANUP = ON)

The above command has the following 3 parameters:

• CHANGE_RETENTION: It is the retention period for which the change tracking information is kept in the database. If AUTO_CLEANUP is set to ON, records older than X-Day will be automatically deleted. The default value for CHANGE_RETENTION  is 2 days. When employing change tracking for synchronization, ensure that the retention period is longer than the maximum time between two synchronizations. Otherwise, the synchronization will be inconsistent. 
• Auto_CleanUp: If toggled ON, this removes all the change tracking information from the database. Since it has no dependency on SQL Server job or Agent, it is compatible with all the SQL Server editions.  
• Retention Units: This setting exists in SQL Server Management Studio only and you can set it to days, hours, or minutes. The default value is 2 days, the minimum is 1 minute, and there’s no maximum value. As seen above, in T-SQL the unit value is appended to the retention period value in CHANGE_RETENTION. e.g. CHANGE_RETENTION = 5 minutes, or CHANGE_RETENTION = 5 hours.

To enable the SQL Server Change Tracking via the SQL Server Management Studio, follow the simple steps given below:

• Step 1: Open up the SQL Server Management Studio, and click on the database you want to track from the Object Explorer.
• Step 2: Navigate to Properties > Change Tracking. Now you can set the parameters and click on the OK button to save the changes.

Enabling Change Tracking in a Table

To get started with change tracking in a table, use the following T-SQL commands:

ALTER TABLE Person.Address
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

Here, Address is a Table of the Person database. For the tables, you only need to specify the TRACK_COLUMNS_UPDATED parameter. If you set it OFF, this lets the Change Tracking feature only store the fact the row has been changed. When Toggled ON, it will also allow the Change Tracking feature to store the information of which columns were updated. You can read all this information using the CHANGE_TRACKING_IS_COLUMN_IN_MASK function. Since this adds overhead, as a precaution it is set to OFF by default.

Similar to the Database, you can use the SQL Server Management Studio to enable the change tracking for a table.

Understanding the Steps to Disable SQL Change Tracking 

Follow the simple steps below to Toggle OFF the Change Tracking for both table and databases:  

• Step 1: You can disable the Change Tracking on a table-level using the Alter table T-SQL command:

USE CTAudit
GO
ALTER TABLE Employee_Main
DISABLE CHANGE_TRACKING
GO

• Step 2: You can now disable the change tracking on the database level using the Alter Table T-SQL command:

USE master
Go
ALTER DATABASE CTAudit 
SET CHANGE_TRACKING=OFF
GO

Track Changes in SQL Server Database

SQL Server provides several mechanisms to track changes in a database:

• Change Tracking: This is a lightweight mechanism for tracking changes to individual rows in a database.
• Change Data Capture (CDC): This is a more comprehensive mechanism for tracking changes to data, including inserts, updates, and deletes.
• Temporal Tables: This is a way to store a history of changes made to data over time, including the original data and the changes made to it.

These mechanisms allow you to track changes made to data in a SQL Server database, and to use this information for various purposes, such as synchronizing changes between databases, or auditing changes for compliance purposes.

What is SQL Server Change Tracking Cleanup?

Change tracking details corresponding to all the tracking-enabled tables are stored in an in-memory row store. The change tracking data associated with each change tracking enabled tables flushed from the in-memory row store to the required internal table on disk at each checkpoint. During the checkpoint, the in-memory row store is also flushed after the row is moved to the on-disk table. 

Each table with change tracking enabled has a corresponding internal table on a disk attached to it. It is used by the change tracking feature to determine the version of the change and the rows that have changed since a certain version. Each time the automatic cleanup thread is reactivated, it searches all user databases on the SQL Server instance to find out which databases have change tracking enabled. Expired records are deleted from each internal on-disk table based on the database retention period setting. 

Manage and Work With Change Tracking (SQL Server)

There are certain permissions, settings, and catalog views that need to be taken care of to ensure successful change tracking. 

Catalog Views

To determine which tables and databases have change tracking enabled, you can use the following catalog views:

sys.change_tracking_databases (Transact-SQL)

sys.change_tracking_tables (Transact-SQL)

Also, the sys.internal_tables catalog view lists the internal tables that are created when change tracking is enabled for a user table.

Security 

The principal needs to have the following permissions in order to use the change tracking functions to access change tracking data:

• SELECT permission on at least the primary key columns on the change-tracked table to the table that is being queried.

• VIEW CHANGE TRACKING permission on the table for which changes are being obtained. The VIEW CHANGE TRACKING permission is required for the following reasons:

1. Change tracking records include information about primary keys of rows that have been deleted. A principal could have been granted SELECT permission for a change tracked table after some sensitive data had been deleted. In this case, you would not want that principal to be able to access that deleted information by using change tracking.
2. Change tracking information can store information about which columns have been changed by update operations. A principal could be denied permission to a column that contains sensitive information. However, because change tracking information is available, a principal can determine that a column value has been updated, but the principal cannot determine the value of the column.

What are the benefits of SQL Server Change Tracking?

Using SQL Server Change Tracking allows you to leverage the following benefits:

• Right after the DML statements make a change, you can identify the modified rows via the change tracking feature.
• This also doesn’t require the SQL Server Agent service to be up and running as it does not need separate SQL Agent jobs in capturing or writing the DML changes. 
• Change Tracking is supported by all the versions of SQL Server.
• An extremely lightweight solution that is compatible with almost every scenario.

What are the Challenges of SQL Server Change Tracking with Custom Coding?

While building your SQL Server Change Tracking mechanisms, you may face the following hurdles with custom coding:

• The native SQL Server change tracking feature efficiently assists you in recording changes, but you need to do a lot of coding to ensure the changes are captured and use them for SQL replication to your data warehouse or data lake. 
• Apart from ensuring reliability and robustness, you also have to continuously monitor and maintain seamless operation. This also includes fixing data issues and handling new tables and data types. 
• Notification alerts and monitoring solutions also need to be manually programmed and looking at the various components needed for this to work, manually programmed solutions are time-consuming and are costly to develop and maintain.

Alternatively, you can select a more economical and effortless approach by opting for a Cloud-based ETL/ELT/Reverse ETL Tool like Hevo Data. Without writing a single line of code, you can ensure SQL Server Database change tracking in real-time. With complete support for Change Data Capture, you can start real-time data transfer from 100 + data sources to your Data Warehouse, Databases like SQL Server, or a destination of your choice in just a few clicks. 

SQL Change Tracking- FAQs

SQL Server Change Tracking has been used as a traditional SQL Server Auditing solution that you can use to track and monitor DML changes in database tables by answering simple questions such as:

• What rows have changed for a user table?
  • It only needs to record that a row has changed and not the times it has been updated or the values it holds.
  • By using the primary key, you retrieve the latest values from the table that is being tracked.
• Has a row changed?
  • Identifying the row that has been modified and recording the information about the change so that it is available in real-time. 

Learn More About:

Oracle Change Tracking

Conclusion

In this article, you learned about the Change Tracking feature in SQL Server. Change Tracking is a lightweight solution that is used to identify the modifications and record the information about the change that was made to each row. For instance, the type of DML operation that made the modification (insert, update, or delete) or the columns that were changed as part of an update operation. In SQL Server, you either use the T-SQL Commands or the SQL Server Management Studio to enable Change Tracking for both databases and specific tables.

As you collect and manage your data across several applications and databases in your business, it is important to consolidate it for complete performance analysis of your business. However, it is a time-consuming and resource-intensive task to continuously monitor the Data Connectors. To achieve this efficiently, you need to assign a portion of your engineering bandwidth to Integrate data from all sources, Clean & Transform it, and finally, Load it to a Cloud Data Warehouse, BI Tool, or a destination of your choice for further Business Analytics. All of these challenges can be comfortably solved by a Cloud-based ETL tool such as Hevo Data.   

FAQ on SQL Server Change Tracking

If you are using SQL Server as your Relational Database and searching for a no-fuss alternative to Manual Data Integration, then Hevo can effortlessly automate this for you. Hevo, with its strong integration with 100+ sources and BI tools (Including 40+ Free Sources), allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

Tell us about your experience of learning about the SQL Server Change Tracking feature! Share your thoughts with us in the comments section below.