×
Community Blog Best Practices for Migrating SQL Server Instance-Level Databases to the Cloud

Best Practices for Migrating SQL Server Instance-Level Databases to the Cloud

This article uses SQL Server 2017 as an example to illustrate the configuration steps for the entire migration process.

By Yuanyi

ApsaraDB RDS for SQL Server offers an instance-level database migration solution, supporting the migration of data of multiple databases or all databases from a self-managed SQL Server instance to an ApsaraDB RDS for SQL Server instance. You just need to back up all databases of the self-managed SQL Server, upload the full backup files to the same folder in an Object Storage Service (OSS) Bucket, and then execute the required migration script.

The backup migration method supports various versions of SQL Server. This article uses SQL Server 2017 as an example to illustrate the configuration steps for the entire migration process. For detailed feature descriptions, please refer to the official documentation: https://www.alibabacloud.com/help/rds/apsaradb-rds-for-sql-server/migrate-data-from-a-self-managed-sql-server-instance-to-an-apsaradb-rds-for-sql-server-instance

Prerequisites

• The RDS instance has sufficient storage space.

• The OSS service is activated.

• If you use a RAM user, the following requirements need to be met:

o The RAM user has the AliyunOSSFullAccess and AliyunRDSFullAccess permissions. For information on granting permissions to a RAM user, please refer to Use RAM to manage OSS permissions and Use RAM to manage ApsaraDB RDS permissions.

o The Alibaba Cloud account (primary account) has authorized the service account of ApsaraDB RDS to access your OSS bucket.

• Only full backup files can be used for the data migration.

Preparation

1.  Install Python 2.7.18 or Python 3.11.7.

Python installation package: 📎python-3.11.7-amd64.zip

2.  Verify whether Python is installed and check the version.

o On the Windows operating system, run c:Python27python.exe -V to check the Python version. If the output is Python 2.7.18, it indicates a successful installation. If the system prompts that the preceding command is not an internal or external command, add the Python installation path and the pip command directory to the Path environment variable.

1
2

The link for installing pip: https://www.jianshu.com/p/2559b55a9309

Install the SDK dependency package:

• Use the source code.

# Clone OpenAPI
git clone https://github.com/aliyun/aliyun-openapi-python-sdk.git
# Install the core library of Alibaba Cloud SDK
cd aliyun-python-sdk-core
python setup.py install
# Install the ApsaraDB RDS SDK
cd aliyun-python-sdk-rds
python setup.py install
# Clone the Alibaba Cloud OSS SDK
git clone https://github.com/aliyun/aliyun-oss-python-sdk.git
cd aliyun-oss-python-sdk
# Install the Alibaba Cloud OSS2 SDK
python setup.py install

The source code package is as follows:

📎aliyun-openapi-python-sdk-master.zip

📎aliyun-oss-python-sdk-master.zip

Step 1: Back up All Databases of the Self-managed SQL Server

Note:

• For data consistency purposes, we recommend that you do not write data to these databases during the full backup. Please schedule this task in advance to avoid impacting your business operations.

• If you do not use the backup script to perform the backup, the backup file must be named in the format of database name_backup type_backup time.bak, for example, Testdb_FULL_20180518153544.bak. Otherwise, it will result in a backup error.

1.  Download the backup script file.

📎RDSBackupSpecifiedDatabasesToLocal.sql

2.  Double-click the backup script file and open it by using Microsoft SQL Server Management Studio (SSMS).

3.  Configure the following parameters according to your business needs.

Configuration Item Description
@backup_databases_list The name of the database that you want to back up. If you specify multiple databases, separate the names of the databases with semicolons (;) or commas (,).
@backup_type The backup type. Valid values:

● FULL: full backup.
● DIFF: differential backup.
● LOG: log backup.
@backup_folder The local directory that is used to store the backup files. If the specified directory does not exist, the system automatically creates one.
@is_run Specify whether to perform a backup. Valid values:

● 1: performs a backup.
● 0: only performs a check, not the backup.

In this example, set the value to FULL.

3

4.  Run the backup script, and databases will be backed up to the specified directory.

4

Step 2: Upload the Backup File to the OSS Bucket

The link for the OSS upload tool: https://gosspublic.alicdn.com/ossutil/1.7.19/ossutil-v1.7.19-windows-amd64.zip

5

Step 3: Run the Migration Script to Migrate the Database to RDS

Download the SQL Server migration script.

📎RDSSQLCreateMigrateTasksBatchly.py

After decompressing, run the following command to view the parameter information needed by this script.

python ./RDSSQLCreateMigrateTasksBatchly.py -h

The result is as follows:

6

Parameter Description
access_key_id The AccessKey ID of the Alibaba Cloud account to which the target RDS instance belongs.
access_key_secret The AccessKey Secret of the Alibaba Cloud account to which the target RDS instance belongs.
rds_instance_id The ID of the target RDS instance.
oss_endpoint The Endpoint address of the OSS bucket that stores the backup files. For information on obtaining the Endpoint address, please refer to the Bucket overview.
oss_bucket The name of the OSS bucket that stores the backup files.
directory The directory in the OSS bucket that stores the backup files. If the backup files are stored in the root directory, please enter a forward slash (/).
RDSSQLCreateMigrateTasksBatchly.py -k <access_key_id> -s <access_key_secret> -i <rds_instance_id> -e <oss_endpoint> -b <oss_bucket> -d <directory>

Example:

7

Run the migration script to complete the migration task.

View the progress of the migration task in the RDS console.

Access the RDS Instances page, select the region in which the RDS instance resides in the top navigation bar, and then click the target instance ID.

8
9

Compare the Migrated Data:

10
11

0 1 0
Share on

ApsaraDB

451 posts | 96 followers

You may also like

Comments