Change Data Capture (CDC) in SQL Server 2008

Recently I was working on one project and client required to audit the data, means, insert / update / delete should be tracked on some important table. There are many ways you can capture these information (Audit Trail), I have written quite a few articles on this subject too but all those needs additional code to be written. Fortunately my client is using SQL Server 2008 R2 version so I don’t even need to write down any specific code to capture changed data as there is facility of CDC (Change Data capture) in SQL Server 2008.
Even before we look at CDC in details, make sure you have your SQL Server Agent is running as CDC in SQL Server 2008 will use SQL Server Agent to make audit trail for you. If your SQL Server Agent is not running at the moment, start it from control Panel->Administrative Tools-> Services. You can find “SQL Server Agent (YourInstanceName)”, just start this service and then you will be able to work on CDC.
create database SQLHub
GO
use SQLHub
go
Create Table ChangeDataCapture
(
      ID INT Identity(1,1)
      ,Name varchar(20)
)
GO
–enable CDC in SQLHub database
–this will create “cdc” schema in SQLhub database too
–along with “cdc” schema, it will create some system table
–in “cdc” schema
USE SQLHub
GO
EXEC sys.sp_cdc_enable_db
GO
–now enable CDC for our table created above.
–when you will enable cdc for ChangeDataCapture table
–it will create two job under SQL Server Agent
–which will read data from transaction whenever you will make any change in data
–and stores it in CDC table
USE SQLHub
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N’dbo’,
@source_name   = N’ChangeDataCapture’,
@role_name     = NULL
GO
–now you have orginal table, named “ChangeDataCapture”
–another table to keep all changes “ChangeDataCapture”,
–which has been created in step above
select * fromChangeDataCapture
–following table will be there under category of “System Tables” folder.
select * from cdc.dbo_ChangeDataCapture_CT
–now let us make some DML operation in ChangeDataCapture table and
–observe how does it stores data in cdc.dbo_ChangeDataCapture_CT
INSERT INTO ChangeDataCapture
SELECT ‘Ritesh Shah’ UNION ALL
SELECT ‘Rajan Shah’ UNION ALL
SELECT ‘Teerth Shah’
GO
–see the data in both tables
select * fromChangeDataCapture
–in cdc.dbo_ChangeDataCapture_CT, you can see value 2 in _$operation field.
–2 represent INSERT.
select * from cdc.dbo_ChangeDataCapture_CT
–see effect of UPDATE now.
UPDATEChangeDataCapture
SET Name=‘Rajan Jain’ WHERE Name=‘Rajan Shah’
–see the data in both tables
select * fromChangeDataCapture
–in cdc.dbo_ChangeDataCapture_CT, you can see value 3 and 4 in _$operation field.
–3 represent value before UPdate and 4 represent new value after update.
select * from cdc.dbo_ChangeDataCapture_CT
–see effect of DELETE now
Delete From ChangeDataCapture WHEREID=2
GO
–see the data in both tables
select * fromChangeDataCapture
–in cdc.dbo_ChangeDataCapture_CT, you can see value 1 in _$operation field.
–1 represent DELETE operation
select * from cdc.dbo_ChangeDataCapture_CT
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.