October 23, 2008 at 12:56 am
Comments posted to this topic are about the item Implementing Change Data Capture in Microsoft® SQL Server 2008
Suresh Yaram
DatabaseArchitect,
Integrators Office - Technology Advisory Group - CHB
Satyam Computer Services Limited
A-2, Satyam Gateway, Hyderabad,
Off - (40) 3065 3097
Mobile - +91-99085-50642
October 23, 2008 at 2:39 am
A detailed but clear explanation - many thanks.
Obviously there must be associated performance hit. Does anyone yet have any real-world experience of how CDC might impact performance?
October 23, 2008 at 5:00 am
Agreed - a good explanation and much easier to read than a lot of more detailed white papers. My only comment on a possible enhancement would have been to show some sample output from the CDC records. I can see a potential use for this to load a real time warehouse of data, but can also see issues with performance and space. Even worse if something goes wrong and we can't query this database prior to purge.
I'm going to give this a try locally and see what I can come up with. I'd also love to hear about anyone using this in the real world. 🙂
October 23, 2008 at 7:16 am
I see this as a way to better ensure auditing of data changes. In the past, we have had to build "history" tables and implement triggers on the base tables to capture the history of changes to the data. Using CDC, it seems like this requirement is much simpler and encompassing. My clients generally deal with sensitive data, therefore we often have to provide an audit trail of who changed what when. Thanks for the article.
October 23, 2008 at 7:48 am
I pulled in the scripts and hit an error on the disable cdc call from the scripts document referenced at the bottom of the article.
EXEC sys.sp_cdc_disable_table
@source_schema = 'dbo'
, @source_name = 'Employee'
, @capture_instance = 'dbo_Employee'
, @supports_net_changes = 1
Throws an error:
Msg 8144, Level 16, State 2, Procedure sp_cdc_disable_table, Line 0
Procedure or function sp_cdc_disable_table has too many arguments specified.
Books online specifies the syntax as:
sys.sp_cdc_disable_table
[ @source_schema = ] 'source_schema' ,
[ @source_name = ] 'source_name'
[ , [ @capture_instance = ] 'capture_instance' | 'all' ]
So if you get rid of the support_net_changes it runs ok.
Besides auditing, another use of CDC is for data warehouseing and reporting. No need to write specialized code to pull in the changed records, you have a nice method of handling that using CDC without relying on the MERGE functionality in 2008 or Slowly Changing Dimensions tasks in SSIS which can be a performance hinderance on large data sets.
October 23, 2008 at 7:53 am
What I want to know is whether the new CDC capability can handle BLOB data such as long text and ntext fields. While trying to do CDC with SSIS 2005, I discovered that the use of embedded SQL made BLOB data a real problem, and was completely unsupported.
October 23, 2008 at 9:08 am
I can see a need for this
but 2008 rendition has too much overhead
October 23, 2008 at 9:10 am
Nice article and worth giving it a try for this new feature. Now, with this feature in 2008, I hope there would be less need to create a history table and those update and delete triggers on tables to capture any data manipulation.
Would definitely try in test environment in couple of days.
SQL DBA.
October 23, 2008 at 9:13 am
danschl (10/23/2008)
I can see a need for thisbut 2008 rendition has too much overhead
Agreed. That's the reason I would not implement 2008 in my project at least for another year or so. By that time we would have a broad idea about 2008 behavior. Its pro's and con's.
SQL DBA.
October 23, 2008 at 9:22 am
Couple of other articles on Change data capture are here with good explanation. I read them sometime back and are very useful.
http://www.databasejournal.com/features/mssql/article.php/3720361
http://www.databasejournal.com/features/mssql/article.php/3725476
http://weblogs.sqlteam.com/derekc/archive/2008/01/28/60469.aspx
SQL DBA.
October 23, 2008 at 9:52 am
This paper discusses the implementation of Change Data Capture in SQL Server 2008 and how it is superior to the traditional change tracking mechanisms in the context of data warehousing applications.
Yes, that is exactly how I understood the suggested article. Read it twice... 🙂
It is clearly covering a topic "How to turn CDC on SQL server on".
Still did not understand: how is it superior to the traditional change tracking?
CDC is just a tool, not a solution. With multiple limitations it currently has, it can not be used for Auditing and could only be used as a part of the "History of changes" tracking solution...
October 23, 2008 at 10:31 am
Is there something similar for SQLServer 2005?
[]s
Rubem Rocha
Manaus, AM - Brazil
October 23, 2008 at 10:57 am
djpardalrocha (10/23/2008)
Is there something similar for SQLServer 2005?[]s
Rubem Rocha
Manaus, AM - Brazil
CDC is new with SQL 2008. You may be able to implement a similar mechanism through triggers on the tables in 2005 and 2000, but there is nothing out of the box that will do this in 2005.
October 23, 2008 at 11:50 am
Good article, well executed. I've been very excited about this development and can't wait for a chance to compare it to the traditional methods (e.g. triggers and time stamps) I've used in the past. While like others I am guarded in my enthusiasm as a result of MS's half-baked implementations of functionality in the past, my reading on this is it will save a lot of time. Thanks for taking the time and effort to work this out for us.
😎 Kate The Great :w00t:
If you don't have time to do it right the first time, where will you find time to do it again?
October 23, 2008 at 4:12 pm
I haven't had the chance to test out CDC yet so my comments do not in any way address reliability, particularly as Kate points out, with MS's past track record. However a number of posters question performance and benefit over traditional methods (eg triggers). A key point about CDC is that SQLServer uses the transaction log to work out the changes. This means that the auditing is done in the background by the server after the user transaction has committed. This is a fundamental point. The trigger method requires that auditing is done as part of the user transaction and so increases the overall time for the transaction to completed, including any potential contention with other transactions. CDC makes it a background task which obviously still uses resources in terms of cpu and cache, but it does not delay the transaction or impact the user experience, assuming you're server has adequate resources to handle the workload of the application, which it would need regardless of CDC or triggers.
Cheers
Roddy
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply