You can migrate incremental backup data to ApsaraDB RDS for SQL Server 2012, 2016, 2017, or 2019. Your service will be disconnected for a few minutes during the migration process.

Prerequisites

  • Your RDS instance must run one of the following SQL Server versions and RDS editions:
    • SQL Server 2017 EE on RDS Cluster Edition
    • SQL Server 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
  • An Object Storage Service (OSS) bucket is created in the region where your RDS instance is created. For more information, see Create buckets.
  • The database uses the full recovery model.
    Note If you need to incrementally migrate your database to your RDS instance, the database must use the full recovery model. If you set the recovery model to simple, the transaction log will not be backed up. In this case, the incremental backup file is large and the migration may take more time.
  • Your RDS instance has sufficient storage space. If not, you must increase the space before migration.
  • The names of databases in your RDS instance must be unique.
  • A privileged account is created for your RDS instance. For more information, see Create databases and accounts for an ApsaraDB for RDS instance running SQL Server 2012, 2016, 2017 SE, or 2019.
  • Ensure that the system does not report allocation errors and consistency errors by executing the DBCC CHECKDB statement.
    Note If the check succeeds, the following messages are 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

You can migrate incremental backup data in the following scenarios:
  • You need to physically migrate data to your RDS instance.
    Note
    • Physical migration is the migration of backup files. Logical migration is the migration of data by executing DML statements in your RDS instance.
    • Physical migration guarantees 100% consistency between the source and destination databases. Logical migration cannot guarantee 100% consistency of database information such as index fragmentation and statistical information.
  • You need to use incremental migration for time-sensitive business to limit the disconnection time to a few minutes.
    Note If your business has a data volume of less than 100 GB and does not provide time-sensitive services, we recommend that you use full backup data migration. This process will disconnect the business for a longer period of time, such as two hours. For more information, see Migrate full backup data to ApsaraDB RDS for SQL Server 2012,2014, 2016, 2017, or 2019.

This topic describes how to use the full backup file and incremental or log files that are stored in your OSS bucket to incrementally migrate your on-premises database to a database in your RDS instance.

Precautions

  • You can migrate only one database to your RDS instance at a time. If you need to migrate multiple or all databases in an instance to ApsaraDB RDS for SQL Server, we recommend that you use the instance-based data migration feature. For more information, see Migrate data from an on-premises SQL Server instance to an ApsaraDB RDS for SQL Server instance.
  • Migrating data from a database to a database of an earlier version is not supported. For example, you cannot migrate data from SQL Server 2016 to SQL Server 2012.
  • The names of backup files cannot contain special characters such as at signs (@) or vertical bars (|). Otherwise, the on-premises database cannot be migrated to your RDS instance.
  • After you have granted the access permissions, the system creates a role named AliyunRDSImportRole in RAM. Do not modify or delete this role. Otherwise, you will not be able to download the backup files when you migrate data to your RDS instance. If you modify or delete this role, you must re-authorize your RDS account.
  • Do not delete backup files stored in OSS before the migration task is complete.
  • The names of backups files can end with only .bak, .diff, .trn, or .log. If you do not use the script in this topic to generate a backup file, you must use the following suffixes:
    • bak: indicates a full backup file.
    • diff: indicates a differential backup file.
    • trn and log: indicate a transaction log backup.

Procedure

The procedure is explained on a timeline basis.

Migration phase Step Description
Full backup and restoration Step 1: Before 00:00 Complete the following preparatory work:
  • Execute the DBCC CHECKDB statement.
  • Shut down the local backup system.
  • Change the recovery model of the on-premises database to full.
Step 2: 00:01 Perform full backup on the on-premises database.
Step 3: 02:00 Full backup is complete. Time taken: one hour. Upload the backup file to the OSS bucket.
Step 4: 03:00 The backup file is uploaded. Time taken: one hour. Restore data from the backup file to your RDS instance in the ApsaraDB for RDS console.
Step 5: 22:00 The backup file is restored. Time taken: 19 hours. Perform incremental log backup and upload the log file to the OSS bucket.
Incremental backup and restoration Step 6: 22:20 The log file is backed up and uploaded. Time taken: 20 minutes. Restore incremental data to your RDS instance by using the log file in the ApsaraDB for RDS console.
Step 7: 22:30
  • The incremental data is restored. Time taken: 10 minutes.
  • Repeat Step 6 and Step 7 to back up and upload log files and restore incremental data to the instance until the last log file is less than 500 MB.
  • Stop writing data to the on-premises database from your on-premises application. Perform incremental log backup and restoration for the last time.
