introduction to change data capture in sql server 2008 tech republic

Introduction to Change Data Capture in SQL Server 2008 | TechRepublic

http://www.techrepublic.com/blog/datacenter/introduction-to-change-data-capture-in-sql-server-2008/520[08/29/2012 3:47:51 PM]

Blogs Downloads Newsletters Galleries Q&A Discussions News

Research Library

Home / Blogs / The Enterprise Cloud

The Enterprise Cloud

Introduction to Change DataCapture in SQL Server 2008By Tim ChapmanJanuary 12, 2009, 11:47 AM PST

Takeaway: In todays database tip, SQL Server database consultant Tim Chapman takes a lookat 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 especiallytrue 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 isimportant 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 makeuse of some type of auditing to identify those records where the data has changed. Change DataCapture (CDC), a brand new feature in SQL Server 2008, features the ability to capture and storestructure 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 toset up and manage database data auditing without requiring custom auditing procedures andtriggers. This feature captures DML operations (Insert, Update, Delete statements) and makes thealtered database available for later reporting. CDC is first enabled at the database level, and thento the necessary tables. After you enable a table for CDC, a similar table is created to track thedata changes.

Insert and delete operations are represented as a single record in the change audit table, whereasupdate statements are represented as two records. Insert and delete statements involve only asingle aspect of data: the row that has been inserted or the row that has been deleted. On theother hand, update statements involve two different sets of data: the record values that existedbefore the update statement and those that exist after the statement occurs. The CDC systemcaptures both of these records, which makes it very convenient to compare values correlated tothe update statement.

In addition to capturing the previous value and new value from Update statements, CDC alsocaptures the fields that were updated in the form of a bitmask. This mask can be used to easilydetermine 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 someof the data that has been captured by the CDC system. Luckily, SQL Server 2008 includes aretention policy for CDC that allows you to specify and remove data that has been captured andheld for a certain amount of days.

Follow this blog:

IT Management Development IT Support Data Center Networks Security

Log In Join TechRepublic FAQ Go Pro!ZDNet Asia SmartPlanet TechRepublic

http://ad.doubleclick.net/click;h=v8/3ce0/0/0/%2a/d;44306;0-0;0;74341537;31-1/1;0/0/0;;~sscs=%3fhttp://www.techrepublic.com/http://www.techrepublic.com/http://www.techrepublic.com/blogshttp://www.techrepublic.com/downloadshttp://www.techrepublic.com/newslettershttp://www.techrepublic.com/photoshttp://www.techrepublic.com/forum/questionshttp://www.techrepublic.com/forum/discussionshttp://www.techrepublic.com/newshttp://www.techrepublic.com/research-libraryhttp://www.techrepublic.com/http://www.techrepublic.com/blogshttp://www.techrepublic.com/blog/datacenterhttp://ad.doubleclick.net/click;h=v8/3ce0/0/0/%2a/j;44306;0-0;0;74341537;3823-300/100;0/0/0;;~sscs=%3fhttp://www.techrepublic.com/search?q=tim+chapmanhttp://blogs.techrepublic.com.com/datacenter/?p=323http://www.techrepublic.com/blog/datacenter?mode=rsshttp://www.techrepublic.com/alerts/add?url=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%3Fmode%3Drss&title=The+Enterprise+Cloud+on+TechRepublic&source=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%2Fintroduction-to-change-data-capture-in-sql-server-2008%2F520&frequency=weekly&rss_type=38http://www.techrepublic.com/members/login?regSrc=global-reghttp://www.techrepublic.com/members/join?regSrc=global-reghttp://www.techrepublic.com/faq/generalhttp://www.techrepublic.com/prohttp://www.zdnetasia.com/http://www.smartplanet.com/http://www.techrepublic.com/



3Comments

Add Your Opinion

See All CommentsMy Contacts

Get IT Tips, news, and reviews delivered directly to your inbox by subscribing to TechRepublics freenewsletters.

About Tim Chapman

CDC also does a pretty good job of keeping track and accommodating new fields and alterationsto the underlying tables that are being audited. This is a great feature for reporting any type ofstructure changes.

The CDC process scans the transaction log to capture the changed data and write it to the audittables. The auditing process is asynchronous and occurs in its own transaction scope, so theresno 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 ofCDCs features. However, there are some things to consider if youre using the Simple recoverymodel with CDC. The major point of interest is the fact that the log will not truncate until the dataaltered has been captured by the CDC process. This may eventually delay log truncationaccomplished by the Checkpoint process, which could cause the log file to remain larger thanexpected. This is something that you should consider if youre limited to space on your SQLServer.

In addition to capturing data that has been changed for CDC audited tables, CDC also has theability to keep track of Data Definition Language (DDL) changes that are executed to the basetables. The ability to capture and maintain this data is paramount not only for auditing purposes,but also for change management purposes.

