Solutions for migrating SQL Server databases to Alibaba Cloud - Data Transmission Service

This topic describes the common solutions for migrating SQL Server databases to Alibaba Cloud. 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

The destination ApsaraDB RDS for SQL Server instance is created. The specifications and storage space of the destination instance are greater than or equal to those of the source database. For more information, see Create an ApsaraDB RDS for SQL Server instance.
Note
If you want to use Data Transmission Service (DTS) to configure a data migration task, make sure that the source and destination database versions are supported by DTS. For more information, see Overview of data migration scenarios.
Execute the SELECT name, compatibility_level FROM sys.databases; statement on both the source and destination databases to check whether the destination database is compatible with the source database. For more information, see ALTER DATABASE (Transact-SQL) compatibility level.
Make sure that the security settings, including firewall rules, whitelists, and security groups, of the source and destination databases do not restrict the access requests from data migration tools.
Note
If you want to use DTS to configure a data migration task, make sure that DTS can access the source and destination databases. For more information, see Add the CIDR blocks of DTS servers.

Cloud migration solutions

Solution		Procedure	References
Cloud migration based on physical backup files	Use Object Storage Service (OSS) to manually migrate data	Set the backup_type parameter of the source database to FULL. Back up full data and upload the backup data to an OSS bucket. Back up and upload incremental logs on a scheduled basis. 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 instance to an ApsaraDB RDS for SQL Server instance Note If the source database runs SQL Server 2008 R2, we recommend that you upgrade the database version before you perform the operation.
Cloud migration based on physical backup files	Use Database Backup (DBS) and DTS to migrate data with a few clicks	Deploy a physical protocol gateway. 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. 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 cloud migration	Use DTS to migrate data in log parsing mode	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). 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
	Use DTS to migrate data in hybrid log parsing mode	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). Stop writing data to the source database. After the last incremental log is successfully replayed, switch the workloads to the new database.
	Use DTS to migrate data in change data capture (CDC) instance polling and querying mode	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. Stop writing data to the source database. After the last incremental log is successfully replayed, switch the workloads to the new database.
Other	Use SQL Server Management Studio (SSMS) to migrate data	Stop writing data to the source database. Use SSMS to export the data of the source database. Use SSMS to import the exported data to the destination database. After data consistency is verified, switch the workloads to the new database.	Use SSMS to migrate data to the cloud

Comparison of the cloud migration solutions

Solution		Benefit	Limit
Cloud migration based on physical backup files	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.
Cloud migration based on physical backup files	Use DBS and DTS to migrate data with a few clicks	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 new 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 cloud migration	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 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 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. Note For information about how to check whether an SQL Server database contains these tables, see the How do I view information about heap tables, tables without primary keys, compressed tables, and tables that contain computed columns in an SQL Server database? section of the FAQ topic. 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. Note For information about how to check whether an SQL Server database contains these tables, see the How do I view information about heap tables, tables without primary keys, compressed tables, and tables that contain computed columns in an SQL Server database? section of the FAQ topic. 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. 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. 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 execute a DDL statement to add or remove columns more than twice within a minute. Otherwise, the data migration task may fail. During data migration, you cannot modify the CDC instances of the source database. Otherwise, the data migration task may fail or data loss may occur. If you configure a DTS task to migrate multiple tables in multiple databases, stability and performance issues may occur.
Other	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	Whether incremental data migration is supported	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 DBS and DTS to migrate data to the cloud based on physical backup files Use DTS to perform logical cloud migration	Use DBS and DTS to migrate data to the cloud based on physical backup files with a few clicks. For more information, see Migrate data from a self-managed SQL Server database to an ApsaraDB RDS for SQL Server instance by using a physical gateway.
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	For more information about how to use the ApsaraDB RDS console to perform end-to-end cloud migration or how to use DTS to perform logical cloud migration, see Migrate data from a SQL Server database on Microsoft Azure to ApsaraDB RDS for SQL Server.
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	For more information about how to use the ApsaraDB RDS console to perform end-to-end cloud migration or how to use DTS to perform logical cloud migration, see Migrate data from an Amazon RDS for SQL Server instance to an ApsaraDB RDS for SQL Server instance.
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	For more information about how to use OSS to manually migrate full data to the cloud based on physical backup files, see Migrate the full backup data of a self-managed SQL Server instance to an ApsaraDB RDS instance that runs SQL Server 2008 R2 with cloud disks or runs SQL Server 2012 or later. 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 obtain backup data, see Creating a Manual Backup and Downloading a Backup File.
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	For more information about how to use DTS to perform logical cloud migration, see Migrate data from a self-managed SQL Server database to an ApsaraDB RDS for SQL Server instance.
TencentDB for SQL Server database	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.	For more information about how to use DTS to perform logical cloud migration, see Migrate data from a self-managed SQL Server database to an ApsaraDB RDS for SQL Server instance.

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. For more information, see Configure a data verification task.

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. For more information, see the Update database statistics section of the Manage the attributes of the database topic.