Deploying DacPac/BacPac to SqlServer during startup

[egil]

This is how I managed to get Aspire to spin up a SqlServer and deploy a DacPac/BacPac to the server to set up databases.

To get DacPac building working on Linux, I followed @ErikEJ's guide to set up Visual Studio Sql Server project and a companion netstandard build project, and then, I customized the sqlserver docker image to include dotnet sdk.

This is my solution structure:

/src/Database/                             (VS Sql Server project)
/src/Database.Build/                       (MSBuild.Sdk.SqlProj/2.6.1 project)
/src/AppHost/                              (Aspire host)
/src/AppHost/sql-data/                     (folder for storing .bacpac files)
/src/AppHost/sqlserverconfig/Docker
/src/AppHost/sqlserverconfig/entrypoint.sh
/src/AppHost/sqlserverconfig/configure-db.sh
/src ... other projects

Content of /src/AppHost/sqlserverconfig/Docker (remember to save with LF line endings):

# Use the official Microsoft SQL Server image as the base image
# with the exact version selected, as mssql-tools tend to move around
FROM mcr.microsoft.com/mssql/server:2022-CU14-ubuntu-22.04

# Switch to root user to install dependencies
USER root

# Install dependencies
RUN apt-get update && \
    apt-get install -y wget apt-transport-https && \
    wget https://packages.microsoft.com/config/ubuntu/22.04/packages-microsoft-prod.deb -O packages-microsoft-prod.deb && \
    dpkg -i packages-microsoft-prod.deb && \
    apt-get update && \
    apt-get install -y dotnet-sdk-8.0

# Install sqlpackage dotnet tool
RUN dotnet tool install -g Microsoft.SqlPackage

# Add the directory where dotnet global tools are installed to the PATH
ENV PATH="$PATH:/root/.dotnet/tools"

# Copy the configure-db.sh script into the container
COPY configure-db.sh /usr/config/configure-db.sh
COPY entrypoint.sh /usr/config/entrypoint.sh

# Make the scripts executable
RUN chmod +x /usr/config/configure-db.sh
RUN chmod +x /usr/config/entrypoint.sh

# Run the entrypoint.sh script
CMD /bin/bash /usr/config/entrypoint.sh

Content of /src/AppHost/sqlserverconfig/entrypoint.sh (remember to save with LF line endings):

#!/bin/bash

/usr/config/configure-db.sh &
/opt/mssql/bin/sqlservr

Content of /src/AppHost/sqlserverconfig/configure-db.sh (remember to save with LF line endings):

#!/bin/bash

dbstatus=1
errcode=1
start_time=$SECONDS
end_by=$((start_time + 60))
database_name="MY_DATABASE_NAME"

echo "Starting check for SQL Server start-up at $start_time, will end at $end_by"

while [[ $SECONDS -lt $end_by && ( $errcode -ne 0 || ( -z "$dbstatus" || $dbstatus -ne 0 ) ) ]]; do
    dbstatus="$(/opt/mssql-tools18/bin/sqlcmd -h -1 -t 1 -U sa -P "$MSSQL_SA_PASSWORD" -C -Q "SET NOCOUNT ON; Select SUM(state) from sys.databases")"
    errcode=$?
    sleep 1
done

elapsed_time=$((SECONDS - start_time))
echo "Stopped checking for SQL Server start-up after $elapsed_time seconds (dbstatus=$dbstatus,errcode=$errcode,seconds=$SECONDS)"

if [[ $dbstatus -ne 0 ]] || [[ $errcode -ne 0 ]]; then
    echo "SQL Server took more than 60 seconds to start up or one or more databases are not in an ONLINE state"
    echo "dbstatus = $dbstatus"
    echo "errcode = $errcode"
    exit 1
fi

echo "Check if the database exists"
db_exists=$(/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P $MSSQL_SA_PASSWORD -h -1 -W -Q "IF EXISTS (SELECT name FROM sys.databases WHERE name = N'$database_name') SELECT 1 ELSE SELECT 0" | head -n 1)
if [[ "$db_exists" -eq "0" ]]; then

  echo "Checking if /sql-data/$database_name.bacpac exists"
  if [[ -f "/sql-data/$database_name.bacpac" ]]; then
    echo "Importing BACPAC file to $database_name database"
    sqlpackage /a:Import /tcs:"Data Source=localhost;Initial Catalog=$database_name;User Id=sa;Password=$MSSQL_SA_PASSWORD;Encrypt=false" /sf:"/sql-data/$database_name.bacpac"
  else
    echo "Building Database.Build to generate DacPac"
    cd /Database.Build
    dotnet build
    echo "Applying DacPac to $database_name database"
    sqlpackage /a:Publish /tcs:"Data Source=localhost;Initial Catalog=$database_name;User Id=sa;Password=$MSSQL_SA_PASSWORD;Encrypt=false" /sf:"bin/Debug/netstandard2.0/Database.Build.dacpac"
  fi  
fi

Remember to change the MY_DATABASE_NAME variable to the name of your database!

As you can see, the scripts offer to import a bacpac that is present in the /src/AppHost/sql-data folder if it finds one. Otherwise, it will build a dacpac from the database project and publish that to the server.

The startup time will be slow the first time, but because we are using data volumnes and checking if the database exists, the start up time will be short after the first time it has run.

NOTE: I am by no means a SQL or Aspire expert. Any suggestions for improvements are very welcome!

[ErikEJ]

Have you tried https://www.nuget.org/packages/MSBuild.Sdk.SqlProj.Aspire/8.1.0-preview-0006-gf03438655c ?

[egil]

Saw the open issue related to it, but didn't find the package, so no.

Turns out I actually wanted to import a bacpac, so it would be great if your package supports that too.

[ErikEJ]

You should always just use publish and it will create the database if it does not exist.

[egil]

Am I not doing that?

I don't think I am using anything else but publish with dacpac's in my script.

[ErikEJ]

Missed the bacpac part.