SALES: 1-800-867-1380

How to: Use a DAC BACPAC to Migrate a Database to Azure SQL Database

Updated: April 18, 2014

You can migrate both the schema and the data from a SQL Server database by exporting a BACPAC from an existing database, placing the BACPAC file in a Blob service account, and then importing the BACPAC to Microsoft Azure SQL Database.

Author: Shaun Tinline-Jones
Reviewer: Adam Mahood

Before You Begin

A data-tier application (DAC) is a self-contained unit for developing, deploying, and managing data-tier objects. A DAC enables data-tier developers and database administrators to package Microsoft SQL Server objects, including database objects and instance objects, into a single entity called a DAC package (.dacpac file). The BACPAC format extends the DAC package format to include BACPAC-specific metadata and JavaScript Object Notation (JSON)–encoded table data in addition to the standard DAC package contents. You can package your SQL Server database into a BACPAC file, and use it to migrate both the schema and table data to Microsoft Azure SQL Database.

Recommendations

A DAC package and BACPAC target different scenarios.

  1. A BACPAC contains both schema and data, but does not support being imported to a database project for schema modification. The primary use of a BACPAC is to move a database from one database service to another (either instances of the Database Engine or Microsoft Azure SQL Database). A BACPAC can also be used to archive an existing database in an open format. These uses make it a good tool for migrations where the database requires no schema changes.

  2. DAC packages contain only schema information, but you can import the package into an SSDT database project for further development work. The primary use for a DAC package is in deploying a database schema to development, testing, and then production environments.

The Import and Export Service for Azure SQL Database can directly import or export BACPAC files between a database on Microsoft Azure SQL Database and Azure Blob service. The Import and Export Service for Azure SQL Database provides public REST endpoints for the submission of requests. The Azure Platform Portal has an interface for calling the Import and Export Service for SQL Database.

[Top]

Limitations and Restrictions

A DAC BACPAC can only be used for migrations where no database changes are required to address objects not supported on Microsoft Azure SQL Database. If such changes are required, consider either:

  1. Using a DAC package and the SQL Server Data Tools (SSDT) to modify the database schema and make the required changes before deploying to Microsoft Azure SQL Database. For more information, see How to: Use a DAC Package to Migrate a Database to Azure SQL Database.

  2. Making all schema changes in the source database before exporting the DAC BACPAC.

There is a SQL DAC Examples project that builds an unsupported command prompt utility that can be used to export and import BACPACs. You can download the SQL DAC Examples project from CodePlex. The project requires the DAC Framework. For more information about using the utility built from the project, see DAC Framework Client Side Tools Reference.

[Top]

Prerequisites

To work with a DAC BACPAC, you must have installed the client DAC software, known as the DAC Framework. The DAC Framework is included with SQL Server Data Tools and the SQL Server utilities such as SQL Server Management Studio. When working with Azure SQL Database, the recommended version of the DAC Framework to use is the one included in SQL Server Data Tools and SQL Server 2012. You can also upgrade earlier versions of the DAC framework by installing these three packages from the SQL Server 2012 Feature Pack:

  • Microsoft System CLR Types for Microsoft SQL Server 2012

  • Microsoft SQL Server 2012 Transact-SQL Script DOM

  • Microsoft SQL Server 2012 Data-tier Application Framework

For information about compatibility between versions of the DAC Framework and versions of SQL Server, see DAC Support For SQL Server Objects and Versions.

[Top]

Migrating a DAC BACPAC File

The steps to migrate a database from SQL Server to Azure SQL Database are:

  1. Export a BACPAC File From an Existing SQL Server Database

    You can use the Export Data-tier Application wizard in the SQL Server 2012 version of SQL Server Management Studio to export a BACPAC file directly to a Blob service account. To launch the wizard, right click the database in Object Explorer, select Tasks, and then select Export Data-tier Application. On the Export Settings page, use the control Save to Azure to specify a Blob service location. Optionally, you can use the SQL DAC Examples utility. For more information about exporting a BACPAC from SQL Server, see Export a Data-tier Application.

    You must have a storage account to export a BACPAC to storage.

  2. Move the BACPAC File to Azure Blob Service

    If you used the SQL DAC Example to export the BACPAC to a local file on your computer and want to use the to import the BACPAC to ssSDS, move the BACPAC file to a Blob account. You can copy the file by using either the Azure Management Platform Tool or Microsoft Codename “Data Transfer”.

    If you plan to use the SQL DAC Example to perform the import to Microsoft Azure SQL Database, you do not need to copy the BACPAC file to a Blob service.

  3. Import the BACPAC to Microsoft Azure SQL Database

    Once exported, the BACPAC can be imported to create a database on Microsoft Azure SQL Database. You can use the to import a BACPAC stored in a Blob service. On the ribbon, select Import to launch the Import Database from Windows Storage window. Optionally, you can use the SQL DAC Example to import a BACPAC saved to a local file on your computer.

[Top]

Resources

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft