SQL Server Change Data Capture is a feature that reads historical data from the SQL Server transaction logs and stores them in a special table.
Through the use of special table value functions (TVF) it then allows the user to query this data, making it either possible to get all the changes on a specific table or only the net changes that resulted from the changes within a specific time.
CDC has certain advantages
- It can be configured to only track certain tables or columns.
- It is able to handle model changes to a certain degree.
- It does not affect performance as heavily as triggers because it works with the transaction logs.
- It is easily enabled/disabled and does not require additional columns on the table that should be tracked.
It also has some disadvantages:
- The amount of history data can become huge fast.
- You are not able to track who made the changes (at least not for deletes).
- The history data takes some time to catch up, because it is based on the transaction logs.
- It depends on the SQL Server Agent. If the Agent is not running or crashes, no history is being tracked.
I have read quite a lot about CDC and while I know now how to use it, I am still not sure if it is the right tool for me.
- For which tasks/scenarios is CDC the right tool? (e.g. Allowing users to restore a data object to a certain point in time? Auditing? Showing the complete history of data?)
- When should you rather not use CDC, but resort to a custom trigger-based solution?
- Is it ok to use CDC in an operational database and make use of the CDC data within an operational application? (e.g. showing it to the end user) Or is this clearly a misuse of this feature?
I commonly hear that CDC is an audit tool, but isnt that what SQL Server Audit is for? Are they both different tools for the same task? Or can CDC used for other things?
My current scenario is that I am asked to build a reliable data framework which is supposed to be the basis for multiple future applications. The exact requirements are blurry, but one is that it should be able to track data history and restore older entries together with all related data from other tables. I am evaluating CDC right now as an option, but am uncertain if this is the way to go, because I can't really find any recommended use cases.
While I appreciate advice for my specific scenario, answers should give general advice about when or when not to use Change Data Capture.