Configuring a Job for Synchronizing Data from SQL Server to Doris

Supported Source and Destination Database Versions

**Table 1** Supported database versions
Source Database	Destination Database
SQL Server database (Enterprise Edition 2016, 2017, 2019, and 2022; Standard Edition 2016 SP2 and later, 2017, 2019, and 2022)	Doris (Doris1.2 and Doris2.0)

Database Account Permissions

Before you use DataArts Migration for data synchronization, ensure that the source and destination database accounts meet the requirements in the following table. The required account permissions vary depending on the synchronization task type.

**Table 2** Database account permissions
Type	Required Permissions
Source database connection account	sysadmin or view server state permissions, and db_datareader or db_owner permissions of the database to be synchronized Enable CDC for a database and a table. Enable CDC for a database. USE YourDatabaseName; EXEC sys.sp_cdc_enable_db; -- Check whether CDC is enabled for a database. SELECT is_cdc_enabled, name FROM sys.databases WHERE name = 'YourDatabaseName' Enable CDC for a table. EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', -- Schema @source_name = N'YourTable',-- Table name @role_name = NULL,-- (Optional) CDC access role name @supports_net_changes = 0; -- Check whether CDC is enabled for the table. SELECT name,is_tracked_by_cdc FROM sys.tables WHERE name = 'YourTable'; The following permissions for the SQL Server must be granted to the user configured in the data connection: Grant the CONNECT and VIEW DATABASE STATE permissions to the user. USE YourDatabaseName; GRANT CONNECT, VIEW DATABASE STATE TO [YourUserName]; Grant the SELECT permissions on the CDC schema to the user. USE YourDatabaseName; GRANT SELECT ON SCHEMA::[cdc] TO [YourUserName]; Grant the SELECT permissions on the table to the user. USE YourDatabaseName; GRANT SELECT ON OBJECT::[YourSchema].[YourTable] TO [YourUserName];
Destination database connection account	The account must have the following permissions for each table in the destination database: LOAD, SELECT, CREATE, ALTER, and DROP.

You are advised to create independent database accounts for DataArts Migration task connections to prevent task failures caused by password modification.
After changing the account passwords for the source or destination databases, modify the connection information in Management Center as soon as possible to prevent automatic retries after a task failure. Automatic retries will lock the database accounts.

Supported Synchronization Objects

The following table lists the objects that can be synchronized using different links in DataArts Migration.

**Table 3** Synchronization objects
Type	Note
Synchronization objects	DML operations INSERT, UPDATE, and DELETE can be synchronized. DDL operations cannot be synchronized. Only primary key tables can be synchronized. Transparent Data Encryption (TDE) encrypted databases in the source instance cannot be synchronized. Column encryption is not supported. Auto-increment columns cannot be synchronized. Table structures, common indexes, constraints (primary key, null, and non-null), and comments can be synchronized during automatic table creation.

Important Notes

In addition to the constraints on supported data sources and versions, connection account permissions, and synchronization objects, you also need to pay attention to the notes in the following table.

**Table 4** Important Notes
Type	Usage and Operation Constraint
Database constraints	If Force Protocol Encryption is set to Yes for the source database, Trust Server Certificate also must be set to Yes. Figure 1 Client configuration Object names in the destination database must meet the following requirements: The table name can contain a maximum of 64 characters and must start with a letter. Only letters, digits, underscores (_), and hyphens (-) are allowed. The field name can contain a maximum of 255 characters. You are advised to use common characters. Do not use special characters such as Chinese characters.
Usage constraints	General: During real-time synchronization, the IP addresses, ports, accounts, and passwords cannot be changed. Full synchronization phase: During task startup and full data synchronization, do not perform DDL operations on the source database. Otherwise, the task may fail. Incremental synchronization phase: DML operations INSERT, UPDATE, and DELETE can be synchronized. DDL operations performed on the source database will not be synchronized to the destination database. The IMAGE, TEXT, and NTEXT big data types cannot be deleted. Troubleshooting: If any problem occurs during task creation, startup, full synchronization, incremental synchronization, or completion, rectify the fault by referring to FAQs.
Other constraints	Tables in the source database can contain more or less columns than those in the destination database. However, task failures may occur in the following scenarios: Assume that extra columns in the destination database cannot be null and have no default values. If newly inserted data records are synchronized from the source database to the destination database, the extra columns will become null, which does not meet the requirements of the destination database and will cause the task to fail. Do not perform primary/standby switchover on the source database. Otherwise, the synchronization task will fail. Source Microsoft SQL Server databases using TLS 1.0 or TLS 1.1 cannot be synchronized. To enable synchronization of such databases, you are advised to upgrade the protocol used by the databases to TLS 1.2 or later. Doris cannot use a string as the primary key, even if as one of the fields in a composite primary key.

Procedure

This section uses real-time synchronization from Microsoft SQL Server to Doris as an example to describe how to configure a real-time data migration job. Before that, ensure that you have read the instructions described in Performing a Check Before Using a Real-Time Job and completed all the preparations.

