1

I'm creating a custom Docker image from the existing mcr.microsoft.com/mssql/server:2017-latest-ubuntu by adding some databases with Flyway and then creating backups of the databases within the container.

The idea is to start the Docker container and have it import all databases from the backup files it finds in a certain directory.

If my Docker entrypoint just starts sqlservr and afterwards I exec the shell scripts that restore the DBs from outside, everything works (except that sometimes SQL Server seems to be up, but really isn't).

According to https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-configure-docker?view=sql-server-2017#customcontainer, I should start my shell scripts before sqlservr starts, since it's the foreground process which defines whether the container is up or not.

But it's a chicken/egg situation: How can I execute SQL commands within my shell scripts before SQL Server is running?

I'm aware that normally one would do this with a volume, but this is a test database, and I don't care what happens to the data once the test has finished.

Here's my Docker entrypoint script (I have some custom scripts in /opt/mssql-tools/bin/):

#!/bin/sh

if ! whoami &> /dev/null; then
  if [ -w /etc/passwd ]; then
    echo "${USER_NAME:-sqlservr}:x:$(id -u):0:${USER_NAME:-sqlservr} user:${HOME}:/sbin/nologin" >> /etc/passwd
  fi
fi
/opt/mssql-tools/bin/restore-databases.sh & sqlservr

Thanks for any hints and suggestions what "the Docker way" should be.

eerriicc
  • 1,124
  • 4
  • 17
  • 29

2 Answers2

3

Here is what I do on my side it's not ideal as it involves "sleep" function to wait for SQL server to be up and running before executing the SQL... but it works.

I have the following folder structure:

  • Dockerfile
  • init/
    • database.sql
    • entrypoint.sh
    • setup.sh

My Dockerfile calls my entrypoint.sh as follow:

Dockerfile

FROM microsoft/mssql-server-linux:latest

# Project files
ARG PROJECT_DIR=/srv/db-sql-server
RUN mkdir -p $PROJECT_DIR
WORKDIR $PROJECT_DIR
COPY ./init/ ./

# Grant permissions for scripts to be executable
RUN chmod +x $PROJECT_DIR/entrypoint.sh
RUN chmod +x $PROJECT_DIR/setup.sh

CMD ["/bin/bash", "entrypoint.sh"]

My entrypoint.sh starts SQL Server, calls another custom shell script setup.sh to setup the database and then keeps the container alive:

entrypoint.sh

#start SQL Server, start the script to create the DB and import the data, start the app
/opt/mssql/bin/sqlservr & ./setup.sh & sleep infinity & wait

My custom shell script setup.sh waits 30s for SQL Server to be up and running and then executes my SQL file database.sql to create the database / restore the data:

setup.sh

# Wait for SQL Server to be started
sleep 30s

# Run the setup script to create the database
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -d master -i database.sql
Alexis.Rolland
  • 5,724
  • 6
  • 50
  • 77
0

I bet sp_procoption could work in your case. This allows you to designate a stored procedure to run each time the instance starts and after all databases are mounted and the relational engine begins listening for commands.

Ross Bush
  • 14,648
  • 2
  • 32
  • 55
  • Thanks! Would I be able to read a local directory with backup files and do a restore in this (on Linux)? – eerriicc May 23 '19 at 06:41
  • The sp_procoption should be in the master system database. – Ross Bush May 23 '19 at 12:35
  • The restore command for Linux documentation --> https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-backup-and-restore-database?view=sql-server-2017 – Ross Bush May 23 '19 at 12:37