This topic describes how to migrate the data of some or all of the databases from an on-premises SQL Server instance to an ApsaraDB RDS for SQL Server instance. You can complete the data migration by using full backup files.

Prerequisites

The RDS instance must run one of the following SQL Server versions and RDS editions:
  • SQL Server 2017 EE on RDS Cluster Edition
  • SQL Server 2008 R2, 2012 SE, 2012 EE, 2014 SE, 2016 SE, 2016 EE, 2017 SE, and 2019 SE on RDS High-availability Edition
  • SQL Server 2012 SE, 2012 Web, and 2016 Web on RDS Basic Edition

Background information

ApsaraDB RDS for SQL Server supports the following three Object Storage Service (OSS)-based methods that are used to migrate the data of a single database from an on-premises instance to an RDS instance:

However, if a large number of databases are created on the on-premises instance, these methods are time-consuming.

To resolve the problem, ApsaraDB RDS for SQL Server also provides an OSS-based method that allows you to migrate the data of some or all of the databases from an on-premises instance to an RDS instance. You only need to upload the full backup files of these databases to the same folder in an OSS bucket, and then execute the script that is used to migrate data to an RDS instance.

Precautions

  • Only full backup files can be used for the data migration.
  • To migrate data by using a RAM user, you must grant the RAM user the AliyunOSSFullAccess and AliyunRDSFullAccess permissions. For more information, see Grant permissions to a RAM user.

Before you begin

  1. Install Python 2.7.10. For more information, visit the Python official website.
  2. Check whether Python 2.7.10 is installed.
    • For Windows operating systems

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

      If the system states 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.

      Configure the Path environment variable
    • For Mac, Linux, or Unix operating systems

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

  3. Use one of the following two 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 RDS SDK of Alibaba Cloud.
      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
  4. 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.
    Note Skip this step if an OSS bucket that resides in the same region as the RDS instance already exists.
  5. Create databases on the RDS instance. Make sure that each database whose data you want to migrate from the on-premises instance has a counterpart with an identical name on the RDS instance. In addition, keep the created databases empty.
  6. Back up some or all of the databases on the on-premises instance.
    Warning
    • To ensure data consistency, we recommend that you do not write data 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 database name_backup type_backup time. bak format. Example: Testdb_FULL_20180518153544.bak.
    1. Download the backup script file.
    2. Double-click the backup script file and open it by using the Microsoft SQL Server Management Studio (SSMS) client.
    3. Modify the following parameters.
      Parameter Description
      @backup_databases_list The databases that you want to back up. Separate them with semicolons (;) or commas (,).
      @backup_type The type of the backup. Valid values:
      • FULL: full backup
      • DIFF: incremental backup
      • LOG: log backup
      Notice In this example, the value must be FULL.
      @backup_folder The directory that stores the backup files on the RDS instance. If you do not specify this parameter, a directory will be automatically created.
      @is_run Specifies whether to perform a backup. Valid values:
      • 1: specifies to perform a backup.
      • 0: specifies only to perform a check.

      Example:

      Configuration example by using the Python backup script
    4. Execute the backup script to back up the specified databases and store the backup files to the specified directory.
      Execution results of the backup script
  7. Use one of the following three methods to upload the backup files to the OSS bucket that you created.
    Warning Make 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 allows you to avoid task failures that may occur if the backup files cannot be downloaded.
    Method Description
    Use ossbrowser to upload backup files Use ossbrowser to upload the backup files to OSS. For more information, see Quick start. This is the recommended method.
    Notice If the on-premises instance is deployed in an Elastic Compute Service (ECS) instance that resides in a Virtual Private Cloud (VPC), you can use the OSS endpoint for access over a VPC to upload the backup files. This improves upload efficiency.
    Use the OSS console to upload backup files If the backup files are smaller than 5 GB in size, upload them by using the OSS console. For more information, see Upload an object.
    Call OSS API operations to upload backup files If you want to upload the backup files unattended, use the OSS API to perform a resumable upload. For more information, see Multipart upload and resumable upload.

Procedure

  1. Download the migration script package RDSSQLCreateMigrateTasksBatchly.py.
  2. Decompress the migration script package and run the following command to view the parameters that you need to specify:
    python ~/Downloads/RDSSQLCreateMigrateTasksBatchly.py -h

    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>
    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 (/).
  3. Execute the migration script to complete the migration task.

    Example:

    Migrate all of 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
  4. View the progress of the migration task.
    1. Log on to the ApsaraDB for RDS console.
    2. In the top navigation bar, select the region where y RDS instance resides.
      Select a region
    3. Find your RDS instance and click its ID.
    4. Perform the following steps based on the SQL Server version of your RDS instance:
      • SQL Server 2008 R2

        In the left-side navigation pane, click Database Migration to Cloud. You can view all the submitted migration tasks.

        Note You can also click Refresh in the upper-right corner of the page to view the latest statuses of migration tasks.
        View the progress of migration tasks
      • SQL Server 2012 and later

        In the left-side navigation pane, click Backup and Restoration. Then, click the Backup Data Upload History tab.

        Note To view the migration records of the last seven days. You can specify the time range to view the migration tasks over a specific period of time.

