Migrate SQL Server on Azure to cloud - ApsaraDB RDS - Alibaba Cloud Documentation Center

Migrate schema, full data, and incremental data from an Azure SQL database to ApsaraDB RDS for SQL Server using either the ApsaraDB RDS console or the Data Transmission Service (DTS) console. Supported sources include Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure Virtual Machines.

Choose a migration method

	RDS console (one-stop cloud migration)	DTS console
Migration types	Schema, full data, and incremental data (default, not configurable)	Configurable: schema, full, and incremental, or any combination
Advanced configuration	Throttling and data verification	Throttling, ETL, monitoring and alerting, retry settings, dedicated cluster
Best for	First-time migrations with standard requirements	Migrations requiring custom migration types or fine-grained control

Prerequisites

Before you begin, make sure that you have:

An RDS for SQL Server instance with available storage larger than the total size of the source data. See Create an ApsaraDB RDS for SQL Server instance.
Connected the Azure SQL database to Alibaba Cloud so that DTS can access it. See Connect Azure databases to Alibaba Cloud.
Note
For SQL Server on Azure Virtual Machines: Enterprise Edition requires SQL Server 2008 or later. Standard Edition requires SQL Server 2016 SP1 or later. SQL Server 2017 is not supported.

Limitations

Source database

Maximum 10 databases per migration task. For more than 10 databases, configure multiple tasks.
Azure SQL Database: only one database can be migrated per task.
Read-only instances cannot be used as the source database.
Outbound bandwidth on the source server must be sufficient; otherwise migration speed decreases.
Tables to be migrated must have PRIMARY KEY or UNIQUE constraints with all fields unique; otherwise the destination may contain duplicate records.
During schema migration and full data migration, do not execute DDL statements. The task fails if schema changes occur during migration.
During full-only migration (no incremental), do not write to the source database. To ensure data consistency, select schema migration, full data migration, and incremental data migration together.
We recommend that you enable READ_COMMITTED_SNAPSHOT on the source database during full data migration to avoid shared lock conflicts and data inconsistency.

Data log retention

Incremental-only migration: retain data logs for more than 24 hours.
Full + incremental migration: retain data logs for at least 7 days. After full migration completes, you can reduce retention to more than 24 hours.
If data logs are not retained for the required duration, the Service Level Agreement (SLA) of DTS does not guarantee service reliability or performance.

CDC limits

DTS enables CDC in the source database during the precheck phase, causing table locks lasting a few seconds.
Incremental migration: the number of source tables with Change Data Capture (CDC) enabled cannot exceed 1,000. If the number of CDC-enabled tables to be migrated in a single migration task exceeds 1,000, the precheck fails.
CDC stores incremental data for 3 days by default. Adjust retention with: exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention= <time>; where <time> is in minutes. If daily incremental data exceeds 10 million records, set <time> to 1,440.
If a single field in a CDC-enabled table exceeds 64 KB, run the following command before starting the task: EXEC sp_configure 'max text repl size', -1;
Do not add or remove columns more than twice per minute on CDC-enabled tables.
Do not modify CDC instances on the source database during migration; the task may fail or data loss may occur.
Incremental data migration has a latency of approximately 10 seconds.
DTS cannot migrate DDL operations related to primary keys of CDC-enabled tables.
The incremental data collection modules of multiple data migration instances that share a source SQL Server database are independent of each other.

Source database objects created by DTS

In Incremental Synchronization Based on Logs of Source Database mode, DTS creates a trigger named dts_cdc_sync_ddl, a heartbeat table named dts_sync_progress, and a DDL history table named dts_cdc_ddl_history in the source database. In hybrid log-based parsing incremental synchronization mode, DTS creates the same objects and also enables CDC for the source database and specific tables. Set the maximum records per second for CDC-enabled tables to 1,000.

Destination database

Disable triggers and foreign keys in the destination database for any task that includes incremental data migration. You cannot perform reindexing operations on the destination database during incremental data migration. Otherwise, the task may fail or data loss may occur.
DTS does not migrate foreign keys (including cascade and delete operations) from the source database.
If the database name does not conform to ApsaraDB RDS for SQL Server naming conventions, manually create the database in the destination instance before configuring the task. See Create a database.
After full data migration, the destination tablespace is larger than the source due to concurrent INSERT fragmentation.

Unsupported data types

DTS does not migrate: CURSOR, ROWVERSION, SQL_VARIANT, HIERARCHYID, POLYGON, GEOMETRY, GEOGRAPHY.

FLOAT and DOUBLE precision

DTS retrieves FLOAT and DOUBLE values using ROUND(COLUMN,PRECISION). Default precision: 38 digits for FLOAT, 308 digits for DOUBLE. Verify that these defaults meet your requirements before starting.

Failed task resumption

DTS automatically retries failed tasks for up to 7 days. Before switching workloads to the destination, stop or release any failed tasks, or revoke write permissions from DTS accounts to prevent source data from overwriting the destination.

If a DTS task fails to run, DTS technical support will try to restore the task within 8 hours. During the restoration, the task may be restarted and the parameters of the task may be modified.

Performance impact

Before you migrate data, evaluate the impact of data migration on the performance of the source database and destination cluster. We recommend that you migrate data during off-peak hours. During full data migration, DTS uses the read and write resources of the source and destination databases, which may increase the loads of the database servers.

Billing

Migration type	Instance fee	Internet traffic fee
Schema migration and full data migration	Free	Charged when Access Method of the destination is set to Public IP Address. See Billing overview.
Incremental data migration	Charged (pay-as-you-go). See Billing overview.	—

Required permissions

Azure SQL database (source)

Migration type	Required permission
Schema migration	`SELECT`
Full data migration	`SELECT`
Incremental data migration	`sysadmin` role (required to enable CDC)

The server administrator account provided by the Azure SQL Database console meets all permission requirements listed above.

CDC support by pricing model:

vCore model: all specifications support CDC.
DTU model: only S3 or higher specifications support CDC.

RDS for SQL Server (destination)

All migration types (schema, full, incremental): read and write permissions.

Create a standard or privileged account in the ApsaraDB RDS console and modify account permissions as needed.

Method 1: Use the RDS console

Open the ApsaraDB RDS instance list, select the target region, and click the instance ID.
In the left-side navigation pane, click Data Migration And Sync.

Click One-stop Cloud and configure the source and destination database information.

Source database parameters

Parameter	Description
Task Name	A descriptive name for the task. Does not need to be unique.
Database Type	Defaults to SQL Server. No action needed.
Type	Select Azure.
Access Method	Select Public IP Address or Express Connect, VPN Gateway, or Smart Access Gateway based on how the Azure database connects to Alibaba Cloud. For Azure SQL Managed Instance, select Public IP Address.
Instance Region	For Public IP Address: the region where the Azure SQL database resides (select the nearest region if the exact region is unavailable). For Express Connect, VPN Gateway, or Smart Access Gateway: the region of the Alibaba Cloud virtual private cloud (VPC) connected to the Azure database.
Connected VPC	The Alibaba Cloud VPC connected to the Azure database. Available only when Access Method is Express Connect, VPN Gateway, or Smart Access Gateway.
Domain Name or IP	The domain name or IP address of the Azure SQL database. Specify the domain name when possible.
Port Number	The service port of the Azure SQL database.
Database Account	The account for the Azure SQL database. For Azure SQL Database, use the format `<Administrator username>@<Server name>`. For example, if the administrator account is `testuser` and the server name is `dtstest` (run `SELECT @@SERVERNAME AS ServerName` to query the server name), enter `testuser@dtstest`.
Database Password	The password for the database account.
Encryption	Select Non-encrypted if SSL is not enabled. Select SSL-encrypted if SSL is enabled (DTS trusts the server certificate by default).

Destination database parameters

Parameter	Description
Database Type	Defaults to SQL Server. No action needed.
Access Method	Defaults to Alibaba Cloud Instance. No action needed.
Instance Region	The region of the ApsaraDB RDS for SQL Server instance (read-only).
Instance ID	The ID of the destination instance (read-only).
Database Account	The account for the destination instance with the required permissions.
Database Password	The password for the database account.
Encryption	Select Non-encrypted or SSL-encrypted based on the destination database configuration.

Click Test Connectivity And Proceed.
Important
Add the DTS server CIDR blocks shown in the dialog box to the whitelist of your Azure SQL database. Adding DTS IP addresses may introduce security risks. Implement appropriate security measures such as strengthening password policies and restricting port access.
Select a migration plan:
- Full And Incremental Data Migration: click Configure Objects and continue to the next step.
- Any other plan: follow the steps in View Migration Documentation at the bottom of the page.

On the Configure Objects page, configure the objects to migrate.

Parameter	Description
Method to Migrate Triggers in Source Database	Controls when triggers are migrated. Select Manual Migration to avoid data inconsistency caused by triggers migrating before incremental migration completes. If you do not need to migrate triggers, keep the default setting. See Synchronize or migrate triggers from the source database.
Source Objects	Select objects and click to add them to Selected Objects. Selecting tables or columns excludes views, triggers, and stored procedures.
Selected Objects	Right-click an object to rename it (using object name mapping), add WHERE filter conditions, or select specific SQL operations to migrate. To remove an object, select it and click . Note that renaming an object may cause dependent objects to fail migration.

(Optional) Expand Advanced Settings to configure throttling or data verification.

Parameter	Description
Enable Throttling for Full Data Migration	Limits resource usage during full migration. Configure QPS to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s).
Enable Throttling for Incremental Data Migration	Limits resource usage during incremental migration. Configure RPS of Incremental Data Migration and Data migration speed for incremental migration (MB/s).
Data Verification Mode	Select Full Data Verification to check hash values at 100% sampling. Configure Maximum number of rows of data read per second by full verification and The maximum number of bytes read per second by full verification Byte/s to throttle verification. Set both to 0 to disable throttling.

Click Next: Save Task Settings and Precheck. DTS runs a precheck before starting the migration task. If precheck items fail:
- Click View Details next to each failed item, troubleshoot the issue, and click Precheck Again.
- For alert items that can be ignored: click Confirm Alert Details > Ignore > OK > Precheck Again. Ignoring alerts may result in data inconsistency.
To preview the OpenAPI parameters for this task, hover over the button and click Preview OpenAPI parameters before proceeding.
Wait until Success Rate reaches 100%, then click Next: Purchase Instance.

On the Purchase Instance page, configure the instance class and complete the purchase. Select the Data Transmission Service (Pay-as-you-go) Service Terms check box, then click Buy and Start > OK.

Parameter	Description
Resource Group	The resource group for the migration instance. Defaults to default resource group. See What is Resource Management?
Instance Class	The instance class determines migration speed. See Instance classes of data migration instances.

Monitor progress on the Data Migration page. The following table describes the possible task statuses:

Status	Description
Running	Incremental migration is active. The task runs continuously and does not stop automatically.
Completed	The task finished successfully. Only appears for tasks without incremental migration.

Method 2: Use the DTS console

Open the Data Transmission Service console.
In the left-side navigation pane, click Data Migration and select the region.

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

Source database parameters

Parameter	Description
Task Name	A descriptive name for the task. Does not need to be unique.
Select Existing Connection	Select a previously registered source database from DTS Database Connection Management to skip manual entry.
Database Type	Select SQL Server.
Access Method	Select Public IP Address or Express Connect, VPN Gateway, or Smart Access Gateway. For Azure SQL Managed Instance, select Public IP Address.
Instance Region	For Public IP Address: the region of the Azure SQL database (select the nearest if unavailable). For Express Connect, VPN Gateway, or Smart Access Gateway: the region of the Alibaba Cloud VPC connected to the Azure database.
Replicate Data Across Alibaba Cloud Accounts	Select No when migrating within the same Alibaba Cloud account. Available only when Access Method is Express Connect, VPN Gateway, or Smart Access Gateway.
Connected VPC	The Alibaba Cloud VPC connected to the Azure database. Available only when Access Method is Express Connect, VPN Gateway, or Smart Access Gateway.
Domain Name or IP	The domain name or IP address of the Azure SQL database. Specify the domain name when possible.
Port Number	The service port of the Azure SQL database.
Database Account	The account for the Azure SQL database. For Azure SQL Database, use the format `<Administrator username>@<Server name>`. For example, for administrator account `testuser` and server `dtstest` (run `SELECT @@SERVERNAME AS ServerName` to query), enter `testuser@dtstest`.
Database Password	The password for the database account.
Encryption	Select Non-encrypted or SSL-encrypted based on the source database configuration.

Destination database parameters

Parameter	Description
Select Existing Connection	Select a previously registered destination database from DTS Database Connection Management to skip manual entry.
Database Type	Select SQL Server.
Access Method	Select Alibaba Cloud Instance.
Instance Region	The region of the destination ApsaraDB RDS for SQL Server instance.
Instance ID	The ID of the destination instance.
Database Account	The account for the destination instance with the required permissions.
Database Password	The password for the database account.
Encryption	Select Non-encrypted or SSL-encrypted based on the destination database configuration.

Click Test Connectivity And Proceed.
Important
Add the DTS server CIDR blocks shown in the dialog box to the whitelist of your Azure SQL database. Adding DTS IP addresses may introduce security risks. Implement appropriate security measures such as strengthening password policies and restricting port access.

On the Configure Objects page, configure the migration objects and types.

Parameter	Description
Migration Types	Select the migration types: Schema Migration, Full Data Migration, and/or Incremental Data Migration. For zero-downtime migration, select all three. If Schema Migration is not selected, create the target databases and tables manually and use object name mapping as needed. If Incremental Data Migration is not selected, do not write to the source database during migration.
Method to Migrate Triggers in Source Database	Available when both Schema Migration and Incremental Data Migration are selected. Select Manual Migration to prevent data inconsistency. See Synchronize or migrate triggers from the source database.
SQL Server Incremental Synchronization Mode	Select Polling and querying CDC instances for incremental synchronization. Available only when Incremental Data Migration is selected.
The maximum number of tables for which CDC is enabled that DTS supports.	Retain the default value.
Processing Mode of Conflicting Tables	Precheck and Report Errors (default): fails precheck if destination tables share names with source tables. Ignore Errors and Proceed: skips the check. Use with caution — during full migration, duplicate primary key records are retained in the destination; during incremental migration, they are overwritten. Schema differences may cause partial migration or task failure.
Capitalization of Object Names in Destination Instance	Controls capitalization of database, table, and column names in the destination. Defaults to DTS default policy. See Specify the capitalization of object names in the destination instance.
Source Objects	Select objects and click to add them to Selected Objects. Selecting tables or columns excludes views, triggers, and stored procedures.
Selected Objects	Right-click an object to rename it, add WHERE filters, or select specific SQL operations. To remove an object, select it and click .

Click Next: Advanced Settings to configure advanced parameters.

Parameter	Description
Dedicated Cluster for Task Scheduling	By default, DTS uses the shared cluster. Purchase a dedicated cluster for higher stability. See What is a DTS dedicated cluster.
Retry Time for Failed Connections	Duration DTS retries after a connection failure. Range: 10–1,440 minutes. Default: 720 minutes. Set to at least 30 minutes. If the connection is restored within this window, DTS resumes the task. Note: multiple tasks sharing a source or destination database use the most recently configured retry time. DTS charges for the instance during retries.
Retry Time for Other Issues	Duration DTS retries after DDL or DML operation failures. Range: 1–1,440 minutes. Default: 10 minutes. Set to a value greater than 10 minutes. Must be smaller than Retry Time for Failed Connections.
Enable Throttling for Full Data Migration	Available when Full Data Migration is selected. Configure QPS to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s).
Enable Throttling for Incremental Data Migration	Available when Incremental Data Migration is selected. Configure RPS of Incremental Data Migration and Data migration speed for incremental migration (MB/s).
Environment Tag	An optional tag to identify the DTS instance.
Configure ETL	Select Yesalert notification settings to enable extract, transform, and load (ETL) and enter data processing statements. See Configure ETL in a data migration or data synchronization task. Select No to skip.
Monitoring and Alerting	Select Yes to receive notifications when the task fails or migration latency exceeds a threshold. Configure alert thresholds and notification settings. See Configure monitoring and alerting.

Click Next Step: Data Verification to configure data verification. See Configure data verification.
Click Next: Save Task Settings and Precheck. If precheck items fail:
- Click View Details next to each failed item, troubleshoot the issue, and click Precheck Again.
- For alert items that can be ignored: click Confirm Alert Details > Ignore > OK > Precheck Again. Ignoring alerts may result in data inconsistency.
To preview the OpenAPI parameters for this task, hover over the button and click Preview OpenAPI parameters before proceeding.
Wait until Success Rate reaches 100%, then click Next: Purchase Instance.

Parameter	Description
Resource Group	The resource group for the migration instance. Defaults to default resource group.
Instance Class	Determines migration speed. See Instance classes of data migration instances.

Monitor progress on the Data Migration page. The following table describes the possible task statuses:

Status	Description
Running	Incremental migration is active. The task runs continuously and does not stop automatically.
Completed	The task finished successfully. Only appears for tasks without incremental migration.

Appendix 1: SQL operations supported in incremental data migration

DML operations

INSERT, UPDATE, DELETE

If an UPDATE modifies only large fields, DTS does not migrate it.

DDL operations

Supported DDL operations:

CREATE TABLE (not supported for partitioned tables or tables that contain functions)
ALTER TABLE (ADD COLUMN and DROP COLUMN only)
DROP TABLE
CREATE INDEX and DROP INDEX

DDL operations that are not migrated:

Transactional DDL (adding multiple columns in one statement, or statements combining DDL and DML)
DDL with user-defined types
Online DDL
DDL on objects with reserved keyword names
DDL executed by system stored procedures
TRUNCATE TABLE

Appendix 2: Objects supported in schema migration

Supported object types: table, view, trigger, synonym, SQL stored procedure, SQL function, plan guide, user-defined type, rule, default, sequence.

Not supported: assemblies, service brokers, full-text indexes, full-text catalogs, distributed schemas, distributed functions, CLR stored procedures, CLR scalar-valued functions, CLR table-valued functions, internal tables, systems, aggregate functions.

FAQ

Can I select which objects to verify when configuring a migration task in the ApsaraDB RDS console?

No. The objects verified are always the same as the objects selected for migration.

What verification method is used for tasks configured in the ApsaraDB RDS console?

DTS checks hash values of all migrated data at a 100% sampling ratio, comparing all records in the source and destination databases.