Home / server / Questions / 909661
In Process
Kirsten
Asked: 2018-04-27 18:46:44 +0800 CST

Exception of type “System.OutOfMemoryException” importing BacPac into Azure

  • 772

Because of this question I thought I would try importing a BACPAC file into Azure SQL Server.

When I try and import a Data Tier Application ( a BACPAC file) to a new Azure database I get an out of memory exception.

enter image description here

When I ran the Data Migration Assistant I get 1 unsupported feature Azure SQL Database does not support trace flags

I wonder if that is why I get that error enter image description here

azure

3 Answers

  1. Posting as an answer due to the popularity of the comment...

    Try closing SSMS and reopening it and trying the import again. I have found that if you have previously run some very large select statements that returned millions of rows the SSMS process is already using a lot of memory and more easily gets OutOfMemory errors. So closing and reopening SSMS helps avoid this.

    The System.OutOfMemoryException is a .NET error which usually means the SSMS client tool is out of memory not that the server is out of memory.

    • 3

  2. I've encountered a similar issue when importing a 1TB BacPac file into the Azure SQL Database.

    The solution which worked was to:

    • Create Azure VM with 64GB of RAM (D16ds_v4) - temporary VM for restore process
    • Attach 4TB managed disk to the Azure VM, format it and mount as drive T:
    • Transfer BacPac file into the VM
    • Download 64-bit SqlPackage for .Net Core command-line tool into the Azure VM
    • Run the following in the command prompt (with QuickEdit disabled) inside the Azure VM:
    mkdir T:\Temp
SET TEMP=T:\Temp
SET TMP=T:\Temp
sqlpackage.exe /a:import /tcs:"Data Source=[ServerName].database.windows.net;Initial Catalog=[DatabaseName];User Id=[UserName];Password=[Password]" /sf:E:\BacPacDir\BacPacFile.bacpac /p:DatabaseEdition=Hyperscale /p:DatabaseServiceObjective=HS_Gen5_2
    • Change E:\BacPacDir\BacPacFile.bacpac to the location of your bacpac.
    • Change Azure SQL Database parameters accordingly: /p:DatabaseEdition=Hyperscale /p:DatabaseServiceObjective=HS_Gen5_2
    • For the Standard/Premium SQL DB there is an additional DatabaseMaximumSize parameter: /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P11 /p:DatabaseMaximumSize=1024
    • 3

  3. One thing you can try is to import the Bacpac file using the Azure portal rather than the database. You would upload the Bacpac to Azure storage using a tool like Azure Storage Explorer, then point the portal to that to import it - https://docs.microsoft.com/en-us/azure/sql-database/sql-database-import

    • 1