Configuring SQL Server CDC

Overview

Change Data Capture (CDC) enables ROMA Connect to synchronize data sources in real time and synchronously delete data tables.

This section describes how to enable the CDC function for the SQL Server database.

Prerequisites

Fields of the binary type, such as BINARY, VARBINARY, and IMAGE, cannot be collected.

Procedure

It is recommended that the database administrator configure the CDC function. The following uses the Windows environment as an example.

Enable CDC.
1. Use the SSMS client to connect to the server where the SQL Server database is located, and log in to the database as user sa.
  For details about how to download an SSMS client, see Download SQL Server Management Studio (SSMS).
  
  Password indicates the password of user sa of the database. You can obtain the password from the database administrator.
2. Click New Query and select the database for which you want to enable CDC. Run the following command to start CDC for the SQL Server database:
```
if exists(select 1 from sys.databases where name='fdiromatest' and is_cdc_enabled=0)
begin
    exec sys.sp_cdc_enable_db
end
```
  Replace fdiromatest with the actual database name.
3. Run the following command to check whether CDC is successfully enabled. If 1 is displayed, CDC is successfully enabled.
```
select is_cdc_enabled from sys.databases where name='fdiromatest'
```
  Replace fdiromatest with the actual database name.
4. Enable the table-level configuration.
```
IF EXISTS(SELECT 1 FROM sys.tables WHERE name='baris' AND is_tracked_by_cdc = 0)
BEGIN
    EXEC sys.sp_cdc_enable_table
        @source_schema = 'dbo', -- source_schema
        @source_name = 'baris', -- table_name
        @capture_instance = NULL, -- capture_instance
        @supports_net_changes = 1, -- supports_net_changes
        @role_name = NULL -- role_name
END
```
  Replace baris with the name of the table for which CDC needs to be enabled.
5. Run the following command to query the execution result. If the result is 1, the operation is successful.
```
SELECT is_tracked_by_cdc FROM sys.tables WHERE name='baris'
```
  Replace baris with the name of the table for which CDC needs to be enabled.
  
  After CDC is created, the system creates a series of system catalogs, views, stored procedures, and jobs to implement CDC functions.
After the CDC is configured, you need to create a user and assign permissions to the user.
1. Right-click Security and choose New > Login from the shortcut menu.
2. On the General page, enter the created username and password.
  Select SQL Server authentication and enter the password.
3. On the Server Roles page, select dbcreator and public.
4. On the User Mapping page, select the users and database role members that are mapped to this login.
  1. In the Users mapped to this login area, select the database for which you want to configure CDC, for example, fdiromatest.
  2. In the Database role membership for area, select db_datareader, db_owner, and public.
5. Click OK.

Follow-Up Operations

If the system table structure changes or the table level is adjusted, you need to enable CDC again. The configuration procedure is as follows:

Disable CDC and set schema and name based on the site requirements.

EXEC sys.sp_cdc_disable_table 
 @source_schema = N'dbo',
 @source_name = 'baris', 
 @capture_instance ='all'

Enable CDC again and set schema and name based on the site requirements.

Enable the table-level configuration.

IF EXISTS(SELECT 1 FROM sys.tables WHERE name='baris' AND is_tracked_by_cdc = 0) 
 BEGIN 
     EXEC sys.sp_cdc_enable_table 
         @source_schema = 'dbo', -- source_schema 
         @source_name = 'baris', -- table_name 
         @capture_instance = NULL, -- capture_instance 
         @supports_net_changes = 1, -- supports_net_changes 
         @role_name = NULL -- role_name 
 END

Parent topic: Creating a Composite Data Integration Task

Previous topic: Configuring MySQL CDC (Binlog)

Next topic: Creating a Composite Task