An introduction to Data-Tier applications in SQL Server

An introduction to Data-Tier applications in SQL Server

In this article, I’m going to introduce the data-tier applications in SQL Server. As the official documentation from Microsoft says – “Data-tier applications in SQL Server are a logical entity that can be used to develop and manage most of the SQL Server objects like tables, views, stored procedures, functions etc. as a self-contained package“. Essentially, what that means is it is a component of SQL Server, using which we can develop, build, test and deploy databases for SQL Server just like we can do for any other web or desktop applications.

Data-tier applications in SQL Server were initially released around a decade back with SQL Server 2008 R2. The build of a data-tier application is a DACPAC file – Data-tier AppliCation PACkage, also known as a DAC file in short. A DAC is just another compressed zip folder that contains the model of the database that is being designed in XML format along with some other files necessary for the deployment. Using a DAC, developers can develop DDL scripts for each object in the database and these scripts will be built into a DAC file, which can later be used as a deployment utility to publish the databases to various SQL Server environments. This approach of deploying SQL Server databases has reduced the hassle of managing multiple migration scripts which in other cases the developers need to maintain for each version.

 

Like the DACPAC file, the BACPAC file is also a compressed zip archive which can be renamed into a ZIP extension and we can view the contents within. I have renamed the extension of the file to zip and extracted the contents. 

As you can see in the figure above, in addition to the contents of the DACPAC file, we have two new directories in this – “_rels” and “Data“. In the Data directory, you can see a directory has been created for all the tables that you’ve selected while exporting the BACPAC file. 

 

Conclusion

In this article, I have explained what data-tier applications in SQL Server are. I have also explained how to create a DACPAC and BACPAC using SQL Server Management Studio. DACPAC files are just a copy of the schema from the entire database without any data whereas, in the BACPAC file, data is included as well. In my upcoming articles for data-tier applications in SQL Server, I’ll explain how to start developing the same from scratch using Visual Studio and SQL Server Data Tools.

 

Extract Data-tier Application只导出schema

Export Data-tier Application不仅导出schema，还能导出表里面的数据

 

 

Export a Data-tier Application

Exporting a deployed data-tier application (DAC) or database creates an export file that includes both the definitions of the objects in the database and all of the data contained in the tables. The export file can then be imported to another instance of the Database Engine, or to Azure SQL Database. The export-import operations can be combined to migrate a DAC between instances, to create an archive, or to create an on-premises copy of a database deployed in SQL Database.

Before You Begin

The export process builds a DAC export file in two stages.

1. The export builds a DAC definition in the export file - BACPAC file - in the same way a DAC extract builds a DAC definition in a DAC package file. The exported DAC definition includes all of the objects in the current database. If the export process is run against a database that was originally deployed from a DAC, and changes were made directly to the database after deployment, the exported definition matches the object set in the database, not what was defined in the original DAC.

2. The export bulk copies out the data from all of the tables in the database and incorporates the data into the export file.

The export process sets the DAC version to 1.0.0.0 and the DAC description in the export file to an empty string. If the database was deployed from a DAC, the DAC definition in the export file contains the name given to the original DAC, otherwise the DAC name is set to the database name.

Limitations and Restrictions

A DAC or database can only be exported from a database in SQL Database, or SQL Server 2005 (9.x) Service Pack 4 (SP4) or later.

You cannot export a database that has objects that are not supported in a DAC, or contained users. For more information about the types of objects supported in a DAC, see DAC Support For SQL Server Objects and Versions.

Permissions

Exporting a DAC requires at least ALTER ANY LOGIN and database scope VIEW DEFINITION permissions, as well as SELECT permissions on sys.sql_expression_dependencies. Exporting a DAC can be done by members of the securityadmin fixed server role who are also members of the database_owner fixed database role in the database from which the DAC is exported. Members of the sysadmin fixed server role or the built-in SQL Server system administrator account named sa can also export a DAC.

On Azure SQL Database you need to grant for each database VIEW DEFINITION and SELECT permission on all tables or on specific tables

Using the Export Data-tier Application Wizard

To Export a DAC Using a Wizard

1. Connect to the instance of SQL Server, whether on-premises or in SQL Database.

2. In Object Explorer, expand the node for the instance from which you want to export the DAC.

3. Right-click the database name.

4. Click Tasks and then select Export Data-tier Application...

5. Complete the wizard dialogs:

   • Introduction Page

   • Export Settings Page

   • Validation Page

   • Summary Page

   • Progress Page

   • Results Page

Introduction Page

This page describes the steps for the Export Data-tier Application Wizard.

Options

Do not show this page again. - Click the check box to stop the Introduction page from being displayed in the future.

Next - Proceeds to the Select DAC Package page.

Cancel - Cancels the operation and closes the Wizard.

Export Settings Page

Use this page to specify the location where you want the BACPAC file to be created.

• Save to local disk - Creates a BACPAC file in a directory on the local computer. Click Browse... to navigate the local computer, or specify the path in the space provided. The path name must include a file name and the .bacpac extension.

• Save to Azure - Creates a BACPAC file in an Azure container. You must connect to an Azure container in order to validate this option. Note that this option also requires that you specify a local directory for the temporary file. Note that the temporary file will be created at the specified location and will remain there after the operation completes.

To specify a subset of tables to export, use the Advanced option.

Validation Page

Use the validation page to review any issues that block the operation. To continue, resolve blocking issues and then click Re-run Validation to ensure that validation is successful.

To continue, click Next.

Summary Page

Use this page to review the specified source and target settings for the operation. To complete the export operation using the specified settings, click Finish. To cancel the export operation and exit the Wizard, click Cancel.

Progress Page

This page displays a progress bar that indicates the status of the operation. To view detailed status, click the View details option.

Results Page

This page reports the success or failure of the export operation, showing the results of each action. Any action that encountered an error will have a link in the Result column. Click the link to view a report of the error for that action.

Click Finish to close the Wizard.

Using a .Net Framework Application

To export a DAC using the Export() method in a .Net Framework application.

1. Create a SMO Server object and set it to the instance that contains the DAC to be exported.

2. Open a ServerConnection object and connect to the same instance.

3. Use the Export method of the Microsoft.SqlServer.Management.Dac.DacStore type to export the DAC. Specify the name of the DAC to be exported, and the path to the folder where the export file is to be placed.

See Also

Data-tier Applications
Extract a DAC From a Database

 

作者:Chuck Lu    GitHub