SQL SERVER CHANGE DATA CAPTURE

Even though this topic is still concerned with extraction, it is important enough that it deserves a section of its own. Note that Change Data Capture (CDC) is covered extensively in Books Online (by the actual developer of CDC) and demonstrated in more detail in the related CodePlex samples (www.codeplex.com).

In most nightly batches for your ETL, you want to ensure that you are processing only the most recent data — for instance, just the data from the preceding day’s operations. Obviously, you don’t want to process every transaction from the last five years during each night’s batch. However, that’s the ideal world, and sometimes the source system is not able to tell you which rows belong to the time window you need.