CODEWITHSUNDEEP
sql-serverdocker
Johan Vergeer
Johan Vergeer

Reputation: 5578

How to run a setup script on a Docker SQL Server image?

I'm trying to run a setup script on a Docker SQL Server image

For this I have created a Dockerfile from the mssql image

FROM microsoft/mssql-server-linux:2017-CU8

# Create directory to place app specific files
RUN mkdir -p /usr/src/app
WORKDIR /usr/src/app

# Copy setup scripts
COPY  entrypoint.sh \
    ./

RUN chmod +x ./entrypoint.sh

CMD /bin/bash ./entrypoint.sh

In entrypoint.sh I'm starting SQL Server and I want to run some setup commands.

#!/bin/bash

#start SQL Server
/opt/mssql/bin/sqlservr &

echo 'Sleeping 20 seconds before running setup script'
sleep 20s

echo 'Starting setup script'

#run the setup script to create the DB and the schema in the DB
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <MyPassWd> -d master -i setup.sql

echo 'Finished setup script'

When I run this script, the database starts, the setup runs, and after the setup is finished, the container shuts down.

So I thought something in the script makes the container shut down, therefore I stripped the script down to a bare minimum

#!/bin/bash

#start SQL Server
/opt/mssql/bin/sqlservr &

echo 'Sleeping 20 seconds before running setup script'
sleep 20s

That also stops the container after sleep 20s finished.

Moving on...

#!/bin/bash

#start SQL Server
/opt/mssql/bin/sqlservr &

Which stops the container right away

And then...

#!/bin/bash

#start SQL Server
/opt/mssql/bin/sqlservr

Now the container runs, but I can't do any initialization

Does someone know how to get this working?

Upvotes: 7

Views: 5051

Answers (5)

kenorb
kenorb

Reputation: 166667

To create the database on startup, try the approach below.

Dockerfile

FROM mcr.microsoft.com/mssql/server:2019-latest
ENV ACCEPT_EULA Y
ENV DB_NAME test
COPY startup.sh /var/opt/mssql/startup.sh
CMD ["bash", "/var/opt/mssql/startup.sh"]

startup.sh

#!/usr/bin/env bash
if ! [ -f /var/opt/mssql/.initialized ] && [ -n "$DB_NAME" ]; then
  while ! </dev/tcp/localhost/1433 2>/dev/null; do
    sleep 2
  done
  echo "Creating $DB_NAME database..."
  /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "$SA_PASSWORD" -d master \
    -Q "CREATE DATABASE $DB_NAME"
  touch /var/opt/mssql/.initialized
fi &
/opt/mssql/bin/sqlservr

Upvotes: 3

kenorb
kenorb

Reputation: 166667

There is a pull request to allow to run an init SQL script on first time run.

The purpose of this PR is to add into the start.ps1 the ability to check the folder docker-entrypoint-initdb and run all sql scripts inside. Once it is done, the script creates a flag file to avoid running the setup phase on the next startup after a stop.

By mounting a volume from local folder scripts to c:/docker-entrypoint-initdb, the container will execute all .sql scripts files. The volume should be a directory.

E.g.

version: "3.8"
services:
  sqlserver:
    platform: windows/amd64
    environment: 
      - sa_password=<YourPassword>
      - ACCEPT_EULA=Y
    image: microsoft/mssql-server-windows-developer
    volumes: 
      - ./dockerfiles/sqlserver/initdb:c:/docker-entrypoint-initdb:ro
    ports:
      - "1433:1433"

Upvotes: 0

umbersar
umbersar

Reputation: 1931

SQL Server has to be the right most command.

I know it does not make sense as you want SQL Server to run first and then run your scripts to create/restore databases. I guess this is because of the way SQL Server runs on Linux ( Sql server process creates a SQL server process as part of startup).

MSDN documentation makes the order of execution clear at: https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-configure-docker?view=sql-server-ver15#customcontainer

So for your example, you would have to write something like:

/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <MyPassWd> -d master -i setup.sql & /opt/mssql/bin/sqlservr

Upvotes: 0

Malmee.Weerasinghe
Malmee.Weerasinghe

Reputation: 161

Change the password of the sql server to be complex enough. 

docker run -d -p 1433:1433 -e "sa_password=ComplexPW2019!" -e "ACCEPT_EULA=Y" <sqlserverimageid>

Upvotes: 9

Rohit Jindal
Rohit Jindal

Reputation: 677

Root cause of this issue is PID 1 allocation for docker container.

PID 1 will be allocated to command given in CMD in Dockerfile (in our case ./entrypoint.sh)

Container has a life spam according to PID 1(as soon as PID 1 is stop/killed container will be stopped)

1) In case of /opt/mssql/bin/sqlservr &
a child process ID will be allocated to sqlserver cmd and will be executed in background and as soon as rest of the script is executed, container will stop.

2) In case of /opt/mssql/bin/sqlservr
script will not proceed from here until this execution will complete.

so the solution is to assign PID 1 to CMD /opt/mssql/bin/sqlservr and rest of the script should be executed as child process.

I have done below changes and it is working for me.

in Dockerfile

replace CMD /bin/bash ./entrypoint.sh to CMD exec /bin/bash entrypoint.sh

in entrypoint.sh

   #!/bin/bash
   #start SQL Server
   sh -c " 
   echo 'Sleeping 20 seconds before running setup script'
   sleep 20s

   echo 'Starting setup script'

   #run the setup script to create the DB and the schema in the DB
   /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P \"YourStrong!Passw0rd\" -Q 
   \"ALTER LOGIN SA WITH PASSWORD='NewStrong!Passw0rd'\"

    echo 'Finished setup script'
    exit
    " & 
    exec /opt/mssql/bin/sqlservr

Upvotes: 2

Related Questions