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:

1_SQL_Server_How_to_Configure_Change_Data_Capture_(CDC)

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:

2_SQL_Server_How_to_Configure_Change_Data_Capture_(CDC)

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

3_SQL_Server_How_to_Configure_Change_Data_Capture_(CDC)

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

3 Comments on “SQL Server CDC – How to Configure Change Data Capture”

  1. when sys.sp_cdc_enable_db apply CDc on any datbase then each time new two job is created.If there 20 datbase then 20 job will created .Is there any shortcut or any alternate way so one single job will for 20 datbases?

  2. Hai,

    i have two tables. these are primary key to foreign relation.Db has ‘CDC’ how to capture the dml(insert,update,delete) operations.

    give me example.

  3. Hi,

    I’m wondering if data for CDC get purged? eg.

    USE AdventureWorks
    GO
    SELECT *
    FROM HumanResources.Shift
    GO
    USE AdventureWorks
    GO
    SELECT *
    FROM cdc.HumanResources_Shift_CT
    GO

    Would cdc.HumanResources_Shift_CT get purged after a while? If so where do we set how long we keep data?

    Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *