Shulou(Shulou.com)06/01 Report--

This article is about how to configure cdc for ETL in SQL Server. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The data of the core business system oltp needs to be synchronized to the data warehouse through ETL. The original ETL process extracts data from SQL Server through customization. Through the monitoring of the production environment, it is found that the query of the ETL process will cause additional load to the production system. Therefore, a scheme for incremental data synchronization through cdc is developed:

The trigger,CT,CDC and temporary table methods of SQL server are compared in the scheme selection, and the comparison is as follows:

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

Through table comparison, we can see that cdc uses asynchronous non-intrusive incremental data capture, using sp_replcmds, this process is the same as log reader agent in sql server's transactional replication, but the disadvantage is that it will cause data growth to the datafile and logfile that occupy the original database.

During the use of cdc, it is important to place cdc data in a separate filegroup, which reduces the impact on the original oltp in terms of data management recovery and performance. The specific process is as follows:

When you enable cdc at the db level, you need to change the default filegroup of db to cdc, so that the metadata information corresponding to cdc, such as the frequently changing table cdc.lsn _ time_mapping, can be stored in a separate filegroup.

Use the @ fileGroup_Name parameter to specify filegroup for the cdc data of the table

-- 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 = Noble source name = 'T1mRO source name = NobcdcAccording Adminstration name = Noble CDC'

After the establishment of CDC, there are capture job and clean job. When the data increment of cdc is very large, you need to adjust the parameters of job appropriately:

EXEC sys.sp_cdc_change_job

@ job_type = 'capture'

, @ maxtrans = 5000-the maximum number of transactions that can be processed per scan cycle

, @ maxscans = 100-the maximum number of scan cycles to be performed to extract all rows from the log

, @ continuous = 1-run a maximum of (max_trans * max_scans) transactions in a row

, @ pollinginterval = 1

Thank you for reading! This is the end of the article on "how to configure cdc for ETL in SQL Server". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.