SQL Server: Change Data Capture - Init

<body> <h1>SQL Server: Change Data Capture</h1> <strong>Introduction</strong><br /><br />CDC (Change Data Capture) captures data changes caused by DML (Data Manipulation Language) statements like inserts, updates, and deletions and makes it possible to archive data changes without any additional programming. These captured changed are stored in change tables that mirror the column structure of the original, tracked, user-created source tables with additional columns that include extra metadata. With CDC no changes are required to the existing source table schemas. Several database objects are provided (stored procedures, functions, and jobs) to handle the captured changes and integrate with external systems like a DWH (Data Warehouse).<br /><br /><strong>Overview</strong><br /><br />All data changes caused by inserts, updates, and deletions are saved in the transaction log file <databasename>_log.ldf, which is used as a source for the change data capture process. This change data capture process is scheduled by the SQL Server Agent that executes the capture job cdc.<databasename>_capture. The capture job scans the transaction log every 5 seconds, processing a maximum of 1000 transactions (these numbers are based on default settings, which can be changed for performance optimalisation purposes). Take into account that there’s latency between the time that a change is committed to the source table and the time that the change appears within its associated change table as the capture process extracts data from the transaction log. Archiving changes causes huge amounts of data and therefore a cleanup job cdc.<databasename>_cleanup exists that removes change data from the change tables that is older than 3 days.</databasename></databasename></databasename><br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/-JKIrq8x5miY/TwwqhQT8eLI/AAAAAAAAADk/bifaEkpFaSQ/s1600/CDC+01.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="381" src="http://4.bp.blogspot.com/-JKIrq8x5miY/TwwqhQT8eLI/AAAAAAAAADk/bifaEkpFaSQ/s400/CDC+01.png" width="400" /></a></div><br /><br />The CDC functionality is only supported by the Enterprise and Developer editions of SQL Server 2008 R2.<br /><br /><strong>Setup</strong><br /><br />For using CDC functionality you have to go through the following steps.<br /><div></div><ul><li><strong>Enable CDC for database</strong><br />Before changes to any individual table within a database can be tracked change data capture must be explicitly enabled for the database, which you can do by executing the following T-SQL script:<br /><br /><div style="border: 1px solid rgb(0, 112, 192); overflow-x: auto; padding: 10px; width: 500px;"><span lang="NL" style="font-family: "Courier New"; font-size: 8pt; line-height: 115%;">EXEC sys.sp_cdc_enable_db</span></div><br />After executing this T-SQL script the following database objects are created:<br /><br /><table border="0" cellpadding="0" cellspacing="0" style="border: 1px solid silver; font-size: 11px; width: 500px;"><tbody><tr style="background-color: #0070c0; color: white; font-weight: bold;"> <td style="border-bottom-color: silver; border-bottom-style: solid; border-bottom-width: 1px; padding-left: 3px; width: 150px;">Object</td> <td style="border-bottom-color: silver; border-bottom-style: solid; border-bottom-width: 1px; padding-left: 3px; width: 350px;">Description</td> </tr><tr> <td style="border-bottom-color: silver; border-bottom-style: solid; border-bottom-width: 1px; padding-left: 3px;">Schema</td> <td style="border-bottom-color: silver; border-bottom-style: solid; border-bottom-width: 1px; padding-left: 3px;">Cdc</td> </tr><tr> <td style="border-bottom-color: silver; border-bottom-style: solid; border-bottom-width: 1px; padding-left: 3px;">Tables</td> <td style="border-bottom-color: silver; border-bottom-style: solid; border-bottom-width: 1px; padding-left: 3px;">Several tables are created using the schema “cdc”< </body>