Next timeToday I looked at the capabilities and features of the new Change Data Capture technology inSQL Server 2008. These are without doubt great new features. However, the functionality presentin CDC is available in SQL Server 2005, and can be accomplished with some custom coding anda little bit of replication work. Next time Ill expand on the CDC subject and show you how you canset up data auditing using CDC on your SQL Server 2008 system. Perhaps in a future article Illlook at how you can develop your own custom CDC functionality in SQL Server 2005.

Join the conversation!

Full Bio Contact

Configure the screen savertimeout in Windows Server2008 Core Edition

Oil & Water: Hyper-V and 3Dgraphics adapters

People who read this...

Four cool new features slated for SQL Server 2008

Choose the proper Oracle design for auditing

Follow via:

Staff Picks Top Rated Most Recent

RE: Introduction to Change Data Capture in SQL Server2008

0Votes

http://www.techrepublic.com/forum/discussions/102-283167http://www.techrepublic.com/newslettershttp://www.techrepublic.com/newslettershttp://www.techrepublic.com/blog/datacenter/configure-the-screen-saver-timeout-in-windows-server-2008-core-edition/481http://www.techrepublic.com/blog/datacenter/configure-the-screen-saver-timeout-in-windows-server-2008-core-edition/481http://www.techrepublic.com/blog/datacenter/configure-the-screen-saver-timeout-in-windows-server-2008-core-edition/481http://www.techrepublic.com/blog/datacenter/configure-the-screen-saver-timeout-in-windows-server-2008-core-edition/481http://www.techrepublic.com/blog/datacenter/oil-water-hyper-v-and-3d-graphics-adapters/521http://www.techrepublic.com/blog/datacenter/oil-water-hyper-v-and-3d-graphics-adapters/521http://www.techrepublic.com/blog/datacenter/oil-water-hyper-v-and-3d-graphics-adapters/521http://www.techrepublic.com/blog/datacenter/four-cool-new-features-slated-for-sql-server-2008/148?tag=btxcsimhttp://articles.techrepublic.com.com/5100-10878_11-5067776.html?tag=btxcsimhttp://www.techrepublic.com/forum/discussions/102-283167/rsshttp://www.techrepublic.com/alerts/add?url=http%3A%2F%2Fwww.techrepublic.com%2Fforum%2Fdiscussions%2F102-283167%2Frss&title=Discussion+on+Introduction+to+Change+Data+Capture+in+SQL+Server+2008+on+TechRepublic&source=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%2Fintroduction-to-change-data-capture-in-sql-server-2008%2F520&frequency=weekly&rss_type=42http://www.techrepublic.com/util/votehttp://www.techrepublic.com/util/vote



Join Login

See all comments

Join the TechRepublic Community and join the conversation! Signing-up isfree and quick, Do it now, we want to hear your opinion.

Hello, Here is some screenshots for a sample usage of CDC in sql2008http://www.kodyaz.com/articles/change-data-capture.aspx CDC is a handy featurewhere it can be used for also logging changed data... Read Whole Comment +

View in thread

eralper.yilmaz@... 19th Jan 2009

View in thread

CDC by whom?ashepard@... 14th Jan 2009

CDC for DML seem much easier than archive log miner of Oracle. The first questionis What was changed and who changed it.

Any word on "on line table rebuild" like SQL 2005 online index rebuild??

0Votes

View in thread

Excellent ArticleTerry_Pino@... 13th Jan 2009

Well written, thanks for the overview. Would you happen to have any references toCDC in mssql2005?

Thanks

0Votes

http://www.techrepublic.com/forum/discussions/102-283167http://www.techrepublic.com/members/join?regSrc=disc-starthttp://www.techrepublic.com/members/join?regSrc=disc-starthttp://www.techrepublic.com/members/login?regSrc=disc-starthttp://www.techrepublic.com/members/login?regSrc=disc-starthttp://www.techrepublic.com/forum/discussions/102-283167http://www.techrepublic.com/forum/discussions/102-283167-2682597http://www.techrepublic.com/members/profile/5208982http://www.techrepublic.com/forum/discussions/102-283167-2679203http://www.techrepublic.com/members/profile/4607718http://www.techrepublic.com/util/votehttp://www.techrepublic.com/util/votehttp://www.techrepublic.com/forum/discussions/102-283167-2678519http://www.techrepublic.com/members/profile/4600634http://www.techrepublic.com/util/votehttp://www.techrepublic.com/util/vote

techrepublic.comIntroduction to Change Data Capture in SQL Server 2008 | TechRepublic

1zcWwtc2VydmVyLTIwMDgvNTIwAA==: form1: q: button3:

introduction to change data capture in sql server 2008 tech republic

Documents