ApsaraDB RDS:Migrate data between ApsaraDB RDS for SQL Server instances

Scale limits

• A single migration task supports up to 10 databases. Exceeding this limit causes stability and performance issues. Split databases across multiple tasks if needed.

• For incremental migration, keep the number of tables from the source database below 1,000. Exceeding this causes task latency or instability.

• When using object name mapping to rename tables in the destination, a single task supports up to 1,000 tables. Exceeding this causes a request error. Split the tasks or migrate the entire database instead.

• Split into multiple tasks if any of the following conditions apply to the source instance:

  • More than 10 databases

  • Log backup frequency exceeds once per hour for a single database

  • DDL frequency exceeds 100 per hour for a single database

  • Log volume exceeds 20 MB/s for a single database

Table structure requirements

• Tables to be migrated must have PRIMARY KEY or UNIQUE constraints with all unique fields. Otherwise, the destination database may contain duplicate records.

• If the incremental synchronization mode is set to Incremental synchronization based on logs of source database (heap tables are not supported), tables must have clustered indexes that include primary key columns. Heap tables, tables without primary keys, compressed tables, and tables with computed columns are not supported.

• If the incremental synchronization mode is set to Hybrid log-based parsing, the above table structure limitations do not apply, but the Change Data Capture (CDC) jobs in the source database must be running properly.

• CDC cannot be enabled for tables with clustered columnstore indexes.

DDL operation restrictions

• Do not perform DDL operations (such as modifying table or database structures) during schema migration or full data migration. The task will fail.

• Do not run continuous add/drop column operations during incremental migration. More than two add or drop column DDL operations within one minute can cause task failure. Keep intervals between such operations greater than 10 minutes.

• DTS cannot migrate DDL operations on the primary key of a CDC-enabled table.

• DTS does not migrate: transactional DDL (multi-column DDL or DDL combined with DML in a single statement), DDL with user-defined types, online DDL, DDL on objects whose names contain reserved keywords, or DDL run via system stored procedures.

Reindexing restriction

Do not perform reindexing operations during incremental data migration. The task may fail and data loss may occur.

Unsupported data types

The following data types are not supported: CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, GEOGRAPHY.

Log retention requirements

• Incremental migration only: retain source database logs for more than 24 hours

• Full data + incremental data migration: retain logs for at least 7 days (after full migration completes, this can be reduced to more than 24 hours)

• For incremental migration tasks, the data logs of the source database must be enabled, the backup mode must be set to Full, and a full physical backup must have completed successfully.

CDC configuration requirements

• If CDC needs to be enabled on source tables, the srvname field in the sys.sysservers view must match the return value of the SERVERPROPERTY function.

• If the source database is ApsaraDB RDS for SQL Server, the database owner must be sqlsa.

• The CDC component retains incremental data for 3 days by default. Adjust this based on your business requirements.

• If daily average incremental SQL changes for a single table exceed 10 million, set the CDC retention time to 1,440 minutes (1 day).

• If a single field in a CDC-enabled table needs to write data exceeding 64 KB, run the following command on the source database before starting the task:

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

Triggers and foreign keys

If the migration task includes incremental data migration, disable all triggers and foreign keys in the destination database before starting. Otherwise, the task may fail or data loss may occur.

Database naming

If the database name to be migrated does not comply with ApsaraDB RDS for SQL Server naming conventions, create the database in the destination instance manually before configuring the migration task.

Transparent Data Encryption (TDE)

If the source database is ApsaraDB RDS for SQL Server and the task includes incremental migration, disable TDE to ensure stable operation of the migration instance.

DTS system accounts

Do not delete or modify DTS system accounts (such as rdsdt_dtsacct) during task execution. The task will fail.

Task auto-resume and cutover risk

DTS automatically tries to resume migration tasks that failed within the last 7 days. Before switching workloads to the destination instance, end or release the task, or revoke the write permissions of the DTS account on the destination instance. Otherwise, data in the destination instance may be overwritten.

Other notes

