SQL Server - How to Monitor and Audit Data Changes in Tables Using Change Data Capture (CDC)

Example:

Example:

Example of use:

Result:

Once CDC is activated on the instance, you can verify that a “cdc” schema will be created in the database in question:

In addition, some system tables were created using the “cdc” scheme:

The tables created by CDC are:

• cdc.captured_columns: This system table will list all columns of the tables that have CDC enabled. This information can also be queried using the sys.sp_cdc_get_source_columns system SP.

  

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-captured-columns-transact-sql

• cdc.change_tables: This system table will list all tables that have CDC enabled. This information can also be queried using the sys.sp_cdc_help_change_data_capture system SP.

  

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-change-tables-transact-sql

• cdc.ddl_history: This system table will store all DDL changes made to tables that have CDC enabled. This information can also be queried using the sys.sp_cdc_get_ddl_history system SP.

  

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-ddl-history-transact-sql

• cdc.index_columns: This table stores the index information associated with tables that have CDC enabled. This information can also be queried using the sys.sp_cdc_help_change_data_capture system SP.

  

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-index-columns-transact-sql

• cdc.lsn_time_mapping: Returns one row for each table transaction with CDC enabled. This table is used to map committed values ​​between the Log Sequence Number (LSN) and the time the transaction is committed. This information can also be retrieved with the sys.fn_cdc_map_lsn_to_time and sys.fn_cdc_map_time_to_lsn functions.

  

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-lsn-time-mapping-transact-sql

Example of use:

Result:

After running SP above, Change Data Capture (CDC) monitoring has been enabled on the desired table. This will create a new table in cdc.schema_tabela_CT format with the same columns as the original table plus some metadata tables for change control.

If you want to track changes to specific columns, not all columns in the table, you can use this syntax:

Now, let's make some changes to the table to see how CDC behaves?

Data Entry

Data update

Data Removal

Truncating Table Data
As you can see from the print below, once you enable CDC on a table, you will not be able to truncate the data in a table.

Table structure change

As you can see, the column __ $ operation allows us to identify what type of operations are performed in the table, in which the possible values ​​are:

• 1: DELETE
• 2: INSERT
• 3: Value BEFORE UPDATE
• 4: Value AFTER UPDATE

Remember to enable database level CDC before attempting to enable CDC on a table. If you do not do this, you will come across this error message:

And these 2 jobs have the following purpose:

• cdc.dirceuresende_capture: Job that runs whenever SQL Server Agent starts and runs System SP sys.sp_MScdc_capture_job, which in turn runs SP sys.sp_cdc_scan, starting table monitoring.

  Reference: https://technet.microsoft.com/en-us/library/cc645591(v=sql.105).aspx

• cdc.dirceuresende_cleanup: Job that runs daily at 02: 00 and is intended to control the size of CDC control tables to prevent them from growing wildly. This job runs System SP sys.sp_MScdc_cleanup_job,
  which in turn runs the SP sys.sp_cdc_cleanup_job_internal.

  Reference: https://technet.microsoft.com/en-us/library/cc645885(v=sql.105).aspx

To that question, the answer is NO. By default, the data will grow until it reaches the default SQL Server limit (4320 minutes = 3 days), which can be a lot or a little, depending on your need.

If you want to change the retention time, you can use the sys.sp_cdc_change_job stored procedure:

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-change-job-transact-sql

The maximum value for this retention parameter is 52494800 minutes (100 years), but if you want to disable this limit, just disable the cleanup job (cdc.dirceuresende_cleanup)

To view the current retention parameters, use the query below:

Result:

Example:

Result:

Please note that when disabling database level CDC, ALL active table level CDC monitoring will also be disabled and historical data will all be lost as well (and you will NOT be changed about the existence of these active table level monitoring). ).

Please note that it is possible to disable CDC at the database level even if there are active table level monitoring (and you will NOT be warned about it). At the end of this topic I left some warnings about what happens when you do this. Read until the end!

Identifying the name of the CDC capture instance:

Result:

Once we have identified the instance name (dbo_Clients), we can now perform the sys.sp_cdc_disable_table to effectively disable CDC in this table:

Result:

After disabling CDC in the table, you may notice that the monitoring table has been automatically deleted. BE CAREFUL with this, so as not to lose the recorded values ​​and lose your history. If you want to disable CDC but do not intend to lose history, copy the data from the history table to another table before disabling CDC in the table.

Please note that when disabling CDC at the database level, ALL active table-level CDC monitoring will also be disabled and historical data will also be lost as well.

Restoring the same database in the same instance
In this situation, the restore will be done normally and the CDC will still be up and running after the base is restored. Nothing changes.

Restoring the backup on the same instance but with another database name or another instance
In either case, the CDC will be disabled and the recorded metadata information will be lost, which would be quite bad. To prevent this from happening, you must use the keep_cdc parameter in the restore command.

Example:

After restore, you will need to run the following commands to recreate CDC jobs: