SQL Server如何配置cdc進行ETL

企業核心業務系統oltp的資料需要透過ETL同步到資料倉儲，原始的ETL流程透過定製化從SQL Server中進行資料抽取，經過生產環境的監控，發現ETL過程的query會對生產系統造成額外負載。於是制定了透過cdc進行增量資料同步的方案:

方案選型中比對了SQL server的trigger,CT,CDC, temporary table各種方式，選型對比如下：

	Trigger	CT	CDC	Temporal table
Sync way	Synchronous	Synchronous	Asynchronous	Synchronous
Internal work	Heavy than index	Same as index	Call
Table part in transaction	Yes	Yes	No	Yes
Historical Data retention	Manual control	No	Yes	Yes

透過表格對比可以看到，cdc透過非同步非侵入式進行增量資料捕獲，使用了sp_replcmds，這個過程和sql server的transactional replication中的log reader agent是相同的，缺點就是會對佔用原始database的datafile和logfile，造成資料增長。

在cdc的使用過程中，比較重要的是將cdc資料置於單獨的filegroup內，這樣，在資料管理恢復和效能上會減少對於原始oltp的影響。具體過程如下：

1. 在db級別啟用cdc時需要先將db的預設filegroup改成cdc，這樣cdc對應的後設資料資訊，例如經常變化的表cdc.lsn_time_mapping可以存放到單獨的filegroup中

2. 對於表的cdc資料使用@fileGroup_Name引數指定filegroup

--enable cdc filegroup

USE DB1

ALTER DATABASE DB1 ADD FILEGROUP CDC

GO

ALTER DATABASE DB1 ADD FILE

(

NAME='CDC',

FILENAME='D:\DATA\DB_CDC1.ndf',

SIZE = 1024MB,

MAXSIZE = unlimited,

FILEGROWTH=256MB

)TO FILEGROUP CDC

GO

USE DB1

GO 

ALTER DATABASE DB1 MODIFY FILEGROUP [CDC] DEFAULT

GO  

EXEC sys.sp_cdc_enable_db  

GO  

ALTER DATABASE DB1 MODIFY FILEGROUP [DATA] DEFAULT

go

EXEC sys.sp_cdc_enable_table @source_schema = N'dbo',@source_name = 'T1',@role_name = N'cdc_Admin',@fileGroup_Name = N'CDC' 

CDC建立後有capture job和clean job，當cdc的資料增量非常大的時候，需要適當調整job的引數：

EXEC sys.sp_cdc_change_job  

 @job_type = 'capture' 

 ,@maxtrans = 5000      --每個掃描迴圈可以處理的最多事務數 

 ,@maxscans = 100       --為了從日誌中提取所有行而要執行的最大掃描迴圈次數 

 ,@continuous = 1       --連續執行最多處理(max_trans * max_scans)個事務 

 ,@pollinginterval = 1