Data Transmission Service:Migrate RDS for SQL Server to AnalyticDB for MySQL 3.0

Schema migration

• DTS does not migrate foreign keys from the source to the destination.

• During full and incremental migration, DTS temporarily disables constraint checks and foreign key cascade operations at the session level. If cascade update or delete operations occur in the source database while the task is running, data inconsistency may occur.

• During schema migration, do not perform DDL operations on the databases or tables being migrated. Otherwise, the task fails.

Source database requirements

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

• A single task supports a maximum of 1,000 tables when table-level objects are edited (for example, when mapping table or column names). If you exceed this limit, split the tables into multiple tasks or migrate the entire database.

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

• Tables with the same name but different schema names cannot be migrated to the same destination database.

• If the source is a read-only instance, DDL operations cannot be migrated.

• If the source is an Azure SQL Database, a single DTS task can migrate only one database.

• Do not use the sp_rename command to rename objects (such as stored procedures) before the initial schema synchronization runs. Use the ALTER command instead.

Incremental migration requirements

Log requirements:

• Backup mode must be set to Full, and a full physical backup must have completed successfully.

• For incremental-only tasks, retain data logs for more than 24 hours.

• For tasks that include both full and incremental migration, retain data logs for at least 7 days. After full migration completes, you can reduce the retention period to more than 24 hours. Retaining logs for less than the required period is not covered by the DTS Service-Level Agreement (SLA) and may cause task failure or data inconsistency.

CDC requirements for incremental migration:

• 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): upgrade the database version before migrating.

Additional requirements by mode:

Hybrid Log-based Parsing:

• 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, and enables database-level CDC.

• SELECT INTO, TRUNCATE, and RENAME COLUMN are not supported on tables with CDC enabled.

• Do not manually delete DTS-created triggers.

• Do not consecutively add or remove columns in the source database within 10 minutes. For example, running the following statements in sequence causes a task error:

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

• Recommended maximum: 1,000 CDC-enabled tables per task.

• Recommended maximum data change rate for CDC-enabled tables: 1,000 records per second (RPS).

• By default, the CDC component retains incremental data for 3 days. To extend the retention period, run:

  exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention = <time>;

  <time> is in minutes. If a single table generates more than 10 million incremental records per day, set <time> to 1440.

• The CDC prerequisite module may briefly lock the source database during initialization.

Log-based (non-heap tables):

• DTS uses the fn_log function to read source database logs. Do not clear source logs prematurely, as this may cause the task to fail.

• Tables must have a clustered index containing primary key columns. Heap tables, tables without primary keys, compressed tables, tables with computed columns, and tables with sparse columns are not supported.

  Note

  To view information about these table types, see How to view information about heap tables, tables without primary keys, compressed tables, tables with computed columns, and tables with sparse columns in SQL Server.

Polling CDC:

• The DTS account must have permission to enable CDC. Database-level CDC requires the sysadmin role. Table-level CDC requires a privileged account.

  Note

  The privileged accounts for Azure SQL Database (console), Amazon RDS for SQL Server, and vCore-based Azure instances meet these requirements. For vCore-based Azure SQL databases, all instance types support CDC. For DTU-based Azure SQL databases, only instance types of S3 and later support CDC. Clustered columnstore index tables are not supported with CDC.

• Do not consecutively add or remove columns. For example, more than two DDL operations within one minute may cause the task to fail.

• Do not change the CDC instance of the source database. This may cause data loss.

• Recommended maximum: 1,000 tables per task. Migrating more tables across multiple databases may cause stability and performance issues.

• By default, the CDC component retains incremental data for 3 days. To extend the retention period, run:

  exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention = <time>;

  <time> is in minutes. If a single table generates more than 10 million incremental records per day, set <time> to 1440.

• The CDC prerequisite module may briefly lock the source database during initialization.

• Incremental migration latency is approximately 10 seconds.

Full data migration

• Enable READ_COMMITTED_SNAPSHOT on the source database to prevent shared locks from affecting data writes. Disabling this is not covered by the DTS SLA and may cause data inconsistency or instance failures.

• Do not write new data to the source during full data migration if incremental migration is not enabled. Otherwise, data inconsistency occurs between source and destination.

• Full data migration uses concurrent INSERT operations, which cause table fragmentation in the destination. After migration completes, the table storage size in the destination will be larger than in the source.

• If the source is an RDS for SQL Server instance with incremental migration enabled, disable transparent data encryption (TDE) to maintain task stability. See Disable TDE.

Destination database requirements

• The destination must have a custom primary key, or you must set Primary Key Column in the Configurations for Databases, Tables, and Columns step. Otherwise, migration may fail.

• If disk space usage on any node in the destination cluster exceeds 80%, write performance slows and DTS task latency increases. If usage exceeds 90%, writes fail and the task fails. Estimate required storage before starting.

