SQL Server Best Practices: Migrating to ApsaraDB RDS for SQL Server Using SDK

By Wang Jianming, Senior Engineer

So far, we have discussed Using Asymmetric Keys to Implement Column Encryption, Database Recovery Models and Backups, Monitoring Backup and Restore Progress, and Using Filegroups to Implement Cold and Hot Data Isolation Backup Solution in the SQL Server Best Practices Series.

In this issue, we show you how to use Alibaba Cloud RDS SDK to implement the migration of offline user data backup files to Alibaba Cloud ApsaraDB RDS for SQL Server.

Scenario Description

For those who do not want to implement the database migration to cloud RDS SQL Server through RDS console and have advanced programming ability, consider using RDS SDK to implement the offline database migration to cloud the RDS SQL instance.

Implementation Analysis

The offline user database backup files are located in OSS, so the migration to the cloud requires designing an OSS-related SDK and an RDS-related SDK.

Required OSS Details

The offline user database backup files are stored in Bucket on OSS, so obtaining database backup files from OSS requires using the OSS SDK. When getting the backup files from OSS, we also need to know the Region where OSS Bucket is located because the migration to the cloud requires the RDS instance to be in the same Region as OSS Bucket. From these analyses, we must know the name of OSS Bucket, its Endpoint and backup filename bak_file_key.

Required RDS Details

The RDS instance refers to the target RDS SQL instance that users need to migrate to the cloud. We need to know the version information of the RDS SQL instance (input parameters for RDS SQL 2008R2 and 2012 and above are slightly different), the Region where the instance is located (RDS instance needs to be in the same Region as OSS bucket), and the name of the database on the target instance. Based on the analysis, for the RDS instance, we need to know the RDS SQL ID and database name.

Input Parameters

To access Alibaba Cloud resources, users need to use the AK of the Alibaba Cloud account or sub-account, namely, access_key_id and access_key_secret, therefore, these two parameters are also necessary. So, finally, our input parameters are defined as the following seven parameters.

Implementation Steps

After the implementation analysis is completed, the following sections describe the specific implementation methods in detail, including preparations, code implementation, and usage.

Preparations

Installing Python

First, install the appropriate Python version according to the guidance on the Official Website of Python; we recommend installing version 2.7.10.

Once installed, view the Python version.

Windows

If the above information is displayed, it means that you have successfully installed Python 2.7.10. If the system prompts “Not an internal or external command”, check the configuration “Environment Variables” — “Path” and add the Python installation path, as shown in the figure:

Mac/Linux/Unix

Installing the SDK Dependency Package

Use pip installation or git clone source code installation. We recommend using pip installation, which is simple, convenient and quick.

Pip installation

Install via source code

Source Code for Python SDK

In this article, the Python RDS SDK is used to implement database migration to the cloud RDS SQL Server. You can also use other versions, such as C# and Java. The detailed code is implemented as follows:

For the above code, you can also download the Python script referenced here.

Implementing the Migration

View Help

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

Example Implementation

The following is a specific example of migration using the RDS SDK:

Output Results

The output from executing the above command is divided into three 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
  3. The third part is the call return: return information of RDS OPENAPI SDK

Summary

This document described the best practices for using RDS SDK OPENAPI as well as the automation implementation of migrating use offline databases to Alibaba Cloud ApsaraDB RDS for SQL Server.

Reference:https://www.alibabacloud.com/blog/sql-server-best-practices-migrating-to-apsaradb-rds-for-sql-server-using-sdk_594482?spm=a2c41.12584177.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