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

• April 26th, 2022

Change Tracking - Featured Image

Organizations demand real-time data to Fastrack their decision-making process based on the latest 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. 

Table of Contents

What is 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.   

How does Change Tracking work?

To get started with change tracking you have to use the DDL statements or SQL Server Management Studio. Initially, you need to first enable change tracking in the desired database and toggle it on for the tables you need to keep a tab on. This doesn’t require you to modify the table definition or create a trigger. Now, the changes by DML statements done on a row are recorded as the change tracking information for each affected row. Using the Change Tracking feature you can now identify the rows that were modified and query them to get information about the changes. 

This features only records the value of the primary key column from the tracking table along with the change information. These values can assist you in identifying the changed row. Hence, to retrieve the latest data, the application uses these values ​​in the primary key column to associate the source table with the tracking table. 

Load your data in SQL Server in Minutes Using Hevo’s No-Code Data Pipeline

Hevo Data, a Fully-managed No-Code Data Pipeline, can help you automate, simplify & enrich your data ingestion and integration process in a few clicks. With Hevo’s out-of-the-box connectors & complete support for Change Data Capture & Change Tracking, you can load data in real-time from 100+ Data Sources(including 40+ free data sources) and load it straight into your Data Warehouse, Databases like SQL Server, or any destination. Hevo also supports SQL Server as a Source. To further streamline and prepare your data for analysis, you can process and enrich Raw Granular Data using Hevo’s robust & built-in Transformation Layer without writing a single line of code!”

Get Started with Hevo for Free

Hevo is the fastest, easiest, and most reliable data replication platform that will save your engineering bandwidth and time multifold. Try our 14-day full access free trial to experience an entirely automated hassle-free Data Replication!

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. 

What questions can you answer with SQL Server Auditing?

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. 

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: 

Two-way Synchronization Applications

Change Tracking - Two Way sync
Image Source

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 datastores during the time between synchronizations. The ability to effectively handle such situations ensures that no data is lost.  

One-way Synchronization Applications

Change Tracking - One Way sync
Image Source

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.

Here’s What Makes Hevo’s Solution Unique!

Aggregating & loading your data from various applications to SQL Server can be a mammoth task without the right set of tools. Hevo’s automated platform empowers you with everything you need to have a smooth Data Collection, Processing, and Aggregation experience. Our platform has the following in store for you!

  • Exceptional Security: A Fault-tolerant Architecture that ensures Zero Data Loss.
  • Built to Scale: Exceptional Horizontal Scalability with Minimal Latency for Modern-data Needs.
  • Data Transformations: Process and Enrich Raw Granular Data using Hevo’s robust & built-in Transformation Layer without writing a single line of code.
  • Built-in Connectors: Support for 100+ Data Sources, including Databases, SaaS Platforms, Files & More. Native Webhooks & REST API Connector available for Custom Sources.
  • Incremental Data Load: With Change Data Capture & Change tracking features, Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Auto Schema Mapping: Hevo takes away the tedious task of schema management & automatically detects the format of incoming data and replicates it to the destination schema. You can also choose between Full & Incremental Mappings to suit your Data Replication requirements.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
Sign up here for a 14-Day Free Trial!

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.
Change Tracking - Database Properties
Image Source

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.

Change Tracking - Table Properties
Image Source

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

What is 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 the 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 the databases for which change tracking is enabled. Expired records are deleted from each internal on-disk table based on the database retention period setting. 

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.   

Visit our Website to Explore Hevo

Hevo Data, a No-code Data Pipeline can transfer real-time data from a vast sea of 100+ sources to a Data Warehouse, Databases like SQL Server, BI Tool, or a Destination of your choice. Hevo also provides SQL Server as a Source. It is a reliable, completely automated, and secure service that doesn’t require you to write any code!  

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.

No-code Data Pipeline for MS SQL Server