SQL Server – Change Data Capture (CDC) for Auditing
Change Data Capture (CDC) is a new feature in SQL Server 2008 which records insert, update and delete activity in SQL Server tables.
CDC is intended to capture Insert, Update and Delete activity on a SQL table and place the information into a separate relational table. It uses an asynchronous capture mechanism that reads the transaction logs and populates the CDC table with the row’s data which change. The CDC table mirrors the column structure of the tracked table, together with metadata regarding the change.
To use the CDC feature, first we have to enable it database level.
Steps to enable CDC on database level:
– Create Database
Create database SampleCDC;
–Create Table
use SampleCDC;
create table Salary
(
EmployeeID int Identity(1,1) NOT NULL,
EmployeeName varchar(50),
Salary int
);
Alter table Salary add constraint pk_Salary PRIMARY KEY (EmployeeID);
–Insert Records into Table
insert into Salary values(‘Ishtiyaq’,’9000′),(‘MJ’,’11000′),(‘HD’,’500′);
– View Records in Table
select * from Salary;
To Enable CDC on Database execute the below query
–Enable CDC on database
Use SampleCDC;
exec sp_cdc_enable_db ;
|
CDC feature can be applied at the table-level to any CDC enabled database.
- You must have database owner permission (db_owner fixed role)
- SQL Agent Service must be running
Enable CDC on Table:
–Enable CDC on Table
use SampleCDC;
exec sys.sp_cdc_enable_table
@source_schema =N’dbo’,
@source_name=N’Salary’,
@role_name=NULL;
|
Jobs Details
cdc.SampleCDC_Capture – Capture the changes by doing log scan
cdc.SampleCDC_Cleanup – Cleanup the database changes tables
Once the above query executes successfully, it will create 1 more system table cdc.dbo.Salary_CT for the tracking purpose.