Data Transmission Service:Migrate data from RDS SQL Server to DataHub

Source database

• The source server must have enough outbound bandwidth. Insufficient bandwidth reduces migration speed.

• Tables to be migrated must have PRIMARY KEY or UNIQUE constraints, with all fields unique. Tables without these constraints may produce duplicate records in the destination.

• If you rename tables or columns in the destination, a single migration task supports up to 1,000 tables. Exceeding this limit causes a request error. In this case, split the tables across multiple tasks or migrate at the database level instead.

• A single migration task supports up to 10 databases. Migrating more than 10 databases in one task may reduce performance and stability.

• For incremental data migration:

  • Enable data logging on the source database. Set the backup mode to Full and perform a full physical backup.

  • If you run incremental data migration only, retain transaction logs for more than 24 hours. If you run both full and incremental data migration, retain logs for at least 7 days. DTS may fail to obtain logs if the retention period is too short, which can cause task failure, data inconsistency, or data loss. After full data migration completes, you can reduce the retention period to more than 24 hours. Retention periods that do not meet these requirements are outside the scope of the DTS Service Level Agreement (SLA).

• For CDC to work correctly, the following conditions must be met. If they are not met, the precheck fails:

  • 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 databases, the database owner must be the sa user. For ApsaraDB RDS for SQL Server databases, the database owner must be the sqlsa user.

  • Enterprise edition: SQL Server 2008 or later.

  • Standard edition: SQL Server 2016 SP1 or later.

  • If the source runs SQL Server 2017 (Standard or Enterprise edition), update to a later version before migration.

• DTS uses the fn_log function to read source database logs. Do not clear logs before the task completes — clearing logs can cause the task to fail.

• During schema migration and full data migration, do not execute DDL statements on the source database. Schema changes during migration cause the task to fail.

• If you run incremental data migration only, do not write data to the source database during migration. To guarantee data consistency, run schema migration, full data migration, and incremental data migration together.

• If the source is an Azure SQL Database, only one database can be migrated per task.

• If the source is an ApsaraDB RDS for SQL Server instance and the task includes incremental data migration, disable Transparent Data Encryption (TDE) on the instance. See Configure TDE.

• During full data migration, enable the READ_COMMITTED_SNAPSHOT isolation level on the source database. Without it, shared locks may block writes, cause data inconsistency, or prevent the instance from running. These issues are outside the scope of the DTS SLA.

• In hybrid log-based parsing mode, do not add or remove columns more than once within a 10-minute window. For example, executing both of the following statements within 10 minutes causes a task error:

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

Destination DataHub project

• A single string value cannot exceed 2 MB in length.

Objects and data types

DTS supports schema migration for: schemas, tables, views, functions, and stored procedures.

The following object types are not migrated:

• Assemblies

• Service brokers

• Full-text indexes and full-text catalogs

• Distributed schemas and distributed functions

• Common language runtime (CLR) stored procedures, CLR scalar-valued functions, and CLR table-valued functions

• Internal tables, system objects, and aggregate functions

• Tables that contain computed columns

The following SQL Server data types are not migrated:

Incremental data migration

• DML operations supported: INSERT, UPDATE, DELETE.

  If an UPDATE operation modifies only large field values, DTS does not migrate that operation.

• Reindexing operations are not allowed while incremental data migration is running. Reindexing can cause the task to fail and may result in data loss.

  DTS cannot migrate DDL operations related to the primary key of a CDC-enabled table.

• If the number of CDC-enabled tables in a single migration task exceeds the maximum supported by DTS, the precheck fails.

• If CDC-enabled tables require field values larger than 64 KB, run the following command on the source database before starting the task:

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

  By default, a CDC job can process data up to 64 KB per field.

• DTS does not migrate foreign keys. Cascade and delete operations defined on the source database are not replicated to the destination.

Internal objects created by DTS

DTS creates the following objects in the source database to track migration progress:

For tables with CDC enabled, set the maximum ingestion rate to 1,000 records per second (RPS).

