Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Best Practices for Migrating SQL Server 2017 Databases to the Cloud Using Incremental Backups

Best Practices for Migrating SQL Server 2017 Databases to the Cloud Using Incremental Backups

This article uses SQL Server 2017 as an example to illustrate the configuration steps for the entire migration process.

By Yuanyi

The incremental backup migration method supports various versions of SQL Server. This article uses SQL Server 2017 as an example to illustrate the configuration steps for the entire migration process. For detailed feature descriptions, please refer to the official documentation.

Applicable Scenarios

The migration method in this topic is suitable for the following scenarios:

• Migrate data to the destination database on your RDS instance in physical mode rather than in logical mode.

• Situations where downtime is critical and migrate data with minute-level downtime.

Note:

The incremental backup migration method supports various versions of SQL Server. This article uses SQL Server 2017 as an example to illustrate the configuration steps for the entire migration process. For detailed feature descriptions, please refer to the official documentation.

This solution can only migrate one database at a time to the cloud, so it is only applicable when only a single database within an instance needs to be migrated to the cloud. If all databases within an instance need to be migrated to the cloud at once, we recommend using the instance-level migration solution:

(1) Official documentation

(2) For detailed configuration, you can refer to the best practices starting from line 202.

Prerequisites

• The RDS instance has sufficient storage space.

• A high-privilege account is created for the RDS instance.

• The recovery mode of the local SQL Server database must be set to FULL mode.

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'model';
GO

1

• Execute the DBCC CHECKDB statement in the local database environment to ensure there are no allocation errors or consistency errors in the database. If no allocation errors or consistency errors occur, the following execution result is returned:

2

• The OSS service is activated.

Notes

  • The migration method described in this topic is at the database level, meaning only a single database can be migrated at a time.

  • The names of the backup files must be suffixed with bak, diff, trn, or log. The following list describes the suffixes:

    • bak: indicates full backup files.
    • diff: indicates differential backup files.
    • trn or log: indicates log backup files of transactions.

Step 1: Back up the Local Database

Use SSMS to back up the database.

3
4
5
6

Note: Use a script to back up.

Script: https://help-static-aliyun-doc.aliyuncs.com/file-manage-files/zh-CN/20220815/dcwq/1660544793749RDSBackupSpecifiedDatabasesToLocal.sql.

7

Step 2: Upload the Backup File to the OSS Bucket

  1. Create a Bucket.
  2. Log on to the OSS console.
  3. In the left-side navigation pane, click Buckets. On the page that appears, click Create Bucket.

The region where the Bucket resides must be consistent with the region where the ECS instance and RDS instance reside if you upload data to the Bucket from an ECS instance over an internal network and then restore the data to the RDS instance over the internal network.

8

Step 3: Create a Data Migration Task

1.  Access the RDS Instances page, select the region in which the RDS instance resides in the top navigation bar, and then click the target instance ID.

2.  In the left-side navigation pane, click Backup and Restoration.

3.  Click Migrate OSS Backup Data to RDS at the top of the page.

9

4.  On the Data Import Wizard page, click Next twice to enter the data import step.

5.  Configure the following parameters and click OK.

Configuration Item Description
Cloud Migration Plan Select Access Pending.

Immediate Access (only one full backup file): Full backup data migration, suitable for scenarios where only one full backup file is to be migrated. At this time, the following parameter settings take effect in the CreateMigrateTask operation: BackupMode = FULL and IsOnlineDB = True.
Access Pending (there are also differential backups or log files): Incremental data migration, suitable for scenarios where full backup files plus log backups (or differential backup files) are to be migrated. At this time, the following parameter settings take effect in the CreateMigrateTask operation: BackupMode = UPDF and IsOnlineDB = False.

Please wait until the migration task is complete. You can click Refresh to view the latest status of the migration task.

10

Step 4: Import Differential or Log Backup Files

After the full backup of the local database on the SQL Server instance is imported into the destination database on your RDS instance, you need to import differential or log backup files next.

  1. Access the RDS Instances page, select the region in which the RDS instance resides in the top navigation bar, and then click the target instance ID.
  2. In the left-side navigation pane, click Backup and Restoration. On the page that appears, click the Backup Data Upload History tab.
  3. In the task list, find the record for the backup file to be imported, click Upload Incremental Files on the right, select the incremental file, and then click OK.

Note: If you have multiple log backup files, you must use the same method to upload these files one by one.

Incremental data export:

11
12
13

Upload differential files to OSS for incremental migration to the cloud.

14

15

Step 5: Open the Database

After importing all the files, the destination database on your RDS instance is in the In Recovery or Restoring state. If your RDS instance runs RDS High-availability Edition, the destination database is in the In Recovery state. If your RDS instance runs RDS 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. In the task list, find the record for the backup file to be imported, and click Open Database on the right.
  2. Select how to open the database and click OK.

16

Step 6: View Details of the Backup Files of the Migration Task

You can log on to the DMS console to view the data in the imported database:

17

View the data in the source database:

18

If there are multiple incremental files, add them multiple times. Through testing the incremental backup operations, users need to pause their applications for a very brief period, only stopping writes before the final LOG Backup.

Database Developers Best practices SQL Server Cloud migration Data Migration database migration Tutorials SQL Server 2017
0 1 0
Share on

Read previous post:

Introduction to the Intelligent Elasticity Model of AnalyticDB

Read next post:

Best Practices for Importing Vector and Raster Data to GanosBase

ApsaraDB

454 posts | 98 followers

You may also like

Comments

ApsaraDB

454 posts | 98 followers

Related Products

More Posts by ApsaraDB

See All