Migrate SQL Server databases from an Azure SQL database to Amazon RDS for SQL Server using bacpac method

December 2023: This post was reviewed and updated for accuracy.

Customers choose Amazon Relational Database Service (Amazon RDS) for SQL Server because it manages time-consuming database administration tasks including provisioning, backups, software patching, monitoring, and hardware scaling. With Amazon RDS for SQL Server, you can enable single-click high availability by enabling the Multi-AZ flag in the RDS Console. With Multi-AZ, we replicate data synchronously across different availability zones. In case the primary node crashes, your database automatically fails over to the secondary and AWS automatically re-builds the secondary. Increasingly, customers are looking to migrate their databases to Amazon RDS for SQL Server to take advantage of its benefits. Industry analyst firm Gartner has published the Solution Scorecard for Amazon Relational Database Service, with AWS earning a 95 rating, including 100% of required criteria by Gartner for an operational database platform as a service (dbPaaS). You can check details of the report here. Although there are several ways to migrate SQL Server databases, using SQL Server backup files is the simplest and most reliable method.

In this post, we demonstrate the process to migrate Azure SQL databases to Amazon RDS for SQL Server. Specifically, we show how to take the .bacpac file from your Azure SQL database and restore the database to an Amazon RDS for SQL Server database.

Migration options

Microsoft Azure provides three distinct SQL Server offerings:

SQL Server on Azure VMs
Azure SQL Managed Instance, which is a managed database service that is compatible with the latest SQL Server database engine
Azure SQL Database, which is a cloud-based managed service that provides features such as serverless compute and Hyperscale storage

The standard methodology of migrating a database from source to target servers consists of using the native backup and restore mechanism. SQL Server comes with two common database backup options: the native backup (.bak) file and the data-tier application backup package file (.bacpac). Both options have their pros and cons, which we will discuss in this section.

For more information about how to export database to .bacpac file, refer to exporting database to .bacpac file.

Native backup files (.bak)

A native backup is a copy of a database that consists of data records (data file) and log records (log file). Native backup comes with different types like full backup, differential backup, transactional backup, partial backups, or file group backup. Depending on the recovery model set for a database, you can achieve point in time recovery. The extension of the backup file is usually denoted as .bak. There are various ways through which we can take a backup of the database, the most common are using SQL Server Management Studio (SSMS) or the command line.

Native backup has the following advantages:

A full backup takes a complete copy of a database
A full backup is transactionally consistent
You can take a portion of the database, like backing up a particular file group, data file, or log file
It provides point in time recovery with full recovery mode
You can stripe the backup files for a large database to improve speed and stripe the files across different disks for any storage concerns on a particular disk
A full backup provides the flexibility to minimize the downtime window on cutover. For more information, refer to Migrating SQL Server to Amazon RDS using native backup and restore
A full backup can be compressed in SQL Server Editions of Enterprise, Standard, and Developer. This compressed backup file occupies less space and can be done faster

However, native backup has the following challenges:

The size of the .bak file is larger compared to the .bacpac file because it takes a full copy of data contained on the database files
Azure SQL Database doesn’t allow you to take native backups (.bak) as it uses a different technology for backups. Refer to Automated backups to learn more
Azure SQL Database doesn’t support taking backups from higher SQL Server versions and restoring to an earlier version.

BACPAC files

A BACPAC (backup package) file consists of copied metadata and the actual data compressed to a file. The extension of this file is usually denoted as .bacpac. This is the best choice for a small database size (approximately 200 GB). We can take a .bacpac file through an Export\Import data-tier application (GUI) or through a SQLPackage.exe (command line) utility.

This method has the following advantages:

You can migrate data from different sources to targets with different SQL versions with few compatibility issues
The .bacpac size is comparatively smaller than a .bak file because it takes the metadata and data copy. It doesn’t take a backup of indexes; instead, it generates scripts to create indexes on the target

However, this method has the following challenges:

