Step-by-Step Guide: Migrating an Azure SQL Database to AWS RDS for SQL Server Using a .bacpac File

Introduction

Organizations increasingly adopt multi-cloud strategies, making it common to migrate workloads between providers like Azure and AWS. A key challenge is migrating an Azure SQL Database to an AWS RDS for SQL Server instance while ensuring data integrity and minimizing downtime.

This guide details a real-world migration process using a .bacpac file for transfer. It also covers handling compatibility issues, such as blocked permissions and credentials, with a practical workaround involving a local SQL Server container on an EC2 instance.

Why Choose the .bacpac Method

The .bacpac file format is lightweight and includes both schema and data from an Azure SQL Database. It simplifies exporting, transferring, and importing across environments, making it suitable for cross-cloud migrations where tools like AWS Database Migration Service (DMS) might be unnecessary.

This approach avoids direct replication complexities and supports schema-based transfers effectively.

1. Export the .bacpac File from Azure SQL Database

Exporting a .bacpac file from Azure SQL can be done via the portal or command line. Choose the method that fits your workflow.

Using the Azure Portal

1. Access your Azure SQL Database in the Azure portal.
2. Go to Settings > Export.
3. Choose an Azure Storage Account and container for the output.
4. Enter valid SQL credentials and select OK.
5. Download the resulting .bacpac file from Azure Blob Storage.

Using sqlpackage (CLI)

For command-line users, run the following:

Transfer the exported .bacpac file to your EC2 instance using scp or upload to S3.

2. Set Up the AWS EC2 Environment

Use an Ubuntu EC2 instance in the same VPC as your RDS for this migration. This setup facilitates secure, low-latency operations.

Install Dependencies

Install sqlpackage

The latest sqlpackage version for Linux is 170.1.61, available as a self-contained zip.

Challenges with Direct Import to RDS and Our Approach

While you can attempt to import the .bacpac file directly into AWS RDS for SQL Server using sqlpackage, real-world scenarios often reveal compatibility hurdles due to RDS restrictions. In our experience, direct imports frequently fail because AWS RDS enforces strict security policies that block certain SQL Server operations present in Azure SQL exports.

We encountered issues with GRANT/REVOKE permissions in the .bacpac file, particularly around BACKUP DATABASE and BACKUP LOG statements, which RDS prohibits to prevent unauthorized backups. These were resolved by importing the file into a local SQL Server instance first, editing the database to remove the offending permissions, and then re-exporting a cleaned .bacpac.

Subsequently, an Azure credential for a storage account, used for features like auditing was identified and removed through further edits in the local database. This step ensures the schema aligns with RDS limitations, avoiding errors like rds_deny_backups_trigger during import.

To address these challenges systematically, we launch a temporary local SQL Server container on the EC2 instance. This acts as an intermediary for preprocessing, allowing safe modifications without risking the target RDS environment.

3. Launch a Temporary Local SQL Server in Docker

Run a local SQL Server container to preprocess the .bacpac file before RDS import. This isolates compatibility fixes.

Check the container status:

If port 1433 is in use:

4. Import the .bacpac into the Local SQL Server

Import the file into the local instance:

Replace placeholders like passwords with secure values in production.

5. Clean RDS-Blocked Permissions and Credentials

AWS RDS restricts operations like granting backup permissions or creating external credentials (e.g., for Azure Blob Storage). Clean these in the local instance to prevent import failures.

Connect using sqlcmd in a new container:

Execute these SQL commands:

6. Export the Cleaned .bacpac

Export the modified database as a new .bacpac:

7. Import the Cleaned .bacpac into AWS RDS

Import into your RDS instance:

For schema conflicts, add /p:DropObjectsNotInSource=true.

8. Validate the Migration

Verify the RDS import by connecting and checking tables:

bash

Run:

Successful table listing confirms the migration.

Key Takeaways

• .bacpac files enable straightforward Azure-to-AWS migrations for schema and data transfers.
• Local preprocessing in Docker resolves RDS-specific security restrictions.
• Tools like sqlpackage and Docker create repeatable migration pipelines.

Conclusion

Cross-cloud database migrations can be efficient with sqlpackage, Docker, and targeted scripting. This method provides confidence in multi-cloud transitions.