Migrate data from all SQL Server databases in an instance to ApsaraDB RDS SQL Server

Scenarios

ApsaraDB RDS SQL Server has released the following OSS-based methods to migrate data to ApsaraDB for RDS: Migrate data from an on-premises database to ApsaraDB RDS SQL Server 2008 R2 using full backup files, Migrate full backup data to ApsaraDB RDS SQL Server 2012, 2016, or 2017, and Migrate incremental backup data to ApsaraDB RDS SQL Server 2012, 2016, or 2017. These methods can be used to migrate data from a single on-premises or ECS-based database to ApsaraDB for RDS.

However, these methods are not suitable for the scenarios where dozens or hundreds of SQL Server databases are migrated from an on-premises or ECS-based SQL Server instance. To solve this problem, ApsaraDB RDS SQL Server provides an instance-based data migration feature to allow you to migrate a large number of databases to ApsaraDB for RDS. You only need to upload the full backup files of all databases to a folder in the OSS bucket, and then run the Python script to migrate data from the instance to ApsaraDB for RDS.

Prerequisites

• Only full backup files can be used to migrate instance-based data to ApsaraDB for RDS.

  This feature allows you to migrate data from all databases in an instance to ApsaraDB for RDS only by using full migration files, but not incremental backup files.

• The ApsaraDB RDS SQL Server instance must be one of the following editions:
  • ApsaraDB RDS SQL Server 2008 R2
  • ApsaraDB RDS SQL Server 2012/2016 Web Edition
  • ApsaraDB RDS SQL Server 2012 Enterprise Basic Edition
  • ApsaraDB RDS SQL Server 2012/2016 Standard/Enterprise Edition
  • ApsaraDB RDS SQL Server 2017 Enterprise Cluster Edition
• The OSS bucket is located in the same region as the ApsaraDB RDS SQL Server instance.

  Make sure that the OSS bucket is located in the same region as the ApsaraDB RDS SQL Server instance, so that you can download full backup files smoothly.

• Both Alibaba Cloud accounts and RAM users can be used to migrate instance-based data to ApsaraDB for RDS, but only Alibaba Cloud accounts have permissions to access OSS and RDS. To migrate data to ApsaraDB for RDS by using a RAM user, perform the following operations to authorize the RAM user:
  1. Choose Products > Monitor and Management > Resource Access Management to log on to the RAM console.
  2. In the left-side navigation pane, click Users. In the user list, find the RAM user you want to authorize, and click Authorize in the Actions column. The Edit User-Level Authorization dialog box appears.
  3. Authorize the account the AliyunOSSFullAccess, AliyunOSSReadOnlyAccess, AliyunRDSFullAccess, and AliyunRDSReadOnlyAccess permissions, and click OK, as shown in the following figure.
• Database backup files must meet the naming conventions.

  To ensure that the full backup files of all on-premises or ECS-based databases are used to migrate data to ApsaraDB for RDS, you must name the backup files by following the naming conventions. Each database name can then be obtained from the backup file names.

  Each database backup file must be named in the databaseName_backupType_backupTime.bak format, including the database name, backup type, and backup time.

  For example, TestDb_FULL_20180518153544.bak indicates the following information:

  • The database name is TestDb.
  • The database backup type is FULL.
  • The backup time is 20180518153544.

  • The backup file is suffixed with bak.

    We recommend that you use the database backup script provided by Alibaba Cloud. For more information, see "Back up all on-premises or ECS-based databases" in the "Preparations" section.

Preparations

Make the following preparations only once, including Python installation, dependency package installation, and OSS bucket creation.

1. Install Python.

   Install a version of Python, 2.7.10 recommended, by following the wizard on the Python official website.

2. Check the Python version.

   • Windows OS:

     Run the C:\>c:\Python27\python.exe -V command to check the Python version. If Python 2.7.10 is displayed, you have installed Python 2.7.10.

     If it is prompted 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, as shown in the following figure.

   • Mac, Linux, or Unix OS:

     Run the $ python -V command to check the Python version. If Python 2.7.10 is displayed, you have installed Python 2.7.10.

3. Install the SDK dependency package.

   Use either 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.

     # Use the git clone command to 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 RDS SDK of Alibaba Cloud.
       cd aliyun-python-sdk-rds
       python setup.py install
       # Use the git clone command to 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

4. Create an OSS bucket. Make sure that the OSS bucket is in the same region as the destination RDS instance. Skip this step if a bucket already exists.
   1. Log on to the OSS console.
   2. Click the plus sign + to the right of Buckets in the left-side navigation pane.
   3. Configure Bucket Name, Region, Storage Class, and Access Control List, and click OK, as shown in the following figure.
5. Create a database for the destination instance.
   • If your destination instance is ApsaraDB RDS SQL Server 2012 or later versions, skip this step.
   • If your destination instance is ApsaraDB RDS SQL Server 2008 R2, create all databases with the same names in the destination instance through the RDS console, and leave the databases empty. For more information about how to create databases for ApsaraDB RDS SQL Server 2008 R2 instances, see Create databases and accounts for an RDS instance in SQL Server 2008 R2.
