Migrating database to new SDK style SQL project and containerising it

Using .NET Framework based SQL Project for MS SQL Server database in very common and have been around for years. With recent changes in dotnet and focus on cross-platform compatibility, it became necessary to have a cross-platform project equivalent to old style SQL Project. This is made possible with DacFx components by Microsoft.

If you have database projects targetting MS SQL server with hard dependency on Windows and .NET Framework, this article will help you migrate to a new SDK style project which can be developed, built and published from Linux, OSX or even Docker Containers.

Below are the steps on how to migrate the existing project. You can use the same for a new project too.

Install MSBuild SQL Project Templates

dotnet new install Microsoft.Build.Sql.Templates

Initialise new SQL project

If you are planning to remove the old database project, initialise the new SQL project in the same directory as the existing project. And delete existing SQL project.

dotnet new sqlproj -n <project-name>

If you are planning to keep both projects for any reason, copy database objects (.sql) scripts from existing project to new project

cp -r <path-to-existing-project> <path-to-new-project>

Update .sqlproj file

Add missing properties and items so the SQL project file would look like -

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="0.1.12-preview" />
  <PropertyGroup>
    <Name>Database.DacFx</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
  </PropertyGroup>

  <!-- Compilation properties -->
  <PropertyGroup>
      <GenerateCreateScript>True</GenerateCreateScript>
      <IncludeCompositeObjects>True</IncludeCompositeObjects>
  </PropertyGroup>

  <!-- Pre- and Post-Deployment scripts -->
  <ItemGroup>
      <PostDeploy Include="Scripts\PostDeployment.sql" />
      <PreDeploy Include="Scripts\PreDeployment.sql" />
  </ItemGroup>
</Project>

Build project

dotnet build

After build, dacpac file and a SQL file will be generated in bin/Debugdirectory which can be used to deploy to target database or generate diff scripts.

Publish project

Here, we will use sqlpackage to deploy SQL project to database.

Install sqlpackage as dotnet tool

dotnet tool install -g microsoft.sqlpackage

Publish to MS SQL LocalDB that is installed with Visual Studio data tools

sqlpackage /Action:Publish /SourceFile:"bin/Debug/Database.DacFx.dacpac"  /TargetServerName:"(localdb)\MSSQLLocalDB" /TargetDatabaseName:DatabaseNameHere

Once done, you can verify the deployed database using SQL Server Management Studio. You should see a database with all objects created and Pre- and Post-Deployment scripts run as specified in SQL Project.

Containerise database

Here I have created multistaged Dockerfile, one for build using dotnet sdk base image and one for sql server using mssql base image.

The first stage uses dotnet CLI to build sqlproj and generate create SQL script.

FROM mcr.microsoft.com/dotnet/sdk:8.0 AS build
LABEL maintainer="Iqan Shaikh"
WORKDIR /src
COPY Database.DacFx/Database.DacFx.sqlproj .
RUN dotnet restore
COPY Database.DacFx .
COPY init-db.sh .
RUN dotnet build

FROM mcr.microsoft.com/mssql/server:2022-CU13-ubuntu-22.04 AS server
WORKDIR /db
COPY --from=build /src/bin/Debug/Database.DacFx_Create.sql .
COPY --from=build /src/init-db.sh .
RUN ./init-db.sh Database.DacFx_Create.sql

This script is used to publish the database objects to the database at build time. This is not necessary, but a way of ensuring the database image have got all objects and some seed data in it when we run the container. It speeds up testing and debugging in local machine.

timeout=20
echo "Setting Environment variables."
export ACCEPT_EULA=Y
export MSSQL_SA_PASSWORD=SuperSecretPassword#1
echo "Environment variables set."
echo "Starting SqlServr..."
/opt/mssql/bin/sqlservr &
sleep $timeout | echo "Waiting for $timeout seconds to start Sql Server"
echo "Creating DB..."
/opt/mssql-tools/bin/sqlcmd -U sa -P SuperSecretPassword#1 -q "CREATE DATABASE Database.DacFx"
/opt/mssql-tools/bin/sqlcmd -U sa -P SuperSecretPassword#1 -i $1
echo "DB created."

Here, timeout is seconds to wait for MS SQL server to start in intermediate container while building. Depending on machine this build process is done, you may need to update it (increase if database creation or post-deployment scripts isn’t run).

To build docker image

docker build -t <tag-for-image>.

To run SQL Server in linux container with your database preloaded

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=SuperSecretPassword#1" \
   -p 1433:1433 --name sql1 --hostname sql1 \
   <tag-for-image-provided-in-build>

This concludes this article on how to migrate old SQL Database project to new SDK style SQL Project which can be developed, built and published on any OS and in Docker containers.

Thanks for reading the article. Hope it helped.

Feel free to have a look at the repository which contains source code used in this article. Any questions or issues, feel free to ask.

GitHub - iqan/sql-project-with-dacfx: Contains source code for database project and documentation…

Have a good day ahead :)

References

• DacFx — https://github.com/microsoft/DacFx
• Quick start — MS SQL Docker — https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver16&pivots=cs1-bash
• sqlcmd — https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-use-utility
• sqlpackage — https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage