In an earlier post, I went over the different options that SQL Server provides for tracking changes to data. This post will go a little more in depth with Change Data Capture. Enabling CDC will allow you to track any additions, removals, or updates to a specific table. You can set a retention period to keep the change data for a certain period of time.
I’ve posted this script to GitHub to go over the specifics of setting up tracking for a table.
You’ll need the SQL Agent running to record any changes. You’ll need to enable CDC for the database:
We’ll also need to enable and configure the capture for a specific table.
EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'CdcTest', @role_name = NULL;
Once CDC is enabled for a table, a copy of the table is made in the CDC schema, with ‘_CT’ appended to the end of the name. So, for my example table dbo.CdcTest, the capture table cdc.dbo_CdcTest_CT is created.
The CT table will track Deletes, Inserts and Updates. For Updates, we’ll see the state of the record before and after the Update. You can query the CT table, but it’s advised to use a table-valued function to return the results. Two functions will be created for each table enabled for CDC, one to get all changes (cdc.fn_cdc_get_all_changes) and one to get net changes. In our sample case:
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_CdcTest (0x0000002A000015170003, 0x0000002A000015170003, 'all');
The first two values in the function are the start and end LSN(Log Sequence Number) from the CT table. We can also use the LSNs to join to the cdc.lsn_time_mapping table to get the time range that operations took place.
CDC is a good option to choose for data auditing. Temporal tables are still a better choice if we need to perform queries to see data as it was at a certain point in time, but CDC lets us track changes made to a specified table.