Tracking Data Changes of DML Operations In SQL Server

Stephen West | September 19th, 2016 | SQL |

Overview

There are three newly introduced features of SQL server 2008, these features are considered as auditing features of the server. List of these features are given below:

  • Change Tracking
  • Change Data Capture
  • SQL Server Audit

In this blog, users will be able to find out a complete information about change tracking, how to setup this feature within the server, and an overview on change data capture feature.

Introduction to Change Tracking

Change tracking in SQL server enables users to find out only the changes, which are made on the user’s table. It recognises the rows that were modified but does not provide information about the values that were altered. Following examples will help users to understand the concept more clearly:

  • In a table, if you are inserting a row, then it will not give detail (or values) that are inserted. However, it will only notify that a row was inserted into a particular table.
  • If you have made updation or deletion within the table, then SQL server change tracking function is only to notify that any updation or deletion operation is performed into the table. It will not provide the values that were before and after the updation or deletion.

How to Enable Change Tracking In SQL Server?

There are some requirements that need to be fulfilled before enabling change tracking feature and they are mentioned below:

  • The Compatibility level of the server database should be set on 90 or above
  • There must be a primary key of the table where this feature is to be imposed
  • Enable snapshot isolation for ensuring information consistency of this feature

Tip: To enable snapshot isolation, users can use the following set of commands:

ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT ON
GO

ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

Enable Change Tracking on Table: The server users may enable the feature on each table of a database. This method will not affect the original structure of the table. One can run following command in T-SQL for doing the same:

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

Now, settings of each server table are altered in such a way that if any DML operation (Insertion, Deletion, and Updation) is performed on the server, then it will be tracked and stored for further reference.

Enable Change Tracking on Entire Database: Users can perform this enabling operation either by T-SQL commands or Management Studio. Hence, to enable change tracking with help of T-SQL, execute the following command set:

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

Following are the instruction for enabling this audit feature with help of SQL Server Management Studio:

  • Launch the management studio on your machine
  • Right-click on the database from Object Explorer
  • Now select Properties >> Change Tracking
  • Set the parameter as per your requirements

Description of Parameters Used In Commands:

  • CHANGE_TRACKING: It is an approach that provides solution to track mechanism for programs in SQL server. Change tracking is not enabled on the table of SQL server therefore, this parameter is used to enable this operation on the server.
  • TRACK_COLUMNS_UPDATED: SQL server change tracking performance can be determined by tracking the rows of a table. If set value is OFF, then it will only store the fact whether the rows are modified or not. Moreover, if set value is ON, then it stores the detail of the row that is updated, which can be read using CHANGE_TRACKING_IS_COLUMN_IN_MASK function.
  • CHANGE_RETENTION: This parameter is used to change the default value of retention period. This period indicates the time duration when details of Change Tracking information is stored in the database. If AUTO_CLEANUP is enabled, then the entries older than X days will get automatically deleted. By default, this retention parameter is 2 days. The units of retention period appear only in SQL server Management Studio and can be adjusted on the basis days, hours, or minutes. By default, the values are 2 days, 1 minute (minimum), and no maximum limit is there.

NOTE: If users are using this audit feature just for synchronization, then remember that value of the parameter should be longer than maximum time duration between 2 synchronizations.

AUTO_CLEANUP: This server parameter eliminates the tracking data from the database. It does not require any SQL server job or Agent and has its own mechanisms. Therefore, it can be used on other editions of SQL server.

Overview on Change Data Capture

In SQL server 2008, this audit feature is brought to make processes like transform, extract, and load easier. It also catches details about the data changes like insertion, deletion, and updation. This feature is used for capturing purpose and the data captured are different from each other.

NOTE: Change Data Capture and Change Tracking both are the audit features of SQL server but are different from each other. The functioning of both of them is a different and they capture different amount of information.

Conclusion

The audit feature i.e., Change tracking feature is an outstanding feature to track DML operations in SQL server. This provides an ease to keep an eye on the modification made on the tables of a database. However, it is not similar to Change Data capture feature and hence, a different mechanism to analyze data within the server.

Useful Links

Become a Writer
Do you have flair of writing for SQL Server & other Database related Technology
We welcome the technical post from bloggers & technical writers who can contribute interesting stories about SQL Server on regular basis. If you think you are a good fit, then this SQL Server Explorer is a Platform for you. Stay in Touch with us.