6. Back up all on-premises or ECS-based databases.

   Before you perform a full backup for on-premises or ECS-based databases, make sure that you have stopped writing data. The data written during the backup process is not backed up.

   Under the precondition that database backup files meet the naming conventions, you can perform a full backup by using any feasible methods. We recommend that you use the following method to perform a full backup:

   1. Download the backup script and open it with SQL Server Management Studio (SSMS).
   2. Modify the following parameters as needed.

      Parameter	Description
      @backup_databases_list	The databases to be backed up. Separate multiple databases with semicolons (;) or commas (,).
      @backup_type	The type of the backup. Valid values: FULL: full backup DIFF: differential backup LOG: log backup
      @backup_folder	The local directory that stores the backup file. A directory will be automatically created if not specified.
      @is_run	Specifies whether to perform a backup. Valid values: 1: performs a backup. 0: only performs a check.

   3. Run the backup script.
7. Upload backup files to OSS.

   Assume that your database is located in an on-premises or ECS-based user-created SQL Server instance, and the ECS instance resides in a VPC. The upload speed in a VPC can reach a maximum of 100 MB/s. To upload backup files to OSS through the internal network in a more efficient way, use the OSS VPC endpoint when you log on to ossbrowser.

   1. Obtain the OSS VPC endpoint, as shown in the following figure.
   2. Specify parameters in ossbrowser as follows.

      When you use the AccessKey-based method to log on to ossbrowser, select Custom for Endpoint and enter the VPC endpoint such as http://oss-cn-beijing-internal.aliyuncs.com. Enter the AccessKey ID and AccessKey secret.

      Method	Description
      Use ossbrowser to upload backup files	We recommend that you use ossbrowser to upload backup files to OSS. For more information, see ossbrowser.
      Use the OSS console to upload backup files	If the size of backup files is smaller than 5 GB, you can upload the files in the OSS console. For more information, see Upload an object.
      Call OSS API operations to upload backup files	If you want to complete the migration unattended, you can use the OSS API to perform a multipart or resumable upload. For more information, see Multipart upload and resumable upload.

Demo

Procedure

1. Download the Python script.

   Download the instance-based migration script RDSSQLCreateMigrateTasksBatchly.py by clicking here.

2. Run the $ python ~/Downloads/RDSSQLCreateMigrateTasksBatchly.py -h command to view the help information.

   The following information is displayed:

   ~/Downloads/RDSSQLCreateMigrateTasksBatchly.py -k <access_key_id> -s <access_key_secret> -i <rds_instance_id> -e <oss_endpoint> -b <oss_bucket> -d <directory>

   Parameter description:

   Parameter	Description
   access_key_id	The AccessKey ID of the Alibaba Cloud account.
   access_key_secret	The AccessKey secret of the Alibaba Cloud account.
   rds_instance_id	The ID of the destination ApsaraDB RDS SQL Server instance.
   oss_endpoint	The endpoint of the OSS bucket where backup files are located. For more information about how to obtain the endpoint, see the screenshot in "OSS endpoint errors" in the "Common errors" section.
   oss_bucket	The OSS bucket where backup files are located.
   directory	The directory of backup files in the OSS bucket. If it is the root directory, enter a forward slash (/).

3. Run the instance-based migration script to complete the migration task.

   Example 1: Migrate all backup files from the Migration/OPENAPIDemo directory in the OSS bucket named atp-test-on-ecs to the ApsaraDB RDS SQL Server instance named rm-2zesz5774ud8s71i5.

   python ~/Downloads/RDSSQLCreateMigrateTasksBatchly.py -k LTAIQazXKPRwwErT -s BMkIUhroubQOLpOMqfA09IKlqp4G2k -i rm-2zesz5774ud8s71i5 -e oss-cn-beijing.aliyuncs.com -b atp-test-on-ecs -d Migration/OPENAPIDemo

   Example 2: Migrate all backup files from the root directory in the OSS bucket named atp-test-on-ecs to the ApsaraDB RDS SQL Server instance named rm-2zesz5774ud8s71i5.

   python ~/Downloads/RDSSQLCreateMigrateTasksBatchly.py -k LTAIQazXKPRwwErT -s BMkIUhroubQOLpOMqfA09IKlqp4G2k -i rm-2zesz5774ud8s71i5 -e oss-cn-beijing.aliyuncs.com -b atp-test-on-ecs -d /

4. View the migration task in the RDS console.

   Perform the following operations to view all the submitted migration tasks in the RDS console:

   ApsaraDB RDS SQL Server 2008 R2

   1. Log on to the ApsaraDB for RDS console.
   2. In the upper-left corner of the page, select the region where the destination instance is located.
   3. Click the ID of the destination instance.
   4. In the left-side navigation pane, click Database Migration to Cloud.
   5. On the Database Migration to Cloud page, view all the submitted migration tasks. You can also click Refresh in the upper-right corner of the page to view the latest statuses of migration tasks, as shown in the following figure.

   ApsaraDB RDS SQL Server 2012 and later versions

   Perform the following operations to view backup migration records within a period of time:

   In the left-side navigation pane, click Backup and Restoration. Click the Backup Data Upload History tab to view the migration records of the last seven days. You can specify the time range to view the records over a specific period of time.

Common errors

AccessKey ID errors

Error information

HTTP Status: 404 Error:InvalidAccessKeyId.NotFound Specified access key is not found. RequestID: XXXXXXXXXXXXXXXXX

Cause analysis

The AccessKey ID used to call the API is incorrect.

Solution

Use the correct AccessKey ID. You can find your AccessKey ID and AccessKey secret as follows:

1. Log on to the Alibaba Cloud console.
2. Move the pointer over your profile picture in the upper-right corner. A page shown in the following figure appears.
3. Click AccessKey to view your AccessKey ID and AccessKey secret.

AccessKey secret errors

Error information

HTTP Status: 400 Error:IncompleteSignature The request signature does not conform to Aliyun standards. server string to sign is:......

Cause analysis

The AccessKey secret used to call the API is incorrect.

Solution

The solution is the same as that for the preceding AccessKey ID errors.

Failure to support the RDS engine

Error information

RDS engine doesn't support, this is only for RDS SQL Server engine.

Cause analysis

The instance-based database migration feature does not support other RDS engines than SQL Server.

Non-existent instance ID

Error information

Couldn't find specify RDS [XXX].

Cause analysis

The ApsaraDB RDS SQL Server instance ID does not exist.

Solution

Check whether the specified ApsaraDB RDS SQL Server instance ID is correct. If not, enter the correct instance ID.

OSS endpoint errors

Error information

{'status': -2, 'request-id': '', 'details': "RequestError: HTTPConnectionPool(host='xxxxxxxxxxxxxxxxx', port=80): Max retries exceeded with url: /? bucketInfo= (Caused by NewConnectionError('<urllib3.connection.HTTPConnection object at 0x10e996490>: Failed to establish a new connection: [Errno 8] nodename nor servname provided, or not known',))"}

Cause analysis

The OSS endpoint is incorrect.

Solution

Make sure that you have entered a correct OSS endpoint. You can view the OSS endpoint as follows:

Log on to the OSS console. On the Overview tab of an OSS bucket, check the endpoint of the OSS bucket. You can use an external endpoint, as shown in the following figure.

OSS bucket errors

Error information

{'status': 404, 'request-id': 'xxxxxxxxx', 'details': {'HostId': 'xxxxxxxxx', 'Message': 'The specified bucket does not exist.', 'Code': 'NoSuchBucket', 'RequestId': 'xxxxxxxx', 'BucketName': 'aaaatp-test-on-ecs'}}

Cause analysis

The OSS bucket used to call the API does not exist.

Solution

Enter the correct OSS bucket name.

Non-existent folder or backup file in the OSS bucket

Error information

There is no backup file on OSS Bucket [xxxxxx] under [xxxxxxxxx] folder, check please.

Cause analysis

The corresponding folder does not exist in the OSS bucket, or the folder does not contain SQL Server backup files that meet the conditions.

Solution

Check whether the folder exists in the OSS bucket and whether the folder contains the backup files that meet the conditions.

Invalid backup file names

Error information

Warning!!!!!, [autotest_2005_ent_broken_full_dbcc_failed.bak] is not backup file, filtered.

Cause analysis

The backup file name that contains the database name fails to meet the naming conventions. For more information, see the naming conventions for backup file names.

Solution

Name the backup files based on the naming conventions.

API error responses

Error information

OPENAPI Response Error !!!!! : HTTP Status: <Http Status Code> Error:<Error> <Description>. RequestID: 32BB6886-775E-4BB7-A054-635664EE6AE4

Cause analysis

An error occurs when the API is called.

Solution

Analyze the specific error cause by checking the error information following HTTP Status. The following table describes the related error information.

Insufficient permissions for RAM users

Error information

The similar error information is returned:

HTTP Status: 403 Error:Forbidden.RAM The user is not authorized to operate the specified resource, or this operation does not support RAM. RequestID: xxxxx
{'status': 403, 'request-id': 'xxxx', 'details': {'HostId': 'atp-test-on-ecs.oss-cn-beijing.aliyuncs.com', 'Message': 'The bucket you visit is not belong to you.', 'Code': 'AccessDenied', 'RequestId': 'xxxx'}}

Cause analysis

The RAM user used to migrate databases to ApsaraDB for RDS does not have sufficient OSS or RDS permissions.

Solution

Authorize the RAM user sufficient permissions by following the authorization method.

Related API operations