Updates from the 162.3 release of SqlPackage and the DacFx ecosystem

[Guest DrewSkwiersKoballa]

The quarterly release cadence for SqlPackage and DacFx continues with the 162.3.563 release on June 6, 2024. The most notable change in this version is the preview release of a target platform for Fabric mirrored SQL databases. The 162.3.563 release also includes fixes related to script parsing (ScriptDOM) and deployments. Read more about the fixes in the SqlPackage release notes.

 

 

 

[HEADING=1]Target platforms in SQL projects[/HEADING]

 

SQL projects are a logical framework around your database code and this sets the foundation for two development capabilities:

 

1. Build-time validation of references between objects and the syntax against a specific version of SQL
   
2. Deployment of the build artifact to new or existing databases
   

 

When a SQL project is built, the relationships between objects contained in the project files are validated. For example, the columns or tables referenced in a view definition must exist in the SQL project.

 

 

 

SQL project files in VS Code

 

 

 

Additionally, a SQL project contains a property called the "target platform" in the DSP element of the .sqlproj file. This setting is used during the build process to validate that the functions and T-SQL syntax is supported in the specified version of SQL. For example, the JSON functions added in SQL Server 2022 cannot be used in a SQL project set to the SQL Server 2017 (Sql140DatabaseSchemaProvider) target platform.

 

Mirroring your SQL database to Microsoft Fabric provides a streamlined experience to avoid complex ETL (Extract Transform Load) and integrate existing your Azure SQL Database estate with the rest of your data in Microsoft Fabric. In the new target platform for “Fabric mirrored SQL database (preview)” (SqlDbFabricDatabaseSchemaProvider) added in DacFx 162.3.563, the tables in the SQL project are validated for compatibility with mirroring to Microsoft Fabric. Having a target platform for a mirrored SQL database means that you can develop your Azure SQL application with confidence that the application’s data will be available for analytics with minimal additional effort.

 

 

 

Diagram of Fabric Database Mirroring for Azure SQL Database

 

The target platform is available immediately for use with SDK-style projects with the Microsoft.Build.Sql project SDK version 0.1.19-preview. Your existing SDK-style SQL projects can be moved from the Azure SQL Database target platform to the Fabric mirrored SQL database (preview) target platform by updating the version of the Sdk to 0.1.19-preview (line 3 below) and the DSP (target platform) to SqlDbFabricDatabaseSchemaProvider (line 7 below). For a walkthrough of mirroring your Azure SQL Database to Microsoft Fabric, check out the tutorial.

 

 

 

 

The "Fabric mirrored SQL database (preview)" target platform will be available directly in the SQL projects extension for VS Code and Azure Data Studio as well as Visual Studio in the upcoming months. In the meantime, SQL projects can be built and deployed with the new target platform from the command line with dotnet build and SqlPackage.

 

 

 

[HEADING=1]SqlPackage and SQL projects on arm64[/HEADING]

 

GitHub recently announced arm64 actions runners with the initial availability in public preview of images built on Ubuntu 22.04. Whether you’re using the recently launched arm64 runners, an Apple M-series workstation, or one of the arm-powered Surface devices the base requirements for working with SqlPackage and SDK-style SQL projects remain the same – the .NET SDK must be installed. The current Ubuntu 22.04 arm64 preview image includes the .NET SDK.

 

With the .NET SDK installed in an arm64 environment such as the GitHub actions arm64 runners, your SQL development workflow can leverage the same commands as x64 environments. Dotnet build is still used to create a dacpac from a SQL project, but you may need to install SqlPackage as a dotnet tool to perform additional actions:

 

 

 

 

 

[iCODE]dotnet install -g microsoft.sqlpackage[/iCODE]

 

 

 

 

 

Once SqlPackage is installed it can be used as usual, including deploying (publish) a dacpac to update the schema a database or to extract the objects in a database out to files.

 

 

 

 

 

# publish
sqlpackage /Action:Publish /SourceFile:bin/Debug/AdventureWorks.dacpac /TargetConnectionString:<YourConnectionString>

# extract
sqlpackage /Action:Extract /SourceConnectionString:<YourConnectionString> /TargetFile:AdventureWorks /p:ExtractTarget=SchemaObjectType

 

 

 

 

 

For developers interested in continuing to use the original SQL projects in Visual Studio, we also note that SQL Server Data Tools (SSDT) has been released for Visual Studio on arm64.

 

 

 

[HEADING=1].gitignore with SQL projects[/HEADING]

 

You may recall about a year ago we introduced project templates for quickly starting a new SQL project from the command line. Earlier this year an option was added to the templates (-g) which includes a default gitignore file with a new SQL project. With a .gitignore file you can avoid accidentally checking cached binaries in the bin and obj folders into source control and minimize noise in your source control history.

 

Installing the SQL project templates and creating a new SQL project with the Fabric mirrored SQL database (preview) target platform and a gitignore file is completed with these two commands:

 

 

 

 

 

dotnet new install Microsoft.Build.Sql.Templates

dotnet new sqlproj -n "AdventureWorksLT" -tp "SqlDbFabric" -g

 

 

 

 

 

 

 

[HEADING=1]Ways to get involved[/HEADING]

 

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. Eagle-eyed readers will notice that the last preview release prior to the full release only differs by a single patch build. Get the quick instructions on installing and updating the preview releases in the SqlPackage documentation.

 

 

 

All are welcome to stop by the GitHub repository to provide feedback, whether it is bug reports, questions, or enhancement suggestions. Here are a few recent feature suggestions you might want to weigh in on:

 

• Support for dotnet publish Support dotnet publish · Issue #447 · microsoft/DacFx
  
• Composite projects in schema comparison Visual Studio SQL Schema Compare - Targeting Split/Composite SQL Server Database Projects · Issue #437 · microsoft/DacFx
  
• Automatic skipping of Windows logins SqlPackage - option to skip Windows logins · Issue #426 · microsoft/DacFx
  

 

Continue reading...