This topic describes how to migrate the data of some or all databases from a self-managed SQL Server instance to an ApsaraDB RDS for SQL Server instance by using full backup files.
Background information
To migrate data from multiple databases to RDS instances, ApsaraDB RDS for SQL Server provides an instance-level migration method to migrate the data to an RDS instance. You need to only upload the full backup files of these databases to the same folder in an Object Storage Service (OSS) bucket, and then run the required script to migrate data to an RDS instance.
If you use a database-level migration method, you can migrate the data of only one database at a time. ApsaraDB RDS for SQL Server provides the following OSS-based cloud migration methods:
Prerequisites
The RDS instance runs SQL Server 2012 or later.
The source database is a self-managed SQL Server instance.
If you use a RAM user to migrate data, make sure that the AliyunOSSFullAccess and AliyunRDSFullAccess policies are attached to the RAM user. For more information about how to grant permissions to RAM users, see Use RAM to manage OSS permissions and Use RAM to manage ApsaraDB RDS permissions.
Limits
Only full backup files can be used for the data migration.
Preparations
Install Python 2.7.18. For more information, visit the Python official website.
Check whether Python 2.7.18 is installed.
Windows operating systems
Run the
c:\Python27\python.exe -V
command to check the Python version. IfPython 2.7.18
is displayed, Python 2.7.18 is installed.If the system prompts that the preceding command is not an internal or external command, add the Python installation path and the pip command path to the Path environment variable.
Mac, Linux, or Unix operating systems
Run the
python -V
command to check the Python version. IfPython 2.7.18
is displayed, Python 2.7.18 is installed.
Use one of the following methods to install the SDK dependency package:
Run the pip command
pip install aliyun-python-sdk-rds pip install oss2
Use the source code
# Clone the API repository. git clone https://github.com/aliyun/aliyun-openapi-python-sdk.git # Install the SDK core repository of Alibaba Cloud. 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 OSS SDK. git clone https://github.com/aliyun/aliyun-oss-python-sdk.git cd aliyun-oss-python-sdk # Install oss2. python setup.py install
Create an OSS bucket. Make sure that the OSS bucket resides in the same region as the RDS instance. For more information, see Create buckets.
NoteIf an OSS bucket that resides in the same region as the RDS instance already exists, skip this step.
Create databases on the RDS instance. Make sure that each database whose data you want to migrate has a counterpart with an identical name on the RDS instance. In addition, keep the created databases empty.
If the RDS instance runs SQL Server 2012 or later, skip this step.
If the RDS instance runs SQL Server 2008 R2, create databases based on the descriptions in Create an account and a database for an ApsaraDB RDS instance that runs SQL Server 2008 R2.
Back up all databases on the self-managed SQL Server instance.
WarningFor data consistency purposes, we recommend that you stop data writes to these databases during the full backup.
If you do not use the backup script to perform the full backup, the names of the generated backup files must follow the format of
Database name_Backup type_Backup time.bak
, such asTestdb_FULL_20180518153544.bak
. If the name is in a different format, the backup fails.
Download the backup script file.
Double-click the backup script file to open it by using Microsoft SQL Server Management Studio (SSMS). For more information about how to use SSMS for connections, see official documentation.
Configure the following parameters.
Parameter
Description
@backup_databases_list
The name of the self-managed database that you want to back up. If you specify multiple databases, separate the names of these databases with semicolons (;) or commas (,).
@backup_type
The backup type. Valid values:
FULL: full backup
DIFF: incremental backup
LOG: log backup
ImportantIn this example, set the value to FULL.
@backup_folder
The directory that is used to store backup files on the self-managed database. If the specified directory does not exist, the system automatically creates one.
@is_run
Specifies whether to perform a backup or a check. Valid values:
1: performs a backup.
0: performs a check.
Examples:
SELECT /** * Databases list needed to backup, delimiter is : or , * empty('') or null: means all databases excluding system database * example: '[testdb]: TestDR, Test, readonly' **/ @backup_databases_list = N'[dtstestdata],[testdb]' @backup_type = N'FULL', -- Backup Type? FULL: FULL backup; DIFF: Differential backup; LOG: Log backup @backup_folder = N'C:\BACKUP' -- Backup folder to store backup files. @is_run = 1 -- Check or run? 1, run directly; 0, just check
Run the backup script to back up the specified databases and store the backup files to the specified directory.
Use one of the following methods to upload the backup files to the OSS bucket that you created.
WarningMake sure that the OSS bucket and the RDS instance reside in the same region. This allows the RDS instance to read the backup files at faster speeds. This also helps prevent failures that may occur if the backup files cannot be downloaded.
Method
Description
Use ossbrowser to upload backup files
We recommend that you use ossbrowser to upload backup files to the OSS bucket. For more information, see Use ossbrowser.
ImportantIf the self-managed instance is deployed in an Elastic Compute Service (ECS) instance that resides in a virtual private cloud (VPC), you can upload the backup files to the specified OSS bucket by using the VPC endpoint of the OSS bucket. This improves upload efficiency.
Use the OSS console to upload backup files
If the size of backup files is smaller than 5 GB, you can upload the files by using the OSS console. For more information, see Upload objects.
Call OSS API operations to upload backup files
If you want to upload the backup files unattended, use the OSS API operations to perform resumable uploads. For more information, see Multipart upload.
Procedure
Download the migration script package.
Decompress the migration script package and run the following command to view the parameters that you need to specify:
python ~/Downloads/RDSSQLCreateMigrateTasksBatchly.py -h
A similar result is returned:
RDSSQLCreateMigrateTasksBatchly.py -k <access_key_id> -s <access_key_secret> -i <rds_instance_id> -e <oss_endpoint> -b <oss_bucket> -d <directory>
Table 1. Parameters Parameter
Description
access_key_id
The AccessKey ID of the Alibaba Cloud account to which the RDS instance belongs.
access_key_secret
The AccessKey secret of the Alibaba Cloud account to which the RDS instance belongs.
rds_instance_id
The ID of the RDS instance.
oss_endpoint
The endpoint of the OSS bucket that stores the backup files. For more information about how to obtain the endpoint, see Bucket overview.
oss_bucket
The name of the OSS bucket that stores the backup files.
directory
The folder that stores the backup files in the OSS bucket. If the backup files are stored in the root folder, enter a forward slash (
/
).Run the migration script to complete the migration task.
Examples:
You can run the migration script to migrate all the backup files that meet the specified conditions from the Migrationdata folder in the OSS bucket named testdatabucket to the RDS instance whose ID is rm-2zesz5774ud8s****.
python ~/Downloads/RDSSQLCreateMigrateTasksBatchly.py -k LTAIQ**** -s BMkIUhroub******** -i rm-2zesz5774ud**** -e oss-cn-beijing.aliyuncs.com -b testdatabucket -d Migrationdata
View the progress of the migration task.
- Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
Perform the following steps based on the SQL Server version of your RDS instance:
SQL Server 2008 R2
In the left-side navigation pane of the page that appears, click Database Migration to Cloud. You can view all the migration tasks that you have submitted.
NoteYou can click Refresh in the upper-right corner of the page to view the latest status of the migration tasks.
SQL Server 2012 and later
In the left-side navigation pane of the page that appears, click Backup and Restoration. Then, click the Backup Data Upload History tab.
NoteBy default, the migration records over the last seven days are displayed. You can specify a time range to view the migration tasks over the specified time range.
Common errors
Error message | Cause | Solution |
| The AccessKey ID that is used to call API operations is invalid. | Use the valid AccessKey ID and AccessKey secret. For more information, see FAQ about AccessKey pairs. |
| The AccessKey secret that is used to call API operations is invalid. | |
| The RDS instance to which you want to migrate data does not run SQL Server. | Use an RDS instance that runs SQL Server. |
| The ID of the RDS instance does not exist. | Check whether the ID of the RDS instance is valid. If the ID of the RDS instance is invalid, enter the valid instance ID. |
| The endpoint that is used to connect to the OSS bucket is invalid. | Check whether the endpoint that is used to connect to the OSS bucket is valid. If the endpoint is invalid, enter the valid endpoint. For more information about how to obtain the endpoint, see Bucket overview. |
| The OSS bucket does not exist. | Check whether the entered name of the OSS bucket is valid. If the entered name is invalid, enter the valid name. |
| The required folder does not exist in the OSS bucket, or the folder does not contain the backup files that meet the specified conditions. | Check whether the folder exists in the OSS bucket and whether the folder contains the backup files that meet the specified conditions. If the folder does not exist in the OSS bucket and the folder does not contain backup files that meet the specified conditions, create the folder in the OSS bucket and import backup files that meet the specified conditions. |
| The names of the backup files do not meet the naming conventions. | If you do not use the backup script to perform the full backup, the names of the generated backup files must follow the format of |
| The RAM user does not have the required permissions. | Attach the AliyunOSSFullAccess and AliyunRDSFullAccess policies to the RAM user. For more information about how to authorize a RAM user, see Authorize a RAM user. |
| An error occurs when an API operation is called. | Analyze the specific error cause based on the error information that is described in Error codes. |
HTTP status code | Error message | Meaning | Description |
403 | InvalidDBName | The specified database name is not allowed. | The error message returned because the specified database names are invalid. For example, if the name of a database is the same as the name of a system database, the name of the database is invalid. |
403 | IncorrectDBInstanceState | Current DB instance state does not support this operation. | The error message returned because the RDS instance is not in a required state. For example, the RDS instance is in the Creating state. |
400 | IncorrectDBInstanceType | Current DB instance type does not support this operation. | The error message returned because the RDS instance does not run SQL Server. |
400 | IncorrectDBInstanceLockMode | Current DB instance lock mode does not support this operation. | The error message returned because the RDS instance is in a locking state that does not support the operation. |
400 | InvalidDBName.NotFound | Specified one or more DB name does not exist or DB status does not support. | The error message returned because the specified databases cannot be found.
|
400 | IncorrectDBType | Current DB type does not support this operation. | The error message returned because the operation is not supported by the database engine that is run on the RDS instance. |
400 | IncorrectDBState | Current DB state does not support this operation. | The error message returned because the databases are being created or receiving data from another migration task. |
400 | UploadLimitExceeded | UploadTimesQuotaExceeded: Exceeding the daily upload times of this DB. | The error message returned because the number of data migration tasks that are performed on a single database on the day exceeds 20. |
400 | ConcurrentTaskExceeded | Concurrent task exceeding the allowed amount. | The error message returned because the number of data migration tasks that are performed on a single database on the day exceeds 500. |
400 | IncorrectFileExtension | The file extension does not support. | The error message returned because the file name extensions of the backup files are invalid. |
400 | InvalidOssUrl | Specified oss url is not valid. | The error message returned because the specified URL to download backup files from the OSS bucket is invalid. |
400 | BakFileSizeExceeded | Exceeding the allowed bak file size. | The error message returned because the total size of the backup files exceeds 3 TB. |
400 | FileSizeExceeded | Exceeding the allowed file size of DB instance. | The error message returned because the size of the data restored from the backup files exceeds the available storage of the RDS instance. |
Related operations
Operation | Description |
Creates a migration task. | |
Opens a database. | |
Queries the data migration tasks. | |
Queries the backup file details of a data migration task. |