What is SQL Server Change Tracking and how to set it up?

SQL DBAs are sometimes confused by the differences in SQL Server Change Tracking and Change Data Capture features. Not only can their names be mixed up, but also feature specifications. The goal of this series is to present each of 3 SQL Server auditing features (Change Tracking, Change Data Capture and SQL Server Auditing) and ApexSQL Audit – a complete third-party solution. We will show their features, similarities, differences, advantages, and disadvantages in order to help users determine the right tool for their auditing requirements

The reasons for internal auditing are various – ensuring a safe and protected environment, quality control, providing accurate records, etc. There can also be external auditing requests – regulations and federal laws such as HIPAA, SOX, PCI, GLBA, FERPA and Basel II, that require data auditing and audit reports

What questions should SQL Server auditing answer?

“Who did what”, “who changed the records“, “who deleted the records“, “when was that data changed“ and “I want to see all data changes on a specific table in a specific time period“ are common auditing requests. It’s not only the changes that are of interest, but also tracking object access and execution of the SELECT statements. Before SQL Server 2008, there were no native auditing methods, so third party tools or self-made applications were used. These solutions utilized triggers, timestamp columns, SQL traces, etc.

SQL Server Change Tracking

Three new auditing features were introduced in SQL Server 2008 – Change Tracking, Change Data Capture, and SQL Server Audit. In this article, we’ll start with SQL Server Change Tracking

MSDN defines Change Tracking as “Change tracking in SQL Server 2008 enables applications to obtain only changes that have been made to the user tables, along with the information about those changes” [1]

Change Tracking identifies the rows that have changed, but doesn’t provide information about the values that were changed. For example, if there was a row inserted, it will just notify you that there was an insert into a specific table. The exact inserted values will not be shown. If you have access to the current version of the row, you can see what was inserted. However, in case of UPDATEs and DELETEs, you will not be able to see the original value before the update, or the deleted records

The questions Change Tracking can answer are:

• “What rows have changed for a user table?
• Only the fact that a row has changed is required, not how many times the row has changed or the values of any intermediate changes
• The latest data can be obtained directly from the table that is being tracked
• Has a row changed?
• The fact that a row has changed and information about the change must be available and recorded at the time that the change was made in the same transaction.”[2]

How to set up Change Tracking?

There are a couple of requirements for your SQL Server databases and tables before you can set up Change Tracking. The database compatibility level must be set to 90 or greater and the tables you want to audit must have a primary key defined

There is also a recommendation to enable snapshot isolation [3] in order 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

To set up Change Tracking, use either T-SQL or SQL Server Management Studio

1. Enable Change Tracking on the database. To be able to do that, you must have the permission to modify the database

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

There are 3 parameters involved:

CHANGE_RETENTION is a retention period, the time the Change Tracking information is kept in the database. The records older than X days will be automatically removed if AUTO_CLEANUP is set to ON. The default CHANGE_RETENTION value is 2 days. If you use Change Tracking for synchronization, make sure that this value is longer than maximal time between 2 synchronizations; otherwise, the synchronization will be inaccurate

AUTO_CLEANUP removes the tracking information from the database. Just note that it uses its own mechanisms and doesn’t require a SQL Server job or Agent, so it can be used on all SQL Server editions

The Retention Period Units option exists in SQL Server Management Studio only and can be set to days, hours, or minutes. The default value is 2 days, minimum is 1 minute, and there’s no maximum value. In T-SQL, the unit value is added to CHANGE_RETENTION, e.g. CHANGE_RETENTION = 5 minutes, or CHANGE_RETENTION = 5 hours

These parameters can be changed after Change Tracking is enabled

To enable Change Tracking in SQL Server Management Studio

1. Right click the database in Object Explorer
2. Select Properties
3. Select the Change Tracking tab
4. Set the parameters



2. Enable Change Tracking for each table you want to audit. This causes no modification in the table structure

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

The only parameter is TRACK_COLUMNS_UPDATED. When set to OFF, only the fact that the row has been changed is stored. When set to ON, the information which columns were updated is also stored, which can be read using the CHANGE_TRACKING_IS_COLUMN_IN_MASK function, as shown in Part II. As this option adds overhead, it is set to OFF by default

Now, all table row changes invoked by INSERT, DELETE, or UPDATE statements will be tracked and stored

In the next part of this series, How to read SQL Server Change Tracking results, you will see how to read the Change Tracking information and what details about the transactions the Change Tracking feature can provide

All articles in this series:

1. What is SQL Server Change Tracking and how to set it up?
2. How to read SQL Server Change Tracking results
3. SQL Server Change Data Capture (CDC) – Introduction
4. How to enable and use SQL Server Change Data Capture
5. How to analyze and read Change Data Capture (CDC) records
6. SQL Server Audit feature – Introduction
7. How to set up and use SQL Server Audit
8. How to analyze and read SQL Server Audit information
9. Auditing SQL Server data changes – the centralized solution

References:

[1] MSDN – Change Tracking
[2] MSDN – About Change Tracking (SQL Server)
[3] MSDN – Work with Change Tracking (SQL Server)

Useful resources:

MSDN – Enable and Disable Change Tracking (SQL Server)
MSDN – Change Tracking Functions (Transact-SQL)