SQL Server CDC – How to Configure Change Data Capture

Hello Folks,

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_add_job
• sys.sp_cdc_generate_wrapper_function
• sys.sp_cdc_change_ job
• sys.sp_cdc_get_captured_columns
• sys.sp_cdc_cleanup_change_table
• sys.sp_cdc_get_ddl_history
• sys.sp_cdc_disable_db
• sys.sp_cdc_help_change_data_capture
• sys.sp_cdc_disable_table
• sys.sp_cdc_help_ jobs
• sys.sp_cdc_drop_ job
• sys.sp_cdc_scan
• sys.sp_cdc_enable_db
• sys.sp_cdc_start_ job
• sys.sp_cdc_enable_table
• sys.sp_cdc_stop_ job

System Functions:

• cdc.fn_cdc_get_all_changes_<capture_instance>
• sys.fn_cdc_has_column_changed
• cdc.fn_cdc_get_net_changes_<capture_instance>
• sys.fn_cdc_increment_lsn
• sys.fn_cdc_decrement_lsn
• sys.fn_cdc_is_bit_set
• sys.fn_cdc_get_column_ordinal
• sys.fn_cdc_map_lsn_to_time
• sys.fn_cdc_get_max_lsn
• sys.fn_cdc_map_time_to_lsn
• sys.fn_cdc_get_min_lsn

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!!

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook