Introduction
SQL Server 2008 includes several inbuilt features for Auditing – namely Change Data Capture (CDC) and Auditing and Change Tracking – this article we are going to discuss Change Tracking.
What is Change Tracking
Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications.
In case of data warehousing you will need to extract data from different data sources. If you need to extract data from a large table it is necessary to extract only the changes. If you don’t have a dedicated mechanism either you will have to extract all the data once again, implement triggers or else implement a timestamp mechanism. All of these are difficult to implement and may not be scalable. There are other solutions such as Slowly Changing Dimensions in SSIS, Change Data Capture in SQL Server. Compared with SSIS and CDC, Change Tracking is a lightweight solution to extract modified data from one SQL Server to another SQL Server .
Configuring Change Tracking
To enable change tracking for a table, you will need to enable it on the database first which can be done in two ways.
The below T-SQL script enables Change tracking in a DB_TRACK database:
USE [master]
GO
ALTER DATABASE [DB_Track]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 3 DAYS,AUTO_CLEANUP = ON)
GO
There are two optional parameters in the above query – CHANGE_RETENTION and AUTO_CLEANUP. CHANGE_RETENTION allows you to configure the period for which tracking data should be kept, ( 2 days by default) data which are older will be removed automatically. Enabling the AUTO_CLEANUP option will enables the cleanup task of removing old data, which is ON by default. You can change those parameters at any given time after enabling the change tracking.
The following T-SQL enables change tracking with default values:
ALTER DATABASE [DB_Track]
SET CHANGE_TRACKING = ON
Set the change tracking to off using the below T-SQL:
ALTER DATABASE [DB_Track]
SET CHANGE_TRACKING = OFF
Note – you are not allowed to set change tracking to off unless you have disabled all the table for change tracking.
Similarly, you can use SQL Server Management Studio (SSMS) to enable change tracking by following the below steps.
1. Right click the database that you want to enable change tracking on.
2. Select properties from the context menu
3. Select the Change Tracking option (see below screenshot)
You can set the relevant parameters from this page to set the Change Tracking on.
After configuring Change Tracking, you can access that information by querying a system view:
SELECT * FROM
sys.change_tracking_databases
Above query will give information about the entire database which has Change Tracking enabled.
Requirements
· You will need to have at least dbcreator server role to enable Change Tracking.
· Database compatibility should be 90 or greater to enable Change Tracking (you can enable Change Tracking in a database of lesser compatibility but some change tracking functions will return errors.)
· Using snapshot isolation is the easiest way for you to help ensure that all change tracking information is consistent. For this reason, it is recommend that snapshot isolation be set to ON for the database by executing below command. Snapshot isolation specifies that data read by any statement in a transaction will be a consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The overall effect is the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.
ALTER DATABASE [DB_Track]
SET ALLOW_SNAPSHOT_ISOLATION
ON;