Migrate data between ApsaraDB RDS for SQL Server instances using DTS - ApsaraDB RDS

This topic describes how to migrate data between ApsaraDB RDS for SQL Server instances by using the Data Transmission Service (DTS) console. DTS supports schema migration, full data migration, and incremental data migration. You can select all of these migration types to ensure business continuity during database migration.

Prerequisites

You have created the source and destination ApsaraDB RDS for SQL Server instances.
The storage space of the destination instance must be larger than the used storage space of the source instance. If the storage space is insufficient, increase the instance storage capacity in advance.

Notes

Pay attention to the following key limits before migration. Ignoring these limits may cause task failure or errors:

Database quantity limit: A single migration task cannot migrate more than 10 databases. Otherwise, stability and performance risks may occur.
Table quantity limit: For incremental migration, the number of tables to be synchronized from the source database cannot exceed 1,000. Otherwise, task latency or instability may occur.
Source database operation limits: During schema migration and full data migration, do not perform DDL operations (such as modifying database or table structures). Otherwise, the task will fail.
Table structure requirements: The tables to be migrated must have PRIMARY KEY or UNIQUE constraints, and all fields must be unique. Otherwise, duplicate data may appear in the destination database.
Foreign keys and triggers: If the migration task includes incremental data migration, you must disable the triggers and foreign keys that are enabled in the destination database. Otherwise, the task may fail or data loss may occur.
Database name standards: If the name of the database to be migrated does not comply with the naming conventions of ApsaraDB RDS for SQL Server, you must manually create a database in ApsaraDB RDS for SQL Server in advance. Otherwise, the task may not run properly.
Data log retention time: For incremental migration tasks, the data logs of the source database must be retained for more than 24 hours. For full data and incremental data migration tasks, the data logs must be retained for at least 7 days. Otherwise, the task may fail or data inconsistency may occur.

Click to expand all limits and notes

Limits

Data type limitations
The following data types are not supported for migration: CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, and GEOGRAPHY.
Incremental migration limitations
If the data migration task involves incremental data migration, DTS does not allow you to perform the reindexing operation. Otherwise, the task may fail and data loss may occur.
Note
Incremental data migration is not supported when the source database version is ApsaraDB RDS for SQL Server 2008 or 2008 R2.
Table structure limitations
- If the incremental synchronization mode is set to parse the source database logs for incremental synchronization (heap tables not supported), the tables to be migrated must have clustered indexes, and the clustered indexes must 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 parsing, the above limitations do not apply, but you need to ensure that the CDC jobs in the source database are running properly.
CDC-related limitations
- CDC cannot be enabled for tables with clustered columnstore indexes.
- Changes to the CDC Instance in the source database are not supported. Otherwise, the task may fail or data loss may occur.
DDL operation limitations
- Continuous execution of add/drop column operations (more than two add or drop column DDL operations within one minute) is not supported. Otherwise, the task may fail.
- DTS cannot migrate DDL operations related to the primary key of a table for which CDC is enabled.
Incremental data collection limitations
DTS uses polling to query the CDC Instance of each table to obtain incremental data. Therefore, it is not recommended that the number of tables to be migrated from the source database exceeds 1,000. Otherwise, task latency or instability may occur.
Destination database limitations
If the destination instance is ApsaraDB RDS for SQL Server, DTS automatically creates databases. If the name of the database to be migrated does not comply with the naming conventions of ApsaraDB RDS for SQL Server, you need to manually create the database before configuring the migration task.
Special scenario limitations
- If the daily average number of incremental SQL changes for a single table in the source database exceeds 10 million, you need to adjust the CDC data retention time to 1,440 minutes (1 day).
- If a single field in a table with CDC enabled needs to write data exceeding 64 KB, you need to execute the following command to adjust the source database configuration in advance:
```
EXEC sp_configure 'max text repl size', -1;
```
Task recovery and overwrite risks
DTS attempts to resume data migration tasks that failed within the last seven days. To prevent data in the destination instance from being overwritten by data from the source database after the task is automatically resumed, you must end or release the task, or revoke the write permissions of the DTS account that is used to access the destination instance before you switch your workloads to the destination instance.

Notes

