Change Tracking and Change Data Capture

SQL Server 2008 introduced 2 features that allow tracking changes to table data, namely Change Tracking and Change Data Capture.

Change Tracking is enabled on database level using the statement

ALTER DATABASE [mydb]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

and on table level using

ALTER TABLE [myTable]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

To query the server which databases are actually tracking-enabled, use the following query

SELECT * FROM [sys].[change_tracking_databases]

The list of tracking-enabled tables can be retrieved by this statement

SELECT * FROM [sys].[change_tracking_tables]

Change Data Capture is enabled using the stored procedure sys.sp_cdc_enable_on:

EXEC sys.sp_cdc_enable_db

If your database editition does not support CDC, you will receive the error:

Msg 22988, Level 16, State 1, Procedure sp_cdc_enable_db, Line 12
This instance of SQL Server is the [***] Edition. Change data capture is only available in the Enterprise, Developer, and Enterprise Evaluation editions.

CDC adds the cdc schema and other database objects

When a database is enabled for change data capture, the cdc schema, cdc user, metadata tables, and other system objects are created for the database.

so the easiest method to detect whether CDC is enabled is to check for the [cdc] schema.

A table can be enabled for CDC using the SP sys.sp_cdc_enabled_table:

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'MyTable',
@role_name     = N'MyRole',
@filegroup_name = N'MyDB_CT',
@supports_net_changes = 1

The parameter @role_name must be provided, but can be NULL. The following parameters are optional.

To find out which tables are CDC-enabled, use the SQL statement

SELECT * FROM [cdc].[change_tables]

Advertisements