SQL Server Change Data Capture (CDC)

Introduction

"Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred." -MSDN Article

Introduction
Real world Scenario.
Performance.
System Requirements
Change Data Capture Step-by-Step

Real world Scenario.

There are two scenario where Change Data Capture can play an important part:
      1. For data auditing
      2. For Use with Data warehousing

In the first scenario CDC can be very useful in tracking data changes for auditing purposes.
A concrete example for this is a bank application where a depositor would query
what acqually happens to his account  and how his account has change
or in a much simplier word a historical view of his count.

Another scenario where CDC can play an important part is an accounting application
where dispute may usually happen. Any application that would require an immediete access to the
tail-end part of the transactionwould be a great candidate for an immediete audit requirements.

CDC however is design for a short time audit because it has a retention period. However you can disable
retention and cleansing of cdc data tohave a beginning to date audit. The default retention is 3 days.

The second scenario where CDC can play an important part is in datawarehousing. CDC table can be used as data source
for the ETL rather than the actual OLTP this is due to the fact that CDC can contain a more historical data as compared
to the regular OLTP tables. Another good reason is that you can do the ETL without disturbing the base tables.

Performance.

The strongest selling point of CDC is performance. As you can see from the diagram below.
CDC tables are taken directly from the database log without disturbing your OLTP tables.
The downside of course is that the logs can not be trimmed unless CDC has completed its task.
Although CDC is make use of transaction logs, database are not required to be in full recovery mode.

Figure 1. CDC Architecture. Diagram courtesy of MSDN

System Requirements

CDC work on the following version of SQL server
1. enterprise edition
2. Developer edition

Change Data Capture Step-by-Step

Step #1. Create a Database to be enabled in Step 2

Create database cdctestdb

Step #2. Enable CDC on the database

use cdctestdb
exec sys.sp_cdc_enable_db

Step #3. Verify that Cdc is enabled on the database

select name, is_cdc_enabled from sys.databases

Step #4. Create a Table

CREATE TABLE [dbo].[students](
[student_id] [int] IDENTITY(1,1) NOT NULL,
[lastname] [varchar](50) NULL,
[firstname] [nchar](50) NULL,
[middle_name] [nchar](50) NULL,
CONSTRAINT [PK_students] PRIMARY KEY CLUSTERED
(
[student_id] ASC
))

Step #4. Enable CDC on the Table

exec sys.sp_cdc_enable_table
@source_schema=N'dbo',
@source_name=N'students',
@role_name=NULL

Step #5. Verify that CDC is enabled in the table

select name, is_cdc_enabled from sys.databases
where is_tracked_by_cdc=1

Step #6. Insert records

insert into students (lastname,firstname,middle_name)
values
('student1ln','student1fn','student1mn'),
('student2ln','student2fn','student2mn'),
('student3ln','student3fn','student3mn')

Step #7. Check if the inserted record are being tracked