This topic describes how to migrate the full backup data of a self-managed SQL Server database from an Object Storage Service (OSS) bucket to an ApsaraDB RDS for SQL Server instance.

Prerequisites

  • The RDS instance runs one of the following SQL Server versions and RDS editions:
    • SQL Server 2017 EE or SQL Server 2019 EE on RDS Cluster Edition
    • SQL Server 2012 SE, SQL Server 2012 EE, SQL Server 2016 SE, SQL Server 2016 EE, SQL Server 2017 SE, or SQL Server 2019 SE on RDS High-availability Edition
    • SQL Server 2012 EE Basic, SQL Server 2012 Web, or SQL Server 2016 Web on RDS Basic Edition
    Note For more information about how to migrate the full backup data of a self-managed instance to an RDS instance that runs SQL Server 2008 R2 on RDS High-availability Edition, see Migrate the full backup data of a self-managed SQL Server database to an ApsaraDB RDS instance that runs SQL Server 2008 R2.
  • The available storage of the RDS instance is sufficient. If the available storage is insufficient, you must expand the storage capacity of the RDS instance before you start the migration.
  • The names of existing databases on the RDS instance are different from the name of the self-managed database.
  • A privileged account is created for the RDS instance. For more information, see Create an account and a database for an ApsaraDB RDS instance that runs SQL Server 2012, 2016, 2017 SE, or 2019 SE.
  • The OSS bucket that stores the full backup data of the self-managed database resides in the same region as the RDS instance. For more information about how to create an OSS Bucket, see Create buckets.
  • The DBCC CHECKDB statement is executed, and the return result indicates that no allocation errors or consistency errors occur.
    Note If no allocation errors or consistency errors occur, the following execution result is returned:
    ...
    CHECKDB found 0 allocation errors and 0 consistency errors in database 'xxx'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  • If you use the credentials of a Resource Access Management (RAM) user, make sure that the following requirements are met:
    • The RAM user has the AliyunOSSFullAccess and AliyunRDSFullAccess permissions. 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.
    • The service account of ApsaraDB RDS is authorized by using your Alibaba Cloud account to access the OSS bucket.
    • A custom policy is manually created in RAM and attached to the RAM user by using your Alibaba Cloud account. For more information about how to create a custom policy, see Create a custom policy on the JSON tab.
      You must use the following content for the custom policy:
      {
          "Version": "1",
          "Statement": [
              {
                  "Action": [
                      "ram:GetRole"
                  ],
                  "Resource": "acs:ram:*:*:role/AliyunRDSImportRole",
                  "Effect": "Allow"
              }
          ]
      }

Usage notes

  • The migration method that is described in this topic is at the database level. You can migrate the full backup data only of a single self-managed database to your RDS instance at a time. If you want to migrate the full backup data of multiple or all databases in a self-managed instance at a time, we recommend that you use an instance-level migration method. For more information, see Migrate data from a self-managed SQL Server instance to an ApsaraDB RDS for SQL Server instance.
  • The migration from a later SQL Server version to an earlier SQL Server version is not supported. For example, if the self-managed database runs SQL Server 2016 and the RDS instance runs SQL Server 2012, you cannot migrate the full backup data of the self-managed database to the RDS instance.
  • Differential backup files and log backup files are not supported.
  • The name of the full backup file that is used for the migration cannot contain special characters, such as at signs (@) and vertical bars (|). If the name contains special characters, the migration fails.
  • After you authorize the service account of ApsaraDB RDS to access the OSS bucket, a role named AliyunRDSImportRole is created in RAM. Do not modify or delete this role. If you modify or delete this role, the backup files cannot be downloaded from the OSS bucket. If you modify or delete this role, you must re-authorize the service account by using the migration wizard.
  • The RDS instance does not carry over the accounts of the self-managed database. After the migration is complete, you must create accounts for the RDS instance in the ApsaraDB RDS console.
  • Before the migration is complete, do not delete the backup files from the OSS bucket. If you delete the backup files before the migration is complete, the migration fails.
  • The names of the backup files can be suffixed by bak, diff, trn, or log. If the backup files are not generated by using the backup script that is provided in this topic, you must add one of the following suffixes to the file names:
    • bak: indicates a full backup file.
    • diff: indicates a differential backup file.
    • trn or log: indicates a transaction log backup file.
    Note By default, the full backup files of the RDS instance are in the ZIP format. If you download a ZIP file, you must decompress the ZIP file to obtain a full backup file whose name is suffixed by bak. Then, you can use the full backup file to migrate the data to your RDS instance.

Back up the self-managed database

Note Before you perform a full backup, you must stop all data writes to the self-managed database. The data that is written to the self-managed database during the full backup process cannot be backed up.
  1. Download the backup script file. Then, open the file by using SQL Server Management Studio (SSMS).
  2. 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: differential backup
    • LOG: log backup
    @backup_folder The directory that is used to store the backup files of 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.
  3. Execute the backup script.

Upload the generated full backup file to the OSS bucket

After the full backup on the self-managed database is complete, you must use one of the following methods to upload the generated full backup file to the OSS bucket:

  • Method 1: Use the ossbrowser tool

    We recommend that you use the ossbrowser tool to upload the generated full backup file to the OSS bucket. For more information, see Use ossbrowser.

  • Method 2: Use the OSS console

    If the size of the generated full backup file is less than 5 GB, you can upload the full backup file by using the OSS console. For more information, see Upload objects.

  • Method 3: Use the OSS API

    You can call an OSS API operation to upload the generated full backup file by using the resumable upload method. For more information, see Multipart upload and resumable upload.

Create a migration task

  1. Access RDS Instances, select a region at the top, and then click the ID of the target RDS instance.
  2. In the left-side navigation pane, click Backup and Restoration.
  3. On the page that appears, click Migrate OSS Backup Data to RDS.
  4. In the Import Guide wizard, click Next twice.
    Note If you use the OSS-based migration wizard for the first time, you must authorize the service account of ApsaraDB RDS to access the OSS bucket. In this case, you must click Authorize and complete the authorization. Otherwise, the OSS Bucket drop-down list in the Import Data step is empty.
  5. Configure the following parameters.
    Parameter Description
    Database Name Enter the name of the destination database on the RDS instance. The destination database is used to store the data that is migrated from the self-managed database. The name of the destination database must be different from the name of the self-managed database.
    Note The name of the destination database must meet the requirements of open source SQL Server.
    OSS Bucket Select the OSS bucket that stores the full backup file.
    OSS Subfolder Name Enter the name of the OSS subfolder that stores the full backup file.
    OSS File Specify the full backup file that you want to import. You can enter a prefix in the search box and click the search icon to search for the full backup file by using fuzzy match. The name, size, and update time of each full backup file whose name contains the prefix are displayed. Select the full backup file that you want to migrate to the RDS instance.
    Cloud Migration Method
    • Immediate Access (Full Backup): If you want to migrate only a full backup file, select this migration method. For this example, select Immediate Access (Full Backup). In this case, the following parameter settings take effect in the CreateMigrateTask operation: BackupMode = FULL and IsOnlineDB = True.
    • Access Pending (Incremental Backup): If you want to migrate a full backup file and a log or differential backup file, select this migration method. In this case, the following parameter setting takes effect in the CreateMigrateTask operation: BackupMode = UPDF and IsOnlineDB = False.
    Consistency Check Mode
    • Asynchronous DBCC: The DBCC CHECKDB statement is executed after the destination database is opened. This reduces the time that is required to open the destination database and minimizes the downtime of your application. If the destination database is large, a long period of time is required to execute the DBCC CHECKDB statement. Therefore, if your application is sensitive to downtime but is not sensitive to the result of the DBCC CHECKDB statement, we recommend that you select this consistency check mode. In this mode, the following parameter setting takes effect in the CreateMigrateTask operation: CheckDBMode = AsyncExecuteDBCheck.
    • Synchronous DBCC: The DBCC CHECKDB statement is executed at the same time when the destination database is opened. If you want to identify consistency errors between the self-managed database and the destination database based on the result of the DBCC CHECKDB statement, we recommend that you select this consistency check mode. However, the time that is required to open the destination database increases. In this mode, the following parameter setting takes effect in the CreateMigrateTask operation: CheckDBMode = SyncExecuteDBCheck.
  6. Click OK.

Wait until the migration task is complete. You can click Refresh to view the latest status of the migration task. If the migration task fails, you can troubleshoot the failure based on the description of the migration task. For more information, see Common errors.

View the migration task

If you want to view details about the migration task, go to the Backup and Restoration page and click the Backup Data Upload History tab. By default, this tab displays the migration tasks over the last week.

Common errors

Each migration task record on the Backup Data Upload History tab of the Backup and Restoration page contains a task description. If the migration task fails or an error is reported, you can troubleshoot the failure or error based on the task description. The following common errors may occur:

  • An existing database on the RDS instance has the same name as the self-managed database.
    • Error message: The database (xxx) is already exist on RDS, please backup and drop it, then try again.
    • Cause: If an existing database on the RDS instance has the same name as the self-managed database, the migration is not supported. This mechanism is designed to ensure the security of your data.
    • Solution: If you want to overwrite an existing database on the RDS instance, back up the database, delete the database from the RDS instance, and then create and run a migration task again.
  • A differential backup file is used.
    • Error message: Backup set (xxx.bak) is a Database Differential backup, we only accept a FULL Backup.
    • Cause: The file that you upload is a differential backup file rather than a full backup file. The migration method in this topic supports only full backup files.
  • A log backup file is used.
    • Error message: Backup set (xxx.trn) is a Transaction Log backup, we only accept a FULL Backup.
    • Cause: The file that you upload is a log backup file rather than a full backup file. The migration method in this topic supports only full backup files.
  • The full backup file fails the verification.
    • Error message: Failed to verify xxx.bak, backup file was corrupted or newer edition than RDS.
    • Cause: The full backup file is corrupted, or the self-managed database runs a later SQL Server version than the RDS instance. For example, this error occurs if the self-managed database runs SQL Server 2016 and the RDS instance runs SQL Server 2012.
    • Solution: If the full backup file is corrupted, perform a full backup on the self-managed database again. Then, create and run a migration task again. If the self-managed database runs a later SQL Server version than the RDS instance, select a different RDS instance that runs the same version as or a later version than the self-managed database.
  • The DBCC CHECKDB statement fails.
    • Error message: DBCC checkdb failed.
    • Cause: The self-managed database encounters allocation or consistency errors.
    • Solution: Execute the following statement on the self-managed database to fix the error. Then, create and run a migration task again.
      Note If you execute the following statement, your data may be lost.
      DBCC CHECKDB (DBName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS
  • The available storage of the RDS instance is insufficient.
    • Error message: Not Enough Disk Space for restoring, space left (xxx MB) < needed (xxx MB).
    • Cause: The available storage of the RDS instance is less than the minimum storage that is required to restore data by using the full backup file.
    • Solution: Expand the storage capacity of the RDS instance.
  • The available storage of the RDS instance is insufficient.
    • Error message: Not Enough Disk Space, space left xxx MB < bak file xxx MB.
    • Cause: The available storage of the RDS instance is less than the size of the full backup file.
    • Solution: Expand the storage capacity of the RDS instance.
  • No privileged account is created on the RDS instance.
    • Error message: Your RDS doesn't have any init account yet, please create one and grant permissions on RDS console to this migrated database (XXX).
    • Cause: No privileged account is created on the RDS instance. As a result, the system cannot determine which account needs to be authorized during the migration task. However, the data has been restored from the full backup file to the RDS instance, and the migration task is successful.
    • Solution: Create a privileged account on the RDS instance. For more information, see Create an account and a database for an ApsaraDB RDS instance that runs SQL Server 2012, 2016, 2017 SE, or 2019 SE.
  • The RAM user does not have the required permissions.

    The parameters that are described in Step 5 of Create a migration task are correctly configured, but the OK button is dimmed. Why?

    The reason may be that you are using a RAM user and the RAM user does not have the required permissions. Make sure that the required permissions have been granted based on the "Prerequisites" section of this topic.

Related operations

Operation Description
Create a migration task Creates a migration task
Open the database to which backup data is migrated Opens a database.
Query backup data migration tasks Queries the migration tasks.
Query the backup file details of a backup data migration task Queries the details about the backup files that are uploaded to an OSS bucket.