AWS Database Blog
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.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
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.
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:
For example:
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.
- Provide the target location to which the
.bacpac
file will be saved. Figure 5, highlights the path provided. - 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.
For example:
Figure 6, shows a successful import of our Azure SQL database using SQLPackage.exe.
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.