Within the past 4 months, we’ve had 2 minor releases and a patch release for SqlPackage. In this article, we’ll recap the features and notable changes from SqlPackage 162.1 (October 2023) and 162.2 (February 2024). Several new features focus on giving you more control over the performance of deployments by preventing potential costly operations and opting in to online operations. We’ve also introduced an alternative option for data portability that can provide significant speed improvements to databases in Azure. Read on for information about these improvements and more, all from the recent releases in the DacFx ecosystem. Information on features and fixes is available in the itemized release notes for SqlPackage.
.NET 8 support
The 162.2 release of DacFx and SqlPackage introduces support for .NET 8. SqlPackage installation as a dotnet tool is available with the .NET 6 and .NET 8 SDK. Install or update easily with a single command if the .NET SDK is installed:
# install
dotnet tool install -g microsoft.sqlpackage
# update
dotnet tool update -g microsoft.sqlpackage
Online index operations
Starting with SqlPackage 162.2, online index operations are supported during publish on applicable environments (including Azure SQL Database, Azure SQL Managed Instance, and SQL Server Enterprise edition). Online index operations can reduce the application performance impact of a deployment by supporting concurrent access to the underlying data. For more guidance on online index operations and to determine if your environment supports them, check out the SQL documentation on guidelines for online index operations.
Directing index operations to be performed online across a deployment can be achieved with a command line property new to SqlPackage 162.2, “PerformIndexOperationsOnline”. The property defaults to false, where just as in previous versions of SqlPackage, index operations are performed with the index temporarily offline. If set to true, the index operations in the deployment will be performed online. When the option is requested on a database where online index operations don’t apply, SqlPackage will emit a warning and continue the deployment.
An example of this property in use to deploy index changes online is:
sqlpackage /Action:Publish /SourceFile:yourdatabase.dacpac /TargetConnectionString:”yourconnectionstring” /p:PerformIndexOperationsOnline=True
More granular control over the index operations can be achieved by including the ONLINE=ON/OFF keyword in index definitions in your SQL project. The online property will be included in the database model (.dacpac file) from the SQL project build. Deployment of that object with SqlPackage 162.2 and above will follow the keyword used in the definition, superseding any options supplied to the publish command. This applies to both ONLINE=ON and ONLINE=OFF settings.
DacFx 162.2 is required for SQL project inclusion of ONLINE keywords with indexes and is included with the Microsoft.Build.Sql SQL projects SDK version 0.1.15-preview. For use with non-SDK SQL projects, DacFx 162.2 will be included in future releases of SQL projects in Azure Data Studio, VS Code, and Visual Studio. The updated SDK or SQL projects extension is required to incorporate the index property into the dacpac file. Only SqlPackage 162.2 is required to leverage the publish property “PerformIndexOperationsOnline”.
Block table recreation
With SqlPackage publish operations, you can apply a new desired schema state to an existing database. You define what object definitions you want in the database and pass a dacpac file to SqlPackage, which in turn calculates the operations necessary to update the target database to match those objects. The set of operations are known as a “deployment plan”.
A deployment plan will not destroy user data in the database in the process of altering objects, but it can have computationally intensive steps or unintended consequences when features like change tracking are in use. In SqlPackage 162.1.167, we’ve introduced an optional property, /p:AllowTableRecreation, which allows you to stop any deployments from being carried out that have a table recreation step in the deployment plan.
- /p:AllowTableRecreation=true (default) SqlPackage will recreate tables when necessary and use data migration steps to preserve your user data
- /p:AllowTableRecreation=false SqlPackage will check the deployment plan for table recreation steps and stop before starting the plan if a table recreation step is included
SqlPackage + Parquet files (preview)
Database portability, the ability to take a SQL database from a server and move it to a different server even across SQL Server and Azure SQL hosting options, is most often achieved through import and export of bacpac files. Reading and writing the singular bacpac files can be difficult when databases are over 100 GB and network latency can be a significant concern. SqlPackage 162.1 introduced the option to move the data in your database with parquet files in Azure Blob Storage, reducing the operation overhead on the network and local storage components of your architecture.
Data movement in parquet files is available through the extract and publish actions in SqlPackage. With extract, the database schema (.dacpac file) is written to the local client running SqlPackage and the data is written to Azure Blob Storage in Parquet format. With publish, the database schema (.dacpac file) is read from the local client running SqlPackage and the data is read from or written to Azure Blob Storage in Parquet format.
The parquet data file feature benefits larger databases hosted in Azure with significantly faster data transfer speeds due to the architecture shift of the data export to cloud storage and better parallelization in the SQL engine. This functionality is in preview for SQL Server 2022 and Azure SQL Managed Instance and can be expected to enter preview for Azure SQL Database in the future. Dive into trying out data portability with dacpacs and parquet files from the SqlPackage documentation on parquet files.
Microsoft.Build.Sql
The Microsoft.Build.Sql library for SDK-style projects continues in the preview development phase and version 0.1.15-preview was just released. Code analysis rules have been enabled for execution during build time with .NET 6 and .NET 8, opening the door to performing quality and performance reviews of your database code on the SQL project. To enable code analysis rules on your project, add the item seen on line 7 of the following sample to your project definition (<RunSqlCodeAnalysis>True</RunSqlCodeAnalysis>).
<Project DefaultTargets="Build">
<Sdk Name="Microsoft.Build.Sql" Version="0.1.15-preview" />
<PropertyGroup>
<Name>synapseexport</Name>
<DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
<ModelCollation>1033, CI</ModelCollation>
<RunSqlCodeAnalysis>True</RunSqlCodeAnalysis>
</PropertyGroup>
</Project>
During build time, the objects in the project will be checked against a default set of code analysis rules. Code analysis rules can be customized through DacFx extensibility.
Ways to get involved
In early 2024, we added preview releases of SqlPackage to the dotnet tool feed, such that not only do you have early access to DacFx changes but you can directly test SqlPackage as well. Get the quick instructions on installing and updating the preview releases in the SqlPackage documentation.
Most of the issues fixed in this release were reported through our GitHub community, and in several cases the person reporting put together great bug reports with reproducing scripts. Feature requests are also discussed within the GitHub community in some cases, including the online index operations and blocking table recreation capabilities. All are welcome to stop by the GitHub repository to provide feedback, whether it is bug reports, questions, or enhancement suggestions.
