Skip to content

More advanced Docker Compose

Jump to bottom
Andrew Pruski edited this page Sep 25, 2020 · 5 revisions

In a previous wiki entry we went through a simple docker compose file to easily spin up one SQL Server container.

Here we'll go through a more advanced setup, using a dockerfile to create a SQL image from scratch and env files to hold our environment variables.

These are the files we're going to use: -

dockerfile
docker-compose.yaml
sqlserver.env
sapassword.env

Let's have a look at the dockerfile first: -

# build from the Ubuntu 18.04 image
FROM ubuntu:18.04
 
# create the mssql user
RUN useradd -u 10001 mssql
 
# installing SQL Server
RUN apt-get update && apt-get install -y wget software-properties-common apt-transport-https
RUN wget -qO- https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"
RUN apt-get update && apt-get install -y mssql-server
 
# creating directories
RUN mkdir /var/opt/sqlserver
RUN mkdir /var/opt/sqlserver/data
RUN mkdir /var/opt/sqlserver/log
RUN mkdir /var/opt/sqlserver/backup
 
# set permissions on directories
RUN chown -R mssql:mssql /var/opt/sqlserver
RUN chown -R mssql:mssql /var/opt/mssql
 
# switching to the mssql user
USER mssql
 
# starting SQL Server
CMD /opt/mssql/bin/sqlservr

So this is going to: -

1. Pull down the Ubuntu 18.04 image and base this new image off it
2. Create the mssql user
3. Install SQL Server as you would on Linux
4. Create the required directories
5. Change the owner of those directories to the mssql user
6. Switch over to run the next command as the mssql user
7. Start SQL Server

Great stuff, we have a dockerfile that'll create us a custom SQL image. Now let's have a look at the docker-compose.yaml file: -

version: '3.7'
services:
    sqlserver1:
        build: 
          context: .
          dockerfile: dockerfile
        ports:  
          - "15789:1433"
        env_file:
          - sqlserver.env
          - sapassword.env
        volumes: 
          - sqlsystem:/var/opt/mssql/
          - sqldata:/var/opt/sqlserver/data
          - sqllog:/var/opt/sqlserver/log
          - sqlbackup:/var/opt/sqlserver/backup
volumes:
  sqlsystem:
  sqldata:
  sqllog:
  sqlbackup:

Stepping through this we: –

1. Define a service called sqlserver1, setting a build context to the current directory and specifying our dockerfile
2. Set our ports, mapping 15789 on the host to 1433 in the container
3. Specify our environment variable files
4. Then set our volumes, matching the directories created in the dockerfile

Cool! Ok, finally let's have a look at the env files: -

ACCEPT_EULA=Y
MSSQL_DATA_DIR=/var/opt/sqlserver/data
MSSQL_LOG_DIR=/var/opt/sqlserver/log
MSSQL_BACKUP_DIR=/var/opt/sqlserver/backup

And: -

MSSQL_SA_PASSWORD=Testing1122

We've used a separate file for the SA password so that we could drop it into a .gitignore file to prevent it being pushed to a public git repo.

OK, we have our files...let's navigate to them in powershell and spin up a container: -

docker-compose up -d

We can see our custom image being built, our custom network being created, and the named volumes: -

N.B. - I've cheated slightly with this screenshot as I built the image in the dockerfile beforehand. That's why you see Using cache under each step. When run for the first time, you'll see the ubuntu 18.04 pulled down and then SQL being installed.

Once that's completed we can check everything created by running: -

docker network ls
docker volume ls
docker image ls
docker container ls

And now we can connect to SQL using: -

mssql-cli -S localhost,15789 -U sa -P Testing1122 -Q "SELECT @@VERSION AS [Version];"

Awesome stuff! We can work away with SQL in the container and when we're done: -

docker-compose down

That's spin down our container, drop our custom network, but we'll still have the custom image and the named volumes so we can spin another container up and not have lost any of our work!

Clone this wiki locally