Creating Docker images for SQL Server databases

Setting up a database image is just like any other Docker image. I'll be encapsulating the setup tasks in a Dockerfile. Broadly, for a new database, the steps will be:

1. Install SQL Server
2. Configure SQL server
3. Run DDL scripts to create the database schema
4. Run DML scripts to populate static data

This fits in very well with a typical build process using Visual Studio's SQL database project type and the Dacpac deployment model. The output from publishing the project is a .dacpac file which contains the database schema and any custom SQL scripts to run. Using the SqlPackage tool you can deploy the Dacpac file to a SQL Server instance, and it will either create a new database if one doesn't exist, or it will upgrade an existing database so the schema matches the Dacpac.

This approach is perfect for a custom SQL Server Docker image. I can use multi-stage builds again for the Dockerfile, so other users don't need Visual Studio installed to package the database from the source code. This is the first stage of the Dockerfile for the dockeronwindows/ch03-nerd-dinner-db:2e image:

# escape=`
FROM microsoft/dotnet-framework:4.7.2-sdk-windowsservercore-ltsc2019 AS builder
SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop';"]

# add SSDT build tools
RUN nuget install Microsoft.Data.Tools.Msbuild -Version 10.0.61804.210

# add SqlPackage tool
ENV download_url="https://download.microsoft.com/download/6/E/4/6E406.../EN/x64/DacFramework.msi"
RUN Invoke-WebRequest -Uri $env:download_url -OutFile DacFramework.msi ; `
    Start-Process msiexec.exe -ArgumentList '/i', 'DacFramework.msi', '/quiet', '/norestart' -NoNewWindow -Wait; `
    Remove-Item -Force DacFramework.msi

WORKDIR C:\src\NerdDinner.Database
COPY src\NerdDinner.Database .

RUN msbuild NerdDinner.Database.sqlproj `
  /p:SQLDBExtensionsRefPath="C:\Microsoft.Data.Tools.Msbuild.10.0.61804.210\lib\net46" `
  /p:SqlServerRedistPath="C:\Microsoft.Data.Tools.Msbuild.10.0.61804.210\lib\net46" 

There's quite a bit in here, but it's all straightforward. The builder stage starts from Microsoft's .NET Framework SDK image. That gives me NuGet and MSBuild, but not the dependencies I need to build the SQL Server Dacpac. The first two RUN instructions install the SQL Server Data Tools and the SqlPackage tool. I could package this as a separate SQL Server SDK image if I had many database projects to containerize.

The rest of the stage just copies in the SQL project source and runs MSBuild to produce the Dacpac.

Here's the second stage of the Dockerfile, which packages the NerdDinner Dacpac to run in SQL Server Express:

FROM dockeronwindows/ch03-sql-server:2e

ENV DATA_PATH="C:\data" `
    sa_password="N3rdD!Nne720^6"

VOLUME ${DATA_PATH}

WORKDIR C:\init
COPY Initialize-Database.ps1 .
CMD powershell ./Initialize-Database.ps1 -sa_password $env:sa_password -data_path $env:data_path -Verbose

COPY --from=builder ["C:\\Program Files...\\DAC", "C:\\Program Files...\\DAC"]
COPY --from=builder C:\docker\NerdDinner.Database.dacpac . 

There are no new instructions here, beyond what you've seen so far. There's a volume set up for the SQL Server data files, and an environment variable to set the default data file path to C:\data. You'll see that there are no RUN commands, so I'm not actually setting up the database schema when I build the image; I'm just packaging the Dacpac file into the image so I have everything I need to create or upgrade the database when the container starts.

In the CMD instruction, I run a PowerShell script which sets up the database. It's sometimes not a good idea to hide all the startup details in a separate script, because that means you can't see from the Dockerfile alone what's going to happen when the container runs. But in this case, the startup procedure has quite a few functions, and they would make for a huge Dockerfile if we put them all in there.

The base SQL Server Express image defines an environment variable called sa_password to set the administrator password. I extend this image and set a default value for the variable. I'll use the variable in the same way in order to allow users to specify an administrator password when they run the container. The rest of the startup script deals with the problem of storing database state in a Docker volume.