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 as follows:

• Install SQL Server
• Configure SQL server
• Run DDL scripts to create the database schema
• 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 that 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 it 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 you 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 image:

# escape=`
FROM sixeyed/msbuild:netfx-4.5.2-ssdt AS builder

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

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

The builder stage just copies in the SQL project source and runs MSBuild to produce the Dacpac. I'm using a variant of the public sixeyed/msbuild image on Docker Cloud, which includes the NuGet packages you need to compile SQL projects.

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

FROM microsoft/mssql-server-windows-express

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

VOLUME ${DATA_PATH}
WORKDIR C:\init

COPY Initialize-Database.ps1 .
CMD ./Initialize-Database.ps1 -sa_password $env:sa_password -data_path $env:data_path -Verbose

COPY --from=builder C:\src\NerdDinner.Database\bin\Debug\NerdDinner.Database.dacpac .

There are no new instructions here, beyond what you've seen so far. 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 CMD, I run a PowerShell script that sets up the database. It's usually not a good idea to hide all the startup details in a separate script because that means you can't see what's going to happen when the container runs from the Dockerfile alone. But in this case, the startup procedure has 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 environment variables called ACCEPT_EULA, so the user can accept the license agreement and sa_password to set the administrator password. I extend this image and set default values for the variables. I'll use the variables 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 the database state in a Docker volume.