Common errors

Error Cause Solution
HTTP Status: 404 Error:InvalidAccessKeyId.NotFound Specified access key is not found. RequestID: XXXXXXXXXXXXXXXXX The AccessKey ID used to call the API is incorrect. Use the correct AccessKey ID and AccessKey secret. For more information, see FAQ about AccessKey pairs.
HTTP Status: 400 Error:IncompleteSignature The request signature does not conform to Aliyun standards. server string to sign is:...... The AccessKey secret used to call the API is incorrect.
RDS engine doesn't support, this is only for RDS SQL Server engine. The RDS instance does not run SQL Server. Use an RDS instance that runs SQL Server as the RDS instance.
Couldn't find specify RDS [XXX]. The ID of the RDS instance does not exist. Check whether the ID of the RDS instance is correct. If the ID of the RDS instance is incorrect, enter the correct instance ID.
{'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',))"} The OSS endpoint is incorrect. Check whether the entered OSS endpoint is correct. If the entered OSS endpoint is incorrect, enter the correct OSS endpoint. For more information, see Bucket overview.
{'status': 404,'-id': 'xxxxxxxxx', 'details': {'HostId': 'xxxxxxxxx', 'Message': 'The specified bucket does not exist.', 'Code': 'NoSuchBucket', 'RequestId': 'xxxxxxxx', 'BucketName': 'aaaatp-test-on-ecs'}} The OSS bucket does not exist. Check whether the entered OSS bucket name is correct. If the entered OSS bucket name is incorrect, enter the correct OSS bucket name.
There is no backup file on OSS Bucket [xxxxxx] under [xxxxxxxxx] folder, check please. 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 conditions, create the folder in the OSS bucket and import backup files that meet the conditions.
Warning!!!!!, [autotest_2005_ent_broken_full_dbcc_failed.bak] is not backup file, filtered. The names of the backup files do not meet the naming conventions. If you do not use the backup script to perform a backup, the names of the backup files must follow the database name_backup type_backup time. bak format, for example, Testdb_FULL_20180518153544.bak.
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'}} The RAM user does not have the required permissions. Grant the RAM user the AliyunOSSFullAccess and AliyunRDSFullAccess permissions. For more information about how to authorize the RAM user, see Authorize a RAM user.
OPENAPI Response Error !!!!! : HTTP Status: <Http Status Code> Error:<Error> <Description>. RequestID: 32BB6886-775E-4BB7-A054-635664**** An error occurs when the API is called. Analyze the specific error cause based on the error information described in Table 2.
Table 2. Error codes
HTTP status code Error code Error message Description
403 InvalidDBName The specified database name is not allowed. The error message returned because the specified database names are invalid. For example, the name of a database is invalid if it is the same as the name of a system database.
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 do not exist.
  • SQL Server 2008 R2: Create databases on the RDS instance before the data migration. Make sure that each database whose data you want to migrate from the on-premises instance has a counterpart with an identical name on the RDS instance.
  • SQL Server 2012 and later: Make sure that each database whose data you want to migrate from the on-premises instance does not have a counterpart with an identical name on the RDS instance.
400 IncorrectDBType Current DB type does not support this operation. The error message returned because the operation is not supported by the database engine of 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 uploads on the day exceeds 20 for a single database.
400 ConcurrentTaskExceeded Concurrent task exceeding the allowed amount. The error message returned because the number of data uploads on the day exceeds 500 for a single database.
400 IncorrectFileExtension The file extension does not support. The error message returned because the extensions of the backup file are invalid.
400 InvalidOssUrl Specified oss url is not valid. The error message returned because the specified OSS download URL is invalid.
400 BakFileSizeExceeded Exceeding the allowed bak file size. The error message returned because the 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 space of the RDS instance.

Related operations

Operation Description
CreateMigrateTask Creates a migration task that is used to migrate backup files from an OSS bucket to an ApsaraDB for RDS instance.
CreateOnlineDatabaseTask Opens a database to which a migration task needs to migrate data on an ApsaraDB for RDS instance.
DescribeMigrateTasks Queries the migration tasks of an ApsaraDB for RDS instance.
DescribeOssDownloads Queries the backup files that a migration task needs to migrate to an ApsaraDB for RDS instance.