Data Transmission Service:Migrate from RDS SQL Server to RDS MySQL

Source database limits

Incremental migration log requirements:

• Logs must be enabled with the backup mode set to Full. A full physical backup must have been completed.

• Log retention: more than 24 hours for incremental-only tasks; at least 7 days for tasks that include both full and incremental migration. You can change the log retention period after full migration completes. Issues caused by a log retention period shorter than required are not covered by the DTS SLA.

• DTS uses the fn_log function to obtain source database logs. Do not clear source database logs too early; otherwise, the DTS task may fail.

CDC prerequisites:

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

• Database owner: sa for self-managed SQL Server; sqlsa for RDS for SQL Server.

• 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 configuring CDC.

Operation restrictions during migration:

• During initial schema synchronization and full data migration, do not perform DDL operations on database or table schemas; otherwise, the task fails.

• If you perform only full data migration, do not write new data to the source instance during migration; otherwise, data inconsistency occurs. To maintain real-time data consistency, select Initial Schema Synchronization, Full Data Migration, and Incremental Data Migration.

• If a 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.

Web Edition RDS for SQL Server:

Set SQL Server Incremental Synchronization Mode to Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) when configuring the task.

Other limits

• Complex DDL operations cannot be migrated.

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

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

• If the number of CDC-enabled tables in a single task exceeds 1,000, the precheck fails.

• If data contains four-byte characters (uncommon characters), the destination database and tables must use the utf8mb4 charset. If DTS performs schema migration, also set the character_set_server parameter to utf8mb4.

• If incremental data written to a single field of a CDC-enabled table exceeds 64 KB, run exec sp_configure 'max text repl size', -1; on the source database in advance. By default, a CDC job can process a maximum of 64 KB per field.

• Migrate data 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 field column names differ only in case in a table written to the destination MySQL database, the migration result may not be as expected, because column names in MySQL are case-insensitive.

• If a DDL statement fails to write to the destination database, the DTS task continues running. Check the task logs for the failed statement. See Query task logs.

• If multiple DTS instances use the same SQL Server database as the source, their incremental data ingestion modules are independent of each other.

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

• Full data migration uses concurrent INSERT operations, which causes table fragmentation in the destination database. After full data migration, the table storage space in the destination database is larger than that in the source instance. After migration completes (the instance Status changes to Completed), run analyze table <table_name> to confirm all data has been written to the destination table. For example, if an HA switchover is triggered in the destination MySQL database, data may be written only to the memory, causing data loss.

Polling CDC mode limits

• The source database account must have permissions to enable CDC. To enable database-level CDC, the account needs the sysadmin role. To enable table-level CDC, a privileged account is required.

  The privileged account provided by Azure SQL Database meets these requirements. For vCore-based databases, all instance types support CDC. For DTU-based databases, only S3 and later instance types support CDC. The privileged account of Amazon RDS for SQL Server meets the requirements for enabling database-level CDC via stored procedures. Clustered columnstore index tables do not support CDC.

• DTS polls the CDC instance of each table to get incremental data. Migrate no more than 1,000 tables; otherwise, task latency or instability may occur.

• By default, incremental data in the CDC component is retained for 3 days. To adjust the retention period, run:

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

  <time> is in minutes. If a single table has more than 10 million incremental change SQL statements per day, set <time> to 1440.

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

• Do not change the CDC instance of the source database; otherwise, the task may fail or data may be lost.

Hybrid log parsing mode limits

• Incremental migration depends on the CDC component. Make sure the CDC job in the source database is running; otherwise, the DTS task fails.

• By default, incremental data in the CDC component is retained for 3 days. To adjust, run:

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

  <time> is in minutes. Set <time> to 1440 if a single table has more than 10 million incremental change SQL statements per day.

• Enable CDC for no more than 1,000 tables in a single task; otherwise, task latency or instability may occur.

• The prerequisite module of an incremental migration task enables CDC for the source database. During this process, the source database may be briefly locked due to SQL Server database kernel limits.

• Do not consecutively add or remove columns within an interval of less than 10 minutes. For example, running the following SQL statements consecutively causes an 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 also enables database-level CDC and CDC for some tables. Keep the data change rate of CDC-enabled tables at or below 1,000 records per second (RPS).

• You cannot execute SELECT INTO, TRUNCATE, or RENAME COLUMN statements on tables with CDC enabled in the source database. You cannot manually delete triggers created by DTS in the source database.

Special cases

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

SQL Server as a source — important note

SQL Server is a commercial closed-source database. Due to known or unknown format-specific limits, issues may occur when DTS performs CDC and log parsing. Before enabling incremental synchronization or migration for a SQL Server source in production, perform a comprehensive proof of concept (POC) test covering all business change types, table schema changes, and peak-hour stress tests. Make sure the business logic in the production environment matches that in the POC test to guarantee the efficiency and stability of DTS.

Step 1: Go to the migration task list

Use one of the following methods:

Step 2: Configure source and destination databases

Click Create Task to open the task configuration page, then configure the following:

After completing the configuration, click Test Connectivity and Proceed at the bottom of the page.

Make sure the IP address range of the DTS service is added (automatically or manually) to the security settings of the source and destination databases. See Add DTS server IP addresses to a whitelist. If the source or destination database is self-managed (Access Method is not Alibaba Cloud Instance), also click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

Step 3: Configure task objects

On the Configure Objects page, set the following parameters:

Step 4: Configure advanced settings

Click Next: Advanced Settings to configure the following:

Step 5: Configure data validation

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

Step 6: Save and run the precheck

Click Next: Save Task Settings and Precheck.

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

Before the task starts, DTS runs a precheck. The task starts only after it passes the precheck.

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

• If a warning is reported:

  • For items that cannot be ignored, click View Details, fix the issue, and rerun the precheck.

  • For ignorable items, click Confirm Alert Details > Ignore > OK > Precheck Again. Ignoring warnings may cause data inconsistency and pose business risks.

Step 7: Purchase the instance

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

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

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

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

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