AWS Database Blog

Migrating an on-premises SQL Server database to Amazon RDS on VMware using SQL Server native backup and restore

Amazon Relational Database Service (Amazon RDS) on VMware delivers managed SQL Server databases in on-premises VMware environments. Managing SQL Server can be complex and time-consuming, but RDS on VMware makes it easy to provision, scale, and operate SQL Server in VMware vSphere clusters. You can migrate your existing on-premises SQL Server databases to RDS on VMware to benefit from its managed services.

You have multiple options when migrating a SQL Server database to RDS on VMware. One option is to use the SQL Server native Import and Export Wizard to copy data from the on-premises SQL Server to the destination SQL Server managed by RDS on VMware. With this option, you migrate the metadata and the data separately. Another approach is to migrate your entire on-premises database using SQL Server native backup and restore. This post describes the steps to migrate your on-premises SQL Server database to RDS on VMware using SQL Server native restore from a VMware datastore.

Solution overview

At a high level, we accomplish the migration in three simple steps:

  1. Take a full backup of your on-premises SQL Server database.
  2. Upload the database backup file (*.bak) to a VMware datastore.
  3. Restore the database backup to the target RDS for SQL Server.

The following diagram illustrates our solution architecture.

Prerequisites

Before getting started, your VMware vSphere environment should have RDS on VMware onboarded with the following requirements met:

  • VMware vSphere v6.5 or above Enterprise Plus edition.
  • An AWS account.
  • A vSphere cluster onboarded with RDS on VMware. For instructions, see Getting Started with Amazon RDS on VMware.
  • A VMware vSphere user account.
  • A SQL Server VM as the source of the migration.
  • A VM with SQL Server client tool installed. For this post, we use mssql-cli as the client tool. You can also use SQL Server Management Studio (SSMS) or any other SQL Server client tool of your choice.

Migrating your database

To migrate your on-premises SQL server database to RDS on VMware, complete the following steps:

  1. Stop the application connections to the source database, or make the database read-only.
  2. Perform a full backup of the database.
  3. Launch the vSphere client from the SQL Server VM.
  4. Upload the database backup file to the datastore.

The following screenshot shows that iSCSI is our datastore, migration-demo is a folder under the iSCSI datastore, and AdventureWorksLT2014.bak is the name of the full backup file.

You’re now ready to restore the database backup to the target RDS for SQL Server.

  1. Connect to the target RDS for SQL Server.

For this post, we use mssql-cli as the client tool for accessing RDS for SQL Server, but you can use SSMS or any other client tool of your choice.

For Amazon RDS to restore the database backup from the VMware datastore, you need to use the stored procedure rds_set_configuration to set up the vSphere environment information for the restore. You use the following parameters:

Configuration Parameter Value
vsphere_datacenter Name of the vSphere datacenter where the vSphere cluster is deployed.
vsphere_datastore Name of the datastore where the database backup file is stored.
vsphere_username

vSphere user name. This user needs have permission to download the *.bak file from the vSphere endpoint. For example, <vsphere_endpoint>/folder/<filepath>?dcPath=<datacenter>&dsName=<datastore>.

 

vsphere_password Password of the vSphere user.
vsphere_endpoint vSphere endpoint (HTTP or HTTPS prefix is expected).

 

  1. Configure RDS for SQL Server to enable a native restore from VMware datastore with the following script (replace the username and password with your vSphere user credentials):
    USE rdsadmin
    GO
    
    EXEC rds_set_configuration 'vsphere_datacenter', ‘ENV6-Datacenter’;
    EXEC rds_set_configuration 'vsphere_datastore', 'iSCSI';
    EXEC rds_set_configuration 'vsphere_username', 'user@vSphere.local';
    EXEC rds_set_configuration 'vsphere_password', '[password]';
    EXEC rds_set_configuration 'vsphere_endpoint', 'https://vcenter.env6.amazon.local';
    GO

The following screenshot shows your output.

  1. Verify the configurations with the following code:
    USE rdsadmin
    GO
    
    rds_show_configuration
    GO

The result should contain the values you set using the stored procedure rds-set_configuration.

  1. Run a restore of the database with the following code:
    USE msdb
    GO
    
    EXEC rds_restore_database 
    @restore_db_name='AdventureWorksLT2014',
    @datastore_path_to_restore_from='migration-demo/AdventureWorksLT2014.bak'
    GO

The result returns a task_id that you can use to track the progress of the task. The following screenshot shows that the task_id returned is 10.

We track the progress of our database restore task with the following code:

EXEC rds_task_status @task_id = 10
GO

When the restore is complete, you should see results similar to the following screenshot, with the % complete column showing 100 and the lifecycle column showing SUCCESS. The result should also show the time it took to restore the database. In this example, it took 2 minutes to restore the AdventureWorksLT2014 database.

If required, you can cancel the task that’s in progress. When a task is in progress, the lifecycle column shows the value INPROGRESS. For example, you can run the following script to cancel the previous restore task if the task is still in progress:

USE msdb
GO

EXEC rds_cancel_task @task_id = 10
GO

When the restore task is complete, you can point your applications to the RDS for SQL Server running on vSphere, and start running queries against the database on an RDS for SQL Server instance (see the following screenshot).

Limitations

When using this feature, you should be aware of the limitations (as of this writing):

  • Restore supports full database backup only.
  • Restore from multiple file backups is not supported.
  • Backing up RDS for SQL Server to a VMware datastore is not supported.
  • RDS on VMware currently supports SQL Server 2016 SP2 Enterprise Edition. Therefore, the restore feature supports backup files taken from the same or previous SQL Server version.

Summary

The post showed how to migrate an on-premises SQL Server database to RDS on VMware using SQL Server native backup and restore and a VMware datastore.

For more information about RDS on VMware, see Amazon RDS on VMware.


About the Author

Wanda He is a Sr.  Database  Specialist Solutions Architect at Amazon Web Services. She works with customers on design, deploy, and optimize relational databases on AWS.