Tag Archives: docker

A Small Lesson on env Files in docker-compose

Docker

I’ve been working a lot with docker lately and learning learning learning. I have written a 3-part series for my MSDN Mag Data Points column that will be out in April, May and June 2019 issues. I have another YUGE blog post that I will publish to accompany the May article. And I’m working on others.

I explored Docker environment variables and different ways to feed them into a Docker image or container.

My docker-compose file referenced an environment variable named DB_PW without specifying it’s value.

dataapidocker:
   image: ${DOCKER_REGISTRY-}dataapidocker
     build:
       context: .
       dockerfile: DataAPIDocker/Dockerfile
     environment:
       - DB_PW

Docker will read environment variables from the host to discover the value.

But this was a pain. I wasn’t permanently storing the DB_PW on my development machine and had to remember to set it frequently. Elton Stoneman (from Docker) said I should *really* consider using the Docker env file feature. This lets you set variables in an environment file and let the docker-compose file read from that. And I can keep that special file out of my source control. (Always my worry!)

I started by following docs that showed using a file named anything.env. I created a file called hush-hush.env where I specified the variable. This is the full content of the file:

DB_PW=thebigsecret

Then in docker-compose, in the service, the env_file tag lets you point to it. You can even remove the environment tag in the yml file.

dataapidocker:
   image: ${DOCKER_REGISTRY-}dataapidocker
     build:
       context: .
       dockerfile: DataAPIDocker/Dockerfile
     env_file:
       - hush-hush.env

This worked perfectly. My app was able to discover the environment variable in code.

But then I evolved my solution to use another container for my database. The primary container depends on the new mssql container. And the mssql container requires I pass in the database password as an environment variable. Since DB_PW already exists, I can do that easily enough with substitution (via curly braces). Here’s the new docker-compose file:

version: '3.4'
 services:
   dataapidocker:
     image: ${DOCKER_REGISTRY-}dataapidocker
   build:
       context: .
       dockerfile: DataAPIDocker/Dockerfile
   env_file:
     - hush-hush.env  
   depends_on:
     - db
   db:
     image: mcr.microsoft.com/mssql/server
     volumes:
       - mssql-server-julie-data:/var/opt/mssql/data
     environment:
       SA_PASSWORD: "${DB_PW}"
       ACCEPT_EULA: "Y"
     ports:
       - "1433:1433"
 volumes:
   mssql-server-julie-data: {}

And there’s an order of operations issue here. When I build the docker-compose file, it complains that DB_PW is not available and my app fails. The db service is not getting the contents of my hush-hush.env file. I tried a number of things, such as adding env_file to the db service. In the end, here’s what I learned.

The substitution use requires that the DB_PW variable be defined in docker-compose. I added that back in to the primary service, but it was not getting the value from hush-hush.env.

But you can have a .env file that has no name. The extension *is* the full name of the file. Docker-compose will read that early enough and provide the value from the .env file to the declared DB_PW. Then all of the pieces fell in place. The mssql container was spun up with the value from DB_PW as its environment variable. And my app code was able to read the environment variable that Docker passed into the running container for its own tasks.

The final docker-compose.yml file looks like this:

version: '3.4'
 services:
   dataapidocker:
     image: ${DOCKER_REGISTRY-}dataapidocker
   build:
       context: .
       dockerfile: DataAPIDocker/Dockerfile
   environment :
     - DB_PW 
   depends_on:
     - db
   db:
     image: mcr.microsoft.com/mssql/server
     volumes:
       - mssql-server-julie-data:/var/opt/mssql/data
     environment:
       SA_PASSWORD: "${DB_PW}"
       ACCEPT_EULA: "Y"
     ports:
       - "1433:1433"
 volumes:
   mssql-server-julie-data: {}

And it relies on a file named “.env” with my variable key value pair defined (same as hush-hush.env above).

DB_PW=thebigsecret

Resources:
https://docs.docker.com/compose/environment-variables/

Getting the SQL Server 2019 for Linux CTP2.0 Docker Image

Tools,

If you are used to pulling the mssql-server images from the microsoft repository, e.g.,

docker pull microsoft/mssql-server

that won’t work for the 2019 CTP.

I was able to repull (aka update) using the former repository, but that wasn’t working for the CTP whose tag is vNext-CTP2.0-ubuntu.

I finally noticed the new docker pull command on the docker hub page for the image

It says: docker pull mcr.microsoft.com/mssql/server

So the command for pulling the CTP using it’s tag is as follows:

docker pull mcr.microsoft.com/mssql/server:vNext-CTP2.0-ubuntu

 

What’s in that sqlservr.sh file on the mssql-sqlserver-linux docker image anyway?

Data Access, ,

Update June 3, 2017: The team has revised the docker image and the bash file is gone, presumably with its logic broken up in to various locations. Still I’m glad I grabbed this when I did to satisfy my curiosity!

Microsoft has created 4 official Docker images for SQL Server: SQL Server for Linux, SQL Server Developer Edition, SQL Server Express and (windows) SQL Server vNext) . They can be found on the Docker hub (e.g. https://hub.docker.com/r/microsoft/mssql-server-linux/) and there is also a Github repository for them at github.com/Microsoft/mssql-docker. Some of the files that go along with that image are not on Github. The Dockerfile files for each image run some type of startup script. The Windows images have a PowerShell script called start.ps1. You can see those in the Github repo. The Linux image runs a bash file called sqlservr.sh. That’s not included in the repo though and I was curious what it did.

Note: I wrote a blog post about using the SQL Server for Linux container (Mashup: SQL Server on Linux in Docker on a Mac with Visual Studio Code)  and I’m also writing an article about using the containers for my July MSDN Magazine Data Points column (watch this space).

Still a bit of a bash noob, I learned how to read a file from a docker container on ..you guessed it…StackOverflow.  Following those instructions, I created a snapshot of my running container

➜ MySqlServerLinuImage git:(master) docker commit juliesqllinux  mysnapshot

sha256:9b552a1e24df7652af0c6c265ae5e2d7cb7832586c431d4b480c30663ab713f0

and ran the snapshot with bash:


➜  MySqlServerLinuImage git:(master) docker run -t -i mysnapshot bin/bash

[email protected]:/#

Then at the new prompt (#), used ls to get the listing

[email protected]:/# ls

SqlCmdScript.sql  SqlCmdStartup.sh  bin  boot  dev  entrypoint.sh  etc  home  install.sh  lib  lib64  media  mnt  opt  proc  root  run  sbin  srv  sys  tmp  usr  var

then navigated to  folder where the bash file is and listed its contents:

[email protected]:/opt/mssql/bin# ls

compress-dump.sh  generate-core.sh  mssql-conf  paldumper  sqlpackage  sqlservr  sqlservr.sh

Once I was there I used the cat command to list out the contents of the sqlservr.sh file and see what it does. Here is the secret sauce in case, like me, you NEED to know what’s going on under the covers!

[email protected]:/opt/mssql/bin# cat sqlservr.sh 

#!/bin/bash

#

# Microsoft(R) SQL Server(R) launch script for Docker

#

ACCEPT_EULA=${ACCEPT_EULA:-}

SA_PASSWORD=${SA_PASSWORD:-}

#COLLATION=${COLLATION:-SQL_Latin1_General_CP1_CI_AS}

have_sa_password=""

#have_collation=""

sqlservr_setup_prefix=""

configure=""

reconfigure=""

# Check system memory

#

let system_memory="$(awk '/MemTotal/ {print $2}' /proc/meminfo) / 1024"

if [ $system_memory -lt 3250 ]; then

    echo "ERROR: This machine must have at least 3.25 gigabytes of memory to install Microsoft(R) SQL Server(R)."

    exit 1

fi

# Create system directories

#

mkdir -p /var/opt/mssql/data

mkdir -p /var/opt/mssql/etc

mkdir -p /var/opt/mssql/log

# Check the EULA

#

if [ "$ACCEPT_EULA" != "Y" ] && [ "$ACCEPT_EULA" != "y" ]; then

 echo "ERROR: You must accept the End User License Agreement before this container" > /dev/stderr

 echo "can start. The End User License Agreement can be found at " > /dev/stderr

 echo "http://go.microsoft.com/fwlink/?LinkId=746388." > /dev/stderr

 echo ""

 echo "Set the environment variable ACCEPT_EULA to 'Y' if you accept the agreement." > /dev/stderr

 exit 1

fi

# Configure SQL engine

#

if [ ! -f /var/opt/mssql/data/master.mdf ]; then

 configure=1

 if [ ! -z "$SA_PASSWORD" ] || [ -f /var/opt/mssql/etc/sa_password ]; then

 have_sa_password=1

 fi

# if [ ! -z "$COLLATION" ] || [ -f /var/opt/mssql/etc/collation ]; then

# have_collation=1

# fi

 if [ -z "$have_sa_password" ]; then

        echo "ERROR: The system administrator password is not configured. You can set the" > /dev/stderr

        echo "password via environment variable (SA_PASSWORD) or configuration file" > /dev/stderr

        echo "(/var/opt/mssql/etc/sa_password)." > /dev/stderr

 exit 1

 fi

fi

# If user wants to reconfigure, set reconfigure flag

#

if [ -f /var/opt/mssql/etc/reconfigure ]; then

 reconfigure=1

fi

# If we need to configure or reconfigure, run through configuration

# logic

#

if [ "$configure" == "1" ] || [ "$reconfigure" == "1" ]; then

 sqlservr_setup_options=""

# if [ -f /var/opt/mssql/etc/collation ]; then

# sqlservr_setup_options+="-q $(cat /var/opt/mssql/etc/collation)"

# else

# if [ ! -z "$COLLATION" ]; then

# sqlservr_setup_options+="-q $COLLATION "

# fi

# fi

 set +e

 cd /var/opt/mssql

 echo 'Configuring Microsoft(R) SQL Server(R)...'

 if [ -f /var/opt/mssql/etc/sa_password ]; then

 SQLSERVR_SA_PASSWORD_FILE=/var/opt/mssql/etc/sa_password /opt/mssql/bin/sqlservr --setup $sqlservr_setup_options 2>&1 > /var/opt/mssql/log/setup-$(date +%Y%m%d-%H%M%S).log

 elif [ ! -z "$SA_PASSWORD" ]; then

 SQLSERVR_SA_PASSWORD_FILE=<(echo -n "$SA_PASSWORD") /opt/mssql/bin/sqlservr --setup $sqlservr_setup_options 2>&1 > /var/opt/mssql/log/setup-$(date +%Y%m%d-%H%M%S).log

 else

 if [ ! -z '$sqlservr_setup_options' ]; then

 /opt/mssql/bin/sqlservr --setup $sqlservr_setup_options 2>&1 > /var/opt/mssql/log/setup-$(date +%Y%m%d-%H%M%S).log

 fi

 fi

 retcode=$?

 if [ $retcode != 0 ]; then

 echo "Microsoft(R) SQL Server(R) setup failed with error code $retcode. Please check the setup log in /var/opt/mssql/log for more information." > /dev/stderr

 exit 1

 fi

 set -e

 rm -f /var/opt/mssql/etc/reconfigure

 rm -f /var/opt/mssql/etc/sa_password

 echo "Configuration complete."

fi

# Start SQL Server

#

exec /opt/mssql/bin/sqlservr $*