Data Transmission Service:Migrating Between RDS for SQL Server Instances

Source database requirements

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

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

• For table-level migration with object mapping (such as renaming tables or columns), a single task supports a maximum of 1,000 tables. Exceeding this limit causes an error. Split the tables into multiple tasks, or configure the task to migrate entire databases instead.

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

• If you select specific objects rather than an entire database, you cannot migrate tables with the same name but different schema names to the same destination 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.

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

• If the source is a Web Edition RDS for SQL Server instance, set SQL Server Incremental Synchronization Mode to Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported).

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

Log requirements for incremental migration:

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

• For an incremental-only task, the source database logs must be retained for more than 24 hours. For a task combining full and incremental migration, retain logs for at least 7 days. Log retention periods shorter than the minimum are not covered by the DTS Service-Level Agreement (SLA).

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

CDC prerequisites for incremental migration:

To enable change data capture (CDC) for the tables to be migrated from the source database, the following conditions must be met. Otherwise, the precheck fails:

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

• If the source database is a self-managed SQL Server instance, the database owner must be sa. If the source database is an RDS for SQL Server instance, the database owner must be sqlsa.

• If the source database is Enterprise Edition, it must be SQL Server 2008 or later.

• If the source database is Standard Edition, it must be SQL Server 2016 SP1 or later.

• If the source database is SQL Server 2017 (Standard or Enterprise Edition), upgrade the database version.

During migration:

• Do not perform DDL operations to change the schemas of databases or tables during initial schema synchronization and full data migration. This causes the task to fail.

• If you run only full data migration, do not write new data to the source instance. To maintain real-time data consistency, enable schema migration, full data migration, and incremental data migration together.

• Disable Transparent Data Encryption (TDE) on the source RDS for SQL Server instance when the task includes incremental migration. For more information, see Disable TDE.

• During full data migration, enable the READ_COMMITTED_SNAPSHOT transaction isolation parameter on the source database. Shared locks may otherwise interfere with data writes, causing data inconsistency or instance failures. Such exceptions are not covered by the DTS SLA.

• To migrate triggers from the source database, the database account must have Owner permissions on the destination database.

• For incremental migration, disable triggers and foreign keys on the destination database. Leaving them enabled causes the migration task to fail.

Unsupported data types

The following data types cannot be migrated: CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, GEOGRAPHY.

If a field of the TIMESTAMP data type in the destination database cannot be written to, DTS does not support full or incremental migration for that table. This may cause data inconsistency or task failures.

Float and double precision

DTS reads FLOAT and DOUBLE columns using ROUND(COLUMN, PRECISION). Without explicit precision configuration, FLOAT values are migrated with a precision of 38 and DOUBLE values with a precision of 308. Confirm that this precision meets your requirements before starting the migration.

Cross-version migration

Check version compatibility before migrating data across different SQL Server versions.

Incremental synchronization mode limitations

Log-based parsing (heap tables are not supported):

Tables to be migrated must have a clustered index that contains primary key columns. Tables without clustered indexes, heap tables, tables without primary keys, compressed tables, tables with computed columns, and tables with sparse columns are not supported.

For information on how to identify 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.

Hybrid log-based parsing:

• DTS creates the dts_cdc_sync_ddl trigger, dts_sync_progress heartbeat table, and dts_cdc_ddl_history DDL storage table in the source database. It also enables database-level CDC and CDC for some tables.

• Do not run SELECT INTO, TRUNCATE, or RENAME COLUMN on tables with CDC enabled. Do not manually delete triggers that DTS creates in the source database.

• Do not perform consecutive add-column or drop-column operations 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('');

• The CDC job in the source database must be running. If it stops, the DTS task fails.

• The recommended maximum number of CDC-enabled tables per task is 1,000. Exceeding this may cause task latency or instability.

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

• The prerequisite module of an incremental migration task enables CDC on the source database. The source database may be briefly locked during this process.

