Change data capture (CDC) in SQL Server 2008

Introduction
Change data capture or (CDC) is very helpful feature in SQL Server 2008
Change Data Capture records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables, and makes a record available of what changed, where, and when, in simple relational ‘change table” The SQL Server DBA can then easily monitor the activity for the logged table using these new audit tables .(Really i love this feature very nice microsoft)
Enabling Change Data Capture
CDC Enabling have two steps one on Database level and one on Tables level before Enabling CDC you must be Check CDC is enabled in any Database so Run the Query to check whether it is enabled for any database.

USE master
GO
SELECT [name], database_id, is_cdc_enabled
FROM sys.databases
GO

After you run this script you will see your all database name and id and you will know  from this column  (IS_CDC_ENABLED) if CDC  feature is enabled in any Database or not enabled  (is_cdc_enabled = 0 ) that’s mean the CDC not enabled in this Database (is_cdc_enabled = 1) that’s mean the CDC is enabled in this Database
Know lets Create Database and tables To Enable the CDC

1.Create Database CDC
Create Database CDC
GO
2- Create Table
use CDC
go
2-Create Table Employee
(Emp_ID int Primary Key, Emp_Name Nvarchar(50),Emp_Job Nvarchar(50), Emp_Salary bigint )
GO
USE CDC
INSERT INTO dbo.Employee Values
(1,’Mostafa Elmasry’ , ‘DBA’ , ‘1000’),
(2,’Mohamed Omar’ , ‘Web Developer’ , ‘1500’),
(3,’Mohamed Osman’ , ‘Web Developer’ , ‘2000’)

Enabling Change Data Capture on a Database

USE CDC
GO
EXEC sys.sp_cdc_enable_db
GO

After run this Script the SQL Server will create five tables in Database CDC under System tables this table’s help you to know some Info about CDC in your database and Tables. Let’s See How

cdc.captured_columns (This table returns result for list of captured column)
-cdc.change_tables (This table returns list of all the tables which are enabled for capture)
-cdc.ddl_history (This table contains history of all the DDL changes since capture data enabled)
-cdc.index_columns (This table contains indexes associated with change table)
-cdc.lsn_time_mapping (This table maps LSN number (for which we will learn later) and time.)

Enabling Change Data Capture on one or more Database Tables

Before this steps must be known CDC is enabled in any tables in Database CDC or no let’s see how to know this information

USE CDC
GO
SELECT [name], is_tracked_by_cdc
FROM sys.tables
GO

the result will be rwo column name (all tables name in database CDC) , is_tracked_by_cdc ( if this column = 0 that,s mean the CDC feature not enable in this table else CDC feature is enabled in this table name )

Enable CDC on table name Employee
—Syntax   (ياريت نركز شويه فى الخطوه دى لانها مهمه جدا)

USE Database_name
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N’Schema_Name’, —- Mandatory
@source_name = N’Table_Name’,    —- Mandatory
@role_name = N’Role_Name’or NULL, —- Mandatory
@supports_net_changes = 0 or 1,
@capture_instance = N’CDC Instance Name’,
@index_name = Null or Index_Name,
@captured_column_ list = Column_Name,
@filegroup_name = FileGroup_name ,
@partition_switch = TRUE or FALSE
GO

•@source_schema is the schema name of the table that you want to enable for CDC
•@source_name is the table name that you want to enable for CDC
•@role_name is a database role which will be used to determine whether a user can access the CDC data; the role will be created if it doesn’t exist. You can add users to this role as required; you only need to add users that aren’t already members of the db_owner fixed database role.
•@supports_net_changes determines whether you can summarize multiple changes into a single change record; set to 1 to allow, 0 otherwise.
•@capture_instance is a name that you assign to this particular CDC instance; you can have up two instances for a given table.
•@index_name is the name of a unique index to use to identify rows in the source table; you can specify NULL if the source table has a primary key.
•@captured_column_list is a comma-separated list of column names that you want to enable for CDC; you can specify NULL to enable all columns.
•@filegroup_name allows you to specify the FILEGROUP to be used to store the CDC change tables.
•@partition_switch allows you to specify whether the ALTER TABLE SWITCH PARTITION command is allowed; i.e. allowing you to enable partitioning (TRUE or FALSE).

Now before try to enabled CDC on Table name ‘Employee’ Must be Check your SQL Server Agent is enabled because after you run the script The SQL Server will Create 2 jobs in your SQL Agent and SQl Server will create new table on your database CDC under System Tables this table name will be ‘@capture_instance+CT’ = ‘cdc.Employee_CDC_CT’ run the Script to enable CDC on table Employee and then check the jobs and new table. (خليك فاكر الكلمتين دول كويس)

USE CDC
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N’dbo’,
@source_name   = N’Employee’,
@role_name     = NULL ,
@capture_instance = ‘Employee_CDC’
GO

Now go to your SQL server agent tocheck the two jobs you will see

1.cdc.AdventureWorks_capture
When this job is executed it runs the system stored procedure sys.sp_MScdc_capture_job. The procedure sys.sp_cdc_scan is called internally by sys.sp_MScdc_capture_job. This procedure cannot be executed explicitly when a change data capture log scan operation is already active or when the database is enabled for transactional replication. This system SP enables SQL Server Agent, which in facts enable Change Data Capture feature.
2.cdc.AdventureWorks_cleanup
When this job is executed it runs the system stored procedure sys.sp_MScdc_cleanup_job. This system SP cleans up database changes tables .
then now go to CDC database > tables > system tables > you will see new table name ‘cdc.Employee_CDC_CT’ if you change the @capture_intance in your script (Enable CDC on table) when you enable the CDC  if you change it from ‘Employee_CDC’ to ‘Emp’ the table name will be ‘cdc.Emp_CT’ instead of ‘cdc.Employee_CDC_CT’

Now let’s Check the New table in System tables you will see there are five additional columns into the mirrored original table (cdc.Employee_CDC_CT)
• __$start_lsn
• __$end_lsn
• __$seqval
• __$operation
• __$update_mask
There are two Column which are very important to us is __$operation and __$update_mask
Column _$operation contains value which corresponds to DML Operations Following is quick list of value and its corresponding meaning.
• Delete Statement = 1
• Insert Statement = 2
• Value before Update Statement = 3
• Value after Update Statement = 4
The column _$update_mask I will explain it after the example no one (لاتقلق كله جااااى فى الطريق)

Example of Change Data Capture

USE CDC
GO
SELECT *
FROM dbo.Employee
GO
USE CDC
GO
SELECT *
FROM cdc.Employee_CDC_CT
GO

You will see 3 rows in The original Table Employee and no data in table CDC.Employee_CDC_CT, whereas the table CDC.Employee_CDC_CT is totally empty. This table will have entries after an operation on the tracked table

Insert Operation

use CDC
INSERT INTO dbo.Employee Values
(4,’khaled Jmael’ , ‘IT’ , ‘3000’)
Go

Now try to check the table name ‘CDC.Employe_cdc_CT’ you will see

Update Operation

USE CDC
UPDATE DBO.Employee SET Emp_Job = ‘Technical Support’ where Emp_ID = 1
GO

Check now cdc.Employee_cdc_CT you will see the value before update and after update let’s see

Delete operation

USE CDC
DELETE FROM DBO.Employee WHERE Emp_ID = 1
GO

Check now cdc.Employee_cdc_CT you will see the value deleted from dbo.Employee

The summary
To Enable Change Data Capture (CDC) Feature in SQL server 2008 :
1- Check if CDC feature is enabled in your database or no .
2- Enable CDC feature in your database . (if not enabled)
3- Check the five new table under system tables in your database .
4- Check in if CDC enabled in any tables in your database or no .
5- Check the SQL Server Agent is Enabled.
6- Enable CDC feature  on your table/s (if not enabled) .
7- Check the two jobs in your SQL server Agent , Check the new table under the system table
8- Insert data in the oraginal table .
9- Update Data in the original table .
10- Delete data from the orignal table .
in the finale that’s very very help to all DBA that’s my opnion
in the next blog i will explain How to Enable the CDC feature in Some column in Table .
Best Regardes
Mostafa Elmasry