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.
At a high level, we accomplish the migration in three simple steps:
- Take a full backup of your on-premises SQL Server database.
- Upload the database backup file (*.bak) to a VMware datastore.
- Restore the database backup to the target RDS for SQL Server.
The following diagram illustrates our solution architecture.
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:
- Stop the application connections to the source database, or make the database read-only.
- Perform a full backup of the database.
- Launch the vSphere client from the SQL Server VM.
- 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.
- 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:
||Name of the vSphere datacenter where the vSphere cluster is deployed.|
||Name of the datastore where the database backup file is stored.|
vSphere user name. This user needs have permission to download the *.bak file from the vSphere endpoint. For example, <vsphere_endpoint>
||Password of the vSphere user.|
||vSphere endpoint (HTTP or HTTPS prefix is expected).|
- 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):
The following screenshot shows your output.
- Verify the configurations with the following code:
The result should contain the values you set using the stored procedure
- Run a restore of the database with the following code:
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:
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
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:
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).
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.
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.