Use CDC to capture SQLSERVER data changes

Recently, according to the company's plan, we need to incrementally fetch data from some tables in the previous SQL Server database to the ORACLE database, and decide to use the newly added CDC (Change Data Capture) function in sqlserver2008. Procedure

Recently, according to the company's plan, we need to incrementally fetch data from some tables in the previous SQL Server database to the ORACLE database, and decide to use the newly added CDC (Change Data Capture) function in sqlserver2008. Procedure

Recently, according to the company's plan, we need to incrementally fetch data from some tables in the previous SQL Server database to the ORACLE database, and decide to use the newly added CDC (Change Data Capture) function in sqlserver2008.

The procedure is as follows:

1. Check whether the CDC function is enabled for the specified database.
SELECT is_cdc_enabled FROM sys. databases WHERE name = 'jointown'

2. Enable the CDC function at the database level
EXEC sys. sp_cdc_enable_db

3. Create a test table
Create table t1 (id int, name varchar (20 ));

4. Enable CDC on the test table
EXEC sys. sp_cdc_enable_table

@ Source_schema = 'dbo ',

@ Source_name = 't1 ',

@ Role_name = NULL,

@ Capture_instance = NULL,

@ Supports_net_changes = 1,

@ Index_name = NULL,

@ Captured_column_list = NULL,

@ Filegroup_name = default

An error is reported during execution. For a U.S. server, you must set a primary key or a unique key in the table.

Enabled again successfully:

5. Reminder: To enable the CDC to work properly, in addition to the above configurations, you must enable the agent service for the VM!

6. Check whether the specified table CDC is enabled
SELECT is_tracked_by_cdc FROM sys. tables WHERE name = 't1' and schema_id = SCHEMA_ID ('dbo ')

7. Check the Change Data Capture table. Based on the previously created table t1, the table that stores the change data is cdc. dbo_t1_ct. No data exists in the table.

8. insert and change one data entry in Table t1 before viewing
Insert into t1 values (1, 'A ');

Select * from cdc. dbo_t1_ct

In the tracking table, we can see that data insertion in the source table has been captured. Compared with the original table, the cdc table adds five columns to the website space, generally, we need to pay attention to the __$ start_lsn and __$ operation columns. The former column stores the database lsn corresponding to this operation, and the latter column stores the current operation type, the operation Type of the __$ operation column is as follows:
1 = delete,
2 = insert,
3 = update (old value ),
4 = update (new value ).

Through the above operations, we can see that the configuration of the CDC process is relatively simple, and you can easily track the data operations performed in the original table in the corresponding CDC table, with the tracked data, the corresponding developers can easily capture the data in the source database and table of sqlserver and synchronize it to the corresponding other databases, compared with the 2005 trigger method, the efficiency and convenience are greatly improved.

This article is from the "Tian Zhi technical blog" blog. Please keep this source