SQL Server Environment Preparation

By enabling Change Data Capture (CDC) for the data source, you can implement real-time data synchronization and DDL synchronization of data tables.

This document mainly introduces how to enable the CDC function for the SQL Server database.

Confirming the Database Version

Ensure that the SQL Server version is supported by Data Pipeline before using SQL Server databases for data synchronization.

Data Pipeline supports data reading from SQL Server databases of the following types and versions.

Data Pipeline supports data writing into SQL Server databases of the following types and versions.

Enter the database operation page by using the sqlcmd -S Server address -U Username -P Password command or through SQL Server Management Studio.

You can view the database version by using the following command.

select @@version

Enabling CDC for the Source-End Database

Starting the SQL Server Agent Service

Ensure that SQL Server Agent is running.

Checking method: Use a user account granted with the sysadmin role to execute EXEC master.dbo.xp_servicecontrol N'QUERYSTATE', N'SQLSERVERAGENT'. Ensure the result is Running, which indicates that SQL Server Agent is running.

If the result is Stopped, use the user account granted with the sysadmin role to start the database agent service.

Paged-based operations:

Click Start, select Microsoft SQL Server, and click Configuration Tools to open SQL Server Configuration Manager.

Click SQL Server Services, right-click SQL Server Agent service, and click Start, as shown in the following figure.

The green arrow on the icon next to SQL Server Agent and on the toolbar indicates that SQL Server Agent has been started successfully, as shown in the following figure.

Enabling CDC

Enabling CDC for the Database

Use the following command to enable CDC:

use Database name
go
EXEC sys.sp_cdc_enable_db
go

Note:

Enter the name of the database for which you want to enable incremental replication.

Use the following command to confirm that CDC is enabled:

SELECT name, database_id, is_cdc_enabled 
FROM sys.databases WHERE name ='Database name'
go

Note:

Enter the name of the database for which you want to enable incremental replication.

Enabling CDC for the Data Table

Note:

After enabling CDC for the database, you need to enable CDC for the data table as well, otherwise you cannot configure the pipeline task.

Use the following command to enable CDC:

use Database name
go
EXEC sys.sp_cdc_enable_table
@source_schema = N'Schema name',
@source_name = N'Table name',
@role_name = N'Role'
go

Note:

The table name should not contain the schema name. Enter a role that can access the modified data. To use the Data Pipeline function, use a db_datareader database-level role of the SQL Server database.

For example, if the schema name is dbo, the command is as follows.

use Roxy
go
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'test',
@role_name = N'public'
go

The database query page is shown in the following figure.

Use the following command to confirm that CDC is enabled:

use Database name
go
SELECT name,is_tracked_by_cdc
FROM sys.tables
WHERE name = N'Table name'
go

Confirming Database Account Permission

You need to create an account that can access the data source, which will be used in pipeline tasks to access the data source for data extraction and writing.

For details about creating an account, see CREATE USER (Transact-SQL).

Use the following command to create a user, as shown in the following figure.

use Database name;
create login Username with password='Password';
go
create user Username for login Username;
go

Note:

test is the username and your password should be changed to the user's actual database password

You can now use this user to configure the data source, as shown in the following figure.

Role Requirement

To use the Data Pipeline function, use a db_datareader database-level role of the SQL Server database.

You can use the following command to add the user to the fixed database-level role db_datareader.

use Database name;
go
exec sp_addrolemember 'db_datareader','Username'
go

Permission Requirement

To use the SQL Server database in Data Pipeline, use a database user with SELECT permission on tables, INFORMATION_SCHEMA, and sys of the database.

• Grant SELECT permission on the table to be synchronized.

use Database name;
grant select on OBJECT::Schema name.Table name to Username;
// Assuming the database name is master, the schema name is dbo, the table name is users, and the username is test, the command is as follows:
use master;
grant select on OBJECT::dbo.users to test;

• Grant SELECT permission on all tables in a schema.

use Database name;
grant select on SCHEMA::Schema name to Username;
// Assuming the database name is master, the schema name is dbo, and the username is test, the command is as follows:
use master;
grant select on SCHEMA::dbo to test;

Note:

If the capture instance specifies a role, the current user must belong to the role.

• Grant SELECT permission on the cdc schema for incremental synchronization.

-- Incremental permission
use Database name;
grant select on SCHEMA::cdc to Username;

After you configure the database, you can configure the data source in FineDataLink.

Configure the data connection to source and target databases in FineDataLink before configuring pipeline tasks for real-time synchronization. This will allow you to control synchronization tasks by selecting the data source name during the task configuration process. For details, see Data Source Creation and Management.

Set up the pipeline task. For details, see Pipeline Task Configuration.

For details about disabling CDC for a database or a data table, see Enabling and Disabling CDC.

For details about cleaning up CDC logs, see Troubleshooting Change Tracking Auto Cleanup Issues.