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

Prerequisites

  • A destination database that has the same name as the self-managed database 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 2008 R2.
  • If you use a RAM user, make sure that the following requirements are met:
    • The AliyunOSSFullAccess and AliyunRDSFullAccess policies are attached to the RAM user. 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 by using your Alibaba Cloud account and is attached to the RAM user. 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.

Billing

If you use the method described in this topic to migrate data, you are charged only for the use of OSS buckets.

Billing rules
ScenarioFee
Upload backup files to an OSS bucketFree of charge.
Store backup files in an OSS bucketYou are charged storage fees. For more information, visit the Pricing page of OSS.
Migrate backup files from an OSS bucket to your RDS instance
  • If you migrate backup files from an OSS bucket to your RDS instance over an internal network, no fees are generated.
  • If you migrate backup files over the Internet, fees are generated for outbound traffic over the Internet on OSS. For more information, visit the Pricing page of OSS.

Procedure

  1. Back up the self-managed database.
    1. Start the Microsoft SQL Server Management Studio (SSMS) client.
    2. Log on to the source database.
    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
      Important When the recovery model is set to FULL, more logs are generated. Make sure that 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 [dbtest] to disk ='d:\backup\backup.bak' WITH COMPRESSION,INIT;
      go
    6. Execute the following statements to check the integrity of the backup file:
      USE master
       GO
       RESTORE FILELISTONLY 
         FROM DISK = N'D:\backup\backup.bak';
      Important
      • If a result set is returned, the backup file is valid.
      • If an error is returned, the backup file is invalid. In this case, you must back up the source database again.
    7. Optional:Execute the following statement to reset the recovery model:
      Important 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 self-managed database to an OSS bucket.
    • For more information about how to upload a file that is smaller than 5 GB in size, see Upload an object.
    • For more information about how to upload multiple files or a file that is larger than 5 GB in size, see Multipart upload and resumable upload. For more information about how to use the graphical management tool ossbrowser, see ossbrowser.
    Important Your RDS instance and OSS bucket can communicate over an internal network only when they reside in the same region. Therefore, you must upload the backup file to the OSS bucket that resides in the same region as your RDS instance.
  3. Set the validity period and obtain the URL of the backup file.
    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. In the left-side navigation pane, click Files.
    5. Select the backup file.
    6. In the panel that appears, change the value of the Validity Period (Seconds) parameter to 28800 (8 hours).
      Important The URL of the backup file is required when you migrate the file from the OSS bucket to your RDS instance. The data migration fails 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 an 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.

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

Related operations

OperationDescription
Create a migration taskCreates a migration task
Open the database to which backup data is migratedOpens a database.
Query backup data migration tasksQueries the migration tasks.
Query the backup file details of a backup data migration taskQueries the details about the backup files that are uploaded to an OSS bucket.