RSS

Database Audit with Change Data Capture (CDC) in SQL Server 2012

23 Apr

There are two feature in SQL Server 2008 (and above) to track data changes, Change Tracking (CT) and Change Data Capture (CDC).

Tracked Data: DML changes, DML type, columns, historical data (only in CDC).

Difference: CT doesn’t know the old and new value of the data being changed.

DML Operations:

  • 1 = delete
  • 2 = insert
  • 3 = update (before image)
  • 4 = update (after image)

Objects: The following are objects (system tables, stored procedures – sp, or functions – fn) used in CDC (Change Data Capture)

Object Description
sys.sp_cdc_enable_db Stored procedure to disable CDC in a specified database.
sys.sp_cdc_disable_db Stored procedure to disable CDC in a specified database.
sys.sp_cdc_enable_table Stored procedure to enable CDC in a specified table.
sys.sp_cdc_disable_table Stored procedure to disable CDC in a specified table.
cdc.fn_cdc_get_all_changes_<capture_instance> Function to return all changes that occurred for the specified interval.
cdc.fn_cdc_get_net_changes_<capture_instance> Function to return one change per modified source table row.
sys.fn_cdc_get_max_lsn Function to return the maximum log sequence number (LSN) from the start_lsn column in the cdc.lsn_time_mapping system table.
sys.fn_cdc_get_min_lsn Function to returns the start_lsn column value for the specified capture instance from the cdc.change_tables system table.
cdc.lsn_time_mapping System table to store one row for each transaction having rows in a change table. This table contains commit time for each data changes.
cdc.change_tables System table to store one row for each change table in the database.
sys.dm_tran_database_transactions System table to store information about transactions at the database level.

Source: MSDN

Advertisements
 
1 Comment

Posted by on April 23, 2012 in General

 

Tags:

One response to “Database Audit with Change Data Capture (CDC) in SQL Server 2012

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

 
%d bloggers like this: