You would have heard about this one. Well it was introduced first time with the arrival of SQL Server CDC in 2008.
I have noted down some keynotes about Change Data Capture (CDC), so please follow it carefully:
- CDC can be used to capture insertions, updates, and deletes in an SQL table in a database and place the changes in another table.
- The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.
- Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server 2008.
- The following code will let you know, how to configure CDC for auditing of a table in a database:
First, you have to enable the Change Data Capture for a database.
Before a capture instance can be created for individual tables, a member of the sysadmin fixed server role must first enable the database for change data capture. Therefore, you the following stored procedure:
USE TEST EXEC sys.sp_cdc_enable_db
We can also see from the result set:
Now, it’s time to enable the CDC on the table. So execute the following stored procedure:
EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'myTable', @role_name = 'cdc'
The result can be seen as:
- To read the data from the CDC table, there are a series of system stored procedures and functions available, or you can query the tables directly.
System Stored Procedures:
- sys.sp_cdc_change_ job
- sys.sp_cdc_help_ jobs
- sys.sp_cdc_drop_ job
- sys.sp_cdc_start_ job
- sys.sp_cdc_stop_ job
You can also be able to view the templates for Change Data Capture in a Template Explorer-
Well this was about How to Configure Change Data Capture.
And also comments on this!!