D
DrewSkwiersKoballa
SQL Server Data Tools (SSDT) in Visual Studio provides a development environment for SQL Server, Azure SQL, and Synapse Data Warehouse databases based on SQL projects. SQL projects enable database development to directly integrate with DevOps workflows, including functionality for connected databases to be converted into code and for databases to be updated from the SQL projects build artifact (a .dacpac file). Microsoft.Build.Sql is a project SDK that modernizes the well-established capabilities of SQL Server Data Tools, improving the flexibility and compatibility of SQL projects. In the preview 2 release of Visual Studio 17.12, Microsoft.Build.Sql SDK-style SQL projects begin their preview availability in Visual Studio as the component “SQL Server Data Tools, SDK-style (preview).”
Declarative development with SQL projects creates an environment where source control contains the source of truth for a database and the provided tooling facilitates change testing and deployment. The Microsoft.Build.Sql project SDK brings SQL projects to .NET Core with an expanded feature set for improved usability in CI/CD scenarios. The Microsoft.Build.Sql project SDK improves on the original SQL projects with:
SQL projects based on the Microsoft.Build.Sql SDK can be readily incorporated into automated pipelines because of its lightweight dependency on the .NET SDK instead of both .NET Framework and externally managed project targets. A few examples of CI/CD concepts with SQL projects capabilities are:
SDK-style SQL Server Data Tools is based on the same project SDK (Microsoft.Build.Sql) as the SQL Database Projects extension for VS Code and Azure Data Studio. When the project is built in these tools or with the direct command dotnet build, the .dacpac build artifact is compatible with the SqlPackage command line tool and existing Azure DevOps/GitHub Actions tasks. Guidance for converting existing original SQL projects to SDK-style SQL projects is included in the SQL projects documentation for teams that want to explore the SDK-style projects preview.
In preview 2 of Visual Studio 17.12, the file extension for the SQL project file is .sqlprojx instead of .sqlproj as part of limitations with the side by side functionality of original style and SDK-style SQL projects in Visual Studio. It can be expected that SDK-style SSDT will standardize on .sqlproj as the file extension for both original and SDK-style projects in future releases. SDK-style SQL Server Data Tools is not supported for side-by-side install with the original SQL Server Data Tools, so a separate Visual Studio install is recommended from your primary development environment at this time.
Visual Studio 17.12 preview 2 ships with SDK-style SQL projects based on Microsoft.Build.Sql 0.2.0-preview and a limited subset of the interface components from the original SQL Server Data Tools. Implementation of SSDT functionality such as schema compare and table designer will be completed for SDK-style SQL projects in future releases of Visual Studio. As a rich developer environment, there is a significant feature set to complete in Visual Studio to replace original SQL projects with SQL projects based on Microsoft.Build.Sql. In the process of upgrading SSDT to SDK-style projects, each component is being investigated for stability and performance such that your experience with Visual Studio and SSDT is smooth and productive.
As mentioned previously in this post, package references to code analysis extension libraries is coming in a future release. SQL code analysis rules allow SQL projects to check for best practices during local development and as part of CI checks. The SQL projects code analysis rules are extensible such that you can include additional rules from community contributors or your own environment.
The release candidate for the SQL projects SDK, Microsoft.Build.Sql, is nearing completion. Your feedback and contributions are welcome at the DacFx GitHub repository, where development of the SQL projects SDK Microsoft.Build.Sql takes place.
Download the preview version of Visual Studio and install the preview of SQL Server Data Tools, SDK-style projects to try out the first iteration of SDK-style SQL projects support in Visual Studio. A separate Visual Studio install is recommended from your primary development environment at this time to avoid side-by-side install with the original SQL Server Data Tools. More information on the preview of SDK-style SQL projects in Visual Studio is available in the SSDT documentation and your feedback is encouraged in the Visual Studio Developer Community.
If you’re diving into the full SQL projects capabilities, the universal SQL projects section of documentation is recommended to get to know the entire SQL projects ecosystem.
Continue reading...
Key functionality
Declarative development with SQL projects creates an environment where source control contains the source of truth for a database and the provided tooling facilitates change testing and deployment. The Microsoft.Build.Sql project SDK brings SQL projects to .NET Core with an expanded feature set for improved usability in CI/CD scenarios. The Microsoft.Build.Sql project SDK improves on the original SQL projects with:
- Cross-platform support with .NET: In addition to support for .NET Framework, Microsoft.Build.Sql supports dotnet build with .NET 6+
- Default inclusion of all *.sql files: Original-style projects have a verbose project file where each object is explicitly listed, while Microsoft.Build.Sql includes every *.sql file within the project’s directory tree
- Modernized database references: Package feed references (nupkg) for database components (same or different database)
- Code analysis extensibility: Package references for code analysis extensions (coming soon!)
SQL projects based on the Microsoft.Build.Sql SDK can be readily incorporated into automated pipelines because of its lightweight dependency on the .NET SDK instead of both .NET Framework and externally managed project targets. A few examples of CI/CD concepts with SQL projects capabilities are:
- SQL projects build with code analysis for a continuous integration (CI) check during pull request review
- Deployment to temporary test environments in isolation or as part of broader unit testing
- Ongoing staging to production deployment report generation to anticipate and plan for significant upcoming changes
Tooling compatibility
SDK-style SQL Server Data Tools is based on the same project SDK (Microsoft.Build.Sql) as the SQL Database Projects extension for VS Code and Azure Data Studio. When the project is built in these tools or with the direct command dotnet build, the .dacpac build artifact is compatible with the SqlPackage command line tool and existing Azure DevOps/GitHub Actions tasks. Guidance for converting existing original SQL projects to SDK-style SQL projects is included in the SQL projects documentation for teams that want to explore the SDK-style projects preview.
In preview 2 of Visual Studio 17.12, the file extension for the SQL project file is .sqlprojx instead of .sqlproj as part of limitations with the side by side functionality of original style and SDK-style SQL projects in Visual Studio. It can be expected that SDK-style SSDT will standardize on .sqlproj as the file extension for both original and SDK-style projects in future releases. SDK-style SQL Server Data Tools is not supported for side-by-side install with the original SQL Server Data Tools, so a separate Visual Studio install is recommended from your primary development environment at this time.
Visual Studio 17.12 preview 2 ships with SDK-style SQL projects based on Microsoft.Build.Sql 0.2.0-preview and a limited subset of the interface components from the original SQL Server Data Tools. Implementation of SSDT functionality such as schema compare and table designer will be completed for SDK-style SQL projects in future releases of Visual Studio. As a rich developer environment, there is a significant feature set to complete in Visual Studio to replace original SQL projects with SQL projects based on Microsoft.Build.Sql. In the process of upgrading SSDT to SDK-style projects, each component is being investigated for stability and performance such that your experience with Visual Studio and SSDT is smooth and productive.
Roadmap for SDK-style SQL projects
As mentioned previously in this post, package references to code analysis extension libraries is coming in a future release. SQL code analysis rules allow SQL projects to check for best practices during local development and as part of CI checks. The SQL projects code analysis rules are extensible such that you can include additional rules from community contributors or your own environment.
The release candidate for the SQL projects SDK, Microsoft.Build.Sql, is nearing completion. Your feedback and contributions are welcome at the DacFx GitHub repository, where development of the SQL projects SDK Microsoft.Build.Sql takes place.
Get started today
Download the preview version of Visual Studio and install the preview of SQL Server Data Tools, SDK-style projects to try out the first iteration of SDK-style SQL projects support in Visual Studio. A separate Visual Studio install is recommended from your primary development environment at this time to avoid side-by-side install with the original SQL Server Data Tools. More information on the preview of SDK-style SQL projects in Visual Studio is available in the SSDT documentation and your feedback is encouraged in the Visual Studio Developer Community.
If you’re diving into the full SQL projects capabilities, the universal SQL projects section of documentation is recommended to get to know the entire SQL projects ecosystem.
Previous posts on SQL projects, SqlPackage, and DacFx
- Updates from the 162.3 release of SqlPackage and the DacFx ecosystem
- Announcing SQL Server Data Tools (SSDT) for ARM64 Architecture in Visual Studio 17.10 Preview 2
- Updates from 162.1 and 162.2 releases of SqlPackage and the DacFx ecosystem
- Recently released: Updates to SqlPackage and the DacFx ecosystem
- Announcing General Availability of Azure Data Studio extension SQL Database Projects
- Microsoft.Build.Sql: the next frontier of SQL projects
Continue reading...