<video id="71low"></video>

            ITPub博客

            首頁 > 架構設計 > 數據架構 > SQL Server如何配置cdc進行ETL

            SQL Server如何配置cdc進行ETL

            原創 數據架構 作者:宅慕思_ 時間:2019-10-23 10:07:58 0 刪除 編輯

            企業核心業務系統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 sp_replcmds   to collect from tlog no direct workload.

             


            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



            來自 “ ITPUB博客 ” ,鏈接:http://www.ep4tq.com/69950462/viewspace-2661068/,如需轉載,請注明出處,否則將追究法律責任。

            請登錄后發表評論 登錄
            全部評論

            注冊時間:2019-10-10

            • 博文量
              13
            • 訪問量
              4498
            妹子图每日分享