Recent updates for Data-Tier Application Framework and SqlPackage
In this article, we will cover some recent changes to the Data-Tier Application Framework (DacFx) and SqlPackage. DacFx is a library that provides APIs for database deployment and development scenarios and SqlPackage is a command line interface to DacFx for common uses. Earlier this year SqlPackage released updates including additional support for Azure Synapse Analytics and improved performance. The first preview for the 160 major version of DacFx is now available, containing the completed migration to Microsoft.Data.SqlClient. To facilitate discussions and sharing of code samples for DacFx and SqlPackage, a GitHub repository has been made available.
Recent Updates to SqlPackage
In recent updates for SqlPackage, 18.7 (March 2021) and 18.7.1 (June 2021), functionality was added specific to big data in Azure Synapse Analytics, options were added to improve import performance in some scenarios, and changes were made to make the troubleshooting and general use of SqlPackage easier.
With version 18.7 and above of SqlPackage, Azure Synapse Analytics gains improved portability through the Parquet flat file format. Through additional command line properties on the Extract operation (see documentation) data is written to flat files in Azure Blob Storage while the schema is stored in a Dacpac file. Flat files storing data can be published to a database along with a Dacpac through the Publish operation with similar command line properties. Both Extract and Publish operations have large performance improvements when writing to Azure Blob Storage for large database sizes.
In some SqlPackage import scenarios indexes have a large impact on the performance, whether it is an index assisting with data insertion speed or rebuilding indexes hampering other operations. To allow further tuning SqlPackage’s behavior in these cases, the command line properties “RebuildIndexesOfflineForDataPhase” and “DisableIndexesForDataPhase” were added to the Import action in SqlPackage 18.7.
If you are looking to better understand the SqlPackage operations for troubleshooting or performance improvements, there are some essential steps to capture more information. Enabling a diagnostics file with the parameter “/DiagnosticsFile:<filename>” is fundamental and in the latest SqlPackage release the version and architecture were standardized at the top of the file to assist in troubleshooting version differences. Trace data including timestamps for performance can be included by setting an environment variable “DACFX_PERF_TRACE” to true. To support environments where object names may contain Personally-identifiable information (PII) or otherwise confidential information, SqlPackage 18.7 added a property for all operations that hashes the object names in logs (“HashObjectNamesInLogs”).
DacFx v160 Preview
The DacFx v160 preview release is now available as a NuGet package for use in testing or updating existing applications to Microsoft.Data.SqlClient. External DacFx APIs are not altered by this update, however the dependency on System.Data.SqlClient is removed. An example application leveraging the v160 release of DacFx is available. Existing applications will continue to use the generally available v150 NuGet package unless a major version is not specified and preview releases are included in the package dependencies.
As a component of several SQL tools, including Azure Data Studio, SQL Server Management Studio, and SQL Server Data Tools in Visual Studio, DacFx v160 will be incorporated in those tools to bring functionality forward over the coming months.
DacFx GitHub Repository
Since the DacFx library is surfaced through multiple products, it can be difficult to locate information and provide feedback specific to DacFx and the SqlPackage CLI. To reduce barriers to collaboration, a repository is being opened with a focus on transparently triaging and addressing feedback. You are welcome to open a discussion around a particular use case and we have begun to gather previously reported items from other feedback channels. The code in the repository will comprise of current samples and will be updated based on questions and feedback. The DacFx team looks forward to an environment with a focus on the community’s use of DacFx and building more resources.
