How to Use BACPAC for SQL Migration?

Digital transformation is playing an increasingly important role in organizations across the globe. Many organizations rely on their legacy on-premises systems and siloed processes that act as roadblocks that hamper the modernization process. One such example is the use of legacy versions of on-premises Windows Server and SQL Server 2010 that have reached their end of life (EOL). Continuing with obsolete infrastructure poses challenges related to cost, security, and regulatory compliance. Consequently, many enterprises are migrating their legacy estates to Azure SQL Database. In this article, we will explore how enterprises can use BACPAC for SQL migration.

What Is BACPAC?

BACPAC is a Windows file that includes the database schema and data of the SQL Server. It is specifically designed for moving databases between servers. In the case of migration, BACPAC files can be exported to a different location, such as Azure Blob Storage or an on-premises location, and then imported into SQL Server, Azure SQL Managed Instance, or Azure SQL Database. Although the BACPAC file contains data and metadata, it should only be used for migrating databases and not as a backup option as there could be a loss in data consistency.

How Does SQL Migration with BACPAC Work?

BACPAC SQL migration involves the use of an export data-tier application. A data tier application is a logical database management entity used to define all SQL server objects, such as tables, views, and instance objects. Here is the procedure involved in migrating SQL databases using BACPAC.

• Use Azure Portal, SqlPackage command-line utility, SQL Server Management Studio (SSMS), Azure Data Studio, or PowerShell to create the BACPAC file.
• Store the newly created BACPAC file in Azure Storage.
• Create an Azure Database from the Azure Portal and connect the Azure Blog Storage account with the container for the exported BACPAC file.
• Provide the database name, credentials, and other details to begin importing the database and commence operations.

What Are the Limitations OF BACPAC SQL Migration?

SQL Server migration using BACPAC files is one of the easiest available methods. However, it cannot be used in every scenario. Here are the limitations of migrating SQL databases with this method.

• The maximum size of the BACPAC file that can be uploaded to the Azure Blob Storage is 300 GB. Larger files have to be stored locally. This could hamper the migration process.
• The available resources play an important role in the export-import process. Operations exceeding 20 hours could fail unless the resource utilization is optimized.
• Writing activity cannot be performed during the export process as the process does not guarantee transactional consistency if a child table is modified after the parent table is exported.

Apps4Rent Can Help with Fully Managed SQL Server Migration

There are several methods for migrating SQL databases to Azure. Using the Database Migration Assistant Wizard, Transactional Replication, and PowerShell Scripts are some of the other options. The existing environment and available resources often dictate the appropriate method to be used for SQL Server migration.

As a Microsoft Gold Partner for competencies such as Cloud Platform and Cloud Productivity, and a Tier 1 Microsoft CSP, Apps4Rent can help enterprises upgrade and migrate their on-premises SQL servers to Azure. Contact our Microsoft-certified cloud architects available 24/7 via phone, chat, and email for assistance.