Research And Implementation Of SQL Server Database Change Data Capture Based On Log Analysis

Traditionally, Data Integration, Data Propagation, and ETL (extract, transform and load) processes run on a periodic basis (weekly, daily) and use a bulk data movement approach that moves and integrates the entire source data to a target data source. This approach is limited in its ability to deal with new trends and business requirements, some of which include continuous refreshing of a DW, near real-time business intelligence and operational data stores. Based on transaction log Change-Data-Capture (CDC) technology provides a solution to IT operational challenges and the new business models.This paper introduces the principle of transaction logs and database recovery techniques,proposes a change-data-capture method based on SQL Server database log analysis. By means of the asynchronous read transaction log, this method captures data changes.This thesis addresses the following issues:Firstly, avoid that the transaction logs not captured change data are truncated;Secondly, how to process compensation logs for partial transaction rollbacks;Thirdly, develop the right algorithm for the restart of capture process, and how to reduce restart time;Finally, to improve the efficiency of capture process.