ApsaraDB RDS for SQL Server 2008 R2 instances allow you to easily migrate data to the cloud. You only need to use the backup feature of Microsoft to back up full data on your own database. Then, upload the backup files to Alibaba Cloud Object Storage Service (OSS) to fully migrate the data to the specified database of ApsaraDB for RDS by using the ApsaraDB for RDS console. The backup feature of Microsoft is fully compatible with ApsaraDB RDS for SQL Server.

Prerequisites

A destination database with the same name as the on-premises database is created in the RDS instance. For more information, see Create databases and accounts for an ApsaraDB for RDS instance in SQL Server 2008 R2.

Precautions

You can migrate only one database to ApsaraDB RDS for SQL Server 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.

Billing

When you migrate data to Alibaba Cloud, no additional fees are incurred on ApsaraDB for RDS, but certain fees are incurred on OSS.

Billing
Scenario Billing
Upload backup files to OSS No fees are incurred when you upload backup files to OSS.
Store backup files in OSS Fees are incurred when you store backup files in OSS. For more information, see Pricing.
Migrate backup files from OSS to ApsaraDB for RDS
  • If you migrate backup files from OSS to ApsaraDB for RDS over the internal network, no fees are incurred.
  • If you migrate backup files over the Internet, fees for outbound traffic over the Internet are incurred on OSS. For more information, see Pricing.

Procedure

  1. Back up the on-premises database. Follow these steps:
    1. Start the Microsoft SQL Server Management Studio (SSMS) client.
    2. Log on to the source database again.
    3. Execute the following statements to check the recovery model:
      use master;
      go
      select name, case recovery_model
      when 1 then 'FULL'
      when 2 then 'BULD_LOGGED'
      when 3 then 'SIMPLE' end model from sys.databases
      where name not in ('master','tempdb','model','msdb');
      go
      • If the value of model is not FULL, perform Step iv.
      • If the value of model is FULL, perform Step v.
    4. Execute the following statements to set the recovery model to FULL:
      ALTER DATABASE [dbname] SET RECOVERY FULL;
      go
      ALTER DATABASE [dbname] SET AUTO_CLOSE OFF;
      go
      Notice When the recovery model is set to FULL, more logs are generated. Make sure you have sufficient disk space.
    5. Execute the following statements to back up the source database. In this example, the dbtest database is backed up to the backup.bak file.
      use master;
      go
      BACKUP DATABASE [testdbdb] to disk ='d:\backup\filename.bak' WITH COMPRESSION,INIT;
      go
    6. Execute the following statement to check the integrity of the backup file:
      USE master
       GO
       RESTORE FILELISTONLY 
         FROM DISK = N'D:\Backup\filename.bak';
      Notice
      • If a result set is returned, the backup file is valid.
      • If an error is returned, the backup file is invalid. Back up the source database again.
    7. Optional:Execute the following statement to reset the recovery model:
      Notice If the recovery model of your database is FULL, skip this step.
      ALTER DATABASE [dbname] SET RECOVERY SIMPLE;
      go
  2. Upload the backup file of your on-premises database to OSS.
    • For more information about how to upload a file that is smaller than 5 GB, see Upload an object.
    • For more information about how to upload multiple files or a file that is larger than 5 GB, see Multipart upload and resumable upload. For more information about how to use the graphical management tool ossbrowser, see Quick start.
    Notice The RDS instance and OSS bucket can only communicate over the internal network when they reside in the same region. Therefore, you must upload the backup file to the OSS bucket that is in the same region as your RDS instance.
  3. Set the validity period and obtain the URL of the backup file. Follow these steps:
    1. Log on to the OSS console.
    2. In the left-side navigation pane, click Buckets.
    3. Find the bucket to which you have uploaded the backup file and click its name.
    4. On the page that appears, click the Files tab.
    5. Select the backup file.
    6. In the View Details page, modify Validity Period. We recommend that you specify the value to 28800 seconds (8 hours).
      Notice The URL of the backup file is required when you restore the file from OSS to ApsaraDB for RDS. The data migration will fail when the validity period of the URL expires.
    7. Click Copy File URL.
      Copy the URL of an object
    8. Modify the backup file URL that you obtain.

      By default, the URL contains the public endpoint of the file. If you migrate data over the internal network, you must replace the public endpoint with the internal endpoint in the URL. For example, the URL of the backup file is http://rdstest.oss-cn-shanghai.aliyuncs.com/testmigraterds_20170906143807_FULL.bak?Expires=15141****&OSSAccessKeyId=TMP****. You must replace the public endpoint oss-cn-shanghai.aliyuncs.com with the internal endpoint oss-cn-shanghai-internal.aliyuncs.com.

      Notice The internal endpoint varies according to the network type and region. For more information, see Regions and endpoints.
  4. Restore data to ApsaraDB for RDS by using the backup file in OSS. 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 Databases.
    5. Find the destination database and click Migrate Backup Files from OSS in the Actions column.
    6. In the Import Guide wizard, read the on-screen instructions and click Next. The Upload the backup files to OSS page appears.
    7. Read the on-screen instructions and click Next. The Import data page appears.
    8. Enter the URL of the backup file in the OSS URL of the Backup File field.
      Note ApsaraDB RDS for SQL Server 2008 R2 supports only one-time migration of full backup files.
    9. Click OK.
      In the left-side navigation pane, click Database Migration to Cloud. The migration task list appears. Find the target migration task.
      Notice If Task Status is not Success, you can click View File Details in the Actions column to view the cause. Fix the problem and repeat the preceding restoration procedure again.

Related operations

Operation Description
CreateMigrateTask Restores the backup files from OSS 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.