Is there a faster way (than bacpac export/import) to move SQL on-prem databases to Azure SQL?

Kazoo60 16 Reputation points
2021-07-20T21:41:00.783+00:00

We have about 2 Terrabytes of data in SQL Server 2014 on-prem servers that we need to move into Azure SQL. The data is distributed among about 50 databases with no database larger than 200GB.

The best method we have come up with so far which ends with importing bacpac files to create the database on Azure SQL, is very slow. Even a database less than 100GB takes about 7 hours to complete the process. About 6 of those hours is spent creating and restoring a bacpac file.

The SQL 2014 database implementations are not compatible with Azure SQL so we have to move them through an upgrade process. We are doing that on a SQL 2019 instance we have set up on a Azure SQL Server VM. To get the database on that platform we can run a simple backup restore which is very quick. We then run a DevOps pipeline against each database to update the code and structural issues that need to be addressed. All databases have identical structure. The pipeline process isn't quick (taking about an hour) but has been optimized and so we are not looking to improve it further.

It is the rest of our process that I am wondering if anyone sees ways to improve on:

Next, we create a bacpac of each updated database and store it directly onto an Azure storage account. This is also very slow - over 2 hours for 100GB.

We then create a new Azure SQL database using the bacpac. We have tried doing this from local workstations with fast connections, from another Azure VM both using the "Import Data Tier" application in SSMS, through the command line using sqlpackage.exe, and using the Create Database option on the Azure portal. The SSMS methods fail about 70% of the time with memory errors but the portal and sqlpackage.exe methods work quite reliably. We have found the Azure Portal method to be about 20% faster than sqlpackage.exe. The problem is the time required. Our latest test of a 100GB database was 3 hours 58 minutes for this step.

I have tried increasing the resources the database has. I started with a Gen5, 4 vCores configuration and performance did improve upgrading to a Gen5, 8 vCores configuration. I did not see any appreciable difference moving up to Gen5 16 vCores, however.

Before landing on the bacpac import method, I also tried the data migration wizard tool from Microsoft but never had a successful test on any database over about 10GB - even waiting up to 8 hours for it to complete. No errors were reported.

Does anyone know of a faster way to accomplish this migration? and especially the final step to create the database from a bacpac? Is there a better way? For example, if the database is compatible, is there any way to run a SQL 2019 backup and just restore that onto Azure SQL?

Thanks!

Azure Database Migration service
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,788 questions

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,471 Reputation points
    2021-07-21T06:19:29.217+00:00

    Hi @Kazoo60 ,

    The Azure SQL Database Import/Export service provides a limited number of compute virtual machines (VMs) per region to process import and export operations. The compute VMs are hosted per region to make sure that the import or export avoids cross-region bandwidth delays and charges. If too many requests are made at the same time in the same region, significant delays can occur in processing the operations. The time that's required to complete requests can vary from a few seconds to many hours.

    For more information, refer to Azure SQL Database and Managed Instance Import/Export service takes a long time to import or export a database

    You also can read this article about other ways to migrate the data.

    Best regards,
    Seeya

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. Erland Sommarskog 111.1K Reputation points MVP
    2021-07-21T09:53:40.487+00:00

    One could certainly wish that it would be easier to move databases to Azure. But, no, you cannot simply restore a database backup.

    Jes Borland developed a solution based on containers for a situation which seems similar to yours. I have not tried it myself, but I listened to her presentation about a year ago. Here work is on GitHub here: https://github.com/grrlgeek/bak-to-bacpac.

    0 comments
  3. ErikEJ 341 Reputation points MVP
    2021-07-21T11:41:16.78+00:00
    0 comments
  4. Armando Lacerda 11 Reputation points MVP
    2021-07-21T13:01:42.73+00:00

    Hi @Kazoo60 ,
    It seems you're using a regular Storage Account service which limits the file throughput at 50MB/s. My first recommendation would be using a Storage Account with hierarchical file system instead. Performance can get up to 2TB/s in this type of storage service. That should speed up one side of your data movement process.
    The other end is more limited in throughput options. Azure SQL DB limits (throttles) it's data intake based not only on vCores but also in the service. Your best bets there is either Hyperscale or Business Critical since they offer faster IOPS. But the log write is 64MB/s (on 8 vCores or more) for Business Critical and 100 MB/s for Hyperscale. Problem is one single connection can't push to that limit. The best I've seen is around 30 MB/s.
    So if you apply these suggestions above you would get marginal performance gain since most of your bottleneck is at the single session throughput limit in Azure SQL DB.
    In my experience I've ditched the bacpac route and I've gone by Azure Data Factory with parallelism to move data to Azure SQL DB. It's a bit more complicated but it has served me well.

    1. create all tables with no constraints
    2. import data using ADF copy task
    3. create all constraints (PKs, FKs, triggers, indexes, etc.)

    Hope this helps.

    0 comments
  5. Raj Pochiraju 6 Reputation points Microsoft Employee
    2021-07-21T13:57:08.693+00:00

    Hello,
    Sorry to hear the troubles with your migration experience with bacpac. But you can fully automate these migrations using the CLIs provided by our Azure Database Migration Service (DMS).
    The guidance provided here https://video2.skills-academy.com/en-us/azure/dms/howto-sql-server-to-azure-sql-powershell. We can also provide and end to end powershell script as well.
    You can directly use DMS UI or portal experience or automate using the above guidance. If all these 50 databases needed to be migrated in a single migration window, we recommend to create multiple DMS services, at least 5 distribute these databases among those services.
    The DMS service spun off parallel threads and provide much faster rates than bacpac to migrate, of course it also depends on the network bandwidth and the log rate that target Azure SQL caps.
    Please let us know if you want to try that approach, we can help you.
    Thanks
    Raj

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.