Other considerations

• Migrate data during off-peak hours. Full data migration increases read and write load on both the source and destination.

• Do not use tools other than DTS to write data to the destination DataHub project during migration. Doing so may cause data inconsistency.

• Multiple DTS instances sharing the same source SQL Server database capture incremental data independently.

• If a task fails, DTS technical support attempts to restore it within 8 hours. During restoration, the task may be restarted and task parameters (not database parameters) may be modified. For the parameters that may be modified, see Modify instance parameters.

• Full data migration uses concurrent INSERT operations, which may cause table fragmentation in the destination. The destination tablespace size may be larger than the source after migration.

Special cases

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

Step 1: Open the Data Migration page

Use one of the following consoles:

DTS console

1. Log on to the DTS console.

2. In the left-side navigation pane, click Data Migration.

3. In the upper-left corner, select the region where the migration instance resides.

DMS console

The exact navigation path varies depending on the mode and layout of the DMS console. See Simple mode and Customize the layout and style of the DMS console.

1. Log on to the DMS console.

2. In the top navigation bar, go to Data + AI > DTS (DTS) > Data Migration.

3. From the drop-down list next to Data Migration Tasks, select the region where the migration instance resides.

Step 2: Configure the task

1. Click Create Task.

2. Configure the source and destination databases.

   Warning

   After configuring the source and destination databases, read the Limits displayed at the top of the page before proceeding. Skipping this step may cause the task to fail or result in data inconsistency.

   Source database

   Parameter	Description
   Task Name	A name for the DTS task. DTS generates a name automatically. Specify a descriptive name to make the task easy to identify. The name does not need to be unique.
   Select Existing Connection	If the source instance is registered with DTS, select it from the drop-down list. DTS populates the remaining parameters automatically. Otherwise, configure the parameters below. In the DMS console, select the instance from Select a DMS database instance. See Manage database connections.
   Database Type	Select SQL Server.
   Access Method	Select Alibaba Cloud Instance.
   Instance Region	The region where the source ApsaraDB RDS for SQL Server instance resides.
   Replicate Data Across Alibaba Cloud Accounts	Select No for same-account migration.
   RDS Instance ID	The ID of the source ApsaraDB RDS for SQL Server instance.
   Database Account	The database account for the source instance. See Required database account permissions.
   Database Password	The password for the database account.
   Encryption	Select Non-encrypted if SSL is disabled on the source database. Select SSL-encrypted if SSL is enabled. DTS trusts the server certificate by default.

   Destination database

   Parameter	Description
   Select Existing Connection	If the destination instance is registered with DTS, select it from the drop-down list. DTS populates the remaining parameters automatically. Otherwise, configure the parameters below. See Manage database connections.
   Database Type	Select DataHub.
   Access Method	Select Alibaba Cloud Instance.
   Instance Region	The region where the destination DataHub project resides.
   Project	The name of the destination DataHub project.

3. Click Test Connectivity and Proceed.

   DTS server CIDR blocks must be added to the security settings of the source and destination databases. DTS can add these automatically, or you can add them manually. See Add the CIDR blocks of DTS servers. If the source or destination is a self-managed database not accessed through Alibaba Cloud Instance, click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

Step 3: Select objects to migrate

On the Configure Objects page, configure the following parameters:

Step 4: Configure advanced settings

Click Next: Advanced Settings and configure the following parameters:

Step 5: Run the precheck

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.

DTS runs a precheck before starting the migration task. The task can only start after the precheck passes.

If the precheck fails:

• Click View Details next to each failed item, analyze the cause, resolve the issue, and click Precheck Again.

If a precheck alert is triggered:

• For alerts that cannot be ignored: click View Details, resolve the issue, and run the precheck again.

• For alerts that can be ignored: click Confirm Alert Details > Ignore > OK, then click Precheck Again. Ignoring alerts may result in data inconsistency.

Step 6: Purchase a data migration instance

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

2. On the Purchase Instance page, configure the following:

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

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

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