• If the destination AnalyticDB for MySQL 3.0 cluster runs a backup while the DTS task is active, the task fails.

Data type limitations

Only basic data types can be migrated. The following types cannot be migrated: CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, GEOGRAPHY, and user-defined types created with CREATE TYPE.

DTS reads FLOAT and DOUBLE values using ROUND(COLUMN, PRECISION). The default precision is 38 for FLOAT and 308 for DOUBLE. Confirm that this meets your business requirements before starting.

Other limitations

• If a DDL statement fails to write to the destination, the DTS task continues running. View failed DDL statements in the task logs. See View task logs.

• Do not reindex during incremental migration. Do not change the primary keys of CDC-enabled tables.

• If a single field in a CDC-enabled table receives data larger than 64 KB, run the following command on the source database before starting the task:

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

• If multiple DTS instances use the same SQL Server database as the source, their incremental data ingestion modules operate independently.

• DTS attempts to resume a failed task for up to 7 days. Before switching production traffic to the destination, end or release the task, or revoke write permissions from the DTS account using the revoke command. This prevents source data from overwriting destination data if the task resumes automatically.

• If a task fails, DTS support will attempt to restore it within 8 hours. During restoration, the task may be restarted or its parameters adjusted. Only DTS task parameters are modified—not database parameters. See Modify instance parameters.

• SQL Server is a closed-source commercial database. Before enabling incremental migration in a production environment, run a comprehensive proof of concept (POC) test that covers all business change types, table schema changes, and peak-hour stress scenarios. Business logic in production must match the POC test environment exactly.

Special cases

If the source is an RDS for SQL Server instance, DTS creates an rdsdt_dtsacct account in the source instance. Do not delete this account or change its password while the task is running. See System accounts.

Step 1: Open the migration task list

Use one of the following methods:

Step 2: Configure source and destination databases

1. Click Create Task.

2. On the task configuration page, configure the source database.

   Warning

   After selecting source and destination instances, review the limits displayed at the top of the page. Skipping this step may cause task failure or data inconsistency.

   Parameter	Description
   Task Name	DTS generates a name automatically. Specify a descriptive name for easy identification. Names do not need to be unique.
   Select Existing Connection (Source)	To use a previously registered database instance, select it from the drop-down list. Otherwise, configure the fields below manually. In the DMS console, this parameter is named Select a DMS database instance.
   Database Type	Select SQL Server.
   Connection Type	Select Cloud Instance.
   Instance Region	Select the region where your RDS for SQL Server instance resides.
   Replicate Data Across Alibaba Cloud Accounts	Select No for same-account migration.
   RDS Instance ID	Select your source RDS for SQL Server instance ID.
   Database Account	Enter the database account. See Required permissions.
   Database Password	Enter the account password.
   Encryption	Select Non-encrypted if SSL is disabled on the source; select SSL-encrypted if SSL is enabled. DTS trusts the server certificate by default.

3. Configure the destination database.

   Parameter	Description
   Select Existing Connection (Destination)	To use a previously registered database instance, select it from the drop-down list. Otherwise, configure the fields below manually. In the DMS console, this parameter is named Select a DMS database instance.
   Database Type	Select AnalyticDB MySQL 3.0.
   Connection Type	Select Cloud Instance.
   Instance Region	Select the region where your AnalyticDB for MySQL 3.0 cluster resides.
   Instance ID	Select the ID of your AnalyticDB for MySQL 3.0 cluster.
   Database Account	Enter the database account. See Required permissions.
   Database Password	Enter the account password.

4. Click Test Connectivity and Proceed.

   Note

   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 (where Access Method is not Alibaba Cloud Instance), also click Test Connectivity in the CIDR Blocks of DTS Servers dialog. For details, see Add DTS server IP addresses to a whitelist.

Step 3: Configure task objects

On the Configure Objects page, configure the following settings:

Step 4: Configure advanced settings

Click Next: Advanced Settings and configure the following:

Step 5: Configure data validation

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

Step 6: Configure destination table fields (optional)

Click Next: Configure Database and Table Fields to set the Type, Primary Key Column, Distribution Key, and partition key information (Partition Key, Partitioning Rules, Partition Lifecycle) for the destination tables.

Step 7: Run the precheck

Click Next: Save Task Settings and Precheck.

DTS runs a precheck before the task starts. If the precheck fails:

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

• For warnings that can be ignored: click Confirm Alert Details > Ignore > OK > Precheck Again. Ignoring warnings may cause data inconsistency.

Step 8: Purchase the instance and start migration

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

2. On the Purchase page, select the resource group and instance class. For specification details, see Data migration link specifications.

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

4. Click Buy and Start, then click OK in the confirmation dialog.

View the migration task progress on the Data Migration Tasks list page.