Should I use SQL Change Data Capture for data audit ?

Question: There is lots of information around the SQL Server Change Data Capture (CDC) feature, and I’m trying to decide if CDC is suitable for the purposes of a project which requires data auditing.  

We currently have a sql server triggers based solution in place for capturing data changes on certain tables.

Could you outline some the issues to consider when deciding if CDC is an appropriate tool.

Answer: Deciding on when to adopt CDC is dependant on the specific requirements. The first step is to understand in detail the project requirements. As you mentioned in your question, there are alternatives to capturing data changes e.g SQL Server triggers.  

For Auditing purposes – CDC is an appropriate tool , as CDC can record the complete history of the data. The complete history of the data can then be consumed by different applications.

The question that normally arises when discussing CDC is how does CDC compare to the SQL Server triggers approach. A good starting point is to run a POC and assess the data in the change table created by CDC. Review the data and decide on whether it is appropriate for your organisation.

If the data is not suitable – complete a comparison with the data produced on the SQL Server Triggers comparison .

Should CDC be used on a Production database and then utilise the change data as part of an application? It’s OK to utilise CDC change data for end use , but it isn’t real – time data and there can be a lag from extracting the data from the transaction logs .

There are numerous ways you can utilise change data generated from CDC. CDC records every change. A common method is to use the data in an ETL to a datawarehouse – as opposed to a bulk copy method – which you may see in a scenario where there is a nightly back and restore of the source database.

An useful characteristics of CDC is the change tables are fully available and can be queried for all sorts of purposes – for example , a history log of changes.

Some other things to consider is performance- as the concurrency control Snapshot Isolation is used which has a performance impact – including IO overhead.

These are some of the issues to consider when deciding on CDC. Quite often , particularly if it’s a customers first experience with CDC , it is valuable to set up a demo and introduce the CDC change tables and workflow .

Read more on change data capture and data audit

How to Audit user DML with SQL Server Extended Events

SQL Server Performance Checklist - SQL Server DBA