Skip to content

Custom SQL Server image with a database ready to go

Jump to bottom
Andrew Pruski edited this page Nov 24, 2020 · 8 revisions

One of the really cool things that we can do with building custom images is to have our databases (schema only...let's not go nuts) ready to go when our container spins up.

This is pretty neat as it means we don't have to restore any databases when the container comes up and our database are at a known version.

It's simple to do but there is one trick to it, let's have a look at a dockerfile: -

FROM mcr.microsoft.com/mssql/server:2019-CU5-ubuntu-18.04

USER root

RUN mkdir /var/opt/sqlserver

COPY testdatabase.mdf /var/opt/sqlserver
COPY testdatabase_log.ldf /var/opt/sqlserver
COPY attach-db.sh /var/opt/sqlserver

RUN chown -R mssql:mssql /var/opt/sqlserver

RUN chmod +x /var/opt/sqlserver/attach-db.sh

USER mssql

CMD /var/opt/sqlserver/attach-db.sh & /opt/mssql/bin/sqlservr

Let's go over the steps in this dockerfile.

  1. Building from the SQL Server 2019 official image
  2. Switching to the root user
  3. Creating a custom directory for our database
  4. Copying the database files (mdf and ldf) and a script called attach-db.sh into the image
  5. Changing the owner of our custom directory to the mssql user
  6. Making the attach-db.sh script executable
  7. Switching to the mssql user
  8. Executing the attach-db.sh script and then starting up SQL Server

But hang on a second? Are we running a script to attach databases and THEN starting SQL Server?

That seems the wrong way round yeah? How can we attach a database to SQL before we start SQL Server?

Let's have a look at that attach-db.sh script: -

sleep 30s

/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Testing1122 \
-Q "CREATE DATABASE [testdatabase] ON (FILENAME = '/var/opt/sqlserver/testdatabase.mdf'),(FILENAME = '/var/opt/sqlserver/testdatabase_log.ldf') FOR ATTACH"

The first thing that script does is wait for SQL to spin up (sleep 30s) and then runs an CREATE DATABASE...FOR ATTACH statement.

The reason for this is that a container always needs a process running, otherwise the container will stop.

If we started SQL Server up and then ran the attach script, the attach script becomes the main running process and the container will shut down once it completes.

So by sleeping for 30 seconds and then starting SQL...SQL becomes the main running process, the script executes the CREATE DATABASE...FOR ATTACH after 30 seconds and the container stays up and running!

So let's build the image: -

docker build -t customsqlimage1 .

And then check that the image is there: -

docker image ls

So now we can run a container from that image: -

docker container run -d `
-p 15789:1433 `
--env ACCEPT_EULA=Y `
--env MSSQL_SA_PASSWORD=Testing1122 `
--name sqlcontainer1 `
customsqlimage1

Wait for 30 seconds...and our database is there!

mssql-cli -S localhost,15789 -U sa -P Testing1122 -Q "SELECT [name] FROM sys.databases;"

How powerful is that? We can spin up SQL Server in a container with our databases ready to go! Incredibly useful when deploying development environments.

Clone this wiki locally