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

Views: 4.222
This post is the 9 part of 16 in the series. Security and Audit
Reading Time: 8 minutes

Hello everybody!
In this post, I will show you how to monitor and audit data changes to tables using Change Data Capture (CDC) in SQL Server. I had already written the post SQL Server - How to Create a Data Change History for Your Tables (Audit Logs), which uses the table triggers feature to accomplish this task, but would like to show a more robust (and less manual) solution, which has been available since SQL Server 2008 (Enterprise, Developer and Trial - Thanks for the tip, Fabio Colli) and as of SQL Server 2016 SP1, it is also available in the Standard edition.

Widely used in tables that change and need audits and change logs, CDC allows you to create a history of data changes made to a table, all automatically and without having to create triggers or anything to that effect. With this great feature we can identify data insertions, changes (data before and after update) and data deleted from the table as well as structure changes (DDL) made to the monitored tables.

Once this feature is enabled on a table, a new mirror table will be created, with the same columns as the original table and some more metadata columns to control the change made to the original table. This new table, which will be created automatically, accepts normal queries (SELECT) on your data. It is noteworthy that monitoring remains active even after restarting the service.

Change Data Capture (CDC) has a low impact on the database because it works through a SQL Agent job (one job for each database that has CDC enabled) that reads the transaction log asynchronously and stores changes in the history table. For this reason, the database recovery model is automatically changed to FULL if it is in SIMPLE.

Which databases have active CDC?

View content
To check which databases in your instance have Change Data Capture (CDC) enabled, you can refer to the system view information. sys.databases.

Example:

What tables are being monitored with CDC?

View content
To check which tables in a given database have the CDC feature enabled, simply look up the sys.tables system view, as shown below:

Example:

How to enable CDC on a database (1 Level)

View content
Change Data Capture (CDC) control is done at the database level. To enable CDC, you will use the system Stored Procedure sys.sp_cdc_enable_db.

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:

How to enable CDC and monitor table changes (2 Level)

View content
To start table monitoring and start storing data change history (DML) and structure (DDL), you will need to use system SP sys.sp_cdc_enable_table.

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:

Msg 22901, Level 16, State 1, Procedure sp_cdc_enable_table, Line 39 [Batch Start Line 2] The database 'dirceuresende' is not enabled for Change Data Capture. Ensure that the correct database context is set and retry the operation. To report on the databases enabled for Change Data Capture, query the is_cdc_enabled column in the sys.databases catalog view.

What jobs are created by CDC?

View content
As you can see in the topic “How to activate CDC and monitor changes in tables”, when activating CDC in a table, 2 jobs were created automatically (if you activate CDC in other tables, the same 2 jobs will continue to be used, that is, 2 jobs are created per monitored database and not per table):

And these 2 jobs have the following purpose:

How to disable CDC on a database (1 Level)

View content
If you want to disable CDC from a database, just use system SP sys.sp_cdc_disable_db.

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). ).

How to disable CDC on a table (2 Level)

View content
To disable CDC from a specific table, you first need to identify the name of the CDC capture instance using SP sys.sp_cdc_help_change_data_capture or by consulting cdc.change_tables, then disable monitoring with SP sys.sp_cdc_disable_table.

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.

Change Data Capture (CDC) and Backup / Restore Operations

View content
Because CDC is a built-in feature of SQL Server that creates metadata and jobs to perform some activities, operations such as backup / restore may not behave exactly as expected in certain situations.

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:

That's it folks!
I hope you enjoyed this post and that it can be helpful to you!

A hug and see you next!