SQL Server怎么配置cdc進(jìn)行ETL

這篇文章給大家分享的是有關(guān)SQL Server怎么配置cdc進(jìn)行ETL的內(nèi)容。小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過來看看吧。

企業(yè)核心業(yè)務(wù)系統(tǒng)oltp的數(shù)據(jù)需要通過ETL同步到數(shù)據(jù)倉庫，原始的ETL流程通過定制化從SQL Server中進(jìn)行數(shù)據(jù)抽取，經(jīng)過生產(chǎn)環(huán)境的監(jiān)控，發(fā)現(xiàn)ETL過程的query會(huì)對(duì)生產(chǎn)系統(tǒng)造成額外負(fù)載。于是制定了通過cdc進(jìn)行增量數(shù)據(jù)同步的方案:

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

通過表格對(duì)比可以看到，cdc通過異步非侵入式進(jìn)行增量數(shù)據(jù)捕獲，使用了sp_replcmds，這個(gè)過程和sql server的transactional replication中的log reader agent是相同的，缺點(diǎn)就是會(huì)對(duì)占用原始database的datafile和logfile，造成數(shù)據(jù)增長。

在cdc的使用過程中，比較重要的是將cdc數(shù)據(jù)置于單獨(dú)的filegroup內(nèi)，這樣，在數(shù)據(jù)管理恢復(fù)和性能上會(huì)減少對(duì)于原始o(jì)ltp的影響。具體過程如下：

1. 在db級(jí)別啟用cdc時(shí)需要先將db的默認(rèn)filegroup改成cdc，這樣cdc對(duì)應(yīng)的元數(shù)據(jù)信息，例如經(jīng)常變化的表cdc.lsn_time_mapping可以存放到單獨(dú)的filegroup中

2. 對(duì)于表的cdc數(shù)據(jù)使用@fileGroup_Name參數(shù)指定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，當(dāng)cdc的數(shù)據(jù)增量非常大的時(shí)候，需要適當(dāng)調(diào)整job的參數(shù)：

EXEC sys.sp_cdc_change_job  

 @job_type = 'capture' 

 ,@maxtrans = 5000      --每個(gè)掃描循環(huán)可以處理的最多事務(wù)數(shù) 

 ,@maxscans = 100       --為了從日志中提取所有行而要執(zhí)行的最大掃描循環(huán)次數(shù) 

 ,@continuous = 1       --連續(xù)運(yùn)行最多處理(max_trans * max_scans)個(gè)事務(wù) 

 ,@pollinginterval = 1

感謝各位的閱讀！關(guān)于“SQL Server怎么配置cdc進(jìn)行ETL”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！