Download to read offline
![Introduction to Change Data Capture in SQL Server 2008 | TechRepublic ZDNet Asia SmartPlanet TechRepublic Log In Join TechRepublic FAQ Go Pro! Blogs Downloads Newsletters Galleries Q&A Discussions News Research Library IT Management Development IT Support Data Center Networks Security Home / Blogs / The Enterprise Cloud Follow this blog: The Enterprise Cloud Introduction to Change Data Capture in SQL Server 2008 By Tim Chapman January 12, 2009, 11:47 AM PST Takeaway: In today’s database tip, SQL Server database consultant Tim Chapman takes a look at the great new auditing features available in SQL Server 2008. Almost all industries require some type of data auditing in one form or another. This is especially true in the financial and health care industries, where changes in data can have critical effects. Sarbanes-Oxley compliance is a great example of how important data auditing is. Auditing data is important for processes other than just the retention of data. Extract, Transformation, and Loading (ETL) activities that incrementally load altered data into the data warehouse typically have to make use of some type of auditing to identify those records where the data has changed. Change Data Capture (CDC), a brand new feature in SQL Server 2008, features the ability to capture and store structure and data alterations in your SQL Server system. What is Change Data Capture? Change Data Capture (CDC), a wonderful new feature in SQL Server 2008, provides the ability to set up and manage database data auditing without requiring custom auditing procedures and triggers. This feature captures DML operations (Insert, Update, Delete statements) and makes the altered database available for later reporting. CDC is first enabled at the database level, and then to the necessary tables. After you enable a table for CDC, a similar table is created to track the data changes. Insert and delete operations are represented as a single record in the change audit table, whereas update statements are represented as two records. Insert and delete statements involve only a single aspect of data: the row that has been inserted or the row that has been deleted. On the other hand, update statements involve two different sets of data: the record values that existed before the update statement and those that exist after the statement occurs. The CDC system captures both of these records, which makes it very convenient to compare values correlated to the update statement. In addition to capturing the previous value and new value from Update statements, CDC also captures the fields that were updated in the form of a bitmask. This mask can be used to easily determine which fields were included in the Update statement, and which fields were not. As time goes on, and data keeps changing, you may find yourself in a position to clear out some of the data that has been captured by the CDC system. Luckily, SQL Server 2008 includes a retention policy for CDC that allows you to specify and remove data that has been captured and held for a certain amount of days. http://www.techrepublic.com/blog/datacenter/introduction-to-change-data-capture-in-sql-server-2008/520[08/29/2012 3:47:51 PM]](https://image.slidesharecdn.com/introductiontochangedatacaptureinsqlserver2008techrepublic-121023051528-phpapp02/75/Introduction-to-change-data-capture-in-sql-server-2008-tech-republic-1-2048.jpg)
![Introduction to Change Data Capture in SQL Server 2008 | TechRepublic CDC also does a pretty good job of keeping track and accommodating new fields and alterations to the underlying tables that are being audited. This is a great feature for reporting any type of structure changes. The CDC process scans the transaction log to capture the changed data and write it to the audit tables. The auditing process is asynchronous and occurs in its own transaction scope, so there’s no overhead involved in the transaction that alters the data. Your database does not need to be set to the Full recovery model in order to take advantage of CDC’s features. However, there are some things to consider if you’re using the Simple recovery model with CDC. The major point of interest is the fact that the log will not truncate until the data altered has been captured by the CDC process. This may eventually delay log truncation accomplished by the Checkpoint process, which could cause the log file to remain larger than expected. This is something that you should consider if you’re limited to space on your SQL Server. In addition to capturing data that has been changed for CDC audited tables, CDC also has the ability to keep track of Data Definition Language (DDL) changes that are executed to the base tables. The ability to capture and maintain this data is paramount not only for auditing purposes, but also for change management purposes. Next time Today I looked at the capabilities and features of the new Change Data Capture technology in SQL Server 2008. These are without doubt great new features. However, the functionality present in CDC is available in SQL Server 2005, and can be accomplished with some custom coding and a little bit of replication work. Next time I’ll expand on the CDC subject and show you how you can set up data auditing using CDC on your SQL Server 2008 system. Perhaps in a future article I’ll look at how you can develop your own custom CDC functionality in SQL Server 2005. Get IT Tips, news, and reviews delivered directly to your inbox by subscribing to TechRepublic’s free newsletters. About Tim Chapman Full Bio Contact Configure the screen saver Oil & Water: Hyper-V and 3D timeout in Windows Server graphics adapters 2008 Core Edition People who read this... Four cool new features slated for SQL Server 2008 Choose the proper Oracle design for auditing 3 Join the conversation! Add Your Opinion Comments Follow via: Staff Picks Top Rated Most Recent My Contacts See All Comments RE: Introduction to Change Data Capture in SQL Server 0 2008 Votes http://www.techrepublic.com/blog/datacenter/introduction-to-change-data-capture-in-sql-server-2008/520[08/29/2012 3:47:51 PM]](https://image.slidesharecdn.com/introductiontochangedatacaptureinsqlserver2008techrepublic-121023051528-phpapp02/75/Introduction-to-change-data-capture-in-sql-server-2008-tech-republic-2-2048.jpg)
![Introduction to Change Data Capture in SQL Server 2008 | TechRepublic eralper.yilmaz@... 19th Jan 2009 Hello, Here is some screenshots for a sample usage of CDC in sql2008 http://www.kodyaz.com/articles/change-data-capture.aspx CDC is a handy feature where it can be used for also logging changed data... Read Whole Comment + View in thread CDC by whom? 0 ashepard@... 14th Jan 2009 Votes CDC for DML seem much easier than archive log miner of Oracle. The first question is What was changed and who changed it. Any word on "on line table rebuild" like SQL 2005 online index rebuild?? View in thread Excellent Article 0 Terry_Pino@... 13th Jan 2009 Votes Well written, thanks for the overview. Would you happen to have any references to CDC in mssql2005? Thanks View in thread See all comments Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion. Join Login http://www.techrepublic.com/blog/datacenter/introduction-to-change-data-capture-in-sql-server-2008/520[08/29/2012 3:47:51 PM]](https://image.slidesharecdn.com/introductiontochangedatacaptureinsqlserver2008techrepublic-121023051528-phpapp02/75/Introduction-to-change-data-capture-in-sql-server-2008-tech-republic-3-2048.jpg)
Uploaded byKaing Menglieng
Introduction to change data capture in sql server 2008 tech republic
This document introduces Change Data Capture (CDC) in SQL Server 2008. It discusses how CDC captures insert, update, and delete operations without custom triggers. It captures the previous and new values for updates. CDC also tracks schema changes and has data retention policies. CDC scans the transaction log asynchronously to capture changes with no transaction overhead.
Introduction to change data capture in sql server 2008 tech republic
- 1. Introduction to ChangeData Capture in SQL Server 2008 | TechRepublic ZDNet Asia SmartPlanet TechRepublic Log In Join TechRepublic FAQ Go Pro! Blogs Downloads Newsletters Galleries Q&A Discussions News Research Library IT Management Development IT Support Data Center Networks Security Home / Blogs / The Enterprise Cloud Follow this blog: The Enterprise Cloud Introduction to Change Data Capture in SQL Server 2008 By Tim Chapman January 12, 2009, 11:47 AM PST Takeaway: In today’s database tip, SQL Server database consultant Tim Chapman takes a look at the great new auditing features available in SQL Server 2008. Almost all industries require some type of data auditing in one form or another. This is especially true in the financial and health care industries, where changes in data can have critical effects. Sarbanes-Oxley compliance is a great example of how important data auditing is. Auditing data is important for processes other than just the retention of data. Extract, Transformation, and Loading (ETL) activities that incrementally load altered data into the data warehouse typically have to make use of some type of auditing to identify those records where the data has changed. Change Data Capture (CDC), a brand new feature in SQL Server 2008, features the ability to capture and store structure and data alterations in your SQL Server system. What is Change Data Capture? Change Data Capture (CDC), a wonderful new feature in SQL Server 2008, provides the ability to set up and manage database data auditing without requiring custom auditing procedures and triggers. This feature captures DML operations (Insert, Update, Delete statements) and makes the altered database available for later reporting. CDC is first enabled at the database level, and then to the necessary tables. After you enable a table for CDC, a similar table is created to track the data changes. Insert and delete operations are represented as a single record in the change audit table, whereas update statements are represented as two records. Insert and delete statements involve only a single aspect of data: the row that has been inserted or the row that has been deleted. On the other hand, update statements involve two different sets of data: the record values that existed before the update statement and those that exist after the statement occurs. The CDC system captures both of these records, which makes it very convenient to compare values correlated to the update statement. In addition to capturing the previous value and new value from Update statements, CDC also captures the fields that were updated in the form of a bitmask. This mask can be used to easily determine which fields were included in the Update statement, and which fields were not. As time goes on, and data keeps changing, you may find yourself in a position to clear out some of the data that has been captured by the CDC system. Luckily, SQL Server 2008 includes a retention policy for CDC that allows you to specify and remove data that has been captured and held for a certain amount of days. http://www.techrepublic.com/blog/datacenter/introduction-to-change-data-capture-in-sql-server-2008/520[08/29/2012 3:47:51 PM]
- 2. Introduction to ChangeData Capture in SQL Server 2008 | TechRepublic CDC also does a pretty good job of keeping track and accommodating new fields and alterations to the underlying tables that are being audited. This is a great feature for reporting any type of structure changes. The CDC process scans the transaction log to capture the changed data and write it to the audit tables. The auditing process is asynchronous and occurs in its own transaction scope, so there’s no overhead involved in the transaction that alters the data. Your database does not need to be set to the Full recovery model in order to take advantage of CDC’s features. However, there are some things to consider if you’re using the Simple recovery model with CDC. The major point of interest is the fact that the log will not truncate until the data altered has been captured by the CDC process. This may eventually delay log truncation accomplished by the Checkpoint process, which could cause the log file to remain larger than expected. This is something that you should consider if you’re limited to space on your SQL Server. In addition to capturing data that has been changed for CDC audited tables, CDC also has the ability to keep track of Data Definition Language (DDL) changes that are executed to the base tables. The ability to capture and maintain this data is paramount not only for auditing purposes, but also for change management purposes. Next time Today I looked at the capabilities and features of the new Change Data Capture technology in SQL Server 2008. These are without doubt great new features. However, the functionality present in CDC is available in SQL Server 2005, and can be accomplished with some custom coding and a little bit of replication work. Next time I’ll expand on the CDC subject and show you how you can set up data auditing using CDC on your SQL Server 2008 system. Perhaps in a future article I’ll look at how you can develop your own custom CDC functionality in SQL Server 2005. Get IT Tips, news, and reviews delivered directly to your inbox by subscribing to TechRepublic’s free newsletters. About Tim Chapman Full Bio Contact Configure the screen saver Oil & Water: Hyper-V and 3D timeout in Windows Server graphics adapters 2008 Core Edition People who read this... Four cool new features slated for SQL Server 2008 Choose the proper Oracle design for auditing 3 Join the conversation! Add Your Opinion Comments Follow via: Staff Picks Top Rated Most Recent My Contacts See All Comments RE: Introduction to Change Data Capture in SQL Server 0 2008 Votes http://www.techrepublic.com/blog/datacenter/introduction-to-change-data-capture-in-sql-server-2008/520[08/29/2012 3:47:51 PM]
- 3. Introduction to ChangeData Capture in SQL Server 2008 | TechRepublic eralper.yilmaz@... 19th Jan 2009 Hello, Here is some screenshots for a sample usage of CDC in sql2008 http://www.kodyaz.com/articles/change-data-capture.aspx CDC is a handy feature where it can be used for also logging changed data... Read Whole Comment + View in thread CDC by whom? 0 ashepard@... 14th Jan 2009 Votes CDC for DML seem much easier than archive log miner of Oracle. The first question is What was changed and who changed it. Any word on "on line table rebuild" like SQL 2005 online index rebuild?? View in thread Excellent Article 0 Terry_Pino@... 13th Jan 2009 Votes Well written, thanks for the overview. Would you happen to have any references to CDC in mssql2005? Thanks View in thread See all comments Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion. Join Login http://www.techrepublic.com/blog/datacenter/introduction-to-change-data-capture-in-sql-server-2008/520[08/29/2012 3:47:51 PM]