Data Transmission Service:Synchronize data from an ApsaraDB RDS for SQL Server instance to a DataHub project

Source database requirements

• Tables must have a primary key or unique key, with all fields unique. Tables without these constraints may produce duplicate records in the destination.

• When selecting tables as the synchronization objects and applying object renaming, a single task can synchronize up to 5,000 tables. For more than 5,000 tables, configure multiple tasks or synchronize at the database level.

• A single task can synchronize data from up to 10 databases. For more than 10 databases, configure multiple tasks.

• DTS uses the fn_log function to read transaction logs. Do not clear transaction logs while a task is running — doing so may cause the task to fail.

• Disable Transparent Data Encryption (TDE) on the source RDS SQL Server instance to ensure task stability. See Disable TDE.

• Do not perform DDL operations that change database or table schemas during schema synchronization — the task will fail.

• In hybrid log-based parsing mode, do not execute multiple ADD COLUMN or DROP COLUMN operations within 10 minutes. For example, the following sequence within 10 minutes causes a task error:

  ALTER TABLE test_table DROP COLUMN Flag;
  ALTER TABLE test_table ADD Remark nvarchar(50) NOT NULL DEFAULT('');

• Enable READ_COMMITTED_SNAPSHOT on the source database during full data synchronization to prevent shared lock contention. Without this setting, data inconsistency or instance failures may occur and are not covered by the DTS service level agreement (SLA).

• If the source is an Azure SQL Database, a single task can synchronize data from only one database.

Transaction log requirements

Additional requirements:

• The data logging feature must be enabled with the backup mode set to Full, and a full physical backup must have been performed.

• After full synchronization completes, the log retention period can be reduced to more than 24 hours.

Failure to meet log retention requirements may cause DTS to fail to obtain transaction logs, resulting in task failure, data inconsistency, or data loss. Outcomes in such circumstances are not covered by the DTS SLA.

CDC requirements

If CDC needs to be enabled for the source tables, the following conditions must be met — otherwise the precheck fails:

• The value of the srvname field in the sys.sysservers view must match the return value of the SERVERPROPERTY function.

• For self-managed SQL Server: the database owner must be the sa user.

• For ApsaraDB RDS for SQL Server: the database owner must be the sqlsa user.

• Enterprise edition: SQL Server 2008 or later is required.

• Standard edition: SQL Server 2016 SP1 or later is required.

• If running SQL Server 2017 (Standard or Enterprise edition), update to a newer version.

• The number of tables with CDC enabled in a single task cannot exceed 1,000. Tasks exceeding this limit fail the precheck.

• If a single field value written to a CDC-enabled table exceeds 64 KB, run the following command on the source database:

  By default, CDC processes fields up to 64 KB.

  exec sp_configure 'max text repl size', -1;

Destination and other limits

• A single string in the destination DataHub project cannot exceed 2 MB.

• DTS does not synchronize foreign keys. Cascade and delete operations on the source are not propagated to the destination.

• DTS does not synchronize tables that contain computed columns.

• Do not write data from other sources to the destination during synchronization — this causes data inconsistency. For example, running online DDL operations via Data Management (DMS) while other sources write to the destination may cause data loss.

• If the task involves incremental synchronization, reindexing operations are not supported. Performing a reindex may cause the task to fail and result in data loss.

  DTS cannot synchronize DDL operations related to the primary key of a CDC-enabled table.

• When modifying the objects to be synchronized, you cannot remove databases.

• If multiple DTS instances share the same source SQL Server database, their incremental data collection modules operate independently.

• If a DTS task fails, DTS technical support will attempt to restore it within 8 hours. During this period, the task may be restarted and task parameters (not database parameters) may be modified.

Schema synchronization scope

DTS supports initial schema synchronization for: schemas, tables, views, functions, and procedures.

DTS does not synchronize the following object types: assemblies, service brokers, full-text indexes, full-text catalogs, distributed schemas, distributed functions, CLR stored procedures, CLR scalar-valued functions, CLR table-valued functions, internal tables, systems, and aggregate functions.

DTS does not synchronize the following data types: CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, GEOGRAPHY, and custom types created with CREATE TYPE.

Objects DTS creates in the source database

In Incremental Synchronization Based on Logs of Source Database mode, DTS creates the following objects in the source database to track synchronization state:

In hybrid log-based parsing mode, DTS also enables CDC on the source database and specific tables. Set the maximum throughput to 1,000 records per second (RPS) for CDC-enabled tables.

Step 1: Open the Data Synchronization page

Use one of the following methods:

Step 2: Configure source and destination databases

Click Create Task, then configure the following parameters:

After filling in the parameters, click Test Connectivity and Proceed.

Make sure the CIDR blocks of DTS servers are added to the security settings of the source and destination databases. See Add the CIDR blocks of DTS servers. If the source or destination is a self-managed database with an access method other than Alibaba Cloud Instance, click Test Connectivity in the CIDR blocks of DTS servers dialog box.

Step 3: Configure synchronization objects

In the Configure Objects step, set the following parameters:

Parameter	Description	Required
Synchronization types	Incremental Data Synchronization is selected by default. Also select Schema Synchronization.	Yes
Processing mode of conflicting tables	Precheck and Report Errors: fails the precheck if tables with identical names exist in the destination. Ignore Errors and Proceed: skips the name conflict check — use with caution, as this may cause data inconsistency. See the conflict handling behavior details below.	Yes
Additional column rule	When DTS writes data to a DataHub topic, it adds extra columns. If these columns conflict with existing column names in the destination topic, the task fails. Select New Rule or Previous Rule based on your requirements. Check for name conflicts before setting this parameter. See Naming rules for additional columns.	Yes
Case Policy for Destination Object Names	Controls the casing of database, table, and column names in the destination. Default: DTS default policy. See Specify the capitalization of object names in the destination instance.	No
Source Objects	Select objects and click to move them to Selected Objects. Select columns, tables, or databases. If you select tables or columns, DTS does not synchronize other object types such as views, triggers, and stored procedures.	Yes
Selected objects	To rename a single object, right-click it. See Individual database, table, and column name mapping. To rename multiple objects, click Batch Edit. See Batch database, table, and column name mapping. Right-click an object to select specific SQL operations to synchronize, or to set WHERE conditions to filter rows. See Set filter conditions. Renaming an object may break other objects that depend on it.	Yes

Conflict handling behavior (when Ignore Errors and Proceed is selected):

• Same schema, matching primary or unique key: during full synchronization, the existing destination record is kept; during incremental synchronization, the destination record is overwritten.

• Different schemas: initialization may fail, only some columns may be synchronized, or the task may fail.

Step 4: Configure advanced settings

Click Next: Advanced Settings and configure the following:

Step 5: Run the precheck

Click Next: Save Task Settings and Precheck.

To preview the API parameters for this task configuration, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.

DTS runs a precheck before starting the task. Handle precheck results as follows:

• Failure: click View Details next to the failed item, resolve the issue, then rerun the precheck.

• Alert — cannot be ignored: click View Details, resolve the issue, then rerun the precheck.

• Alert — can be ignored: click Confirm Alert Details. In the View Details dialog box, click Ignore, then click OK. Click Precheck Again to proceed. Ignoring alerts may cause data inconsistency.

Step 6: Purchase and start the instance

1. Wait until Success Rate reaches 100%, then click Next: Purchase Instance.

2. On the Buy page, configure the following:

   Parameter	Description	Required
   Billing method	Subscription: pay upfront; cost-effective for long-term use. Pay-as-you-go: billed hourly; suitable for short-term use. Release the instance when no longer needed to stop charges.	Yes
   Resource group settings	The resource group for the synchronization instance. Default: default resource group. See What is Resource Management?	No
   Instance class	The synchronization throughput tier. Select based on your data volume and latency requirements. See Instance classes of data synchronization instances.	Yes
   Subscription duration	Available for the Subscription billing method. Options: 1–9 months, or 1, 2, 3, or 5 years.	Conditional

3. Read and select Data Transmission Service (Pay-as-you-go) Service Terms.

4. Click Buy and Start. In the dialog box, click OK.

The task appears in the task list. Monitor its progress there.