Data Transmission Service:Migrate RDS for SQL Server to PolarDB for MySQL

Source database requirements

• Tables to be migrated must have primary keys or UNIQUE constraints with unique fields. Otherwise, duplicate data may appear in the destination database.

• A single migration task supports a maximum of 1,000 tables when you edit table-level objects (such as mapping table or column names). If you exceed this limit, split the tables into multiple migration tasks or configure the task to migrate an entire database.

• A single migration task supports a maximum of 10 databases. Exceeding this limit may cause stability and performance issues.

• If you migrate specific objects instead of an entire database, tables with the same name but different schema names cannot be migrated to the same destination database.

• The source database server must have sufficient outbound bandwidth. Insufficient bandwidth reduces migration speed.

• READ_COMMITTED_SNAPSHOT must be enabled on the source database during full data migration. Disabling it allows shared locks that can affect data writes and may cause data inconsistency or instance failures. Exceptions from this cause are not covered by the DTS Service-Level Agreement (SLA).

Incremental migration requirements

For incremental migration, the source database must meet these conditions:

• Logs must be enabled with the backup mode set to Full, and a full physical backup must have completed successfully.

• For incremental-only tasks, DTS requires logs to be retained for more than 24 hours. For tasks that include both full and incremental migration, logs must be retained for at least 7 days. You can increase the retention period to more than 24 hours after full migration completes. Insufficient log retention may cause the DTS task to fail, and in extreme cases, data inconsistency or data loss may occur. Issues caused by insufficient log retention are not covered by the DTS SLA.

• DTS uses the fn_log function to obtain source database logs. Do not clear source database logs prematurely. Otherwise, the DTS task may fail.

• Do not perform DDL schema changes during initial schema synchronization and full data migration. Otherwise, the migration task fails.

• Do not write new data to the source instance if you perform only full data migration. To ensure real-time data consistency, select Schema Migration, Full Data Migration, and Incremental Data Migration together.

• If a migration task includes incremental data migration, do not reindex. Otherwise, the task may fail or data may be lost.

You cannot change the primary keys of tables with CDC enabled.

CDC enablement conditions

To enable CDC for tables in the source database, all of 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 sa.

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

• Enterprise Edition: SQL Server 2008 or later.

• Standard Edition: SQL Server 2016 SP1 or later.

• SQL Server 2017 (Standard or Enterprise Edition): upgrade the database version before enabling CDC.

Data types and objects that cannot be migrated

The following SQL Server data types cannot be migrated: CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, GEOGRAPHY, and user-defined types created with the CREATE TYPE command.

The following object types cannot be migrated: PROCEDURE, FUNCTION, TRIGGER, DATATYPE, SYNONYM, CATALOG, PLAN_GUIDE, and SEQUENCE.

Complex DDL operations cannot be migrated.

Special scenarios

Other considerations

• If you use the sp_rename command to rename objects (such as stored procedures) before the initial schema synchronization task runs, the task may not work as expected or may fail. Use the ALTER command to rename objects instead.

• Full data migration uses concurrent INSERT operations, which cause table fragmentation in the destination database. After full data migration completes, the destination table storage space is larger than the source.

• If a DDL statement fails to write to the destination database, the DTS task continues running. View the failed statement in the task logs. For details, see View task logs.

• Schedule migration during off-peak hours. DTS consumes read and write resources on both source and destination databases during full data migration, which increases database load.

• If a task fails, DTS support staff will attempt to restore it within eight hours. During restoration, they may restart the task or adjust DTS task parameters (not database parameters). For adjustable parameters, see Modify instance parameters.

Schema migration note

During schema migration, DTS migrates foreign keys from the source to the destination database. During full and incremental data migration, DTS temporarily disables constraint checks and cascade operations on foreign keys at the session level. Performing cascade updates or deletes on the source during migration may cause data inconsistency.

Hybrid log parsing mode (additional limits)

In hybrid log parsing mode, do not consecutively add or remove columns within an interval of less than 10 minutes. For example, the following consecutive statements cause a task error:

ALTER TABLE test_table DROP COLUMN Flag;
ALTER TABLE test_table ADD Remark nvarchar(50) not null default('');

DTS creates the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL storage table in the source database. In hybrid incremental synchronization mode, DTS additionally enables database-level CDC and CDC for some tables. Keep the data change rate for CDC-enabled tables at or below 1,000 records per second (RPS).

Step 1: Open the migration task list

Step 2: Configure the source and destination databases

1. Click Create Task.