Create a real-time migration job by following the instructions in Creating a Real-Time Migration Job and go to the job configuration page.
Select the data connection type. Select SQLServer for Source and Doris for Destination.

Figure 2 Selecting the data connection type
Select a job type. The default migration type is Real-time. The migration scenario is Entire DB.

Figure 3 Setting the migration job type

For details about synchronization scenarios, see Synchronization Scenarios.
Configure network resources. Select the created SQL Server and Doris data connections and the migration resource group for which the network connection has been configured.

Figure 4 Selecting data connections and a migration resource group

If no data connection is available, click Create to go to the Manage Data Connections page of the Management Center console and click Create Data Connection to create a connection. For details, see Configuring DataArts Studio Data Connection Parameters.

If no migration resource group is available, click Create to create one. For details, see Buying a DataArts Migration Resource Group Incremental Package.
Check the network connectivity. After the data connections and migration resource group are configured, perform the following operations to check the connectivity between the data sources and the migration resource group.
- Click Source Configuration. The system will test the connectivity of the entire migration job.
- Click Test in the source and destination and migration resource group.
  
  If the network connectivity is abnormal, see How Do I Troubleshoot a Network Disconnection Between the Data Source and Resource Group?
Configure source parameters.
- Select the SQL Server databases and tables to be migrated.
  Figure 5 Selecting databases and tables
  
  Both databases and tables can be customized. You can select one database and one table, or multiple databases and tables.

Configure destination parameters.

Set Database and Table Matching Policy.

Database Matching Policy
- Same name as the source database: Data will be synchronized to the Doris database with the same name as the source SQL Server schema.
- Custom: Data will be synchronized to the Doris database you specify.

Table Matching Policy

Same name as the source table: Data will be synchronized to the Doris table with the same name as the source SQL Server schema.
Custom: Data will be synchronized to the Doris table you specify.
Figure 6 Database and table matching policy in the entire database migration scenario

When you customize a matching policy, you can use built-in variables #{source_db_name} and #{source_table_name} to identify the source database name and table name. The table matching policy must contain #{source_table_name}.

Set Doris parameters.

You can configure the advanced parameters in the following table to enable some advanced functions.

**Table 5** Doris advanced parameters
Parameter	Type	Default Value	Unit	Description
doris.request.connect.timeout.ms	int	30000	ms	Doris connection timeout interval
doris.request.read.timeout.ms	int	30000	ms	Doris read timeout interval
doris.request.retries	int	3	-	Number of retries upon a Doris request failure
sink.max-retries	int	3	-	Maximum number of retries upon a data writing failure
sink.batch.interval	string	1s	h/min/s	Interval at which an asynchronous thread writes data
sink.enable-delete	boolean	true	-	Whether to enable the deletion function. If this function is disabled, data deleted from the source will not be deleted from the destination.
sink.batch.size	int	20000	-	Maximum number of rows that can be written (inserted, updated, or deleted) at a time
sink.batch.bytes	long	10485760	bytes	Maximum number of bytes that can be written (inserted, updated, or deleted) at a time
logical.delete.enabled	boolean	false	-	Whether to enable logical deletion. If this function is enabled, the destination must contain the deletion flag column. When data is deleted from the source database, the corresponding data in the destination database will not be deleted. Instead, the deletion flag column is set to true, indicating that the data is not contained at the source.
logical.delete.column	string	logical_is_deleted	-	Name of the logical deletion column. The default value is logical_is_deleted. You can customize the value.
sink.keyby.mode	string	pk	-	Partitioning mode when concurrent writes are performed on Doris. Default value: pk (primary key). If the source is Kafka, and it does not have a primary key, select table to partition data by table name.
doris.sink.flush.tasks	int	1	-	Number of concurrent flushes of a single TaskManager
sink.properties.format	string	json	-	Data format used by Stream Load. The value can be json or csv.
sink.properties.Content-Encoding	string	-	-	Compression format of the HTTP header message body. Currently, only CSV files can be compressed, and the .gzip format is supported.
sink.properties.compress_type	string	-	-	File compression format. Currently, only CSV files can be compressed. The .gz, .lzo, .bz2, .lz4, .lzop, and .deflate compression formats are supported.

Refresh and check the mapping between the source and destination tables. In addition, you can modify table attributes, add additional fields, and use the automatic table creation capability to create tables in the destination Doris database.

Figure 7 Mapping between source and destination tables
- Automatic table creation: Click Enable Auto Table Creation to automatically create tables in the destination database based on the configured mapping policy. After the tables are created, Existing table is displayed for them.
  Figure 8 Automatic Table Creation
  - DataArts Migration supports only automatic table creation. You need to manually create databases and schemas at the destination before using this function.
  - For details about the field type mapping for automatic table creation, see Field Type Mapping.
  - An automatically created Doris table contains three audit fields: cdc_last_update_date, logical_is_deleted, and _hoodie_event_time. The _hoodie_event_time field is used as the pre-aggregation key of the Doris table.

Configure task parameters.

**Table 6** Task parameters
Parameter	Description	Default Value
Execution Memory	Memory allocated for job execution, which automatically changes with the number of CPU cores.	8GB
CPU Cores	Value range: 2 to 32 For each CPU core added, 4 GB execution memory and one concurrency are automatically added.	2
Concurrency	Maximum number of jobs that can be concurrently executed. This parameter does not need to be configured and automatically changes with the number of CPU cores.	1
Auto Retry	Whether to enable automatic retry upon a job failure	No
Maximum Retries	This parameter is displayed when Auto Retry is set to Yes.	1
Retry Interval (Seconds)	This parameter is displayed when Auto Retry is set to Yes.	120
Write Dirty Data	Whether to record dirty data. By default, dirty data is not recorded. If there is a large amount of dirty data, the synchronization speed of the task is affected. No: Dirty data is not recorded. This is the default value. Dirty data is not allowed. If dirty data is generated during the synchronization, the task fails and exits. Yes: Dirty data is allowed, that is, dirty data does not affect task execution. When dirty data is allowed and its threshold is set: If the generated dirty data is within the threshold, the synchronization task ignores the dirty data (that is, the dirty data is not written to the destination) and is executed normally. If the generated dirty data exceeds the threshold, the synchronization task fails and exits. NOTE: Criteria for determining dirty data: Dirty data is meaningless to services, is in an invalid format, or is generated when the synchronization task encounters an error. If an exception occurs when a piece of data is written to the destination, this piece of data is dirty data. Therefore, data that fails to be written is classified as dirty data. For example, if data of the VARCHAR type at the source is written to a destination column of the INT type, dirty data cannot be written to the migration destination due to improper conversion. When configuring a synchronization task, you can configure whether to write dirty data during the synchronization and configure the number of dirty data records (maximum number of error records allowed in a single partition) to ensure task running. That is, when the number of dirty data records exceeds the threshold, the task fails and exits.	No
Dirty Data Policy	This parameter is displayed when Write Dirty Data is set to Yes. The following policies are supported: Do not archive: Dirty data is only recorded in job logs, but not stored. Archive to OBS: Dirty data is stored in OBS and printed in job logs.	Do not archive
Write Dirty Data Link	This parameter is displayed when Dirty Data Policy is set to Archive to OBS. Only links to OBS support dirty data writes.	-
Dirty Data Directory	OBS directory to which dirty data will be written	-
Dirty Data Threshold	This parameter is only displayed when Write Dirty Data is set to Yes. You can set the dirty data threshold as required. NOTE: The dirty data threshold takes effect for each concurrency. For example, if the threshold is 100 and the concurrency is 3, the maximum number of dirty data records allowed by the job is 300. Value -1 indicates that the number of dirty data records is not limited.	100
Enable Heartbeat Tables	Whether to enable heartbeat tables. It is disabled by default. If heartbeat tables are enabled, the real-time migration job creates a heartbeat table at the source (if there are no such tables). Then data will be periodically updated to the table during job execution. Ensure that the data source instance is writable. Ensure that the account has the permissions to create heartbeat tables, write data to heartbeat tables, and extract data from heartbeat tables.	No
Schema/Tablespace	Schema or tablespace of the heartbeat table	test_database
Table Name	Heartbeat table name If the table does not exist, the real-time migration job will automatically create it. Ensure that the account used for the data source connection has the permission to create tables. You can manually create a table. For details about the table format, see Heartbeat Table Format.	test_hearbeat_table
Heartbeat Generation Interval	Interval at which the real-time migration job generates and writes heartbeat data to the heartbeat table, in seconds	10
Write Heartbeat Data to Topic	Controls if heartbeat data is written to topics. It is disabled by default. If this option is enabled, heartbeat data will be written to a destination topic. This option takes effect only for links where Kafka is the destination. For details about the heartbeat data format, see Heartbeat Data Formats.	No
Add Custom Attribute	You can add custom attributes to modify some job parameters and enable some advanced functions. For details, see Job Performance Optimization.	-

Submit and run the job.

After configuring the job, click Submit in the upper left corner to submit the job.

Figure 9 Submitting the job
Click to enlarge

After submitting the job, click Start on the job development page. In the displayed dialog box, set required parameters and click OK.

Figure 10 Starting the job
Click to enlarge

**Table 7** Parameters for starting the job
Parameter	Description
Synchronous Mode	Incremental Synchronization: Incremental data synchronization starts from a specified time point. Full and incremental synchronization: All data is synchronized first, and then incremental data is synchronized in real time.
Time	This parameter must be set for incremental synchronization, and it specifies the start time of incremental synchronization. NOTE: If you set a time that is earlier than the earliest CDC log time, the latest log time is used.

Monitor the job.

On the job development page, click Monitor to go to the Job Monitoring page. You can view the status and log of the job, and configure alarm rules for the job. For details, see Real-Time Migration Job O&M.

Figure 11 Monitoring the job