All Products
Search

This Product

    ApsaraDB RDS:Migrate SQL Server databases to ApsaraDB RDS for SQL Server

    Document Center

    ApsaraDB RDS:Migrate SQL Server databases to ApsaraDB RDS for SQL Server

    Last Updated:Aug 22, 2025

    This topic describes the common solutions for migrating SQL Server databases to ApsaraDB RDS for SQL Server. You can select a solution based on the location of the data source, the tools used in each solution, and the benefits and limits of each solution.

    Preparations

    Cloud migration solutions

    Physical migration solutions

    Solution

    Procedure

    Reference

    Use Object Storage Service (OSS) to manually migrate data

    1. Set the backup_type parameter of the source database to FULL.

    2. Back up full data and upload the backup data 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 destination database.

    Use Data Disaster Recovery and DTS to quickly migrate data

    1. Deploy a physical protocol gateway.

    2. Use DTS to migrate data to the cloud. The system calls an API operation of OSS to upload the backup data to an OSS bucket.

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

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

    Logical migration solutions

    Solution

    Procedure

    Reference

    Use DTS to migrate data in log parsing mode

    1. Use DTS to migrate data to the cloud.

      Note

      Set the SQL Server Incremental Synchronization Mode parameter 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 destination database.

    Use DTS to migrate data in hybrid log parsing mode

    1. Use DTS to migrate data to the cloud.

      Note

      Set the SQL Server Incremental Synchronization Mode parameter 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 destination database.

    Use DTS to migrate data in change data capture (CDC) instance polling and querying mode

    1. Use DTS to migrate data to the cloud.

      Note

      Set the SQL Server Incremental Synchronization Mode parameter 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 destination database.

    Other solutions

    Solution

    Procedure

    Reference

    Use SSMS to migrate data

    1. Stop writing data to the source database.

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

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

    4. After data consistency is verified, switch the workloads to the destination database.

    Use SSMS to migrate data to the cloud

    Comparison of the cloud migration solutions

    Physical migration solutions

    Solution

    Benefit

    Limit

    Use OSS to manually migrate data

    • The backup and recovery features provided by SQL Server support a large number of scenarios.

    • The migration speed is fast.

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

    • You must manually back up and upload logs, which is cumbersome.

    • You can migrate only one database at a time.

    • You must stop writing data to the source database and wait until the last incremental log is successfully replayed before you switch the workloads to the new database. Therefore, service may be interrupted for several minutes.

    • The version of the destination database cannot be earlier than that of the source database.

    • The name mapping feature for databases, tables, and columns is not supported.

    Use Data Disaster Recovery and DTS to quickly migrate data

    • The migration operations are performed in the DTS console. The configurations and operations are simple and convenient.

    • You can migrate multiple databases at a time.

    • The backup and recovery features provided by SQL Server support a large number of scenarios.

    • The migration speed is fast.

    • The extension of the backup log file name must be bak.

    • You must stop writing data to the source database and wait until the last incremental log is successfully replayed before you switch the workloads to the destination database. Therefore, service may be interrupted for several minutes.

    • You must install AliyunDBSAgent on the server of the source database.

    • The version of the destination database cannot be earlier than that of the source database.

    • The name mapping feature for databases, tables, and columns is not supported.

    Logical migration solutions

    Solution

    Benefit

    Limit

    Use DTS to migrate data in log parsing mode

    • The migration operations are performed in the DTS console. The configurations and operations are simple and convenient.

    • You can migrate data between databases of different versions.

    • The name mapping feature for databases, tables, and columns is supported.

    • When you migrate incremental data by using DTS, you do not need to stop writing data to the source database. Service continuity is ensured.

    • You can migrate data from a third-party cloud platform to Alibaba Cloud.

      For example, you can migrate full data from Amazon RDS for SQL Server to Alibaba Cloud and migrate full and incremental data from Microsoft Azure SQL Database in NoSQL mode to Alibaba Cloud.

    • Only some DDL statements can be migrated. If more than 100 DDL statements are executed per hour, the migration speed may decrease.

    • If the speed of log write in the source database exceeds 10 MB/s, 30 GB/hour, or 500 GB/day, the task may be delayed or even fail.

    • If the frequency of log backup in the source database exceeds one time per hour, DTS may fail to obtain local backup logs. We recommend that you retain backup logs for three days on local disks.

    • DTS creates a trigger and DDL storage table in the source database to capture DDL changes.

    • You must disable FOREIGN KEY constraints during incremental data migration. Otherwise, the task may fail.

    • Heap tables, tables without primary keys, compressed tables, and tables that contain computed columns cannot be migrated.

    • Tables without PRIMARY KEY or UNIQUE constraints may contain duplicate data. If you want to retain tables without PRIMARY KEY or UNIQUE constraints, we recommend that you do not use this solution.

    • DTS uses the fn_log function to pull and parse logs. This function is not stable. If unexpected operations occur, the task may fail.

    • You can migrate no more than 10 databases in a single data migration task. Otherwise, stability and performance issues may occur.

    Use DTS to migrate data in hybrid log parsing mode

    • The migration operations are performed in the DTS console. The configurations and operations are simple and convenient.

    • You can migrate data between databases of different versions.

    • The name mapping feature for databases, tables, and columns is supported.

    • Heap tables, tables without primary keys, and compressed tables can be migrated.

    • You can migrate data from a third-party cloud platform to Alibaba Cloud.

      For example, you can migrate full data from Amazon RDS for SQL Server to Alibaba Cloud and migrate full and incremental data from Microsoft Azure SQL Database in NoSQL mode to Alibaba Cloud.

    • The source SQL Server database must be SQL Server 2008 or later of Enterprise Edition, or SQL Server 2016 SP1 or later of Standard Edition, excluding SQL Server 2017.

    • Only some DDL statements can be migrated. If more than 100 DDL statements are executed per hour, the migration speed is affected.

    • If the speed of log write in the source database exceeds 10 MB/s, 30 GB/hour, or 500 GB/day, the task may be delayed or even fail.

    • If the frequency of log backup in the source database exceeds one time per hour, DTS may fail to obtain local backup logs. We recommend that you retain backup logs for three days on local disks.

    • DTS enables CDC for databases and some tables and creates a trigger and DDL storage table in the source database to capture DDL changes.

    • You must disable FOREIGN KEY constraints during incremental data migration. Otherwise, the task may fail.

    • Tables that contain computed columns cannot be migrated.

    • Tables without PRIMARY KEY or UNIQUE constraints may contain duplicate data. If you want to retain tables without PRIMARY KEY or UNIQUE constraints, we recommend that you do not use this solution.

    • DTS uses the fn_log function to pull and parse logs. This function is not stable. If unexpected operations occur, the task may fail.

    • You can migrate no more than 10 databases in a single data migration task. Otherwise, stability and performance issues may occur.

    Use DTS to migrate data in CDC instance polling and querying mode

    • The migration operations are performed in the DTS console. The configurations and operations are simple and convenient.

    • You can migrate data between databases of different versions.

    • The name mapping feature for databases, tables, and columns is supported.

    • You can migrate data from a third-party cloud platform to Alibaba Cloud.

      For example, you can migrate full and incremental data from Amazon RDS for SQL Server, Microsoft Azure SQL Database, or Google Cloud SQL for SQL Server to Alibaba Cloud.

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

      If you use the native CDC component of SQL Server to obtain incremental data, you do not need to capture data from the transaction logs of the source database. Log truncation in the source database does not affect the DTS instance.

    • If the source database is a Microsoft Azure VM that runs SQL Server Enterprise Edition, its version must be SQL Server 2008 or later. If the source database is a Microsoft Azure VM that runs SQL Server Standard Edition, its version must be SQL Server 2016 SP1 or later. SQL Server 2017 is not supported.

    • The account that DTS uses to access the source database must have the permission to enable database-level and table-level CDC. To enable database-level CDC, an account to which the sysadmin role is assigned is required. To enable table-level CDC, a privileged account is required.

      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.

    • You cannot run a DTS task to migrate more than 1,000 tables from the source database. Otherwise, the task may be delayed or unstable.

    • You can migrate no more than 10 databases in a single data migration task. Otherwise, stability and performance issues may occur.

    • You can migrate no more than 10 databases in a single data migration task. Otherwise, stability and performance issues may occur.

    • Tables without PRIMARY KEY or UNIQUE constraints may contain duplicate data. If you want to retain tables without PRIMARY KEY or UNIQUE constraints, we recommend that you do not use this solution.

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

    • You cannot consecutively perform operations to add and remove columns (more than two DDL operations to add or remove columns within one minute). Otherwise, the task may fail.

    • You cannot perform change operations on the CDC instance of the source database. Otherwise, the task may fail or data may be lost.

    • If you configure a DTS task to migrate multiple tables in multiple databases, stability and performance issues may occur.

    Other solutions

    Solution

    Benefit

    Limit

    Use SSMS to migrate data

    The operations are simple, the process is stable, and the risk of data inconsistency is low.

    • You must stop writing data to the source database. Otherwise, data inconsistency may occur.

    • You must manually perform the migration by using SSMS.

    Select a cloud migration solution

    Important

    If the data source does not support incremental data migration, you must stop writing data to the source database before you migrate data to the cloud.

    Data source

    Support for incremental migration

    Supported solution

    Recommended solution

    Self-managed SQL Server database

    Yes

    • Use OSS to manually migrate data to the cloud based on physical backup files

    • Use Data Disaster Recovery and DTS to migrate data to the cloud based on physical backup files

    • Use DTS to perform logical cloud migration

    Use Data Disaster Recovery and DTS to migrate data to the cloud based on physical backup files

    Azure SQL Database

    Yes

    • Use DTS to perform logical cloud migration

      Note

      If you want to migrate incremental data, you must set the SQL Server Incremental Synchronization Mode parameter to Polling and querying CDC instances for incremental synchronization.

    • Use the ApsaraDB RDS console to perform end-to-end cloud migration

    • Use SSMS to perform cloud migration

    Use the ApsaraDB RDS console to perform end-to-end cloud migration or use DTS to perform logical cloud migration

    Azure SQL Managed Instance

    SQL Server on Azure Virtual Machines

    • Use DTS to perform logical cloud migration

      Note

      If you want to migrate incremental data, you must set the SQL Server Incremental Synchronization Mode parameter to Polling and querying CDC instances for incremental synchronization.

    • Use the ApsaraDB RDS console to perform end-to-end cloud migration

    • Use SSMS to perform cloud migration

    • Use OSS to manually migrate data to the cloud based on physical backup files

    Amazon RDS for SQL Server

    Yes

    • Use DTS to perform logical cloud migration

      Note

      If you want to migrate incremental data, you must set the SQL Server Incremental Synchronization Mode parameter to Polling and querying CDC instances for incremental synchronization.

    • Use the ApsaraDB RDS console to perform end-to-end cloud migration

    • Use SSMS to perform cloud migration

    • Use OSS to manually migrate data to the cloud based on physical backup files

    Use the ApsaraDB RDS console to perform end-to-end cloud migration or use DTS to perform logical cloud migration

    Huawei Cloud RDS for SQL Server

    No

    • Use SSMS to perform cloud migration

    • Use DTS to perform logical cloud migration for full data migration

    • Use OSS to manually migrate full data to the cloud based on physical backup files

    Use OSS to manually migrate full data to the cloud based on physical backup files

    Note

    • If the source database runs SQL Server 2008 R2, we recommend that you upgrade the database version before you perform the operation.

    • For information about how to manually create and download backup files in Huawei Cloud RDS for SQL Server, see the Huawei Cloud official documentation.

    TencentDB for SQL Server database

    Yes

    • Use DTS to perform logical cloud migration

    • Use OSS to manually migrate data to the cloud based on physical backup files

    • Use SSMS to perform cloud migration

    Use DTS to perform logical cloud migration

    No

    Use SSMS to perform cloud migration

    Google Cloud SQL for SQL Server

    Yes

    • Use SSMS to perform cloud migration

    • Use DTS to perform logical cloud migration

      Note

      If you want to migrate incremental data, you must set the SQL Server Incremental Synchronization Mode parameter to Polling and querying CDC instances for incremental synchronization.

    Use DTS to perform logical cloud migration

    What to do next

    Data verification

    After you migrate data from an SQL Server database to an ApsaraDB RDS for SQL Server instance, you can verify whether all data is migrated to the ApsaraDB RDS for SQL Server instance. You can use one of the following data verification methods based on your business requirements:

    Core data verification

    You can sort data by date or auto-increment ID to verify whether the latest business data is migrated. For example, if the core business table Orders contains fields such as OrderID and OrderDate, you can execute the following statement to query the fields in the source and destination databases:

    -- 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 by using DTS

    DTS allows you to verify the data in the source and destination databases without service downtime. You can use DTS to create a full data verification task to check whether all data is migrated from the source database to the destination database.

    Update database statistics

    After you migrate data from an SQL Server database to an ApsaraDB RDS for SQL Server instance, the performance of the destination ApsaraDB RDS for SQL Server instance may decrease significantly due to changes to data distribution in the instance. You can update all statistics in specific databases to restore or improve the performance of the instance.