Data Transmission Service:Migrate data from an ApsaraDB MyBase for SQL Server instance to an ApsaraDB RDS for SQL Server instance

Source database requirements

• The server on which the source database is deployed must have sufficient outbound bandwidth. Otherwise, the data migration speed decreases.

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

• A single migration task can migrate up to 1,000 tables when you rename tables or columns during migration. For more than 1,000 tables, configure multiple tasks or migrate at the database level instead of the table level.

• A single migration task can migrate up to 10 databases. For more than 10 databases, configure multiple tasks.

For incremental data migration, also make sure:

• The data logging feature is enabled. The backup mode must be set to Full, and a full physical backup must have been performed.

• Log retention period: Insufficient log retention may cause the task to fail, or result in data inconsistency or data loss. Failing to meet these requirements also voids the DTS service level agreement (SLA).

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

  • Full + incremental migration: retain logs for at least 7 days. After full migration completes, you can set the retention to more than 24 hours

For CDC-enabled tables, the following conditions must be met (otherwise the precheck fails):

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

• Database owner must be sa (self-managed SQL Server) or sqlsa (ApsaraDB RDS for SQL Server)

• Enterprise edition: SQL Server 2008 or later

• Standard edition: SQL Server 2016 SP1 or later

• Standard or Enterprise edition running SQL Server 2017: upgrade to a later version

Additional source database limits:

• Do not execute DDL statements that change database or table schemas during schema migration and full data migration. This causes the migration task to fail.

• If you perform full data migration only (without incremental), do not write to the source database during migration. To avoid data inconsistency, select all three migration types: schema migration, full data migration, and incremental data migration.

• Do not clear source database logs before the task completes. DTS uses the fn_log function to retrieve logs, which has performance bottlenecks. Clearing logs prematurely may cause the task to fail.

• Read-only source instances do not support DDL operation migration.

• In hybrid log-based parsing mode, do not perform multiple add-column or drop-column operations on the same table within 10 minutes. For example, the following sequence executed within 10 minutes causes an error:

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

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

Other limitations

• DTS does not migrate the following data types: TEXT, CURSOR, ROWVERSION, SQL_VARIANT, HIERACHYID, GEOMETRY.

• If you use Incremental Synchronization Based on Logs of Source Database mode (non-hybrid), tables must have clustered indexes containing primary key columns. Heap tables, tables without primary keys, compressed tables, and tables with computed columns are not supported. To view heap tables, tables without primary keys, compressed tables, or tables with computed columns, see the FAQ.

• Migrate data between compatible database versions only.

• If CDC-enabled tables in a single migration task exceed 1,000, the precheck fails.

• We recommend that you set the maximum number of records per second to 1,000 for the tables for which CDC is enabled in the source database.

• Incremental data migration requires disabling triggers and foreign keys in the destination database.

• Perform migration during off-peak hours. Full data migration uses read and write resources on both source and destination, which increases load on database servers. Concurrent INSERT operations during full migration also cause table fragmentation in the destination database, making the destination tablespace larger than the source.

• DTS uses ROUND(COLUMN,PRECISION) to retrieve values from FLOAT and DOUBLE columns. Default precision: 38 digits for FLOAT, 308 digits for DOUBLE. Verify that these precision settings meet your requirements before starting migration.

• DTS automatically retries failed migration tasks for up to 7 days. Before switching workloads to the destination database, stop or release any failed tasks—or revoke write permissions from DTS accounts on the destination. Otherwise, the resumed task may overwrite data in the destination with source data.

• Do not run reindexing operations during incremental data migration. This may cause the task to fail and result in data loss. DTS also cannot migrate DDL operations on primary keys for CDC-enabled tables.

Step 1: Go to the Data Migration Tasks page

1. Log on to the Data Management (DMS) console.

2. In the top navigation bar, move the pointer over DTS, then choose DTS (DTS) > Data Migration.

Step 2: Select the region

From the drop-down list on the right side of Data Migration Tasks, select the region where your migration instance resides.

Step 3: Configure source and destination databases

Click Create Task, then configure the following parameters.

Source database

Destination database

Step 4: Test connectivity

Click Test Connectivity and Proceed.

DTS automatically adds its server CIDR blocks to the IP address whitelist of the Alibaba Cloud database instance. For self-managed databases on Elastic Compute Service (ECS) instances, DTS adds CIDR blocks to the ECS security group rules—but you must verify that the ECS instance can reach the database. If the database runs across multiple ECS instances, manually add the DTS CIDR blocks to the security group of each ECS instance. For self-managed databases in data centers or third-party cloud environments, manually add the DTS CIDR blocks to the database's IP address whitelist. For the full list of CIDR blocks, see Add the CIDR blocks of DTS servers.

Step 5: Select objects and configure the synchronization mode

Step 6: Configure advanced settings

Click Next: Advanced Settings, then configure the following parameters.

Data verification settings

To enable data verification after migration, see Enable data verification.

Advanced settings

Step 7: Run the precheck

Click Next: Save Task Settings and Precheck.

DTS runs a precheck before the migration starts. The task can only proceed after the precheck passes.

• If a check item fails, click View Details, resolve the issue, then click Precheck Again.

• If a check item shows an alert:

  • If the alert cannot be ignored, click View Details, fix the issue, then rerun the precheck.

  • If the alert can be safely ignored, click Confirm Alert Details > Ignore > OK > Precheck Again. Ignoring an alert may result in data inconsistency.

Step 8: Purchase the instance

Wait until Success Rate reaches 100%, then click Next: Purchase Instance.

On the Purchase Instance page, configure the following:

Step 9: Start the migration

1. Read and select the Data Transmission Service (Pay-as-you-go) Service Terms check box.

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

The task appears in the task list. Monitor its progress from there.