Follow sqlserver-dba.com

Daily & Exclusive Content

Delivered by FeedBurner

SQLServer-DBA.com Links

Dba_db2_button

Powered by TypePad
Change Data Capture

04 May,2018 by Jack Vamvas

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

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Change Data Capture


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer