MSBuild.Sdk.SqlProj - Streamlined Packaging for SQL Server Data-Tier Applications

MSBuild.Sdk.SqlProj Overview

MSBuild.Sdk.SqlProj is a versatile MSBuild SDK designed to simplify the creation of SQL Server Data-Tier Application packages, commonly known as .dacpac files, from a series of SQL scripts. Once created, these .dacpac files can be deployed using the Microsoft.SqlPackage tool. Introduced as a modern alternative to the traditional SQL Server Data Tools (.sqlproj project format), MSBuild.Sdk.SqlProj is built atop the new SDK-style projects that debuted with Visual Studio 2017.

Key Benefits

Seamless Integration: By embracing the advancements in .NET tools and infrastructure, MSBuild.Sdk.SqlProj performs tasks similar to SQL Server Data Tools, offering an intuitive interface for handling SQL Server projects.
Flexible Targeting: Users have the flexibility to target specific versions of SQL Server by setting the appropriate flags in the project setup, ensuring adaptability for different deployment environments.

Getting Started

To kick off a project with MSBuild.Sdk.SqlProj, users should start by installing the required templates with the command:

dotnet new install MSBuild.Sdk.SqlProj.Templates

From there, creating a new project is as simple as:

dotnet new sqlproj

For those not focusing on the latest SQL Server versions, the -s Sql<version> switch allows for specifying exact version targets, ensuring compatibility with existing infrastructure.

Project File Nuances

A typical project file in MSBuild.Sdk.SqlProj includes various settings such as the target framework, SQL Server version, and code analysis tools, alongside references to necessary packages for additional features and rules compliance.

Here's a glance at a basic project file setup:

<Project Sdk="MSBuild.Sdk.SqlProj/3.0.0">
    <PropertyGroup>
        <TargetFramework>netstandard2.1</TargetFramework>
        <SqlServerVersion>Sql150</SqlServerVersion>
        <RunSqlCodeAnalysis>True</RunSqlCodeAnalysis>
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="ErikEJ.DacFX.SqlServer.Rules" Version="1.1.1" />
        <PackageReference Include="ErikEJ.DacFX.TSQLSmellSCA" Version="1.1.1" />
    </ItemGroup>
</Project>

When built, this configuration results in a .dacpac file aligned with the project configurations.

Utilizing SQL Scripts

The project automatically includes all .sql files within the same directory, while providing the flexibility to exclude specific files by modifying the project file:

<Content Remove="Directory\File.sql" />

This tailored approach ensures that only the necessary scripts contribute to the final package.

Item Templates for Rapid Development

MSBuild.Sdk.SqlProj offers a variety of item templates supporting the creation of common database objects such as tables, views, stored procedures, and functions directly from command-line scripts. Developers can use commands like:

Creating a new table:
```
dotnet new table -n TableName
```
Building stored procedures:
```
dotnet new sproc -n ProcedureName
```

Advanced Configurations

The SDK supports SQLCMD variables for dynamic deployment scenarios, pre- and post-deployment scripts for complex execution requirements, package and project references for multi-package solutions, and detailed control over SQL warnings and errors during the build process.

Deployment and Packaging

Once the project is set up correctly, deploying it to SQL Server involves creating .dacpac files that include all necessary components. MSBuild.Sdk.SqlProj also enables developers to package their work into NuGet packages, facilitating easy distribution and reuse across different projects or teams.

Conclusion

MSBuild.Sdk.SqlProj equips developers with a powerful, modern toolset for managing SQL Server projects via SDK-style projects. It bridges the gap between legacy approaches and cutting-edge practices, delivering flexibility, consistency, and performance to SQL Server application development.