Database opening Step 8: 22:34 The last log file is incrementally migrated to your RDS instance. Time taken: four minutes. Prepare to bring the database online.
Step 9: 22:35 The database comes online. If you choose to execute the DBCC statement asynchronously, the database is opened and can be used in one minute.

From the preceding procedure, you can see that you do not need to stop your application until the last log file is generated. In this case, you only need to stop your application for five minutes.

Back up the on-premises database

  1. Download the backup script and open it with SQL Server Management Studio (SSMS).
  2. 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
    @backup_folder The local directory that stores the backup file. If you do not specify this parameter, a directory will be automatically created.
    @is_run Specifies whether to perform a backup. Valid values:
    • 1: performs a backup.
    • 0: only performs a check.
  3. Execute the backup script.

Upload the backup file to OSS

After the on-premises database is backed up, you can use one of the following methods to upload the backup file to your OSS Bucket:

  • Method 1: Use ossbrowser

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

  • Method 2: Use the OSS console

    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.

  • Method 3: Call OSS API operations

    You can use the OSS API to perform a resumable upload. For more information, see Multipart upload and resumable upload.

Create the migration task

  1. Log on to the ApsaraDB for RDS console.
  2. In the top navigation bar, select the region where your RDS instance resides.
    Select a region
  3. Find your RDS instance and click its ID.
  4. In the left-side navigation pane, click Backup and Restoration.
  5. In the upper-right corner of the page, click Migrate OSS Backup Data to RDS.
  6. Click Next twice until the Import Data step appears.
    Note If you are migrating backup data from OSS to your RDS instance for the first time, ApsaraDB for RDS prompts you to authorize the OSS access permission to your Alibaba Cloud account. Click Authorize. On the Cloud Resource Access Authorization page, click Confirm Authorization Policy to complete the authorization.
  7. Configure the following parameters.
    Parameter Description
    Database Name Enter the name of the destination database in the destination instance.
    Note The name of the database must meet the requirements of SQL Server.
    OSS Bucket Select the OSS bucket to store the backup file.
    OSS Subfolder Name Enter the name of the OSS subfolder that you use to store the backup file.
    OSS File Enter the prefix of the backup file name and click the search icon. A list of files is displayed. The list contains the name, size, and update time of each file that matches the query. Select the backup file that you need to restore.
    Cloud Migration Plan
    • Immediate Access (Full Backup): You can use a full backup file to migrate your database to your RDS instance. In this operation, select Immediate Access, and the parameter BackupMode is set to FULL, the parameter IsOnlineDB is set to True in the CreateMigrateTask operation.
    • Access Pending (Incremental Backup): You can use a full backup file and an incremental or log file to incrementally migrate your database to your RDS instance. The parameter BackupMode is set to UPDF and IsOnlineDB is set to False in the CreateMigrateTask operation.
    Consistency Check Mode
    • Asynchronous DBCC: If the database contains a large volume of data, the DBCC CHECKDB statement will take a long time to execute. The DBCC CHECKDB statement is asynchronously executed after the database is opened to decrease the time spent on opening the database and minimize downtime. If your application requires a short downtime, and the result of DBCC CHECKDB does not affect your business, we recommend that you select Asynchronous DBCC. The parameter CheckDBMode is set to SyncExecuteDBCheck in the CreateMigrateTask operation.
    • Synchronous DBCC: If you need to execute the DBCC CHECKDB statement to identify consistency errors, we recommend that you select Synchronous DBCC. In this case, the database takes more time to open. The parameter CheckDBMode is set to SyncExecuteDBCheck in the CreateMigrateTask operation.
  8. Click OK.

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

Import incremental or log files

After the full backup file of the on-premises database is migrated to your RDS instance, you need to migrate data from the incremental or log files. Follow these steps:

  1. Log on to the ApsaraDB for RDS console.
  2. In the top navigation bar, select the region where your RDS instance resides.
    Select a region
  3. Find your RDS instance and click its ID.
  4. In the left-side navigation pane, click Backup and Restoration. On the page that appears, click Backup Data Upload History.
  5. Find the database that you want to import incremental data to, and click Upload Incremental Files next to the database. Configure the parameters and click OK to import the incremental or log file.
    Note
    • If you have multiple log files, you can use the same method to import the log files one by one.
    • When you upload incremental files, ensure that the last file is not larger than 500 MB to reduce the time of incremental migration.
    • Before the last log file is generated, you must stop writing data to the on-premises database to ensure the data consistency between the on-premises database and your RDS database.

Open the database

After backup files are migrated, your RDS database is in the In Recovery or Restoring state. If your RDS instance is of the High-availability Edition, it is in the In Recovery state. If your RDS instance is of the Basic Edition, it is in the Restoring state. Read and write operations cannot be performed on the database in either state. Perform the following steps to open the database:

  1. Log on to the ApsaraDB for RDS console.
  2. In the top navigation bar, select the region where your RDS instance resides.
    Select a region
  3. Find your RDS instance and click its ID.
  4. In the left-side navigation pane, click Backup and Restoration. On the page that appears, click Backup Data Upload History.
  5. Find the database that you need to restore backup files to, and click Open Database to the right of the database.
  6. In the Open Database dialog box, select a consistency check mode and click OK.
    Note There are two ways to perform a database consistency check:
    • Asynchronous DBCC: If the database contains a large volume of data, the DBCC CHECKDB statement will take a long time to execute. The DBCC CHECKDB statement is asynchronously executed after the database is opened to decrease the time spent on opening the database and minimize downtime. If your application requires a short downtime, and the result of DBCC CHECKDB does not affect your business, we recommend that you select Asynchronous DBCC. The parameter CheckDBMode is set to SyncExecuteDBCheck in the CreateMigrateTask operation.
    • Synchronous DBCC: If you need to execute the DBCC CHECKDB statement to identify consistency errors, we recommend that you select Synchronous DBCC. In this case, the database takes more time to open. The parameter CheckDBMode is set to SyncExecuteDBCheck in the CreateMigrateTask operation.

View file details of a migration task

If you need to view the details of all the backup files for a migration task, you can perform the following steps: On the Backup and Restoration page, click the Backup Data Upload History tab. Click View File Details to the right of the migration task. The View File Details message appears and displays details of all backup files related to the task.

Common errors

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

  • Failed to open the database.
    • Error message: Failed to open database xxx.
    • Cause: Some advanced features that are enabled by the on-premises SQL Server database are migrated to your RDS database. If your RDS instance does not support these features, the database fails to be opened. For example, if your on-premises database of the SQL Server Enterprise Edition enables the Data Compression or Partition feature, and you migrate the database to the database of the ApsaraDB RDS for SQL Server Web Edition, the error message is displayed.
    • Solution:
      • Disable the advanced features on the on-premises SQL Server database, back up the data again, and migrate the data by using OSS.
      • Purchase an RDS instance that runs the same SQL Server version as the on-premises SQL Server database.
  • The log sequence numbers (LSN) in the database backup chain are not in sequence.
    • 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: In SQL Server databases, incremental or log files must be restored in order of the LSN sequence. Otherwise, the error message is displayed.
    • Solution: Restore the incremental or log file that has the proper LSN. You can restore the files based on the backup time.
  • Asynchronous DBCC CHECKDB fails.
    • Error message: asynchronously DBCC checkdb failed: CHECKDB found 0 allocation errors and 2 consistency errors in table 'XXX ' (object ID XXX).
    • Cause: Consistency errors occur in your databases.
    • Solution:
      • Execute the following statement in your RDS database:
        DBCC CHECKDB (DBName,REPAIR_ALLOW_DATA_LOSS)
        Note Your data may be lost when you use this statement to fix errors.
      • Execute the following statement in the on-premise database:
        DBCC CHECKDB (DBName,REPAIR_ALLOW_DATA_LOSS)
  • The file types are unmatched.
    • Error message: Backup set (xxx) is a Database FULL backup, we only accept transaction log or differential backup.
    • Cause: After full migration is complete, you can only upload incremental or log files. If you upload a full backup file, the error message is displayed.
    • Solution: You must upload a incremental or log file.
  • The number of migrated databases reaches the limit.
    • Error message: The database (xxx) migration failed due to databases count limitation.
    • Cause: If you migrate a database after the limit is exceeded, the error message is displayed.
    • Solution: You can migrate the database to another RDS instance or delete unnecessary databases from the current RDS instance.

Related operations

Operation Description
Create migration task Restores the backup files from OSS to an ApsaraDB for RDS instance.
Open database Opens a database to which a migration task needs to migrate data on an ApsaraDB for RDS instance.
Query migration tasks Queries the migration tasks of an ApsaraDB for RDS instance.
Query backup data files of migration task Queries the backup files that a migration task needs to migrate to an ApsaraDB for RDS instance.