ApsaraDB RDS for SQL Server supports several migration paths depending on your data source, acceptable downtime, and database characteristics. This page compares the available solutions and recommends one for each source.
Prerequisites
Before you begin, make sure that you have:
An ApsaraDB RDS for SQL Server instance as the destination. The destination instance must have specifications and storage space greater than or equal to those of the source database. See Create an ApsaraDB RDS for SQL Server instance.
Compatibility confirmed between source and destination. Run
SELECT name, compatibility_level FROM sys.databases;on both instances and verify the destination is compatible with the source.Network access from your migration tool to both databases. Open firewalls, IP whitelists, and security groups accordingly.
If using Data Transmission Service (DTS): DTS server IP addresses added to your database IP whitelist, and both databases supported by DTS for migration.
Choose a migration solution
If the source database does not support incremental migration, stop all write operations before starting the migration.
When using DTS for logical migration from any source other than a self-managed SQL Server or TencentDB for SQL Server, use Polling and querying CDC instances for incremental synchronization mode.
| Data source | Incremental migration | Available solutions | Recommended |
|---|---|---|---|
| Self-managed SQL Server | Yes | Manual OSS migration; quick migration using Data Disaster Recovery and DTS; DTS logical migration | Quick migration using Data Disaster Recovery and DTS |
| Azure SQL Database | Yes | DTS logical migration; end-to-end migration using RDS; SSMS migration | End-to-end migration using RDS or DTS logical migration |
| Azure SQL Managed Instance / SQL Server on Azure Virtual Machines | DTS logical migration; end-to-end migration using RDS; SSMS migration; manual OSS migration | ||
| Amazon RDS for SQL Server | Yes | DTS logical migration; end-to-end migration using RDS; SSMS migration; manual OSS migration | End-to-end migration using RDS or DTS logical migration |
| Huawei Cloud RDS for SQL Server | Yes | SSMS migration; DTS logical migration; manual OSS migration (full only) | DTS logical migration |
| TencentDB for SQL Server | Yes | DTS logical migration; manual OSS migration; SSMS migration | DTS logical migration |
| Google Cloud SQL for SQL Server | Yes | SSMS migration; DTS logical migration | DTS logical migration |
Migration solutions
Physical backup migration
Physical backup migration uses SQL Server's native backup and restore mechanism. The backup file is transferred via Object Storage Service (OSS).
| Method | When to use | Limitations | Supported sources |
|---|---|---|---|
| Manual migration using OSS | High migration speed is a priority; you need flexibility over backup file naming. Procedure: (1) Set the database recovery model to FULL. (2) Upload a full backup to an OSS bucket. (3) Upload incremental logs periodically. (4) Stop writes, restore the final log, and cut over. | One database per task; no version downgrade support; no database, table, or column mapping; several minutes of downtime at cutover. | Self-managed SQL Server; Amazon RDS for SQL Server; Azure SQL Managed Instance; SQL Server on Azure Virtual Machines |
| Quick migration using Data Disaster Recovery and DTS | Multiple databases in one task; simple DTS console configuration. Procedure: (1) Deploy a physical gateway (uses OSS internally). (2) Stop writes, restore the final log, and cut over. | Backup log files must use a .bak extension; requires AliyunDBSAgent on the source machine; no version downgrade support; no database, table, or column mapping; several minutes of downtime at cutover. | Self-managed SQL Server |
Tutorials
Logical migration using DTS
DTS logical migration reads changes from the source database and replays them on the destination. Three incremental synchronization modes are available; choose based on your source type and table characteristics.
In the DTS console, set SQL Server Incremental Synchronization Mode to one of the following options.
| Mode | When to use | Key limitations | Supported sources |
|---|---|---|---|
| Log Parsing — *Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported)* | Source has no heap tables, no tables without primary keys, and no compressed or computed-column tables; migration between different SQL Server versions required; database, table, or column mapping needed. | No support for heap tables, tables without primary keys, compressed tables, or tables with computed columns; foreign key constraints must be disabled during incremental migration; log write rate must not exceed 10 MB/s, 30 GB/h, or 500 GB/d; DTS creates triggers and DDL storage tables in the source; uses fn_log (may be unstable); max 10 databases per task. | Self-managed SQL Server (full + incremental); Amazon RDS for SQL Server (full only); Azure SQL Managed Instance (full + incremental); SQL Server on Azure Virtual Machines (full + incremental) |
| Hybrid Log Parsing — *Log-based Parsing for Non-heap Tables and CDC-based Incremental Synchronization for Heap Tables (Hybrid Log-based Parsing)* | Source contains heap tables, tables without primary keys, or compressed tables; migration between different SQL Server versions required. | Source must be Enterprise Edition 2008 or later, or Standard Edition 2016 SP1 or later (excluding 2017); no support for tables with computed columns; foreign key constraints must be disabled; log write rate must not exceed 10 MB/s, 30 GB/h, or 500 GB/d; DTS enables Change Data Capture (CDC) at the database level and creates triggers and DDL storage tables; uses fn_log (may be unstable); max 10 databases per task. | Self-managed SQL Server (full + incremental); Amazon RDS for SQL Server (full only); Azure SQL Managed Instance (full + incremental); SQL Server on Azure Virtual Machines (full + incremental) |
| CDC Instance Polling — *Polling and querying CDC instances for incremental synchronization* | Source is a managed cloud SQL Server (Amazon RDS, Azure SQL Database, or Google Cloud SQL for SQL Server); stable incremental replication with low bandwidth usage is required. | For SQL Server on Azure Virtual Machines: Enterprise Edition must be 2008 or later; Standard Edition must be 2016 SP1 or later (excluding 2017). DTS account must have sysadmin role for database-level CDC and a privileged account for table-level CDC. CDC activation may briefly lock the source database. Clustered columnstore index tables are not supported. Consecutive DDL operations adding or dropping columns (more than 2 within one minute) are not supported. Do not modify the CDC instance on the source — this can cause data loss. Incremental data migration has about 10 seconds of latency. Migrating more than 1,000 tables may cause latency or instability. Migrating a large number of tables across multiple databases may pose stability and performance risks. Max 10 databases per task. | Amazon RDS for SQL Server (full + incremental); Azure SQL Database (full + incremental); Google Cloud SQL for SQL Server (full + incremental) |
For incremental migration tasks using Log Parsing or Hybrid Log Parsing, if the source database is backed up more than once per hour, DTS may fail to retrieve local backup logs. Retain backup logs on a local disk for at least three days.
Duplicate data can occur in tables without a primary key or unique constraint across all three DTS modes. Avoid these methods if you need to migrate such tables.
A single DTS task supports a maximum of 10 databases. Exceeding this limit may cause stability and performance issues.
Tutorials
SSMS migration
SQL Server Management Studio (SSMS) migration exports data from the source and imports it to the destination manually.
| When to use | Limitations | Supported sources |
|---|---|---|
| Simple, stable migration with low risk of data inconsistency; no DTS setup required. Procedure: (1) Stop writes to the source database. (2) Export data using SSMS. (3) Import the data to the destination using SSMS. (4) Verify data consistency and cut over. | Requires stopping writes before migration starts (no incremental sync); fully manual process. | Any source accessible via SSMS |
Tutorial: Migrate data to ApsaraDB RDS for SQL Server using SSMS
What's next
Verify migrated data
After the migration, confirm that all data transferred completely to the ApsaraDB RDS for SQL Server instance.
Check recent records
Sort core business tables by date or auto-incrementing ID and compare the latest rows on both instances. For example, if an Orders table has an OrderID column and an OrderDate column:
-- Run on the source instance
SELECT TOP 10 OrderID, OrderDate, CustomerID, TotalAmount
FROM Orders
ORDER BY OrderDate DESC;
-- Run on the destination instance
SELECT TOP 10 OrderID, OrderDate, CustomerID, TotalAmount
FROM Orders
ORDER BY OrderDate DESC;Compare the results to confirm data is consistent.
Full verification using DTS
DTS can verify data between source and destination without service downtime. Create a full data verification task to check whether all data migrated completely.
Update database statistics
If performance degrades significantly after migration, the cause may be a change in data distribution on the destination instance. Perform a full update of database statistics to restore or improve performance.