• If a table has a CDC-enabled field with data exceeding 64 KB, run the following command on the source database before starting the task:

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

Polling CDC instances:

• The source database account must have permission to enable CDC. To enable database-level CDC, the account requires the sysadmin role. To enable table-level CDC, a privileged account is sufficient.

  The privileged account from the Azure SQL Database console meets this requirement. For vCore-based databases, all instance types support CDC. For DTU-based databases, only S3 and later instance types support CDC. The privileged account for Amazon RDS for SQL Server can enable database-level CDC through stored procedures.

• Clustered columnstore index tables do not support CDC.

• Incremental data migration has a latency of approximately 10 seconds.

• The recommended maximum number of tables per task is 1,000. Migrating more may cause task latency or instability.

• Do not perform consecutive add-column or drop-column operations (more than two DDL operations within one minute). This may cause the task to fail.

• Do not change the CDC instance of the source database while the task is running. This may cause task failure or data loss.

• By default, incremental data is retained for 3 days. Adjust the retention period using:

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

• The prerequisite module of an incremental migration task enables CDC on the source database. The source database may be briefly locked during this process.

General limits

• We recommend that the data change rate of tables with CDC enabled in the source database does not exceed 1,000 records per second (RPS).

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

• DTS attempts to resume a failed task for up to seven days. Before switching your business to the destination instance, end or release the task, or revoke the write permissions of the DTS database account using the revoke command. Otherwise, auto-resume may overwrite data in the destination instance.

• If the task includes incremental migration, do not reindex tables with CDC enabled. Reindexing may cause task failure or data loss. Do not change the primary keys of these tables.

• If the number of CDC-enabled tables exceeds the value set for The maximum number of tables for which CDC is enabled that DTS supports, the precheck fails.

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

• SQL Server is a commercial closed-source database. Due to known or unknown format-specific limitations, issues may occur when DTS performs CDC and log parsing. Before enabling incremental synchronization or migration for a SQL Server source in a production environment, run a thorough proof of concept (POC) test covering all business change types, table schema changes, and peak-hour stress tests. The business logic in production must be consistent with the POC test environment.

• If a task fails, DTS support staff will attempt to restore it within eight hours. They may restart the task or adjust its parameters. Only DTS task parameters are modified—database parameters are not changed. For a list of adjustable parameters, see Modify instance parameters.

Special cases

• 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. For more information, see System accounts.

• Destination is an RDS for SQL Server instance: DTS automatically creates the database in the destination instance. If the database name does not comply with RDS for SQL Server naming conventions, create the database manually before configuring the migration task. For more information, see Create a database.

Step 1: Open the data migration task list

Step 2: Create a task

Click Create Task to open the task configuration page.

(Optional) Click New Configuration Page in the upper-right corner. If the button already reads Back to Previous Version, you are already on the new configuration page. The new and old pages have different parameters; the new page is recommended.

Step 3: Configure source and destination databases

After completing the configuration, click Test Connectivity and Proceed.

DTS service IP address ranges must be added to the security settings (whitelist) of both source and destination databases. For more information, see Add DTS server IP addresses to a whitelist. If the source or destination database uses a non-Alibaba Cloud connection method, click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

Step 4: Configure migration objects

On the Configure Objects page, set the following parameters:

Click Next: Advanced Settings.

Click Next: Data Validation to configure data validation. For more information, see Configure data validation.

Step 5: Save the task and run a precheck

Click Next: Save Task Settings and Precheck.

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

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

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

• For warnings on non-ignorable items, click View Details and fix the issue.

• For ignorable warnings, click Confirm Alert Details > Ignore > OK > Precheck Again to skip the item. Ignoring a warning may cause data inconsistency.

Step 6: Purchase and start the migration instance

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

2. On the Purchase page, configure the 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 the link specification based on your migration speed requirements. 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.

The migration task appears on the Data Migration Tasks list page. Use the following table to interpret the task status: