SQL Server Best Practices: Migrating Multiple Instance-level Databases to ApsaraDB RDS for SQL Server

By Wang Jianming, Senior Engineer

We have discussed Using Asymmetric Keys to Implement Column Encryption, Database Recovery Models and Backups, Monitoring Backup and Restore Progress, Using Filegroups to Implement Cold and Hot Data Isolation Backup Solution, and Migrating to ApsaraDB RDS for SQL Server Using SDK in the ApsaraDB for RDS SQL Server in just one click.

Scenario Description

In the Migrating to ApsaraDB RDS for SQL Server Using SDK article that we covered last month, we implemented the automated migration of an on-premises or self-created database on ECS to ApsaraDB RDS for SQL Server. This is a solution for database-level migration to the cloud, that is, only one offline database is migrated to the cloud each time.

However, a user may have multiple SQL Server instances, and each instance may have tens of databases. For medium to large businesses, there could be thousands of databases that must be migrated to the cloud. The traditional solution for database-level migration to the cloud cannot meet the requirements of this scenario and is very inefficient.

To meet the requirements for migrating a large batch of databases to ApsaraDB RDS for SQL Server, we need to simplify the migration steps, and improve the migration-to-cloud efficiency.

Implementation Analysis

In the article Migrating to ApsaraDB RDS for SQL Server Using SDK, we implemented a solution for migrating a single database to the cloud. Similarly, we can use the following steps to implement a solution for migrating instance-level databases to the cloud:

  1. Upload the full backup files of the databases on a user’s offline instances to a folder in OSS.
  2. In OSS, traverse the full database backup files in that folder.
  3. Create a migration-to-cloud task for each backup file .

Input Parameters

According to the preceding analysis, the implementation method needs to include the six following input parameters. See the following table for the description of the six parameters:

Specific Implementation

Preparations

The preparations for this migration is similar to the steps described in the Migrating to ApsaraDB RDS for SQL Server Using SDKarticle. Please refer to the “Preparations” section for the article for further details.

Source Code for Python SDK

In this article, RDS SDK for Python is used to implement the migration of databases to RDS SQL Server. You can also use RDS SDK for C#, Java, or another language. The following is the detailed code implementation:

For the aforementioned code, you can also download the preceding Python scripts.

Implementing the Migration

We will briefly describe how to migrate instance-level databases to the cloud in just one click from the three following perspectives:

  1. View Help
  2. Example
  3. Output result

View Help

You can use -h to see how scripts are used:

Output Results

The result output of the preceding command is divided into two parts:

  1. The first part is input parameters: shows all your input parameters to query for input errors
  2. The second part is the prompt message: tells you which backup file is migrated to which database of which instance

Here is the input information of an instance:

Summary

This article shows us how to migrate offline users or self-created SQL Server instance-level databases on ECS to the cloud in just one click. This migration solution can significantly improve the migration-to-cloud efficiency and simplify the migration operations, providing much better migration-to-cloud experience for users.

Reference:https://www.alibabacloud.com/blog/sql-server-best-practices-migrating-multiple-instance-level-databases-to-apsaradb-rds-for-sql-server_594483?spm=a2c41.12584191.0.0

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

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store