Bandwidth and performance requirements
- The server to which the source database belongs must have sufficient egress bandwidth. Otherwise, the data migration speed is affected.
- We recommend that you perform migration tasks during off-peak hours to avoid increased database loads due to resource consumption.
Table structure and data consistency
- The tables to be migrated must have PRIMARY KEY or UNIQUE constraints, and all fields must be unique. Otherwise, the destination database may contain duplicate data records.
- During schema migration and full data migration, do not perform DDL operations (such as modifying table structures). Otherwise, the task will fail.
- If you perform only full data migration, do not write new data to the source instance. Otherwise, data inconsistency may occur between the source and destination databases. We recommend that you select schema migration, full data migration, and incremental data migration to ensure data consistency.
Log backup and retention time
- 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 been performed successfully.
- For incremental migration tasks, data logs must be retained for more than 24 hours. For full data and incremental data migration tasks, data logs must be retained for at least 7 days (after full migration is complete, this can be adjusted to more than 24 hours). Otherwise, DTS may fail to obtain logs, which could lead to task failure or even data inconsistency or loss.
CDC configuration and operation requirements
- If CDC needs to be enabled for the tables to be migrated from the source database, the following conditions must be met:
  - The srvname field in the sys.sysservers view must be consistent with the return value of the SERVERPROPERTY function.
  - If the source database is ApsaraDB RDS for SQL Server, the database owner must be sqlsa.
- It is not recommended that the number of tables with CDC enabled exceeds 1,000. Otherwise, task latency or instability may occur.
- The incremental data stored by the CDC component is retained for 3 days by default. We recommend that you adjust the retention time based on your business requirements. If the daily average number of incremental SQL changes for a single table exceeds 10 million, we recommend that you set the retention time to 1,440 minutes (1 day).
Operation limitations
- Do not delete or modify system accounts created by DTS (such as rdsdt_dtsacct) during task execution. Otherwise, the task may fail.
- If the source database is ApsaraDB RDS for SQL Server and the task includes incremental migration, disable Transparent Data Encryption (TDE) to ensure the stable operation of the migration instance.
- Avoid continuous execution of add/drop column operations (with time intervals less than 10 minutes). Otherwise, the task may fail.
- If the migration task includes incremental data migration, you must disable the triggers and foreign keys that are enabled in the destination database.
Task scale and stability
- A single migration task supports migrating up to 10 databases. When this limit is exceeded, stability and performance issues may occur. We recommend that you split the tables to be migrated and configure tasks in batches.
- If you select tables as the objects to migrate and you need to edit the tables (such as renaming tables or columns) in the destination database, up to 1,000 tables can be migrated in a single data migration task. If you run a task to migrate more than 1,000 tables, a request error occurs. In this case, we recommend that you split the tasks or configure a task to migrate the entire database.
- If any of the following conditions exist in the source instance, we recommend that you split the migration into multiple tasks:
  - More than 10 databases.
  - The frequency of log backup operations for a single database exceeds once per hour.
  - The frequency of DDL operations for a single database exceeds 100 per hour.
  - The log volume of a single database exceeds 20 MB/s.
Other notes
- If you need to perform cross-version migration, check compatibility in advance. For more information, see Feature differences between ApsaraDB RDS for SQL Server versions.
- When the source database is ApsaraDB RDS for SQL Server, DTS creates triggers (such as dts_cdc_sync_ddl), heartbeat tables (such as dts_sync_progress), and DDL storage tables (such as dts_cdc_ddl_history) in the source database to ensure the accuracy of incremental data migration.
- We recommend that you enable the READ_COMMITTED_SNAPSHOT transaction processing mode parameter of the source database during full data migration to avoid the impact of shared locks on data writing.
- During full data migration, concurrent INSERT operations cause fragmentation in the tables of the destination database. After full data migration is complete, the tablespace of the destination tables may be larger than that of the source tables.
- DTS attempts to resume data migration tasks that failed within the last seven days. To prevent data in the destination instance from being overwritten by data from the source database after the task is automatically resumed, you must end or release the task, or revoke the write permissions of the DTS account that is used to access the destination instance before you switch your workloads to the destination instance.

Billing

Migration type	Instance configuration fee	Internet traffic fee
Schema migration and full data migration	Free of charge.	When the Access Method parameter of the destination database is set to Public IP Address, you are charged for Internet traffic. For more information, see Billing overview.
Incremental data migration	Charged. For more information, see Billing overview.

Permissions required by database accounts

To ensure that the data migration task runs as expected, make sure that the database accounts of the source and destination databases have the following permissions:

Note

You can create an account in the RDS console and modify account permissions as needed.

Database	Schema migration	Full migration	Incremental migration
Source instance	Read permissions	Read permissions	Owner permissions on the objects to be migrated.
Destination instance	Read and write permissions