• Ensure the server hosting the source database has sufficient egress bandwidth. Insufficient bandwidth reduces migration speed. We recommend performing migration tasks during off-peak hours to avoid increased database loads.

• For cross-version migration, check compatibility in advance. See Feature differences between ApsaraDB RDS for SQL Server versions.

• During hybrid log-based parsing, DTS creates the following objects in the source database: the dts_cdc_sync_ddl trigger, the dts_sync_progress heartbeat table, and the dts_cdc_ddl_history DDL storage table. These cannot be manually deleted.

• During full data migration, enable the READ_COMMITTED_SNAPSHOT transaction isolation mode on the source database to avoid shared locks affecting write operations.

• After full data migration completes, the destination table storage may be larger than the source due to fragmentation from concurrent INSERT operations.

• Incremental data migration is not supported when the source database version is ApsaraDB RDS for SQL Server 2008 or 2008 R2.

Step 1: Connect source and destination databases

1. Go to the Data Transmission Service (DTS) console.

2. In the left navigation pane, click Data Migration and select a region.

3. Click Create Task and configure the source and destination database connections.

   Section	Parameter	Description
   —	Task Name	Enter a descriptive name for easy identification. Uniqueness is not required.
   Source Database	Select Existing Connection	If you previously saved connection details on the DTS Data Connection Management page, select the connection here to skip manual entry.
   	Database Type	Select SQL Server.
   	Access Method	Select Cloud Instance.
   	Instance Region	Select the region of the source ApsaraDB RDS for SQL Server instance.
   	Replicate Data Across Alibaba Cloud Accounts	Select No for migration between instances in the same Alibaba Cloud account.
   	RDS Instance ID	Select the ID of the source instance.
   	Database Account	Enter the source instance database account with the required permissions.
   	Database Password	Enter the password for the database account.
   	Encryption	Select Non-encrypted if SSL is not enabled. Select SSL-encrypted if the source database has Secure Sockets Layer (SSL) enabled — DTS trusts the server certificate by default.
   Destination Database	Select Existing Connection	If you previously saved connection details on the DTS Data Connection Management page, select the connection here to skip manual entry.
   	Database Type	Select SQL Server.
   	Access Method	Select Cloud Instance.
   	Instance Region	Select the region of the destination ApsaraDB RDS for SQL Server instance.
   	Instance ID	Select the ID of the destination instance.
   	Database Account	Enter the destination instance database account with the required permissions.
   	Database Password	Enter the password for the database account.
   	Encryption	Select Non-encrypted if SSL is not enabled. Select SSL-encrypted if the destination database has SSL enabled — DTS trusts the server certificate by default.

4. Click Test Connectivity and Proceed.

   Make sure DTS server IP address ranges are added to the security settings of both the source and destination databases. See Add the CIDR blocks of DTS servers to security settings.

Step 2: Select objects to migrate

On the Configure Objects page, configure the migration settings.

Renaming an object may cause dependent objects to fail migration.

Step 3: Configure advanced settings

Click Next: Advanced Settings and configure the following options.

Step 4: Configure data verification (optional)

Click Next Step: Data Verification to set up a data verification task. See Configure a data verification task.

Step 5: Run the precheck and purchase an instance

1. Click Next: Save Task Settings and Precheck.

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

2. Wait for the precheck to complete. If any items fail, click View Details, troubleshoot the issues, and run the precheck again. If an item triggers an alert that can be ignored, click Confirm Alert Details > Ignore > OK, then click Precheck Again. Ignoring alerts may result in data inconsistency.

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

4. On the Purchase Instance page, configure the instance class.

   Parameter	Description
   Resource Group	The resource group for the migration instance. Default: default resource group. See What is Resource Management?
   Instance Class	Select an instance class based on the required migration speed. See Instance classes of data migration instances.

5. Accept the Data Transmission Service (Pay-as-you-go) Service Terms and click Buy and Start. In the confirmation dialog, click OK.

Monitor the task

View task progress on the Data Migration page.

• Tasks without incremental data migration stop automatically when complete. The status shows Completed.

• Tasks with incremental data migration run continuously and do not stop automatically. The status shows Running.