Identifying and extracting changed data from source tables was replete with lots of efforts; it also requires designing the databases in an appropriate manner for keeping the changed data within tables or log files. The concept of “Change data capture”, a set of software design patterns used to determine (and track) the data that has changed in a database, helps designers to overcome the difficulty of solving such problems.
Fortunately, SQL Server 2008 implemented a mechanism to enable DBAs for automating this process like what we could consider in Oracle 9i which is called “Capturing Change”.
By using this new feature we could specify a log table in which the changes in data could be tracked. These changes are insert, update, and delete. SQL Server 2008 handles the required operation to store the appropriate data.
The following links are very helpful for understanding this new feature which could make ETL process more easily.
http://en.wikipedia.org/wiki/Change_data_capture
http://msdn.microsoft.com/en-us/library/cc645937(printer).aspx
http://www.oracle.com/technology/oramag/oracle/03-nov/o63tech_bi.html
