Change Data Capture (CDC) in SQL Server 2008

Recently I was working on one project and client required toaudit the data, means, insert / update / delete should be tracked on someimportant table. There are many ways you can capture these information (AuditTrail), I have written quite a few articles on this subject too but all thoseneeds additional code to be written. Fortunately my client is using SQL Server2008 R2 version so I don’t even need to write down any specific code to capturechanged data as there is facility of CDC (Change Data capture) in SQL Server2008.
Even before we look at CDC in details, make sure you haveyour SQL Server Agent is running as CDC in SQL Server 2008 will use SQL ServerAgent to make audit trail for you. If your SQL Server Agent is not running atthe moment, start it from control Panel->Administrative Tools-> Services.You can find “SQL Server Agent (YourInstanceName)”, just start this service andthen 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 inSQLHub database
–this willcreate “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 CDCfor our table created above.
–when you willenable cdc for ChangeDataCapture table
–it will createtwo job under SQL Server Agent
–which willread data from transaction whenever you will make any change in data
–and stores itin 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 haveorginal table, named “ChangeDataCapture”
–another tableto keep all changes “ChangeDataCapture”,
–which has beencreated in step above
select * fromChangeDataCapture
–followingtable will be there under category of “System Tables” folder.
select * from cdc.dbo_ChangeDataCapture_CT
–now let usmake some DML operation in ChangeDataCapture table and
–observe howdoes 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 datain both tables
select * fromChangeDataCapture
–incdc.dbo_ChangeDataCapture_CT, you can see value 2 in _$operation field.
–2 representINSERT.
select * from cdc.dbo_ChangeDataCapture_CT
–see effect ofUPDATE now.
UPDATEChangeDataCapture
SET Name=‘Rajan Jain’ WHERE Name=‘Rajan Shah’
–see the datain both tables
select * fromChangeDataCapture
–incdc.dbo_ChangeDataCapture_CT, you can see value 3 and 4 in _$operation field.
–3 representvalue before UPdate and 4 represent new value after update.
select * from cdc.dbo_ChangeDataCapture_CT
–see effect ofDELETE now
Delete From ChangeDataCapture WHEREID=2
GO
–see the datain both tables
select * fromChangeDataCapture
–incdc.dbo_ChangeDataCapture_CT, you can see value 1 in _$operation field.
–1 representDELETE 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 butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: