For an instructor lead, in-depth look at learning SQL click below.
Change Data Capture, also known as CDC, is a powerful feature of SQL Server that allows for the tracking and capturing of changes made to table data in a database without a need for any additional programming. First introduced in SQL Server 2008, the CDC tools can be a boon to those needing to monitor and audit their data.
How Change Data Capture Works
CDC uses the native SQL Server transaction logs to track changes. Every amend, insert, or delete operation made to a CDC-enabled table is read from the transaction log and then saved to a specially created change table which reflects the original table layout with a few additional metadata columns.
Enabling CDC on SQL Server
To enable CDC on a database, we first use the ‘sys.sp_cdc_enable_db’ stored procedure. Once the database is CDC enabled, we can then apply CDC to any database table with the ‘sys.sp_cdc_enable_table’ stored procedure.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Enabling CDC at the database level EXEC sys.sp_cdc_enable_db; -- Enabling CDC for a specific table EXEC s<a href="mailto:ys.sp_cdc_enable_table @source_schema" >ys.sp_cdc_enable_table @source_schema</a> = N'dbo', @source_name = N'YourTable', @role_name = NULL; |
Remember to replace ‘YourTable’ with the title of the table you want to enable CDC for. The changes will now be recorded in a separate, system generated table.
Viewing Changes with CDC
After enabling CDC, it’s time to track the changes. For this, SQL Server provides the cdc.fn_cdc_get_all_changes function. This function will return all the changes made to a table since the specified timestamp.
|
1 2 3 4 5 6 7 |
-- Fetching changes with CDC DECLARE @from_lsn binary(10), @to_lsn binary(10) SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_YourTable') SET @to_lsn = sys.fn_cdc_get_max_lsn() SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_YourTable(@from_lsn, @to_lsn, 'all'); |
The above code fetches all changes. Replace ‘dbo_YourTable’ with the title of the CDC-enabled table.
Disabling CDC on SQL Server
To disable CDC, SQL Server provides the ‘sys.sp_cdc_disable_table’ and ‘sys.sp_cdc_disable_db’ stored procedures for tables and databases, respectively.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Disabling CDC for a specific table EXEC s<a href="mailto:ys.sp_cdc_disable_table @source_schema" >ys.sp_cdc_disable_table @source_schema</a> = N'dbo', @source_name = N'YourTable', @capture_instance = N'dbo_YourTable'; -- Disabling CDC for a database EXEC sys.sp_cdc_disable_db; |
Again, replace ‘YourTable’ with your table title.
Conclusion
These are the basic steps to working with Change Data Capture in SQL Server. Recollect, CDC is a very strong tool when used properly. It allows for easy tracking and auditing of data changes to help maintain the integrity of your data.