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

Question

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?

Answer 1

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>

Answer 2

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