SQL Server Change Tracking Features

dba challenges newsletter Mar 16, 2024

Edition: Saturday, March 16th, 2024

Good morning! Hopefully, this week's edition finds you getting some much-needed rest on a Saturday. It's early here in Tennessee and I'm hoping for a sunny day.

This past week I posted a few LinkedIn challenges on the topic of change tracking in SQL Server:

Understanding these features, their components, and their use cases is important for any database professional working with SQL Server. You'll notice there's no mention of triggers here and that's on purpose. Given today's feature set in SQL Server, there are much better ways to track data changes. Triggers do serve a purpose, but if you're looking to simply track data changes, use one of these features instead.

A common pitfall lies in selecting a feature that does not align with the specific requirements of a project, leading to overcomplicated implementations. Each of these features does provide some overlap but also serves different use cases. Misunderstanding their applications can significantly impact how you perform database management.

Let's dive in.

Change Tracking

Change tracking was added in 2008 and provides a lightweight solution to determine the DML changes (insert, update, and delete) that have occurred on a table. Essential components include enabling CT on the database and tables and using change tracking functions to query changed data. It's important to manage cleanup processes to avoid unnecessary growth of tracking information. An important distinction between this feature and the other two is that it does not maintain a historical copy of the data that changed.

The best way to get familiar with this feature is to enable it on a database in your sandbox and work through examples.

Check out the following resources for more details:

About Change Tracking - SQL Server | Microsoft Learn
Getting Started with Change Tracking in SQL Server - Tim Mitchell
Introduction to SQL Server Change Tracking (youtube.com) - This isn't just a PowerPoint presentation.
Change Tracking and Database Refactoring – SQLServerCentral

Change Data Capture

The next option is Change Data Capture (CDC). This feature was added in 2008 as well (albeit as an Enterprise-only feature and then added to Standard edition with SQL Server 2016 SP1). CDC captures insert, update, and delete activity applied to SQL Server tables and makes the details of the changes available. CDC uses SQL Server Agent jobs to capture changes (using a log reader agent) and clean up old data. Take precautions when you're restoring a database that has CDC enabled. See here for more information when restoring a database that uses CDC.

Additional resources:

Temporal Tables

Temporal tables, introduced in SQL Server 2016, provide a system-versioned historical record of data changes. It requires the creation of two tables: the current table and the history table, with system-versioning enabled. The WideWorldImporters sample database includes multiple tables using temporal tables. I've found this feature to be the easiest to set up, maintain, and use. Be aware there are some limitations and performance impacts when using this option.

Check the additional resources for more information.

Additional resources:

Feature Comparison

Change Tracking (CT):

Use Case - Detecting DML changes (insert, update, delete). Tracks which row changed but doesn't contain a historical state of the data that changed.
Key Components - Tables enabled for CT
System Functions - Obtain changes by using change tracking fuctions
Historical Data Cleanup - Cleanup is managed through retention settings configured at the database and table level. Manual cleanup can be performed via T-SQL commands.
Post-restore implications - Review, Change Tracking and Data Restore, to understand the impact of restoring a database that uses CT.

Change Data Capture (CDC):

Use Case - Capturing detailed DML changes for audit or ETL processes. Tells you what changed and the previous state. Option is the most complex of the three options.
Key Components - Source tables enabled for CDC. SQL Server Agent jobs (capture and cleanup).
System Functions - Query Functions
Historical Data Cleanup - CDC utilizes cleanup jobs (cdc.<capture_instance>_cleanup) to automatically purge data older than the configured retention period.
Post-restore implications - Review, Database restore or attach, to understand the impact of restoring a database that uses CDC.

Temporal Tables:

Use Case - Automatically maintaining historical data for auditing or analytical purposes.
Key Components - Current table and History table.
System Functions - Query data in a system-versioned temporal table - SQL Server | Microsoft Learn
Historical Data Cleanup - In SQL Server 2017 or later, a retention policy can be configured to automatically manage the cleanup of historical data in the history table. Prior versions require manual management via T-SQL scripts. Manage historical data in system-versioned temporal tables - SQL Server | Microsoft Learn
Post-restore implications - Point in time restore considerations

Conclusion:

SQL Server offers many features that can be applied to the same use cases. Knowing about these features, long-term management, and the complexity of each is a crucial part of being a DBA. As a takeaway, I'd recommend setting up multiple databases in your sandbox and enabling the options above. Learn how each is affected by restores and measure the impact placed on the tables being tracked.

That's it for today. Have a great week!

Whenever you're ready, there is one way I can help you gain hands-on experience:

Automated Sandbox Fundamentals: I teach how to build a virtual lab using automation in this course. Learn how to create golden images, using both Windows and Linux, to easily spin up and add additional machines to your sandbox. It's packed with 8 modules and the scripts you'll need to build your environment. Start small, and scale as needed by easily changing the configuration file included with the course.

Give Me the Details