DacFx

The DacFx ecosystem consists of several specialized tools and libraries:

• SqlPackage: Cross-platform CLI for .dacpac/.bacpac management.
• DacFx (Microsoft.SqlServer.DacFx): .NET library for database application lifecycle services.
• DacpacVerify: CLI for checking if two .dacpac packages match.
• Microsoft.Build.Sql: .NET project SDK for compiling T-SQL into .dacpac files.
• ScriptDom: T-SQL parser library.
• Dacpacs (various): NuGet packages containing .dacpac files for system databases (master, msdb) across various SQL Server and Azure SQL versions.

The Microsoft.Build.Sql SDK provides MSBuild support for SQL projects, similar to how Microsoft.NET.Sdk works for .NET. It allows you to store database objects as code in .sql files. When you run dotnet build, the SDK aggregates these files and generates a .dacpac artifact, which can be used to deploy the database schema to a target database.

Key integrations include:

• VS Code & Azure Data Studio: SQL database projects extension
• Visual Studio: SQL Server Data Tools (SSDT)
• Deployment: Use the Microsoft.SqlPackage .NET tool to deploy .dacpac files or update projects from existing databases.

The Microsoft.Build.Sql SDK uses a default globbing pattern to automatically include all **/*.sql files from the project root in the build.

• Manual Exclusion: To prevent a specific .sql file from being included, add <Build Remove="filename.sql"/> to your project file.
• Pre/Post-Deployment Scripts: Files specified via <PreDeploy> or <PostDeploy> tags are automatically excluded from the standard build/validation process but are included for deployment.

After packaging the project, you need to make the NuGet package available to your SQL projects. You can do this by adding a local folder as a NuGet source and copying the package into it.

1. Add a local folder as a NuGet source using dotnet nuget add source <path>.
2. Copy the .nupkg file from the bin/Release directory of the code analysis project to that local source folder.

dotnet nuget add source c:\packages

To enable the code analysis rules in your SQL project, follow these two steps:

1. Reference the package: Add a PackageReference to your .sqlproj file pointing to the code analysis NuGet package.
2. Enable analysis: You must explicitly enable code analysis during the build process. You can do this by setting the <RunSqlCodeAnalysis> property to True in your project file, or by passing the property via the command line.

If enabled, the code analysis output will be generated in the build log.

<ItemGroup>
    <PackageReference Include="Sample.WaitForDelay" Version="1.0.0" />
</ItemGroup>

To run the analysis via CLI:

dotnet build /p:RunSqlCodeAnalysis=True

Use the sqlproj template to scaffold a new SQL project. You can specify the project name, the target platform (e.g., for Azure SQL Database), and whether to include a .gitignore file.

# Create a basic project named AdventureWorks
dotnet new sqlproj -n "AdventureWorks"

# Create a project for Azure SQL Database (SqlAzureV12)
dotnet new sqlproj -n "AdventureWorksLT" -tp "SqlAzureV12"

# Create a project with a .gitignore file included
dotnet new sqlproj -n "AdventureWorks" -g

# Display help for the sqlproj template
dotnet new sqlproj -h

If you are contributing to or customizing the templates, you can build them and install the resulting .nupkg locally. The build process automatically generates a NuGet package in the bin/Debug directory.

dotnet build
dotnet new install bin/Debug/Microsoft.Build.Sql.Templates.1.0.0.nupkg

To create a new SQL project using the sqlproj template, first install the Microsoft.Build.Sql.Templates NuGet package, then use the dotnet new command with the sqlproj template.

1. Install templates:

dotnet new install Microsoft.Build.Sql.Templates

2. Create project:

dotnet new sqlproj -n <ProjectName>

dotnet new install Microsoft.Build.Sql.Templates
dotnet new sqlproj -n ProductsTutorial

To convert a legacy SQL project to the SDK-style Microsoft.Build.Sql format, the primary step is to update the .sqlproj file to use the SDK.

Recommended Workflow:

1. Build the existing project.
2. Retain the current .dacpac for comparison after conversion.
3. Edit the .sqlproj file and add the <Sdk> element inside the <Project> tag.

Conversion Step: Add the following line inside the <Project> tag of your .sqlproj file:

<Sdk Name="Microsoft.Build.Sql" Version="###ASSEMBLY_VERSION###" />

For a detailed walkthrough, refer to the official conversion guide.

<Project>
  <Sdk Name="Microsoft.Build.Sql" Version="###ASSEMBLY_VERSION###" />
  <!-- Other project content -->
</Project>

To deploy a .dacpac generated from a SQL project to a target instance, use the sqlpackage CLI. You can publish to a local SQL Server instance by specifying the publish action, the source dacpac file, the target server name, and the target database name.

sqlpackage /Action:Publish /SourceFile:bin/Debug/SqlProject1.dacpac /TargetServerName:localhost /TargetDatabaseName:SqlProject1