Change Data Capture: Keep up…
So…
It would appear that in the great tumult of development, some things take a while to surface. While people are being all \’agile\’ and \’waterfall\’, it\’s easy not to keep up with what\’s new in the database world.
One thing I\’m being asked about a lot these days is Change Data Capture (CDC), which came in in SQL2008 (I think). The questions generally go along the lines of:
\”Can we use CDC as our database audit ?\”
To which, of course, the answer has to be \”No.\” Not because your DBA doesn\’t want you to use new stuff, but because it seems that people are missing the point of CDC. It\’s \’Capture\’, not \’History\’ that kind of gives the game away here.
A cursory glance at documentation would tell you that SQL Server can now track changes to your tables, and preserve those changes for later inspection. All good stuff. Honest.
But there are some important points to note about CDC, and you should probably be aware of a few things before you decide which way to jump.
1. CDC is a log-reader, and uses the same mechanism as Replication. This means that your transactions are committed to the database but cannot be flushed from the transaction log until CDC has processed them. And that\’s asynchronously. On a high-transaction database, this may lead to a larger transaction log than you would otherwise have enjoyed. Change Tracking is, however, synchronous. Go figure !
2. CDC can cause index fragementation if the retention value is high. OK, I\’ll admit to having to go with the consensus on this one, as I\’ve not proved it myself, but it\’s fairly easy to see how that would happen, especially when you consider that the ChangeTable is a function, not an actual table.
3. CDC has a default retention of 2 days. This allows you 2 days to extract your audit information from the ChangeTable, and put it in a proper audit table. I can see that perhaps 7 days might not be a bad idea as a safety net in case your extract falls over in a heap, but any more than that could be considered excessive.
4. CDC is NOT the same as Change Tracking. CDC captures the actual value changed, Change Tracking only tells you that a value was changed. Think of the difference between \’The price has changed from $5.00 to $4.99\’ and \’The price is different now\’.
5. Implementing CDC is a non-trivial task to get right. And it\’s probably going to be painful if you get it wrong 🙂
6. Preserving CDC when restoring a database is complex. Someone else wrote a nice blog about this. You can read it here: http://www.sqlsoldier.com/wp/sqlserver/cdcinteroperabilitywithmirroringandrecovery
I think I\’ve got the salient points outlined here, and I\’m sure that there\’s a debate to be had on these topics and more. Please remember that this is only in my own personal experience. You or your DBA may have your own opinion.
But I hope I\’ve made a case for NOT using CDC as your only audit option.
Back soon..
The Modern Dataist 