Procedure

Access the Data Transmission Service (DTS) console.
In the left-side navigation pane, click Data Migration and select a region at the top.

Click Create Task and configure the source and destination database information.

Category	Configuration	Description
None	Task Name	Configure a task name that has business significance (no uniqueness requirement) for easy identification. You can also use the task name that is automatically generated by the system.
Source Database	Select Existing Connection	If you have entered the source database information on the DTS Data Connection Management page, you can directly select the database here. This eliminates the need to manually enter the source database information later.
	Database Type	Select SQL Server.
	Access Method	Select Cloud Instance.
	Instance Region	Select the region where the source ApsaraDB RDS for SQL Server instance is located.
	Replicate Data Across Alibaba Cloud Accounts	In this example, the migration is between instances within the same Alibaba Cloud account. Select No.
	RDS Instance ID	Select the ID of the source ApsaraDB RDS for SQL Server instance.
	Database Account	Enter the database account of the source ApsaraDB RDS for SQL Server instance and make sure that the account has the required permissions.
	Database Password	The password that is used to access the database instance.
	Encryption	If the source database does not have Secure Sockets Layer (SSL) encryption enabled, select Non-encrypted. If the source database has SSL encryption enabled, select SSL-encrypted. DTS trusts the server certificate by default.
Destination Database	Select Existing Connection	If you have entered the destination database information on the DTS Data Connection Management page, you can directly select the database here. This eliminates the need to manually enter the destination database information later.
	Database Type	Select SQL Server.
	Access Method	Select Cloud Instance.
	Instance Region	Select the region where the destination ApsaraDB RDS for SQL Server instance is located.
	Instance ID	Select the ID of the destination ApsaraDB RDS for SQL Server instance.
	Database Account	Enter the database account of the destination ApsaraDB RDS for SQL Server instance and make sure that the account has the required permissions.
	Database Password	The password that is used to access the database instance.
	Encryption	If the destination database does not have SSL encryption enabled, select Non-encrypted. If the destination database has SSL encryption enabled, select SSL-encrypted. DTS trusts the server certificate by default.

After the configuration is complete, click Test Connectivity and Proceed at the bottom of the page.
Note
Please ensure that the IP address ranges of DTS can be automatically or manually added to the Security Settings of the source database and destination database to allow access from DTS servers.

Configure the objects to be migrated.

On the Configure Objects page, configure the objects that you want to migrate.

Configuration	Description
Migration Types	For full migration: We recommend that you select Schema Migration and Full Data Migration. For migration with minimal downtime: We recommend that you select Schema Migration, Full Data Migration, and Incremental Data Migration. Note For more information, see Appendix: SQL operations that can be synchronized during incremental data migration. If you do not select Schema Migration, make sure that the destination database contains the database and tables that are required to receive data. Based on your business requirements, use the object name mapping feature in the Selected Objects section. If you do not select Incremental Data Migration, do not write data to the source instance during data migration to ensure data consistency.
Method to Migrate Triggers in Source Database	Select a method to migrate triggers based on your business requirements. If the objects that you want to migrate do not contain triggers, you do not need to configure this parameter. Note This parameter is available only when you select both Schema Migration and Incremental Data Migration for Migration Types.
SQL Server Incremental Synchronization Mode	Log-based Parsing for Non-heap Tables and CDC-based Incremental Synchronization for Heap Tables (Hybrid Log-based Parsing) Advantages: Supports heap tables, tables without primary keys, compressed tables, and tables with computed columns. Provides higher stability and a variety of complete DDL statements. Disadvantages: 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. DTS also enables database-level CDC and table-level CDC for some tables. The SELECT INTO, TRUNCATE, and RENAME COLUMN statements cannot be executed on tables for which CDC is enabled in the source database. The triggers created by DTS in the source database cannot be manually deleted. Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) Advantage: No intrusion into the source database. Disadvantage: Does not support heap tables, tables without primary keys, compressed tables, or tables with computed columns. Polling and querying CDC instances for incremental synchronization Advantages: Supports full and incremental migration when the source database is Amazon RDS for SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure SQL Server on Virtual Machine, or Google Cloud SQL for SQL Server. Uses the native CDC component of SQL Server to obtain incremental data, which makes incremental migration more stable and consumes less network bandwidth. Disadvantages: The account used by the DTS instance to access the source database must have permissions to enable CDC. Incremental data migration has a latency of approximately 10 seconds. When multiple databases and tables are migrated, stability and performance issues may occur. Note This parameter is available only when the Migration Types includes Incremental Data Migration. If the source database is a web edition of RDS for SQL Server, you must select Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported) for SQL Server Incremental Synchronization Mode when you configure the task. In hybrid log parsing mode (when SQL Server Incremental Synchronization Mode is set to Log Parsing For Non-heap Tables And CDC For Heap Tables), the following source databases are supported: Enterprise or Enterprise Evaluation Edition: 2012, 2014, 2016, 2019, or 2022. Standard Edition: 2016, 2019, or 2022.
The maximum number of tables for which CDC is enabled that DTS supports.	Set a reasonable maximum number of tables for which the current migration instance can enable CDC. The default value is 1000. Note This parameter is not available when SQL Server Incremental Synchronization Mode is set to Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported).
Processing Mode of Conflicting Tables	Precheck and Report Errors: checks whether the destination database contains tables that have the same names as tables in the source database. If the destination database does not contain tables that have the same names as tables in the source database, the precheck is passed and the data migration task starts. Otherwise, an error is returned during the precheck and the data migration task does not start. Solutions: If the tables in the destination database cannot be deleted or renamed, you can use the object name mapping feature to change the names of the tables in the destination database. Ignore Errors and Proceed: skips the precheck for identical table names in the destination database. Warning If you select Ignore Errors and Proceed, data inconsistency may occur and your business may be exposed to risks. For example: If the source and destination databases have the same schema but the destination database contains records with the same primary key values as the source database: During full data migration, DTS will retain the existing records in the destination database and will not migrate the corresponding records from the source database to the destination database. During incremental data migration, the new data from the source database may overwrite the data in the destination database, resulting in data loss in the destination database. If the source and destination databases have different schemas, data migration may fail or only some columns may be migrated. Exercise caution when you perform this operation.
Source Objects	Select one or more objects from the Source Objects section. Click the icon to add the objects to the Selected Objects section. Note You can select columns, tables, or schemas as the objects to be migrated. If you select tables or columns as the objects to be migrated, DTS does not migrate other objects, such as views, triggers, or stored procedures, to the destination database.
Selected Objects	To rename an object that you want to migrate to the destination instance, right-click the object in the Selected Objects section. For more information, see Map the name of a single object. To rename multiple objects at a time, click Batch Edit in the upper-right corner of the Selected Objects section. For more information, see Map multiple object names at a time. Note If you use the object name mapping feature to rename an object, other objects that are dependent on the object may fail to be migrated. To configure a WHERE condition to filter data, right-click the table that you want to migrate in the Selected Objects section and configure a filter condition in the dialog box that appears. To select the SQL operations to be migrated at the database or table level, right-click the object that you want to migrate in the Selected Objects section and select the SQL operations that you want to migrate in the dialog box that appears.

Click Next: Advanced Settings to configure advanced settings.

Configuration	Description
Dedicated Cluster for Task Scheduling	By default, DTS schedules the data migration task to the shared cluster if you do not specify a dedicated cluster. If you want to improve the stability of data migration tasks, purchase a dedicated cluster. For more information, see What is a DTS dedicated cluster.
Retry Time for Failed Connections	The retry time range for failed connections. If the source or destination database fails to be connected after the data migration task is started, DTS immediately retries a connection within the retry time range. Valid values: 10 to 1,440. Unit: minutes. Default value: 720. We recommend that you set the parameter to a value greater than 30. If DTS is reconnected to the source and destination databases within the specified retry time range, DTS resumes the data migration task. Otherwise, the data migration task fails. Note If you specify different retry time ranges for multiple data migration tasks that share the same source or destination database, the value that is specified later takes precedence. When DTS retries a connection, you are charged for the DTS instance. We recommend that you specify the retry time range based on your business requirements. You can also release the DTS instance at the earliest opportunity after the source database and destination instance are released.
Retry Time for Other Issues	The retry time range for other issues. For example, if DDL or DML operations fail to be performed after the data migration task is started, DTS immediately retries the operations within the retry time range. Valid values: 1 to 1440. Unit: minutes. Default value: 10. We recommend that you set the parameter to a value greater than 10. If the failed operations are successfully performed within the specified retry time range, DTS resumes the data migration task. Otherwise, the data migration task fails. Important The value of the Retry Time for Other Issues parameter must be smaller than the value of the Retry Time for Failed Connections parameter.
Enable Throttling for Full Data Migration	Specifies whether to enable throttling for full data migration. During full data migration, DTS uses the read and write resources of the source and destination databases. This may increase the loads of the database servers. You can enable throttling for full data migration based on your business requirements. To configure throttling, you must configure the Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s) parameters. This reduces the loads of the destination database server. Note You can configure this parameter only if you select Full Data Migration for the Migration Types parameter.
Enable Throttling for Incremental Data Migration	Specifies whether to enable throttling for incremental data migration. To configure throttling, you must configure the RPS of Incremental Data Migration and Data migration speed for incremental migration (MB/s) parameters. This reduces the loads of the destination database server. Note You can configure this parameter only if you select Incremental Data Migration for the Migration Types parameter.
Environment Tag	The environment tag that is used to identify the DTS instance. You can select an environment tag based on your business requirements. In this example, you do not need to configure this parameter.
Configure ETL	Specifies whether to enable the extract, transform, and load (ETL) feature. For more information, see What is ETL? Valid values: Yes: configures the ETL feature. You can enter data processing statements in the code editor. For more information, see Configure ETL in a data migration or data synchronization task. No: does not configure the ETL feature.
Monitoring and Alerting	Specifies whether to configure alerting for the data migration task. If the task fails or the migration latency exceeds the specified threshold, the alert contacts receive notifications. Valid values: No: does not configure alerting. Yes: configures alerting. In this case, you must also configure the alert threshold and alert notification settings. For more information, see the Configure monitoring and alerting when you create a DTS task section of the Configure monitoring and alerting topic.

Click Next Step: Data Verification to configure the data verification task.
For more information about how to use the data verification feature, see Configure a data verification task.

Save the task settings and run a precheck.
- To view the parameters to be specified when you call the relevant API operation to configure the DTS task, move the pointer over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.
- If you do not need to view or have viewed the parameters, click Next: Save Task Settings and Precheck in the lower part of the page.
Note
- Before you can start the data migration task, DTS performs a precheck. You can start the data migration task only after the task passes the precheck.
- If the task fails to pass the precheck, click View Details next to each failed item. After you analyze the causes based on the check results, troubleshoot the issues. Then, run a precheck again.
- If an alert is triggered for an item during the precheck:
  If an alert item cannot be ignored, click View Details next to the failed item and troubleshoot the issues. Then, run a precheck again.
  If the alert item can be ignored, click Confirm Alert Details. In the View Details dialog box, click Ignore. In the message that appears, click OK. Then, click Precheck Again to run a precheck again. If you ignore the alert item, data inconsistency may occur, and your business may be exposed to potential risks.

Purchase an instance.

Wait until Success Rate becomes 100%. Then, click Next: Purchase Instance.

On the Purchase Instance page, configure the Instance Class parameter for the data migration instance. The following table describes the parameters.

Section	Parameter	Description
New Instance Class	Resource Group	The resource group to which the data migration instance belongs. Default value: default resource group. For more information, see What is Resource Management?
New Instance Class	Instance Class	DTS provides instance classes that vary in the migration speed. You can select an instance class based on your business scenario. For more information, see Instance classes of data migration instances.

Read and agree to Data Transmission Service (Pay-as-you-go) Service Terms by selecting the check box.
Click Buy and Start. In the message that appears, click OK.
You can view the progress of the task on the Data Migration page.
Note
- If a data migration task cannot be used to migrate incremental data, the task automatically stops. The Completed is displayed in the Status section.
- If a data migration task can be used to migrate incremental data, the task does not automatically stop. The incremental data migration task never stops or completes. The Running is displayed in the Status section.

Appendix: SQL operations that support incremental migration

DML operations

INSERT, UPDATE, DELETE

Note

DTS does not migrate UPDATE statements that only update large fields.

DDL operations

ALTER TABLE, only including ADD COLUMN, DROP COLUMN, RENAME COLUMN
CREATE TABLE, CREATE INDEX
Note
CREATE TABLE does not support partitions or functions within table definitions.
DROP TABLE
RENAME TABLE
TRUNCATE TABLE

Note

DTS does not migrate transactional DDL operations. For example, DTS does not migrate an SQL operation that contains DDL operations on multiple columns or an SQL operation that contains both DDL operations and DML operations. Data loss may occur after such SQL operations are migrated.
DTS does not migrate DDL operations that contain user-defined types.
DTS does not migrate online DDL operations.
DTS does not migrate DDL operations performed on objects whose names contain reserved keywords.
DTS does not migrate DDL operations executed by system stored procedures.
DTS does not migrate the TRUNCATE TABLE operation.