Migrate SQL Server databases in Azure to Amazon RDS Custom for SQL Server using backup and bacpac files and Amazon S3

In this post we show you how to migrate from Azure to Amazon Relational Database Service (Amazon RDS) Custom for SQL Server. using the native backup and restore method. We also dive deep into the data-tier application backup package file.

Amazon RDS Custom for SQL Server is a managed database service for legacy, custom, and packaged applications that require access to the underlying OS and database environment. Amazon RDS Custom for SQL Server automates the setup, operation, and backup of databases in the AWS Cloud while granting you access to the database and underlying OS. With Amazon RDS Custom, you get the automation of Amazon RDS with flexibility similar to Amazon Elastic Compute Cloud (Amazon EC2).

As of this writing, the Azure platform has three offerings: Azure SQL VM, Azure SQL Managed Instance, and Azure SQL Database. A common method to migrate SQL Server databases from Azure to Amazon RDS Custom for SQL Server is by using the native backup and restore method (.bak files). However, this is only supported by Azure SQL VM and Azure SQL Managed Instance, which also supports data-tier application backup package files (.bacpac). Azure SQL Database only supports .bacpac files. For .bacpac files migration limitation details, and for a similar solution to Amazon RDS for SQL Server, refer to Migrate SQL Server databases from an Azure SQL database to Amazon RDS for SQL Server using bacpac method.

Solution overview

The following diagram illustrates the typical steps for a SQL Server database migration from Microsoft Azure to Amazon RDS Custom for SQL Server.

The steps are as follows:

1. Back up the database:
   • Azure SQL VM and Azure SQL Managed Instance support native SQL Server backup and restore and .bacpac files.
   • Azure SQL Database only supports .bacpac files.
2. Copy the native backup file or .bacpac file to Amazon Simple Storage Service (Amazon S3).
3. Download the file from Amazon S3 to the RDS instance and restore it.

Prerequisites

We assume that you have the following prerequisites:

• Basic knowledge of AWS services such as Amazon EC2, the AWS Command Line Interface (AWS CLI), and Amazon RDS.
• Basic knowledge of how to set up and launch RDS Custom for SQL Server instances and Azure Platform. For more information, refer to Creating and connecting to a DB instance for Amazon RDS for Custom SQL Server.
• Background knowledge about data-tier applications and SqlPackage.
• An S3 bucket configured to store the necessary database backup files. For instructions, refer to Creating a bucket.

This solution incurs cost on your account due to the AWS resource setup and utilization. Refer to AWS Pricing for more information. Before implementing this solution in a production environment, we strongly recommend that you set this up in a non-production instance and run proper validations.

Migrate SQL databases on Azure Platform using .bak files

This procedure involves performing a full backup of the SQL database hosted in Azure (supported in Azure SQL VM and Azure SQL Managed Instance offerings only), followed by differential and log backups, and then restoring the same backups to the target Amazon RDS Custom for SQL Server instance. Following this process can reduce application cutover time during the migration process. For more information, refer to Migrate on-premises SQL Server to Amazon RDS Custom for SQL Server using native backup and restore and Amazon S3.

Migrate Azure SQL Database using .bacpac files

Bacpac is supported in all the three offerings (Azure SQL VM, Azure SQL Managed Instance, Azure SQL databases) in Azure platform. Bacpac is a compressed zip file (with extension .bacpac) that contains the metadata and data of the database. To ensure that the .bacpac file is transactional consistent, make sure that no write activity takes place during the export. A common method is to stop any write activity to the database or perform the export against a snapshot of the database. After a successful export of the .bacpac file, you can delete the snapshot.

The following are the steps involved in migrating a .bacpac file:

1. Export the .bacpac file from your SQL database in Azure. You can use Azure Portal, SqlPackage.exe, Azure Data Studio, or PowerShell. For more information, refer to Export to a BACPAC file – Azure SQL Database and Azure SQL Managed Instance.Note that when you restore a BACPAC file (with heavy indexes) from Azure SQL Database for Database size more than 150 GB it is recommended to scale up RDS SQL Custom instance to 4x-8x large for better performance. And then scale down once database is restored. It is suggested not to use the SQL Server Management Studio(SSMS) GUI to export bacpac of such large databases.
   Please use below sqlpackage.exe command to export the following parameters:

   sqlpackage.exe /Action:Export /ssn:”tcp:azuresqldbxx.database.windows.net,1433”/sdn:testdb /su:AdminUser /sp:AdminPassword1 /tf:”C:\testdb.bacpac” /p:Storage=File /p:CommandTimeout=120 /p: TempDirectoryForTableData=”d:\temp” /mp:32 /p:CompressionOption=SuperFast

   To learn more about SqlPackage export parameters, refer to SqlPackage Export parameters and properties.

2. Upload the .bacpac file to Amazon S3 by using either the AWS CLI or Amazon S3 console. For details, refer to Uploading objects.
3. Download the .bacpac file to the EC2 instance hosting Amazon RDS Custom for SQL Server using the AWS CLI. It is recommended to use the D drive. For details on how to download the file, refer to Downloading an object.
4. Import the .bacpac file to Amazon RDS Custom for SQL Server using the data-tier application, SqlPackage.exe.

For the rest of this post we assume steps 1-3 were completed and the .bacpac files were downloaded to the EC2 instance in D:\Backup, and you could RDP to the EC2 instance that is hosting Amazon RDS for Custom SQL Server.

Import the data-tier application using SSMS

Open SQL Server Management Studio (SSMS) on the EC2 instance that is hosting Amazon RDS Custom for SQL Server. SSMS has the wizard to import the data from .bacpac using the data-tier application.

1. In SSMS, choose (right-click) Databases.
2. Choose Import Data-tier Application.
   
3. Select Import from local disk and enter the source location of the .bacpac file.
4. Choose Next.
   
5. For New database name, enter a name for the database.

All the SQL Server database files are stored in the D:\rdsdbdata\Data directory. If you create or alter the database file location to be anywhere other than the D: drive, then Amazon RDS Custom places the DB instance outside the support perimeter. For more information, refer to RDS Custom support perimeter and unsupported configurations.

6. Choose Next.

The status bar shows the import progress.

7. When database creation is complete, choose Close.
   

SQLPackage.exe

Another method to import .bacpac files to Amazon RDS Custom for SQL Server is to use SQLPackage.exe, which is a command line utility. You must install this utility on Amazon RDS Custom for SQL Server. For more information, refer to SqlPackage.

Complete the following steps:

1. Install SQLPackage.exe on the EC2 instance hosting Amazon RDS Custom for SQL Server.
2. Run the SQLPackage.exe from a CMD shell on the EC2 server hosting Amazon RDS Custom for SQL Server:

sqlpackage.exe /Action:Import /sf:”Path to bacpac file” /tsn:”RDS for SQL Server Endpoint” /tdn:Target DatabaseName /tu:UserName /tp:Password /TargetEncryptConnection:false

For example:

sqlpackage /a:Import /sf:"D:\Backup\act.bacpac" /tsn:"rdscustomsqlazure.xxxxxxxxxmfr.us-east-1.rds.amazonaws.com" /tdn:ACT /tu:Admin /tp: MyC0mpl3xPWD# /TargetEncryptConnection:False

To learn more about SqlPackage import parameters, refer to SqlPackage Import parameters and properties.

You can set the parameter TargetEncryptConnection to either connect without encryption or to trust the server certificate. In the preceding example, the TargetEncryptConnection is set to false because it’s being run locally on Amazon RDS Custom for SQL Server. To learn more, refer to Connection Security Improvements in SqlPackage.

You can also use PowerShell and Azure Data Studio to import the .bacpac file. For more information, refer to Quickstart: Import a .bacpac file to a database in Azure SQL Database or Azure SQL Managed Instance.

Cleanup

Once you have finished your migration you can remove the files you no longer need from your EC2 instance and your S3 Bucket.

Summary

In this post, we covered how you can migrate SQL Server databases from Azure to Amazon RDS Custom for SQL Server using the bacpac method. You learned how to use native backup/restore for databases in Azure SQL VM and Azure SQL Managed Instance support and use .bacpac files for Azure SQL Databases.

If you have any questions or suggestions, leave them in the comments section.

About the Authors

InduTeja Aligeti is a Lead Database Consultant at AWS. She has 16+years of experience working with Microsoft Technologies with a specialization in SQL Server. She focuses on helping customers to build high-available, cost-effective database solutions and migrate their large scale SQL Server databases to AWS.

Priya Nair is a Database consultant at AWS. She has 18 plus years of experience working with different database technologies. She works as database migration specialist to help Amazon customers to move their on-premises database environment to AWS cloud database solutions.

Jose Amado-Blanco is a Sr. Consultant on Database Migration with over 25 years of experience working with AWS Professional Services. He helps customers on their journey to migrate and modernize their database solutions from on-premises to AWS.