Real-time SQL Server Migration to Alibaba Cloud ApsaraDB RDS for SQL Server

Join us at the Alibaba Cloud ACtivate Online Conference on March 5–6 to challenge assumptions, exchange ideas, and explore what is possible through digital transformation.

By Steve Chen, Solutions Architect

For many enterprises, hosting a Microsoft SQL Server on-premises can be a resource intensive endeavor, especially when it comes to operating and maintaining the server. More and more enterprises are migrating their databases to the cloud to fully enjoy the benefits of a cloud-based deployment. This article illustrates a possible solution for customers to migrate all on-premises SQL Server databases to an Alibaba Cloud ApsaraDB RDS for SQL Server instance with minimal down time.

This article will make up a typical sample use case and environment, and then elaborate the migration solutions on the sample environment.

Sample On-Premises Environment

Source Database

Transactional Database


CPU: 32 Memory: 98259 (MB) Storage: 800GB

Version: 11.0.5058.0 Microsoft SQL Server Enterprise: Core-based Licensing (64-bit)


Customer has a VPN link between on-premises IDC and Alibaba Cloud VPC.

A physical leased line between them is more favourable.

Compatibility Evaluation

Alibaba Cloud ApsaraDB RDS for SQL is a managed database service in Alibaba Cloud. It is mostly compatible with all feature of SQL Server of different versions. However, as this is a managed database service on the cloud which has certain limitations, you should evaluate the differences between the on-premises and RDS before making a right migration decision in terms of database version and features.

Alibaba Cloud Database Assessment Expert tool (DAE) is an offline tool to evaluate the compatibility of your existing SQL Server version on-premises and Alibaba Cloud RDS for SQL Server. This DAE tool will potentially look into following features in your database and give report with regard to that. This tool can be deployed on any Windows OS host with .net environment which is accessible to the targeted SQL Server database.

The following features are fully supported if the right version of RDS for SQL Server is chosen in Alibaba Cloud.

The following may have small differences between Alibaba Cloud RDS and on-premises SQL Server, but adjustment effort for migration is small.

The following features has big gap between on-premises SQL Server and RDS for SQL Server. Certain modifications need to be conducted in the application or databases before migration.

The use of DAE is referred in this link, however, it is currently only available in Chinese.

Migration Plan

There are two different migration plans that can be potentially applied to this case, and both methods have their unique advantages and disadvantages.

The introduction of both plans and migration steps are elaborated in the following sections.

Option 1: Physical Migration

Physical migration will take the full database copy file and log copy files from the existing database instance, move them to cloud and load them into the Alibaba Cloud ApsaraDB RDS for SQL Server instance.

The advantage of physical migration is that it will make sure that the data in the destination RDS is identical to the source instance, because the physical database backup copies are replicated and loaded.

The disadvantage of physical migration is that the cut over RPO is in > 5 minutes, so it needs to be conducted in the business lull period and interruption of the database in minutes.

Migration Steps

Migration flowchart

The following table illustrates the migration steps of a demo project in order to understand the entire migration procedure.

The migration will use Alibaba Cloud RDS for SQL Server and Object Storage Service (OSS).

Option 2: Logical Migration

Logical migration will use Alibaba Cloud Data Transmission Service (DTS) to fetch the full amount and incremental data entries from the source database instance and use DML to insert/delete/update those entries to the destination Alibaba Cloud RDS instance.

The advantage of logical migration is that the cut over RPO is in seconds-level in the lull period, so that the impact to the business will be minimized.

The disadvantage of logical migration is that because DTS uses DML to insert entries in the destination instance, so some database features in the source database might not be supported by DTS and replicated to the destination, limitation of DTS is listed in the following:

Restrictions of data change replication:

DDL operations that can be migrated by data change replication:

Migration Steps

Migration flowchart using DTS:

The following migration steps are based on the assumption that there is a VPN connection between Hostworks environment and Alibaba Cloud.

In case of a VPN connection, apart from the standard DTS configuration below, we need to introduce an Ngnix proxy in the Alibaba Cloud VPC where the RDS sits, all the DTS data retrieval request to the source database in Hostworks will be forwarded by the proxy through VPN connection.

The following illustrates how to migrate the database using DTS. To improve the success rate of migration from on-premises SQL Server to ApsaraDB RDS for SQL Server, DTS uses the following migration process:

  1. Migrate the structure objects: tables, views, synonyms, user-defined data types, rules, defaults, and PlanGuid.
  2. Migrate existing data.
  3. Migrating the structure objects: stored procedures, functions, triggers, and foreign keys.
  4. Replicate data changes.

For a migration task that does not include data change replication, if the migration progress is Structure migration 100%, data migration 100% and the migration status is Migrating, do not end the task manually because the task is performing the preceding step 3. Otherwise, migration data loss may occur.

When DTS is used for data migrations from on-premises SQL Server to ApsaraDB RDS for SQL Server, the migration accounts of the source and target instances must have the following permissions:

InstanceObject structure migrationExisting data migrationData change replicationOn-premises SQL ServerSelectSelectsysadminTarget ApsaraDB RDS for SQL Server instanceRead/Write PermissionRead/Write PermissionRead/Write Permission

The following describes the procedure of configuring a migration task from on-premises SQL Server to ApsaraDB RDS for SQL Server.

Creating a Database on the RDS Instance

During a data migration, if the database to be migrated does not exist in the target RDS instance, DTS automatically creates one in the target RDS instance. However, in either of the following cases, you need to manually create the database before configuring a migration task:

Creating Migration Accounts

When configuring a migration task, you need to provide the migration accounts of the source and target instances. The accounts must have the permissions listed in the preceding Migration permissions section.

Other Preparations Before Launching DTS

To perform the migration without stopping services, you need to set the log format of the source database to full by running the following two commands in the source database:

Configuring a Migration Task in the DTS Console

After the databases and migration accounts are created, configure a migration task as follows:

Step 1: Log on to Alibaba Cloud DTS console and click Create Migration Task at the upper right corner.

Step 2: Configure the connection information of the on-premises SQL Server and the target RDS instance.

Task name

By default, DTS generates a task name automatically. The task name is not required to be unique. You can modify it as needed.

Source database

Target database

Step 3: Configure the migration types and objects to be migrated.

Migration types

To perform a migration without stopping services, select all the three migration types.

To migrate only the existing data, select migrate object structure and migrate existing data.

Objects to be migrated

The objects are databases, tables, and columns. By default, after an object is migrated, the object name in the source instance is the same as that in the target instance. If you want them to be different, use the Object name mapping function.

Step 4: Perform a pre-check.

A pre-check is performed before the migration starts. If the pre-check fails, click Failed next to the failure items, solve the problem, and perform the pre-check again.

Step 5: Start the migration task.

If the pre-check succeeds, start the migration task. You can view the migration status and progress in the task list.

Data change replication is a process of dynamic synchronization. When the data change replication has no latency, verify the data on the target database. If the data is correct, disable the migration task and switch services to the target database to finish the cut over.


Follow me to keep abreast with the latest technology news, industry insights, and developer trends.