Редагувати

Поділитися через

Convert an original SQL project to an SDK-style project

  • Стаття

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Creating a new SDK-style SQL project is a quick task. However, if you have existing SQL projects you can convert them to SDK-style SQL projects in place to take advantage of the new features.

Once you convert the project, you can use the new features of the SDK-style project, such as:

  • cross-platform build support
  • simplified project file format
  • package references

To complete the conversion carefully, we will:

  1. Create a backup of the original project file.
  2. Build a .dacpac file from the original project for comparison.
  3. Modify the project file to an SDK-style project.
  4. Build a .dacpac file from the modified project for comparison.
  5. Verify that the .dacpac files are the same.

SDK-style projects aren't supported by SQL Server Data Tools (SSDT) in Visual Studio. Once converted, you must use one of the following to build or edit the project:

  • the command line
  • the SQL Database Projects extension in Visual Studio Code
  • the SQL Database Projects extension in Azure Data Studio

Prerequisites

Step 1: Create a backup of the original project file

Before you convert the project, create a backup of the original project file. This way, you can revert to the original project if needed.

In file explorer, create a copy of the .sqlproj file for the project you want to convert with .original appended on the end of the file extension. For example, MyProject.sqlproj becomes MyProject.sqlproj.original.

Step 2: Build a .dacpac file from the original project for comparison

Open the project in Visual Studio 2022. The .sqlproj file is still in the original format, so you open it in the original SQL Server Data Tools.

Build the project in Visual Studio by right-clicking on the database node in Solution Explorer and selecting Build.

To build a .dacpac file from the original project, you must use the original SQL Server Data Tools (SSDT) in Visual Studio. Open the project file in Visual Studio 2022 with the original SQL Server Data Tools installed.

Build the project in Visual Studio by right-clicking on the database node in Solution Explorer and selecting Build.

Open the project folder in VS Code or Azure Data Studio. In the Database Projects view of VS Code or Azure Data Studio, right-click the project node and select Build.

SQL database projects can be built from the command line using the dotnet build command.

dotnet build

# optionally specify the project file
dotnet build MyDatabaseProject.sqlproj

The build process creates a .dacpac file in the bin\Debug folder of the project by default. Using file explorer, locate the .dacpac created by the build process and copy it into a new folder outside of the project directory as original_project.dacpac. We use this .dacpac file for comparison to validate our conversion later.

Step 3: Modify the project file to an SDK-style project

Modifying the project file is a manual process, best performed in a text editor. Open the .sqlproj file in a text editor and make the following changes:

Required: Add the SDK reference

Inside the project element, add an Sdk item to reference Microsoft.Build.Sql and the latest version from https://www.nuget.org/packages/Microsoft.build.sql.

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build" ToolsVersion="4.0">
  <Sdk Name="Microsoft.Build.Sql" Version="0.2.0-preview" />
...

Required: Remove unnecessary build target imports

Original SQL projects reference several build targets and properties in Import statements. Except for <Import/> items you explicitly added, which is a unique and deliberate change, remove lines that begin with <Import ...>. Examples to remove if present in your .sqlproj:

...
<Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />
<Import Condition="..." Project="...\Microsoft.Data.Tools.Schema.SqlTasks.targets"/>
<Import Condition="'$(SQLDBExtensionsRefPath)' != ''" Project="$(SQLDBExtensionsRefPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
<Import Condition="'$(SQLDBExtensionsRefPath)' == ''" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
...

Required: Remove Properties folder

Original SQL projects have an entry for a Properties folder that represented access to the project properties in solution explorer. This item needs to be removed from the project file.

Example to remove if present in your .sqlproj:

<ItemGroup>
  <Folder Include="Properties" />
</ItemGroup>

Optional: Remove SSDT references

The original SQL Server Data Tools (SSDT) required extra content in the project file to detect the Visual Studio install. These lines are unnecessary in SDK-style SQL projects and can be removed:

  <PropertyGroup>
    <VisualStudioVersion Condition="'$(VisualStudioVersion)' == ''">11.0</VisualStudioVersion>
    <!-- Default to the v11.0 targets path if the targets file for the current VS version is not found -->
    <SSDTExists Condition="Exists('$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets')">True</SSDTExists>
    <VisualStudioVersion Condition="'$(SSDTExists)' == ''">11.0</VisualStudioVersion>
  </PropertyGroup>

Optional: Remove default build settings

Original SQL projects include two large blocks for Release and Debug build settings, while in SDK-style SQL projects the defaults for these options are known by the SDK. If you don't have customizations to the build settings, consider removing these blocks:

  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
    <OutputPath>bin\Release\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>False</TreatWarningsAsErrors>
    <DebugType>pdbonly</DebugType>
    <Optimize>true</Optimize>
    <DefineDebug>false</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    <OutputPath>bin\Debug\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>false</TreatWarningsAsErrors>
    <DebugSymbols>true</DebugSymbols>
    <DebugType>full</DebugType>
    <Optimize>false</Optimize>
    <DefineDebug>true</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>

Step 4: Build a .dacpac file from the modified project for comparison

The SQL project is no longer compatible with Visual Studio 2022. To build or edit the project, you must use one of:

  • the command line
  • the SQL Database Projects extension in Visual Studio Code
  • the SQL Database Projects extension in Azure Data Studio

The project file is now in the SDK-style format, but to open it in Visual Studio 2022, you must have the SQL Server Data Tools, SDK-style (preview) installed and the project must have the file extension .sqlprojx. Open the project in Visual Studio 2022 with SQL Server Data Tools, SDK-style (preview) installed.

Open the project folder in VS Code or Azure Data Studio. In the Database Projects view of VS Code or Azure Data Studio, right-click the project node and select Build.

SQL database projects can be built from the command line using the dotnet build command.

dotnet build

# optionally specify the project file
dotnet build MyDatabaseProject.sqlproj

The build process creates a .dacpac file in the bin\Debug folder of the project by default. Using file explorer, locate the .dacpac created by the build process and copy it into a new folder outside of the project directory. We use this .dacpac file for comparison to validate our conversion later.

Step 5: Verify that the .dacpac files are the same

To verify that the conversion was successful, compare the .dacpac files created from the original and modified projects. The schema comparison capabilities of SQL projects allow us to visualize the difference in database models.

You can use the schema compare tool in Visual Studio, Visual Studio Code, or Azure Data Studio to compare the .dacpac files. Community tools based on the DacFx .NET library are also available.

Launch Visual Studio without a project loaded. Go to Tools > SQL Server > New Schema Comparison. Select the original .dacpac file as the source and the modified .dacpac file as the target. For more on using Schema Compare in Visual Studio, see using schema compare to compare different database definitions.

Graphical schema comparison isn't yet available in the SDK-style SQL projects preview in Visual Studio. Use Azure Data Studio to compare schemas.

Schema comparison isn't available in Visual Studio Code. Use Azure Data Studio or Visual Studio to compare schemas.

In Azure Data Studio, install the SQL Server Schema Compare extension if it isn't already installed. Launch a new schema comparison from the command palette by opening the command palette with Ctrl/Cmd+Shift+P and typing Schema Compare.

Select the original .dacpac file as the source and the modified .dacpac file as the target.

Graphical schema comparison is available in Visual Studio and Azure Data Studio.

When schema comparison is run, no results should be displayed. The lack of differences indicates that the original and modified projects are equivalent, producing the same database model in the .dacpac file.

Note

The comparison of .dacpac files through schema comparison doesn't validate pre/post-deployment scripts, refactorlog, or other project settings. It only validates the database model. Converting the .dacpac to a .zip archive and manually comparing the contents can provide a more detailed comparison.

Related content