2. Configure the source database.

   Warning

   After you select the source and destination instances, carefully read the limits displayed at the top of the page. Otherwise, the task may fail or data inconsistency may occur.

   Parameter	Value
   Task Name	DTS generates a name automatically. Specify a descriptive name for easy identification. The name does not need to be unique.
   Select Existing Connection	Select a registered database instance from the drop-down list to auto-fill the connection details. If the instance is not registered, configure the connection manually. Note In the DMS console, this parameter is named Select a DMS database instance.
   Database Type	Select SQL Server.
   Access Method	Select Alibaba Cloud Instance.
   Instance Region	Select the region of the source RDS for SQL Server instance.
   Replicate Data Across Alibaba Cloud Accounts	Select No (same-account migration).
   RDS Instance ID	Select the ID of the source RDS for SQL Server instance.
   Database Account	Enter the database account. For permission requirements, see Permissions required.
   Database Password	Enter the account password.
   Encryption	Select Non-encrypted if SSL encryption is disabled on the source database. Select SSL-encrypted if SSL encryption is enabled. DTS trusts the server certificate by default.

3. Configure the destination database.

   Parameter	Value
   Select Existing Connection	Select a registered database instance from the drop-down list to auto-fill the connection details. If the instance is not registered, configure the connection manually. Note In the DMS console, this parameter is named Select a DMS database instance.
   Database Type	Select PolarDB for MySQL.
   Access Method	Select Alibaba Cloud Instance.
   Instance Region	Select the region of the destination PolarDB for MySQL cluster.
   PolarDB Cluster ID	Select the ID of the destination PolarDB for MySQL cluster.
   Database Account	Enter the database account. For permission requirements, see Permissions required.
   Database Password	Enter the account password.
   Encryption	Select a connection method as needed. For details about SSL encryption, see Enable SSL encryption.

4. Click Test Connectivity and Proceed at the bottom of the page.

   DTS automatically adds its IP address ranges to the security settings of the source and destination databases. If the source or destination is a self-managed database (Access Method is not Alibaba Cloud Instance), also click Test Connectivity in the CIDR Blocks of DTS Servers dialog box. For details, see Add DTS server IP addresses to a whitelist.

Step 3: Configure migration objects

On the Configure Objects page, configure the following settings.

Parameter	Description
Migration Types	Select the migration types for your scenario. For full migration only, select Schema Migration and Full Data Migration. For zero-downtime migration, also select Incremental Data Migration. Note If you skip Schema Migration, make sure the destination database already has the target databases and tables. If you skip Incremental Data Migration, do not write new data to the source instance during migration.
Schema Mapping Mode of Source and Destination Databases	Select a schema mapping mode to map schemas between source and destination databases. Warning Tables in different schemas of the source database cannot share the same name. Otherwise, data inconsistency or task failure may occur.
SQL Server Incremental Synchronization Mode	Select a mode based on your source database type and table structure. See Choose an incremental synchronization mode. This parameter appears only when Incremental Data Migration is selected.
The maximum number of tables for which CDC is enabled	Specify the maximum number of CDC-enabled tables for this migration instance. Default: 1,000. This option does not appear when Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) is selected.
Processing Mode of Conflicting Tables	Precheck and Report Errors: checks for tables with the same names in the destination database before starting migration. If matches exist, the precheck fails and migration does not start. Note If a conflicting table cannot be deleted or renamed, use Object name mapping to rename it. Ignore Errors and Proceed: skips the duplicate table check. Warning This may cause data inconsistency. During full migration, DTS keeps the destination record; during incremental migration, the source record overwrites the destination record.
Capitalization of Object Names in Destination Instance	Configure the case sensitivity policy for migrated object names (databases, tables, columns). Default: DTS default policy. For details, see Case sensitivity of object names in the destination database.
Source Objects	In the Source Objects box, click the objects to migrate, then click to move them to the Selected Objects box. Note The migration granularity is schema, table, or column. Selecting only tables or columns excludes views, triggers, and stored procedures.
Selected Objects	To rename an object in the destination, right-click it in the Selected Objects box. For details, see Individual table column mapping. To rename multiple objects at once, click Batch Edit. For details, see Map multiple object names at a time. To filter rows, right-click a table and configure the WHERE condition. For details, see Configure filter conditions. To select specific incremental SQL operations, right-click the object and select the desired operations. Note Object name mapping may cause migration failures for other objects that depend on the renamed object.

Step 4: Configure advanced settings

Click Next: Advanced Settings and configure the following parameters.

Step 5: Configure data validation

Click Next: Data Validation to configure a data validation task. For details, see Configure data validation.

Step 6: Save the task and run the precheck

Click Next: Save Task Settings and Precheck.

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

DTS runs a precheck before the migration starts. The task starts only after it passes all precheck items.

• If the precheck fails, click View Details next to the failed item, fix the issue, and run the precheck again.

• If a warning appears:

  • For warnings that cannot be ignored: click View Details, fix the issue, and run the precheck again.

  • For warnings that can be ignored: click Confirm Alert Details > Ignore > OK > Precheck Again to skip the item. Ignoring warnings may cause data inconsistency or business risks.

Step 7: Purchase the instance and start migration

1. When the Success Rate reaches 100%, click Next: Purchase Instance.

2. On the Purchase page, select the link specification for the migration instance.

   Parameter	Description
   Resource Group Settings	Select the resource group for the instance. Default: default resource group. For details, see What is Resource Management?
   Instance Class	Select a specification based on your migration scenario. The link specification affects migration speed. For details, see Data migration link specifications.

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

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