29

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:

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.

  1. 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?)
  2. When should you rather not use CDC, but resort to a custom trigger-based solution?
  3. 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.

Improve this question
5
  • 1
    Ideally, a "framework" wouldn't make this kind of decision; it would be left to individual projects. But since you're being asked to do this, I would at least make that point to whomever is giving you these requirements: there are different ways to accomplish this, and the best choice is heavily dependent on the exact usage and needs. Ask if they can give you any clarification that might help you decide (like whether performance or flexibility is more important). Another option to consider is to develop both options as part of the "framework" and let real projects choose which one to enable.
    – jpmc26
    Commented Mar 27, 2014 at 18:50
  • @jpmc26, the framework may be needed to stop each project spending time deciding this sort of question.
    – Ian Ringrose
    Commented Apr 1, 2014 at 10:48
  • @IanRingrose My point is that trying to make that decision without considering the specific needs of a project will, in the long run, cause more problems than it solves (and thus actually be more costly than spending that time). This is a decision that can't be effectively made in a general case. The specifics of the project must be considered. Using a blanket decision, time will be spent using the chosen solution and making assumptions around it only for those assumptions to be violated when it's discovered it wasn't an appropriate solution. Then the system will need to be redesigned.
    – jpmc26
    Commented Apr 1, 2014 at 13:35
  • 1
    @jpmc26 I might actually go with the solution you proposed, in case I find a way to pull it off: Developing both trigger-based and CDC-based history tracking, switchable and behind a common interface. Applications then can pick one or the other, depending on their requirements, but don't have to worry about implementing it themselves. Of course I would still like to get a good answer to my above question, because if CDC is not cut out for this kind of task anyway (e.g. because it is only any good for auditing) I might save myself the trouble and always use triggers.
    – magnattic
    Commented Apr 1, 2014 at 22:13
  • "If the Agent is not running or crashes, no history is being tracked" - but if it was restarted, no changes would be lost, right?
    – Andy Joiner
    Commented Dec 13, 2016 at 12:01

4 Answers 4

Reset to default
13

Firstly,

Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.

So that may decide for you if any of your customers will not have the enterprise editions, or you don't yet know you will be using the enterprise editions. (As the spec includes "multiple future applications" this may be an real issue for you)

Unlike triggers it is not real time, this is both an advantage and a disadvantage. Using triggers always slow down an update.

I worked on one system when we used triggers (generated by CodeSmith), as well as tracking all the changes to the records, we also linked the changes together to a “history” table that included the module of the application that made the change, and the UI item the user used to make the change.

However you may be best solving this at the application level, by say writing all update to a message queue that is then replayed to create a database at any given point of time, see Temporal Patterns on Martin Flowler blog for a good overview of options.

Improve this answer
5
  • The link is a very interesting read, thanks for that. Still, solving this at application level is not an option in my case. The framework I am building is supposed to do most of the work, including history tracking, for the applications based on it. The applications are then working with a common interface to store/retrieve data, so that they don't have to care about how the data is stored. I am aware that this task is far from trivial.
    – magnattic
    Commented Apr 1, 2014 at 21:56
  • Also, I am currently not considering Enterprise Edition or not to be a deciding factor in our case. The future applications I am talking about will most likely all be built and hosted by us.
    – magnattic
    Commented Apr 1, 2014 at 22:01
  • @atticae, Your framework does not have to be limited to the database, it can include code that runs outside of the database.
    – Ian Ringrose
    Commented Apr 1, 2014 at 22:25
  • It's not limited to the database of course. (I wouldn't call it a framework in this case.) I see what you mean now by "application level" and I am currently in fact using a variation of the Temporal Property pattern that your link talks about. The framework I build provides this interface to the applications that use it. Still, that is part of the interface side, and nothing of this really answers my questions outlined above.
    – magnattic
    Commented Apr 1, 2014 at 22:46
  • Thanks again for your answer. This is probably the deciding factor for most people, so I think it is a good answer and probably help future visitors to decide not to use CDC. However, I feel that it does not really answer most of my questions, so I will have to give the bounty to stacylaray who was the only one trying to answer all the questions I had. (Although I was hoping for an answer a little bit more elaborate.)
    – magnattic
    Commented Apr 8, 2014 at 12:16
12

Here is a very well written 9 part series that reviews the different ways of auditing SQL Server data changes. Parts 3, 4 & 5 focus on CDC. It's worth reading through all of the articles because this will answer your questions, like the different scenarios where the features would be appropriate and overhead. http://solutioncenter.apexsql.com/tag/methods-for-auditing-sql-server

Improve this answer
1
  • 1
    After skimming through the article, I am still not much smarter. As most articles it goes into detail how to use CDC and how it compares to Change Tracking. That doesn't really answer my above questions though.
    – magnattic
    Commented Apr 1, 2014 at 22:06
9
+50

For which tasks/scenarios is CDC the right tool? (e.g. Allowing users to restore a data object to a certain point in time?

Maybe, it depends.

Auditing?

Yes.

Showing the complete history of data?)

Yes.

When should you rather not use CDC, but resort to a custom trigger-based solution?

When the data in the change table does not meet your needs.

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)

Yes.

Or is this clearly a misuse of this feature?

No, it is not a misuse of this feature.

I commonly hear that CDC is an audit tool, but isn't that what SQL Server Audit is for?

Yes.

Are they both different tools for the same task?

No.

Or can CDC used for other things?

CDC can be used for other things.

There is Change Tracking and there is Change Data Capture. Both have their roots in replication.

Change Tracking provides a way to provide the net changes to a table. An example of use would be a hand held device sync.

CDC, on the other hand, keeps track of every little change, a history. One can use that history to update a data warehouse instead of bulk copying in data, or one can use that history as data itself and generate reports off of it. The change table is not hidden nor does it have some weird schema or something. You can query it and use the data however you want. Just keep in mind...it isn't real time, like Ian said. The data comes from the transaction log, so take care of it like you would using replication, or mirroring, or log shipping. By and large, it will be faster than triggers. You will need to use Snapshot Isolation, which has overhead, and you will have to think about Disaster Recovery.

Improve this answer
0
3

A point of correction. At one time, change data capture was only available in the versions listed above. However, change data capture became available in the standard edition as of 2016 SP1. Thus, many articles written before 2016 SP1 make it sound as if CDC is out of reach for those of us using the Standard edition. This is no longer the case. The Microsoft doc outlining the available of CDC is in the link below.

https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-2017#DW

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.