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.
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.
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.
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.
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.
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).
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 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:
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:
|Instance||Object structure migration||Existing data migration||Data change replication|
|On-premises SQL Server||Select||Select||sysadmin|
|Target ApsaraDB RDS for SQL Server instance||Read/Write Permission||Read/Write Permission||Read/Write Permission|
The following describes the procedure of configuring a migration task from on-premises SQL Server to ApsaraDB RDS for SQL Server.
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:
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.
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:
alter database database_name set recovery_model_desc='full'
BACKUP LOG database_name to DISK= backup_place WITH init
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.
By default, DTS generates a task name automatically. The task name is not required to be unique. You can modify it as needed.
Step 3: Configure the migration types and objects to be migrated.
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.
Alibaba Clouder - August 6, 2020
Alibaba Clouder - July 9, 2020
Alibaba Clouder - January 27, 2021
Alibaba Clouder - February 11, 2020
Cherish Wang - February 20, 2019
Cherish Wang - February 20, 2019
An on-demand database hosting service for SQL Server with automated monitoring, backup and disaster recovery capabilitiesLearn More
An encrypted and secure cloud storage service which stores, processes and accesses massive amounts of data from anywhere in the worldLearn More
Supports data migration and data synchronization between data engines, such as relational database, NoSQL and OLAPLearn More
More Posts by Alibaba Clouder