Building a custom image

In the Persisting Data using Named Volumes section of this wiki we used docker named volumes to persist databases from one container to another but we had a manual step to perform before we could create a database in a custom location: -

docker exec -u 0 testcontainer8 bash -c "chown -R mssql /var/opt/sqlserver"

We had to do this as SQL Server 2019 does not run as root in a container, it runs as the user mssql and as such does not have access to that custom location.

So let's build a new container image that already has granted the mssql user access to /var/opt/sqlserver. To do this we use a dockerfile, which is just a file on our docker host that contains some instructions for the Docker Engine: -

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

USER root

RUN mkdir /var/opt/sqlserver

RUN chown -R mssql /var/opt/sqlserver

ENV MSSQL_BACKUP_DIR="/var/opt/sqlserver"
ENV MSSQL_DATA_DIR="/var/opt/sqlserver"
ENV MSSQL_LOG_DIR="/var/opt/sqlserver"

USER mssql

CMD /opt/mssql/bin/sqlservr

Here's what each step in the dockerfile does: -

1. We start from the SQL Server 2019 CU5 Ubuntu 18.04 image. So our custom image will be based on this
2. Make sure that we're running the following commands as the root user
3. Create the /var/opt/sqlserver location within the container
4. Change the owner of the /var/opt/sqlserver folder to the mssql user
5. The next three lines use environment variables to set the default data/log/backup locations to the /var/opt/sqlserver folder
6. Switch to the mssql user
7. Start SQL Server

So let's go ahead and build our custom image: -

docker image build -t sqlimage1 .

Note the "." at the end. This is saying that the dockerfile is in the current location that I'm at in my terminal. You can specify a filepath here.

Let the command complete and then we can check our local images: -

docker image ls

Cool! Let's go ahead and run a container from that image: -

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

Confirm that the container is running: -

docker container ls -a

And let's check the folder: -

docker exec sqlcontainer1 /bin/bash -c "ls -al /var/opt/"

The mssql user is the owner! Ok that means we can run: -

mssql-cli -S localhost,15789 -U sa -P Testing1122 -Q "CREATE DATABASE [testdatabase];"

No need to manually go in and change the owner! Our database is created!

We can check it's there by running: -

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

And confirm it's file locations: -

mssql-cli -S localhost,15789 -U sa -P Testing1122 -Q "USE [testdatabase]; EXEC sp_helpfile;"

So that's how to use a custom image to allow us to create databases in a custom location for SQL Server 2019 without having to manually grant the mssql user access to the folder.