It’s not transactionally consistent. You must take a snapshot and export the database to make it transactionally consistent
You can’t take a portion of the database, like backing up a particular file group, data file, or log file
It doesn’t provide point in time recovery
It is recommended for smaller databases (within 200 GB) because it may be time-consuming to build the database

Solution overview

The most common method to migrate SQL Server databases from any source to Amazon RDS for SQL Server is to take the native backup from the SQL Server at the source and copy the backup files to an Amazon Simple Storage Service (Amazon S3) bucket. Then, you restore the backup files to Amazon RDS for SQL Server. For more information, see Setting up for native backup and restore.

For migrating SQL databases from the Azure cloud platform to Amazon RDS for SQL Server using the native backup and restore method, we can either use .bak (native backups) or .bacpac (backup package). The following figures illustrate the supportability of backup options for the three offerings in Azure for SQL Server databases.

The first figure shows how .bak and .bacpac files are supported in SQL Server on a virtual machine and SQL managed instance offerings.

The following figure shows how Azure SQL database supports only .bacpac file and not .bak.

The following table summarizes the backup options for migrating SQL Server databases from different offerings of Azure (Azure SQL on VMs, Azure SQL Managed Instance, Azure SQL Database) using .bacpac and .bak files, which are taken to either Azure Storage or physical disks.

Backup Options	Azure SQL on VMs	Azure SQL Managed Instance	Azure SQL Database
Data-tier application (.bacpac) to Azure Storage	Yes	Yes	Yes
Backup(.bak) to Azure Storage	Yes	Yes	No
.bacpac/.bak to physical disk	Yes	No	No

Perform a backup using .bak

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

Perform a backup using .bacpac

The following is the detailed procedure to migrate an Azure SQL database (PaaS solution) to Amazon RDS for SQL Server using the .bacpac option. We walk through the steps for taking a .bacpac file from an Azure SQL database using an export data-tier application and restoring the .bacpac file using SQLPackage.exe on Amazon RDS for SQL Server.

We can export a .bacpac file from Azure Portal using an Export data-tier application or through the command line utility SQLPackage.exe to available storage. Then, copy the .bacpac file to Amazon Elastic Compute Cloud (Amazon EC2). From there, you can connect to the target RDS for SQL Server database, preferably in the same region and availability zone.

Amazon RDS for SQL Server doesn’t support importing the .bacpac file using a data-tier application. The only way to import the .bacpac file is to use sqlpackage.exe.

In order for the .bacpac file to be transactionally consistent, make sure that no write activity is occurring during the export or exporting a transactionally consistent copy. Take a snapshot and then export the .bacpac to make it transactionally consistent, then delete the snapshot after a successful export of .bacpac.

The following are the high-level steps to export and import .bacpac files from your Azure SQL database to Amazon RDS for SQL Server:

Export the .bacpac file from your Azure SQL database
Copy the .bacpac file from Azure Storage to Amazon EC2 EBS storage
Import the .bacpac file to Amazon RDS for SQL Server

The following diagram illustrates this process.

Export the .bacpac file from the Azure SQL database

You can choose from the following methods to export the .bacpac file and save it to storage (either to Azure Storage or a physical file system):

Azure Portal
SQLPackage.exe
Export data-tier application

Azure Portal

To use the Azure Portal, complete the following steps:

Open the Azure Portal and search for the Azure SQL database that you want to migrate
Choose Export
Figure 1, highlights the export option available on the toolbar of Azure Portal.

Figure 1: Highlights the Export utility in Azure Portal

You’re redirected to the next page to save the .bacpac file to Azure Storage as shown in Figure 2.
For File name, leave the default file name or enter a new name
Choose an existing Azure Storage account to export the .bacpac file
Provide the appropriate credentials to access the source database (the SQL Server admin login and password you used to connect to the Azure SQL database)
Choose OK

Figure 2: Export database wizard in Azure Portal

You’re redirected to the next page where you can download the .bacpac file.
Select the .bacpac file
On the options menu, choose Download
Figure 3, highlights the download option available on clicking the three dots.

Figure 3: Highlights Download Option

SQLPackage.exe

SQLPackage is a command line utility to manage the data-tier application. It helps extract SQL objects from your database to a file. To export a SQL database using the SQLPackage command-line utility, download the latest version of the SQLPackage utility to a path and run the following command from a CMD shell to export the .bacpac file and copy this file to Amazon EC2, where the target RDS for SQL Server database is connected. For more information about SQLPackage Export parameters, refer to SqlPackage Export parameters and properties.

See the following code:

sqlpackage.exe /Action:Export /ssn:”tcp:ServerName” /sdn:DatabaseName /su:UserName /sp:Password /tf:”TargetFile” /p:Storage=File /SourceTrustServerCertificate:True

For example:

sqlpackage.exe /Action:Export /ssn:”tcp:azuresqldbxx.database.windows.net,1433”/sdn:testdb /su:AdminUser /sp:AdminPassword1 /tf:”C:\testdb.bacpac” /p:Storage=File /SourceTrustServerCertificate:True

Export data-tier application

SQL Server Management Studio (SSMS) has a wizard to export a database in an Azure SQL database to a BACPAC file.

Open SSMS and connect to the Azure SQL database
Choose the database (right-click) and on the Tasks menu, choose Export Data-tier Application as highlighted in Figure 4.

Figure 4: Export Data-tier Application
Provide the target location to which the .bacpac file will be saved. Figure 5, highlights the path provided.

Figure 5: Export setting wizard
Copy this file to Amazon EC2, where the target RDS for SQL Server database is connected.

Copy the .bacpac file from Azure Storage to Amazon EC2 EBS storage

After the export is successfully saved to the physical disk, copy the .bacpac file from the saved location to Amazon EC2 from where the target RDS for SQL Server database is connected. Use S3 with Amazon EC2 to copy files. You need to import the .bacpac file to restore the database on Amazon RDS for SQL Server.

Import the .bacpac file to Amazon RDS for SQL Server

The GUI version of using the Import data-tier application would fail with permission errors. To resolve this error, prior to exporting the bacpac on the source (Azure SQL database), the RDS for SQL Server master user needs to be added to the Azure SQL database that is used for a bacpac and needs to be member of db_owner.

Amazon RDS for SQL Server supports SQLPackage.exe to import the .bacpac file. The GUI version using the Import data-tier application is not supported and fails with sqladmin permission error. Use the SQLPackage.exe utility and run the following command from a CMD shell from Amazon EC2 to import the .bacpac file into the target RDS for SQL Server database.

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

For example:

sqlpackage /a:Import /sf:”c:\sql\testdbaz.bacpac” /tsn:”rdstestxxxx.xxxxxxxxxxxx.useast-1.rds.amazon.com” /tdn:AZTESTDBXX /tu:sqladmin /tp:xxxxxxxxxxxx /TargetTrustServerCertificate:true

Figure 6, shows a successful import of our Azure SQL database using SQLPackage.exe.

Figure 6: SQLPackage.exe sample execution

For more information, refer to Import a BACPAC File to Create a New User Database. To learn more about SQLPackage Import parameters, refer to SqlPackage Import parameters and properties.

Summary

In this post, we covered how you can migrate Azure SQL Server databases to Amazon RDS for SQL Server using the .bacpac file, SQLPackage, and a data-tier application. With a successful migration to Amazon RDS for SQL Server, you can focus on what matters to your organization’s business, such as optimizing database design, capacity sizing, and performance tuning.

If you have any questions or suggestions, leave a comment.

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.

Yogi Barot is Microsoft Specialist Principal Solutions Architect at AWS, she has 22 years of experience working with different Microsoft technologies, her specialty is in SQL Server and different database technologies. Yogi has in depth AWS knowledge and expertise in running Microsoft workload on AWS.