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
• 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.
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.
The link for installing pip: https://www.jianshu.com/p/2559b55a9309
• 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 installThe source code package is as follows:
📎aliyun-openapi-python-sdk-master.zip
📎aliyun-oss-python-sdk-master.zip
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.
4. Run the backup script, and databases will be backed up to the specified directory.
The link for the OSS upload tool: https://gosspublic.alicdn.com/ossutil/1.7.19/ossutil-v1.7.19-windows-amd64.zip
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 -hThe result is as follows:
| 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:
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.
[Infographic] Highlights | Database New Features in September 2024
Data Encryption without Business Changes? Enough with this Tool
Cherish Wang - February 20, 2019
Alibaba Clouder - August 6, 2020
ApsaraDB - December 6, 2024
Alibaba Cloud Community - November 8, 2024
Alibaba Clouder - February 25, 2020
Cherish Wang - February 20, 2019
Best Practices
Follow our step-by-step best practices guides to build your own business case.
Learn More
Database Migration Solution
Migrating to fully managed cloud databases brings a host of benefits including scalability, reliability, and cost efficiency.
Learn More
Oracle Database Migration Solution
Migrate your legacy Oracle databases to Alibaba Cloud to save on long-term costs and take advantage of improved scalability, reliability, robust security, high performance, and cloud-native features.
Learn More
ADAM(Advanced Database & Application Migration)
An easy transformation for heterogeneous database.
Learn MoreMore Posts by ApsaraDB
