[New article]: Executing a Dacpac during Sql Container Provisioning

[quisitive-crogers]

Proposed topic or title

Executing a Dacpac during Sql Container Provisioning

Location in table of contents.

/database/execute-dacpac.md

Reason for the article

A lot of developers are embracing the SqlPackage approach for doing incremental updates on databases. This allows them to put their SQL code in source control using SQL Projects and manage SQL changes in a more controlled fashion. It is important for Aspire to support this in a simple way that they can easily implement.

Article abstract

In this article, you will learn to configure Aspire to publish a SqlPackage DACPAC file against a SQL Server container as part of the startup process.

Relevant searches

I dug through these documents, a few other repos, including some Aspire Dev Days repos, and some chatter on X and StackExchange from the various developers involved in Aspire, but nobody got it working and documented it.

Specifically, what I did to get it working was the following:

For the SqlContainer setup:

var sqlServer = builder.AddSqlServer("sampledb", port: 4043);
var sqlDatabase = sqlServer.AddDatabase("SampleSqlDB");

var sqlShellFolder = "./sql-server";
var sqlDockerFile = "sql2022wSqlPackage.Dockerfile";
var sqlScriptFolder = "../SampleDB/bin/Debug";
sqlServer
    .WithDockerfile(sqlShellFolder, sqlDockerFile)
    .WithEnvironment("MSSQL_DB_NAME", sqlDatabase.Resource.Name)
    .WithDataVolume("DataVolume") //Keeps dev data and improves efficiency of DacPac deployment
    .WithBindMount(sqlShellFolder, target: "/usr/config")
    .WithBindMount(sqlScriptFolder, target: "/docker-entrypoint-initdb.d")
    .WithEntrypoint("/usr/config/entrypoint.sh");

For the Docker File itself:

FROM mcr.microsoft.com/mssql/server:2022-latest
USER root
VOLUME download
ENV ACCEPT_EULA=Y
RUN apt-get update \
    && apt-get upgrade -y \
    && apt-get install -y \
        unzip \
        msodbcsql18 \
        mssql-tools
RUN wget -O sqlpackage.zip https://aka.ms/sqlpackage-linux \
    && unzip sqlpackage.zip -d /opt/sqlpackage \
    && chmod +x /opt/sqlpackage/sqlpackage \
    && rm /sqlpackage.zip
RUN wget "http://ftp.us.debian.org/debian/pool/main/o/openssl/libssl3_3.1.5-1_amd64.deb" \
    && dpkg -i libssl3_3.1.5-1_amd64.deb \
    && rm libssl3_3.1.5-1_amd64.deb
ENV PATH=$PATH:/opt/mssql-tools/bin:/opt/sqlpackage

The entrypoint.sh was similar to your example for seeding the DB. The configure-db.sh is different, though:

#!/bin/bash

# set -x

# Adapted from: https://github.com/microsoft/mssql-docker/blob/80e2a51d0eb1693f2de014fb26d4a414f5a5add5/linux/preview/examples/mssql-customize/configure-db.sh

# Wait 60 seconds for SQL Server to start up by ensuring that
# calling SQLCMD does not return an error code, which will ensure that sqlcmd is accessible
# and that system and user databases return "0" which means all databases are in an "online" state
# https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-2017

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

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

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

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

if [[ $dbstatus -ne 0 ]] || [[ $errcode -ne 0 ]]; then
    echo "*******************************************************"
    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"
    echo "*******************************************************"
    exit 1
fi

for f in $(find /docker-entrypoint-initdb.d -maxdepth 1 -type f -name "*.dacpac" | sort); do
    echo "*******************************************************"
    echo "Processing $f file..."
    echo "Executing command: /opt/sqlpackage/sqlpackage /Action:Publish /SourceFile:\"$f\" /tec:False /ttsc:True /tsn:\"localhost\" /tdn:\"$MSSQL_DB_NAME\" /tu:"sa" /tp:\"$MSSQL_SA_PASSWORD\" /p:VerifyDeployment=False"
    echo "*******************************************************"

    #NOTE - Target Encrypt Connection (/tec) must be False and Target Trust Server Certificate (/ttsc) must be True for this to work.
    /opt/sqlpackage/sqlpackage /Action:Publish /SourceFile:"$f" /tec:False /ttsc:True /tsn:"localhost" /tdn:"$MSSQL_DB_NAME" /tu:"sa" /tp:"$MSSQL_SA_PASSWORD" /p:VerifyDeployment=False 
done

Hope this helps!