When to use SQL Server Change Data Capture

There’s a number of things to consider in deciding on Change Data Capture (CDC) and deciding on the  ideal use cases for CDC is very much dependant on the specific requirements. So it is important to understand in detail the project requirements. This should be weighed up against other solutions such as Triggers

Some initial thoughts for discussion:

For Auditing – CDC  is an appropriate tool , as it can present the complete history of the data. Deciding on whether you’d rather use triggers will depend on reviewing the data in the change table and assessing whether it’s appropriate for your needs

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 interesting aspect of CDC is the change tables are fully available and can be queries for all sorts of purposes – for example , a history log of changes.

CDC versus  Triggers - both method have pros and cons, and there is lots of information around the web about CDC and Triggers. Choosing between the two will require an examination of how you want to manage  auditing data changes . There is a some flexibility around Triggers - for example , turn them on and off during peak loads but at the same time they can be an issue with troubleshooting - as DBAs may not realise they are utilised

 Other Performance considerations

-If you are considering enabling CDC on a high transaction database , serious consideration should be given whether you should be using CDC. I can't give specific numbers but  there should be a detailed impact analysis and criteria for acceptance on Production systems.

-Snapshot Isolation is used which can have a performance impact - including IO overhead

-CDC is not just a switch on and leave it alone feature. CDC requires careful monitoring and managing cleanup jobs

!