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.
Before you begin:
Recommendations, Limitations and Restrictions, Prerequisites
Migrate a DAC BACPAC
Author: Shaun Tinline-Jones
Reviewer: Adam Mahood
Before You Begin
A DAC package and BACPAC target different scenarios.
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.
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.
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:
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.
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.
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.
Migrating a DAC BACPAC File
The steps to migrate a database from SQL Server to Azure SQL Database are:
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.
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.
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.