Data Transmission Service:Synchronize data from a self-managed SQL Server database to a PolarDB for MySQL cluster

Prerequisites

Before you begin, ensure that you have:

• A self-managed SQL Server database running a version supported by DTS. For supported versions, see Overview of data synchronization scenarios

• A PolarDB for MySQL cluster already created. See Purchase an Enterprise Edition cluster or Purchase a subscription cluster

• Enough available storage on the PolarDB for MySQL cluster to hold all data from the source SQL Server database

If the source database meets any of the following conditions, split the task into multiple subtasks:

• Contains more than 10 databases

• Backs up logs more frequently than once per hour

• Executes more than 100 DDL statements per hour per database

• Writes logs at more than 20 MB/s per database

• Requires change data capture (CDC) to be enabled for more than 1,000 tables

Choose an incremental synchronization mode

Before configuring the task, decide which incremental synchronization mode fits your source database. This choice affects both the source database preparation steps and the limits that apply.

For ApsaraDB RDS for SQL Server instances running the Web edition, you must use Incremental synchronization based on source database logs.

To check whether your tables are heap tables, tables without primary keys, compressed tables, or tables with computed columns, see the FAQ.

Billing

Supported synchronization topologies

• One-way one-to-one synchronization

• One-way one-to-many synchronization

• One-way many-to-one synchronization

For all supported topologies, see Synchronization topologies.

SQL operations that can be synchronized

The following DDL operations are not synchronized:

• CREATE TABLE for partitioned tables or tables containing functions

• DDL operations involving user-defined types

• Online DDL operations

• Transactional DDL statements

Limitations

Prepare the source database

Configure log settings on the self-managed SQL Server database before creating the synchronization task. If you are synchronizing from multiple databases, repeat these steps for each database.

1. Change the recovery model to Full. You can also do this in SQL Server Management Studio (SSMS). See View or Change the Recovery Model of a Database (SQL Server).

   use master;
   GO
   ALTER DATABASE <database_name> SET RECOVERY FULL WITH ROLLBACK IMMEDIATE;
   GO

   Replace <database_name> with the name of your source database. Example:

   use master;
   GO
   ALTER DATABASE mytestdata SET RECOVERY FULL WITH ROLLBACK IMMEDIATE;
   GO

2. Create a logical backup of the source database. Skip this step if a logical backup already exists.

   • <database_name>: source database name

   • <physical_backup_device_name>: path and file name for the backup file

   BACKUP DATABASE <database_name> TO DISK='<physical_backup_device_name>';
   GO

   Example:

   BACKUP DATABASE mytestdata TO DISK='D:\backup\dbdata.bak';
   GO

3. Create a log backup for the source database.

   BACKUP LOG <database_name> to DISK='<physical_backup_device_name>' WITH init;
   GO

   Example:

   BACKUP LOG mytestdata TO DISK='D:\backup\dblog.bak' WITH init;
   GO

Configure the synchronization task

1. Go to the Data Synchronization Tasks page.

   1. Log on to the Data Management (DMS) console.

   2. In the top navigation bar, click Data + AI.

   3. In the left-side navigation pane, choose DTS (DTS) > Data Synchronization.

   The navigation path may vary based on the DMS console mode and layout. See Simple mode and Customize the layout and style of the DMS console. You can also go directly to the Data Synchronization Tasks page.

2. Select the region where the synchronization instance resides. In the new DTS console, select the region in the top navigation bar.

3. Click Create Task. Configure the source and destination databases using the following parameters.

   Section	Parameter	Description
   N/A	Task Name	A descriptive name to identify the task. Does not need to be unique. DTS generates a default name.
   Source Database	Select a DMS database instance	Select an existing DMS database instance to auto-populate the parameters, or leave blank to configure manually.
   	Database Type	Select SQL Server.
   	Access Method	Select Express Connect, VPN Gateway, or Smart Access Gateway.
   	Instance Region	The region where the self-managed SQL Server database resides. If it's a self-managed database, deploy the required network environment first. See Preparation overview.
   	Replicate Data Across Alibaba Cloud Accounts	Select No for this scenario.
   	Connected VPC	The ID of the virtual private cloud (VPC) where the source SQL Server database is deployed.
   	IP address	The server IP address of the source database.
   	Port Number	The service port. Default: 1433.
   	Database Account	The account used to connect to the source database. Must have sysadmin role permissions. See CREATE USER and GRANT (Transact-SQL).
   	Database Password	The password for the database account.
   Destination Database	Select a DMS database instance	Select an existing DMS database instance to auto-populate the parameters, or leave blank to configure manually.
   	Database Type	Select PolarDB for MySQL.
   	Access Method	Select Alibaba Cloud Instance.
   	Instance Region	The region where the PolarDB for MySQL cluster resides.
   	PolarDB Cluster ID	The ID of the destination PolarDB for MySQL cluster.
   	Database Account	The account used to connect to the destination cluster. Must have read and write permissions on the destination database.
   	Database Password	The password for the database account.

4. Click Test Connectivity and Proceed. DTS automatically adds its server CIDR blocks to the whitelist of Alibaba Cloud database instances and to the security group rules of Elastic Compute Service (ECS) instances. For self-managed databases in data centers or on third-party cloud platforms, manually add the DTS server CIDR blocks to the database whitelist. See CIDR blocks of DTS servers.

   Warning

   Adding DTS CIDR blocks to whitelists or security group rules introduces security exposure. Before proceeding, take preventive measures such as using strong credentials, limiting exposed ports, authenticating API calls, and regularly auditing whitelist rules. For additional security, connect the database to DTS through Express Connect, VPN Gateway, or Smart Access Gateway.

5. Configure the objects to synchronize and advanced settings.

   Parameter	Description
   Synchronization Types	Select Schema Synchronization, Full Data Synchronization, and Incremental Data Synchronization. Full data synchronization runs first to copy historical data, providing the baseline for incremental synchronization.
   Processing Mode of Conflicting Tables	Precheck and Report Errors (default): the precheck fails if the destination contains tables with the same names as source tables. Use object name mapping if you need to rename destination tables. Ignore Errors and Proceed: skips the name conflict check. During full synchronization, records with conflicting primary or unique keys are skipped. During incremental synchronization, they overwrite destination records. If schemas differ, initialization may fail or partial data may be lost.
   SQL Server Incremental Synchronization Mode	Select the mode you chose in Choose an incremental synchronization mode. The UI labels are: Log-based Parsing for Non-heap Tables and CDC-based Incremental Synchronization for Heap Tables (Hybrid Log-based Parsing), Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported), and Polling and querying CDC instances for incremental synchronization.
   Source Objects	Select the objects to synchronize and click the arrow icon to move them to Selected Objects. In this heterogeneous database scenario, only tables can be synchronized.
   Selected Objects	To rename a single object in the destination, right-click it and use the rename option. See Map the name of a single object. To rename multiple objects at once, click Batch Edit. See Map multiple object names at a time. To select which SQL operations to synchronize for a specific table, right-click it and configure the operations. To filter rows by condition, right-click the object and specify WHERE conditions. See Specify filter conditions.

6. Click Next: Advanced Settings and configure the following parameters.

   Parameter	Description
   Monitoring and Alerting	Select Yes to receive notifications when the task fails or synchronization latency exceeds a threshold. Configure the alert threshold and notification settings. See Configure monitoring and alerting.
   Retry Time for Failed Connections	How long DTS retries after a connection failure. Range: 10–1,440 minutes. Default: 720. Set to at least 30 minutes. If DTS reconnects within this period, the task resumes. If multiple tasks share the same source or destination database, the shortest retry time takes effect. When DTS retries a connection, you are charged for the DTS instance. We recommend that you specify the retry time range based on your business requirements, and release the DTS instance promptly after the source and destination instances are released.
   Retry Time for Other Issues	How long DTS retries after DDL or DML failures. Range: 1–1,440 minutes. Default: 10. Set to at least 10 minutes. Must be less than Retry Time for Failed Connections.
   Configure ETL	Select Yes to use extract, transform, and load (ETL) to transform data during synchronization. See What is ETL? and Configure ETL.

7. Click Next: Save Task Settings and Precheck. To preview the API parameters for this configuration, hover over the button and click Preview OpenAPI parameters before clicking through.

   DTS runs a precheck before the task starts. If any check fails, click View Details to see the cause and troubleshoot. Then click Precheck Again. If an alert item can be ignored, click Confirm Alert Details, then Ignore, and then Precheck Again. Ignoring an alert may result in data inconsistency.

8. Wait for the Success Rate to reach 100%, then click Next: Purchase Instance.

9. Configure the billing method and instance class for the synchronization instance.

   Section	Parameter	Description
   New Instance Class	Billing Method	Subscription: pay upfront for a fixed term — more cost-effective for long-term use. Pay-as-you-go: billed hourly — suitable for short-term use. Release the instance when you no longer need it to stop billing.
   	Resource Group Settings	The resource group for the synchronization instance. Default: default resource group. See What is Resource Management?
   	Instance Class	The synchronization throughput class. See Instance classes of data synchronization instances.
   	Subscription Duration	Available only for the Subscription billing method. Choose a term of one to nine months, or one, two, three, or five years.

10. Read and accept Data Transmission Service (Pay-as-you-go) Service Terms.

11. Click Buy and Start. In the confirmation dialog, click OK.

The task appears in the task list. You can monitor synchronization progress there.

What's next

• Synchronization topologies — set up one-to-many or many-to-one topologies

• Map object names — rename objects in the destination database

• Configure monitoring and alerting — set up latency and failure alerts

• View task logs — inspect DDL failures and task activity