Before reading this article, I highly recommend reading my previous parts:

Here, we will go deeper and understand few more commands.

When we enable CDC on a table, it will create two SQL Agent jobs for each database:

SQL Agent

  1. Capture job is used to read transaction log and populates change tables with audit details.
  2. Cleanup is used to remove entries from change tables after a specified retention period.

We can find configuration settings of these jobs using SP sys.sp_cdc_help_jobs and the same can be modified by sys.sp_cdc_change_job:

Query output

We can use sys.sp_cdc_help_change_data_capture to retrieve information like capture columns, file group name etc. of each CDC enabled table in a database.

sys.sp_cdc_get_captured_columns used to get column details like name, data type etc. of a CDC enabled table.

Result

DDL modifications on a source table, like adding or dropping a column or changing the data type, are maintained in the cdc.ddl_history table and can be retrieved by sys.sp_cdc_get_ddl_historyas shown below:
dbo

This table will contain source_table name and ddl statement fired on it. Any DDL changes on a table will not be captured in CDC tables automatically. We need to manually create one more/re-create capture instance for tracking newly added columns using SP sp_cdc_enable_table.

We can use sys.sp_cdc_cleanup_change_table to remove rows from change table by passing capture instance and LSN value.

To restore database with change data capture tables, we can use the KEEP_CDC option when restoring the database.

RESTOREDATABASEEmployeeDBFROMDISK=N'c:\SQLServerBackups\test.Bak'WITHFILE=1,
NOUNLOAD,STATS=5,REPLACE,KEEP_CDC

We can restore the database on SQL Server enterprise edition only, if not we will get an error while restoring it.

Tips:

  1. As per CDC design, we can’t keep CDC tables in a different database. So, it’s recommended to store those in separate file group using option @filegroup_name.

  2. We can’t track user ID, by whom changes are done. Alternatively, we can add extra column like ModifiedBy to each table and update that field from UI.

  3. Instead of auditing complete table, audit only required columns using option @captured_column_list.

By using CDC, we can do auditing/change tracking on tables. It is a very useful tool to audit all inserts\updates\deletes at table level.

I am ending things here on CDC, I hope this article will be helpful for all.

Read more articles on SQL Server: