All Products
Search

This Product

    ApsaraDB RDS:SQL Server data migration/cloud migration guide

    Document Center

    ApsaraDB RDS:SQL Server data migration/cloud migration guide

    Last Updated:Jul 05, 2025

    This topic introduces several common solutions for migrating SQL Server databases to the cloud. You can select the most suitable solution based on the data source location, tools used in each solution, and the benefits and limitations of each solution.

    Preparations

    • Create a destination ApsaraDB RDS for SQL Server instance with specifications and storage space greater than or equal to those of the source database.

    • Execute the SELECT name, compatibility_level FROM sys.databases; statement on both the source and destination instances to check whether the destination instance is compatible with the source instance.

    • Make sure that the security settings (including firewall rules, whitelists, and security groups) of the source and destination databases do not restrict the access of migration tools.

    • If you need to use Data Transmission Service (DTS) for cloud migration, ensure that:

    Cloud migration solutions

    Physical backup migration

    Solution

    Procedure

    Tutorials

    Use OSS (manual migration)

    1. Change the database log recovery model (backup_type) to FULL.

    2. Back up full data and upload it to an OSS bucket.

    3. Back up and upload incremental logs on a scheduled basis.

    4. Stop writing data to the source database. After the last incremental log is successfully replayed, switch the workloads to the new database.

    Use Data Disaster Recovery and DTS (one-click migration)

    1. Deploy a physical protocol gateway.

    2. Use DTS to migrate data to the cloud (internally calls OSS Bucket).

    3. Stop writing data to the source database. After the last incremental log is successfully replayed, switch the workloads to the new database.

    Migrate data from a self-managed SQL Server database to an ApsaraDB RDS for SQL Server instance by using a physical gateway

    Logical data migration

    Synchronization mode

    Procedure

    Tutorials

    Use DTS (log parsing mode)

    1. Use DTS to migrate data to the cloud.

      Note

      Set SQL Server Incremental Synchronization Mode to Incremental Synchronization Based on Logs of Source Database (Heap tables are not supported).

    2. Stop writing data to the source database. After the last incremental log is successfully replayed, switch the workloads to the new database.

    Use DTS (hybrid log parsing mode)

    1. Use DTS to migrate data to the cloud.

      Note

      Set SQL Server Incremental Synchronization Mode to Log-based Parsing for Non-heap Tables and CDC-based Incremental Synchronization for Heap Tables (Hybrid Log-based Parsing).

    2. Stop writing data to the source database. After the last incremental log is successfully replayed, switch the workloads to the new database.

    Use DTS (CDC instance polling and querying mode)

    1. Use DTS to migrate data to the cloud.

      Note

      Set SQL Server Incremental Synchronization Mode to Polling and querying CDC instances for incremental synchronization.

    2. Stop writing data to the source database. After the last incremental log is successfully replayed, switch the workloads to the new database.

    Other migration solutions

    Solution

    Procedure

    Tutorials

    Use SSMS tool

    1. Stop writing data to the source database.

    2. Use SSMS to export data from the source database.

    3. Use SSMS to import the exported data to the destination database.

    4. After verifying data consistency, switch the workloads to the new database.

    Use SSMS to migrate data to an RDS for SQL Server instance

    Comparison of cloud migration solutions

    Physical backup migration

    Solution

    Benefits

    Limitations

    Use OSS (manual migration)

    • Supports many scenarios because it uses SQL Server's built-in backup and recovery features.

    • Fast migration speed.

    • You can manually modify the backup file name based on the source database log format.

    • Requires manual backup and log upload, which is cumbersome.

    • Can only migrate one database at a time.

    • Service interruption of several minutes: you need to stop writing data to the source database and wait until the last incremental log is successfully replayed before switching workloads.

    • Does not support migration from higher to lower versions.

    • Does not support database, table, and column mapping features.

    Use Data Disaster Recovery and DTS (one-click migration)

    • Migration operations are performed in the DTS console, making configuration simple and convenient.

    • Supports migrating multiple databases in a single operation.

    • Supports many scenarios because it uses SQL Server's built-in backup and recovery features.

    • Fast migration speed.

    • Backup log format must end with bak.

    • Service interruption of several minutes: you need to stop writing data to the source database and wait until the last incremental log is successfully replayed before switching workloads.

    • Requires installation of AliyunDBSAgent on the physical server of the source database.

    • Does not support migration from higher to lower versions.

    • Does not support database, table, and column mapping features.

    Logical migration

    Synchronization mode

    Benefits

    Limitations

    Use DTS (log parsing mode)

    • Migration operations are performed in the DTS console, making configuration simple and convenient.

    • Supports migration between databases of different versions.

    • Supports database, table, and column mapping features.

    • When DTS instance includes incremental tasks, the source database does not need to stop writing data, supporting smooth migration.

    • Supports migration from third-party cloud databases to Alibaba Cloud.

      For example, supports full migration from Amazon RDS for SQL Server, and supports both full and incremental migration from Azure SQL (non-SQL Database mode).

    • Only supports some DDL statements, and frequent DDL operations (more than 100/hour) will affect migration speed.

    • When source database log volume exceeds 10 MB/s, 30 GB/h, or 500 GB/day, tasks may be delayed or even fail.

    • When source database log backup frequency exceeds once per hour, DTS may not obtain local backup logs (recommended to retain backup logs on local disk for 3 days).

    • DTS creates triggers and DDL storage tables in the source database to capture DDL changes.

    • Foreign key constraints need to be disabled during incremental migration, otherwise tasks may fail.

    • Does not support heap tables, tables without primary keys, compressed tables, tables with computed columns, and other scenarios.

    • Tables without primary keys or unique constraints may have duplicate data. If you need to retain tables without primary keys or unique constraints, this solution is not recommended.

    • DTS uses the fn_log function to pull and parse logs, which is not highly stable. If unexpected behavior occurs, tasks may fail.

    • A single DTS task cannot migrate more than 10 databases, otherwise there may be stability and performance risks.

    Use DTS (hybrid log parsing mode)

    • Migration operations are performed in the DTS console, making configuration simple and convenient.

    • Supports migration between databases of different versions.

    • Supports database, table, and column mapping features.

    • Supports heap tables, tables without primary keys, compressed tables, and other attribute tables.

    • Supports migration from third-party cloud databases to Alibaba Cloud.

      For example, supports full migration from Amazon RDS for SQL Server, and supports both full and incremental migration from Azure SQL (non-SQL Database mode).

    • Source database version requirements: Enterprise Edition must be 2008 or later, Standard Edition must be 2016SP1 or later (excluding 2017 version).

    • Only supports some DDL statements, and frequent DDL operations (more than 100/hour) will affect migration speed.

    • When source database log volume exceeds 10 MB/s, 30 GB/h, or 500 GB/day, tasks may be delayed or even fail.

    • When source database log backup frequency exceeds once per hour, DTS may not obtain local backup logs (recommended to retain backup logs on local disk for 3 days).

    • DTS enables database-level CDC and some table-level CDC, and creates triggers and DDL storage tables in the source database to capture DDL changes.

    • Foreign key constraints need to be disabled during incremental migration, otherwise tasks may fail.

    • Does not support tables with computed columns.

    • Tables without primary keys or unique constraints may have duplicate data. If you need to retain tables without primary keys or unique constraints, this solution is not recommended.

    • DTS uses the fn_log function to pull and parse logs, which is not highly stable. If unexpected behavior occurs, tasks may fail.

    • A single DTS task cannot migrate more than 10 databases, otherwise there may be stability and performance risks.

    Use DTS (CDC instance polling and querying mode)

    • Migration operations are performed in the DTS console, making configuration simple and convenient.

    • Supports migration between databases of different versions.

    • Supports database, table, and column mapping features.

    • Supports migration from third-party cloud databases to Alibaba Cloud.

      For example, supports both full and incremental migration from Amazon RDS for SQL Server, Azure SQL Database, and Google Cloud SQL for SQL Server.

    • Incremental migration is more stable and consumes less network bandwidth.

      Uses SQL Server native CDC components to obtain incremental data without capturing transaction logs from the source database. When the source database triggers log truncation, it does not affect the DTS instance operation.

    • SQL Server on Azure Virtual Machines Enterprise Edition must be version 2008 or later, Standard Edition must be version 2016SP1 or later (excluding 2017 version).

    • The source database account used by the DTS instance must have permissions to enable database-level and table-level CDC. Enabling database-level CDC requires an account with sysadmin role permissions, and enabling table-level CDC requires a privileged account.

      Note

      • A server administrator account in Microsoft Azure SQL Database has the required permissions. CDC can be enabled for all databases that are purchased in Azure SQL Database based on the vCore model. CDC can be enabled for databases that are purchased in Azure SQL Database based on the database transaction unit (DTU) model only if the databases have a service tier of S3 or greater.

      • A privileged account of an Amazon RDS for SQL Server instance has the required permissions. CDC can be enabled for stored procedures at the database level.

      • CDC cannot be enabled for clustered columnstore indexes.

      • The pre-module of an incremental data migration task in DTS enables CDC in the source database. In this process, locked tables that lasts a few seconds occurs in the source database due to the limit of SQL Server database.

    • The number of tables to be migrated from the source database cannot exceed 1,000, otherwise tasks may be delayed or unstable.

    • A single DTS task cannot migrate more than 10 databases, otherwise there may be stability and performance risks.

    • Tables without primary keys or unique constraints may have duplicate data. If you need to retain tables without primary keys or unique constraints, this solution is not recommended.

    • Incremental data migration has a latency of about 10 seconds.

    • Does not support continuous column addition or removal operations (more than two column addition or removal DDL operations within one minute), otherwise tasks may fail.

    • Does not support making changes to the source database CDC instance, otherwise tasks may fail or data may be lost.

    • When migrating multiple databases and tables, there may be stability and performance risks.

    Other migration solutions

    Solution

    Benefits

    Limitations

    Use SSMS tool

    Simple and stable operation process with low risk of data inconsistency.

    • Requires stopping data writing to the source database, otherwise data inconsistency may occur.

    • Requires manual operation using the SSMS tool.

    Cloud migration solution selection

    Important

    If the data source does not support incremental migration, you need to stop writing data to the source database before performing cloud migration operations.

    Data source

    Whether incremental migration is supported

    Available migration solutions

    Recommended solution

    Self-managed SQL Server

    Yes

    • Use OSS physical backup for manual migration

    • Use Data Disaster Recovery and DTS physical backup for one-click migration

    • Use DTS for logical migration

    Use Data Disaster Recovery and DTS physical backup for one-click migration

    Azure SQL Database

    Yes

    • Use DTS for logical migration

      Note

      For incremental data, use the Polling and querying CDC instances for incremental synchronization mode.

    • Use RDS one-stop cloud migration

    • Use SSMS tool for migration

    Use RDS one-stop cloud migration or DTS logical migration

    Azure SQL Managed Instance

    SQL Server on Azure Virtual Machines

    • Use DTS for logical migration

      Note

      For incremental data, use the Polling and querying CDC instances for incremental synchronization mode.

    • Use RDS one-stop cloud migration

    • Use SSMS tool for migration

    • Use OSS physical backup for manual migration

    Amazon RDS for SQL Server

    Yes

    • Use DTS for logical migration

      Note

      For incremental data, use the Polling and querying CDC instances for incremental synchronization mode.

    • Use RDS one-stop cloud migration

    • Use SSMS tool for migration

    • Use OSS physical backup for manual migration

    Use RDS one-stop cloud migration or DTS logical migration

    Huawei Cloud RDS SQL Server

    No

    • Use SSMS tool for migration

    • Use DTS for logical migration (full data only)

    • Use OSS physical backup for manual migration (full data only)

    Use OSS physical backup for manual full data migration

    Note

    TencentDB for SQL Server

    Yes

    • Use DTS for logical migration

    • Use OSS physical backup for manual migration

    • Use SSMS tool for migration

    Use DTS for logical migration

    Google Cloud SQL for SQL Server

    Yes

    • Use SSMS tool for migration

    • Use DTS for logical migration

      Note

      For incremental data, use the Polling and querying CDC instances for incremental synchronization mode.

    Use DTS for logical migration

    What to do next

    Data verification

    After migrating SQL Server to the cloud, you typically need to verify whether all data has been fully migrated to the ApsaraDB RDS for SQL Server instance. You can perform verification based on your specific situation. Here are two common verification methods:

    Core data verification

    You can sort by date or auto-increment ID to verify whether the latest business data has been correctly migrated. For example, if the core business table Orders contains fields such as OrderID (auto-increment ID) and OrderDate (date), you can use the following statements for querying:

    -- SQL query in the source database
SELECT TOP 10 OrderID, OrderDate, CustomerID, TotalAmount
FROM Orders
ORDER BY OrderDate DESC;

-- SQL query in the destination database
SELECT TOP 10 OrderID, OrderDate, CustomerID, TotalAmount
FROM Orders
ORDER BY OrderDate DESC;

    Full data verification using DTS

    DTS supports verifying source and destination databases without service interruption. You can use DTS to create a full data verification task to verify whether all data has been fully migrated.

    Update database statistics

    If the performance of the destination ApsaraDB RDS for SQL Server instance significantly decreases after SQL Server migration, it may be because the data distribution in the ApsaraDB RDS for SQL Server instance has changed. You can perform a full update of statistics for specific databases to restore or improve instance performance.