August 12, 2010 at 12:18 pm
We are looking to utilize CDC in SQL 2008 as a replacement for a long-term historical table. So instead of triggers or logic encapsulated in stored procedures to update a history table we want to simply "turn on CDC".
I know that the clean up job is configurable, up to 100 years or so, but I cannot seem to find any caveats to doing so on a table with millions of expected changes. I've read somewhere that CDC is really intended for use with a DW for slowly changing dimensions, but have not found anything to contradict our use case for CDC above. I'm thinking that this might be another one of those "here's some more rope" type things MS gives us with the configurable retention period.
Anyone have any experience with CDC or care to comment? Any thoughts are greatly appreciated. Thanks.
September 11, 2010 at 9:39 am
You may want to keep the CDC table on its own filegroup. This way you can decide if/when to backup the historical CDC table independent from your user tables.
If you're concern, you can always create your own historical table. This historical table can even be created in a database independent of your CDC/user table. Then create job to move each days CDC tracked data into your historical table.
--Chris Skorlinski
January 25, 2011 at 7:33 am
Hi,
I would definitly not recommend to store data in the cdc tables for long-term use! Extract the data nightly, thats the way it was thought.
You will run into troubles when you are going to query tables (with totally fragmented indexes -> "heavy" rebuild) after some weeks. Not to mention months or years. I think changing the filegroup will not improve the situation.
Also, you will find yourself to run queries on the cdc table where inserts are happening constantly...
This at least was my experience. With "Change Tracking" (wich is not CDC) I have no experiences...
many greetings
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply