sql-serverazure-sql-database

Bacpac vs dacpac for migrating database to Azure

I am deploying a large database (both schema and data) from an on-premise server to Azure SQL. I plan on using sqlpackage.exe from the command line. It looks like there are two options:

  1. Extract a DACPAC and then publish it to Azure, using the ExtractAllTableData option to include data in the extraction
  2. Export a BACPAC and then import it to Azure

Is there a preferred option? I need this process to run in the fastest way possible, so I'd be curious if one option is better for performance.

Solution

  • As Grant said bacpac is dacpac+data.Below is one more definition that might help

    "A bacpac includes the schema and data from the database. A dacpac contains only the schema and not the data"

    You might have understood, you need bacpac.

    I need this process to run in the fastest way possible, so I'd be curious if one option is better for performance.

    We have few databases(400Gb) in Azure.we observed it is faster to import data when we load data from storage..The approach we followed was to load data into storage which is in same location as database and then import data..

    SQLSERVER team tested a few options and they observed BCP is fastest compared to remaining options..

    enter image description here

    Below is how you import bcp data into azure

    bcp TestDB.dbo.Customer in "C:\Users\cesardl\BCP\Customer.txt"
    -c -U mysqlazureuser@mysqlazureservername
    -S tcp:mysqlazureservername.database.windows.net
    -P mypassword

    Further reading/References:

    Importing/Exporting data to SQL Azure databases using BCP and SQL Scripts
    Loading data to SQL Azure the fast way