This topic describes how to migrate the incremental backup data of a self-managed SQL Server database to an ApsaraDB RDS for SQL Server instance. The incremental backup data of the self-managed database is stored in an Object Storage Service (OSS) bucket. You can upload the incremental backup data as a file from the OSS bucket to the RDS instance. This migration solution reduces downtime to minutes.

Prerequisites

  • Your RDS instance runs one of the following SQL Server versions and RDS editions:
    • SQL Server 2017 EE or 2019 EE on RDS Cluster Edition
    • SQL Server 2012 SE, 2012 EE, 2016 SE, 2016 EE, 2017 SE, or 2019 SE on RDS High-availability Edition
    • SQL Server 2012 EE Basic, 2012 Web, or 2016 Web on RDS Basic Edition
  • The OSS bucket that stores the incremental backup data of the self-managed database resides in the same region as your RDS instance. For more information about how to create an OSS bucket, see Create buckets.
  • The self-managed database uses the FULL recovery model.
    Note If the self-managed database uses the SIMPLE recovery model, transaction logs cannot be backed up. In this case, you can use only a differential backup file. However, the differential backup file may be large, which increases the time that is required to complete the migration.
  • The remaining storage space of your RDS instance is sufficient. If the remaining storage space is insufficient, you must expand the storage capacity of your RDS instance before you start the migration.
  • No existing databases on your RDS instance have the same name as the self-managed database.
  • A privileged account is created on your 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 DBCC CHECKDB statement is executed. The execution result indicates that no allocation or consistency errors occur.
    Note If no allocation 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.

Background information

The migration solution in this topic is suitable for the following scenarios:
  • Migrate data to your RDS instance in physical mode rather than in logical mode.
    Note
    • Physical migration allows you to migrate data by using a physical backup file. Logical migration allows you to write the executed data manipulation language (DML) statements to your RDS instance.
    • Physical migration ensures 100% data consistency between the self-managed database and the destination database on your RDS instance. Logical migration cannot ensure 100% data consistency. For example, index fragmentation and statistical information may change after the migration.
  • Migrate data with minute-level downtime.
    Note We recommend that you migrate the data of the self-managed database to your RDS instance by using a full backup file. This applies if your application is insensitive to downtime (for example, your application can tolerate a 2-hour interruption) and the self-managed database has less than 100 GB of data. For more information, see Migrate the full backup data of a self-managed SQL Server database to an ApsaraDB RDS instance (SQL Server 2012, 2016, 2017, and 2019).

In this topic, the migration is performed by using a full backup file and a log or differential backup file. These files are stored in the OSS bucket.

Precautions

  • The migration solution in this topic is at the database level. It allows you to migrate the incremental backup data of a single self-managed database at a time. If you want to migrate the incremental backup data of multiple or all self-managed databases at a time, we recommend that you use the instance-level migration solution. 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, the self-managed database runs SQL Server 2016 and your RDS instance runs SQL Server 2012. In this case, you cannot migrate the incremental backup data of the self-managed database to your RDS instance.
  • The names of the backup files cannot contain special characters, such as at signs (@) and vertical bars (|). If the names of the backup files contain special characters, the migration fails.
  • After you authorize the service account of your RDS instance to access the OSS bucket, a role named AliyunRDSImportRole is created in Resource Access Management (RAM). Do not modify or delete this role. If you modify or delete this role, you cannot download the backup files from the OSS bucket. In addition, if you modify or delete this role, you must re-authorize the service account of your RDS instance by using the migration wizard.
  • The migration does not carry over the accounts of the self-managed database. After the migration is complete, you must create accounts on your RDS instance by using 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 only by using 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.

Migration process

The following flowchart shows the migration process on a timeline.

Migration phase Step Description
Full backup and restoration Step 1. Before 00:00 Complete the following preparations:
  • Execute the DBCC CHECKDB statement on the self-managed database and verify that no allocation or consistency errors occur.
  • Shut down the backup system of the self-managed database.
  • Change the recovery model of the self-managed database to FULL.
Step 2. 00:01 Perform a full backup on the self-managed database. Time required: about 1 hour.
Step 3. 02:00 Upload the full backup file to the OSS bucket. Time required: about 1 hour.
Step 4. 03:00 Restore data from the full backup file to your RDS instance by using the ApsaraDB RDS console. Time required: about 19 hours.
Incremental backup and restoration Step 5. 22:00 Perform a log backup on the self-managed database. Time required: about 20 minutes.
Step 6. 22:20 Upload the log backup file to the OSS bucket. Time required: about 10 minutes.
Step 6. 22:30
  • Repeat Step 5 and Step 6 to perform a log backup on the self-managed database, upload the log backup file to the OSS bucket, and then restore data from the log backup file to your RDS instance. Perform these operations until the size of the last log backup file can be less than 500 MB.
  • Stop data writes to the self-managed database, perform the last log backup, and then upload the last log backup file to the OSS bucket.
Database opening Step 8. 22:34 Restore data from the last log backup file to your RDS instance. Time required: about 4 minutes.
Step 9. 22:35 Open the destination database on your RDS instance. If you execute the DBCC statement in asynchronous mode, the destination database can be opened in 1 minute.

The preceding migration provides an example of how to minimize downtime. Your application can continue to run, and you do not need to stop your application until the last log backup. In this example, the downtime of your application does not exceed 5 minutes.

Back up the self-managed database

  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 Specify the name of the self-managed database that you want to back up. In the other scenarios, this parameter also allows you to specify multiple databases. If you specify more than one database name, separate these database names with semicolons (;) or commas (,).
    @backup_type Specify the type of backup that you want to perform. Valid values:
    • FULL: full backup
    • DIFF: differential backup
    • LOG: log backup
    @backup_folder Specify the local directory that is used to store the backup files of the self-managed database. If the specified directory does not exist, ApsaraDB RDS automatically creates the directory.
    @is_run Specify whether to perform a backup or a check. Valid values:
    • 1: specifies to perform a backup.
    • 0: specifies to perform a check.
  3. Run the backup script.

Upload the full backup file to the OSS bucket

After the full backup and the log or differential backup are complete, you must use one of the following methods to upload the backup files to the OSS bucket:

  • Method 1: Use the ossbrowser tool

    We recommend that you use the ossbrowser tool to upload the backup files. For more information, see ossbrowser.

  • Method 2: Use the OSS console

    If the sizes of the backup files are less than 5 GB, you can upload the backup files 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 backup files in resumable mode. For more information, see Multipart upload and resumable upload.

Create a migration task

  1. Visit the RDS instance list, select a region above, and click the target instance ID.
  2. In the left-side navigation pane, click Backup and Restoration.
  3. In the upper-right corner of the page, click Migrate OSS Backup Data to RDS.
  4. In the Import Guide wizard, click Next twice.
    Note If you run the OSS-based migration wizard for the first time, you must authorize the service account of your RDS instance 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. In the Import Data step, configure the following parameters.
    Parameter Description
    Database Name Enter the name of the destination database on your RDS instance. The destination database stores 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 click the search icon to search for the full backup file by using a prefix-based fuzzy match. ApsaraDB RDS displays the name, size, and update time of the full backup file.
    Cloud Migration Method Select Immediate Access (Full Backup).
    • Immediate Access (Full Backup): If you want to migrate only a full backup file, select this migration method. 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 differential or log backup file, select this migration method. In this case, the following parameter settings take effect in the CreateMigrateTask operation: BackupMode = UPDF and IsOnlineDB = False.
  6. Click OK.

After the migration task is complete, you can click Refresh to view the latest status of the migration task.

Import the log or differential backup file

After the full backup file of the self-managed database is imported into your RDS instance, you must import the log or differential backup file.

  1. Visit the RDS instance list, select a region above, and click the target instance ID.
  2. In the left-side navigation pane, click Backup and Restoration. On the page that appears, click Backup Data Upload History.
  3. Find the destination database and click Upload Incremental Files. In the dialog box that appears, configure the parameters, select the log or differential backup file, and then click OK.
    Note
    • If you have multiple log backup files, you must use the same method to upload these log backup files one by one.
    • Make sure that the size of the last log or differential backup file does not exceed 500 MB. This minimizes the time that is required to complete the migration.
    • Before the last log backup file is generated, you must stop data writes to the self-managed database. This ensures data consistency between the self-managed database and the destination database on your RDS instance.

Open the destination database

After you import all the backup files into the destination database on your RDS instance, the destination database is in the In Recovery or Restoring state. If your RDS instance runs the High-availability Edition, the destination database is in the In Recovery state. If your RDS instance runs the Basic Edition, the destination database is in the Restoring state. In these cases, you cannot perform read or write operations on the destination database. Before you can perform read and write operations, you must open the destination database.

  1. Visit the RDS instance list, select a region above, and click the target instance ID.
  2. In the left-side navigation pane, click Backup and Restoration. On the page that appears, click Backup Data Upload History.
  3. Find the destination database and click Open Database.
  4. In the dialog box that appears, select a consistency check mode and click OK.
    Note ApsaraDB RDS provides the following consistency check modes:
    • 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 insensitive to the result of the DBCC CHECKDB statement, we recommend that you select this consistency check mode. In this case, 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. In this case, the following parameter setting takes effect in the CreateMigrateTask operation: CheckDBMode = SyncExecuteDBCheck. However, the time that is required to open the destination database increases.

View details about the imported backup files

If you want to view details about the backup files that are imported by using a migration task, perform the following operations: Open the Backup and Restoration page. Click the Backup Data Upload History tab. Find the migration task and click View File Details. In the dialog box that appears, view details about the imported backup files.

Common errors

For more information about the common errors that may occur during the migration of full backup data, see Migrate full backup data to ApsaraDB RDS for SQL Server 2012, 2014, 2016, 2017, or 2019. During the migration of incremental backup data, you may encounter the following errors:

  • The destination database cannot be opened.
    • Error message: Failed to open database xxx.
    • Cause: Some advanced features are enabled for the self-managed database. However, these advanced features are not supported by your RDS instance. For example, the self-managed database runs an Enterprise Edition of SQL Server and your RDS instance runs a Web edition of SQL Server. In this case, if the data compression and partition features are enabled for the self-managed database, this error is reported when you open the destination database.
    • Solution:
      • Disable the advanced features for the self-managed database, back up data again, and then migrate the data by using OSS.
      • Purchase an RDS instance that runs the same SQL Server edition as the self-managed database. Then, migrate the data of the self-managed database to the purchased RDS instance.
  • The log sequence numbers (LSNs) in the backup chain are not consecutive.
    • Error message: The log in this backup set begins at LSN XXX, which is too recent to apply to the database.RESTORE LOG is terminating abnormally.
    • Cause: The LSNs in the log or differential backup file are different from the LSNs in the previous backup file that is used for the restoration.
    • Solution: Select the log or differential backup file whose LSNs are the same as the LSNs of the previous backup file that is used for the restoration.
  • The DBCC CHECKDB statement cannot be executed in asynchronous mode.
    • Error message: asynchronously DBCC checkdb failed: CHECKDB found 0 allocation errors and 2 consistency errors in table 'XXX' (object ID XXX).
    • Cause: After data is restored to your RDS instance with the Asynchronous DBCC consistency check mode selected, ApsaraDB RDS executes the DBCC CHECKDB statement. If the destination database fails the consistency check, consistency errors occur in the self-managed database.
    • Solution:
      • Execute the following statement on the destination database:
        DBCC CHECKDB (DBName,REPAIR_ALLOW_DATA_LOSS)
        Note If you use this statement to fix the error, data may be lost.
      • Execute the following statement on the self-managed database to fix the error, and then perform the migration again:
        DBCC CHECKDB (DBName,REPAIR_ALLOW_DATA_LOSS)
  • The selected backup file is a full backup file.
    • Error message: Backup set (xxx) is a Database FULL backup, we only accept transaction log or differential backup.
    • Cause: After the full backup file is restored to your RDS instance, you can select and restore only a log or differential backup file. If you select a full backup file again, this error is reported.
    • Solution: Select and restore a log or differential backup file.
  • The number of specified self-managed databases exceeds the upper limit.
    • Error message: The database (xxx) migration failed due to databases count limitation.
    • Cause: If the number of specified self-managed databases exceeds the upper limit, this error is reported.
    • Solution: Migrate the data of excess self-managed databases to another RDS instance. Otherwise, delete unnecessary databases from the current RDS instance.

Related operations

Operation Description
Create a migration task Creates a migration task that is used to restore backup files from an OSS bucket to an ApsaraDB RDS instance.
Open the database to which backup data is migrated Opens the destination database on an ApsaraDB RDS instance.
Query backup data migration tasks Queries the migration tasks of an ApsaraDB RDS instance.
Query backup data files of migration task Queries details about the backup files that are migrated by using a migration task to an ApsaraDB RDS instance.