SQL Server 2008 Features - Data Tracking Solutions

Data Tracking Solutions

SQL Server 2008 introduces two tracking features that enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database.

1.Change Data capture
2.Change Tracking

Before these features were available, custom tracking mechanisms had to be implemented in applications.

Elimiates expensive techniques

Using Triggers,timestamp columns, new tables to store tracking information, and custom cleanup processes.

Build a Data Tracking Solution by Change Data capture

Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed

Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system

We Can provide answers to a variety of key questions

What are ALL the changes that happened between 12:00AM and 12:00PM?

I want to get a transactional consistent set of all changes that happened in Order table and Order_Detail since 12:00PM

Which column changed?

Was the change an insert or an update?

Implementation

CDC enabled at two levels

Database Level - SP_CDC_ENABLE_DB
Individual tables - SP_CDC_ENABLE_TABLE


USE [MyDataBase]
GO
EXEC sys.sp_cdc_enable_db_change_data_capture
GO


The following CDC tables are created under the CDC schema,

cdc.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping