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.
Table of Contents
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
Image Source
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
Image Source
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.
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!
SQL Server Change Tracking versus Change Data Capture
Change Tracking and Change Data Capture are both features that were added to SQL Server in 2008 and can seem very similar on the surface. They both help understand changes made in a dataset, but in different ways.
In Change Tracking, you can receive information about whether a row has changed since your last query. However, you are not able to receive information about the steps in between, i.e., how many times the row has changed or the changes it has undergone. Change Tracking returns a table that provides information about which rows have changed and the type of DML change, and it needs to be joined with the source table to get the actual changed data.
On the other hand, CDC returns a copy of the original source table with some additional rows containing metadata. For each DML operation, there are one or two rows added to the change table. Inserts get one row showing the new data. Deletes get one row showing the old data, and updates get two rows for old and new data. This provides a lot more data than CT but is slightly more difficult to implement and would require maintenance of the change table as well due to the volume of data.
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.
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.
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
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:
- 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.
- 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.
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!
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.
Sanchit Agarwal is a data analyst at heart with a passion for data, software architecture, and writing technical content. He has experience writing more than 200 articles on data integration and infrastructure. He finds joy in breaking down complex concepts in simple and easy language, especially related to data base migration techniques and challenges in data replication.