How to: Use a DAC Package to Migrate a Database to Azure SQL Database
Updated: October 16, 2014
Data-tier Applications (DAC) support easily extracting the schema, code, and configuration of a database into a single package file. The DAC package can then be used to either deploy a new copy of the database on another system, or import the database definition into a SQL Server Data Tools (SSDT) project for further development. DAC packages do not contain data, only the definitions of the objects in the database.
Before you begin:
Recommendations, Limitations and Restrictions, Prerequisites
Migrate a DAC Package
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 SQL Server objects, including database objects and instance objects, into a single entity called a DAC package (.dacpac file). There are two ways to generate a DAC package file. You can build an SSDT database project to create a DAC package, or you can extract a DAC package from an existing database. The DAC package is a compressed file that contains an XML representation of the database object definitions, or the metadata of the database. You can then deploy the package to create copy of the database in Microsoft Azure SQL Database.
A DAC package is a good tool to use with SQL Server Data Tools to implement any database changes required to migrate a database to Azure SQL Database. Import the DAC package to create a database project, make any required modifications, and then build the project to create a new DAC package.
Using a DAC package and an SSDT database project to transfer the schema in a migration is a good choice when there will be additional development work after the migration project is finished. DAC packages are versioned, and there is a DAC upgrade process. You can use one version of the DAC package to transfer the schema during the migration. If additional development work is done after the migration, you can build a new version of the DAC package and use that to upgrade the production database. For more information about DAC upgrades, see Upgrade a Data-tier Application.
A DAC package does not contain any of the table data, so can only be used to migrate schema definitions. Another process must be used to migrate the data.
Within an SSDT database project, you can specify predeployment and postdeployment scripts. These are Transact-SQL scripts that can perform any action, including inserting data in the postdeployment scripts. However it is not recommended to insert a large amount of data by using DAC package deployment scripts.
To work with DAC packages, 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.
To migrate a SQL Server database schema to Azure SQL Database, first extract a package from an existing database, remove any dependencies on objects not supported in Azure SQL Database, and then deploy the DAC package to Azure SQL Database.
Extract a DAC package from a SQL Server database:
You can extract a DAC package from an existing database in the SQL Server Database Engine using either a PowerShell script or the Extract Data-tier Application Wizard in SQL Server Management Studio. For information about prerequisites and how to perform an extraction, see Extract a DAC From a Database.
The extraction involves the following main steps:
Set the DAC properties, including DAC application name, version, description, and the package file location.
Validate that all the database objects are supported by a DAC.
Build the package.
- Set the DAC properties, including DAC application name, version, description, and the package file location.
Validate The DAC Package Before Deploying to Azure SQL Database:
It is a good practice to review the contents of a DAC package before deploying it into production, especially when the package was not developed in your organization. For more information, see Validate a DAC Package.
Before you can migrate a database to Azure SQL Database, remove any dependencies on objects that are reported as exceptions by either the DAC extraction process or the Azure SQL Database Compatibility Assessment service. Removing these objects from the database will probably require changes to the applications that use the database.
Deploy the DAC package to Azure SQL Database:
You can deploy a DAC package to ssSDS using either a PowerShell script or the Deploy Data-tier Application Wizard in SQL Server Management Studio. For information about prerequisites and how to perform a deployment, see Deploy a Data-tier Application. The Deploy Data-tier Application Wizard can also be launched from the Azure SQL Database Management Portal, for more information see Database Administration (Management Portal for Azure SQL Database).
The deployment involves the following main steps:
Select the DAC package.
Validate the content of the package.
Configure the database deployment properties, where you specify the database on Microsoft Azure SQL Database.
Deploy the package.
- Select the DAC package.