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?

DLeh · Accepted Answer · 2019-03-18 23:12:44Z

9

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>

edited Mar 18, 2019 at 23:12

DLeh

24.6k17 gold badges96 silver badges141 bronze badges

answered Jan 5, 2019 at 16:41

Malmee.Weerasinghe

1611 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ShadeToD Over a year ago

That was the problem I had. Changing password to more complex one solved the issue.

kenorb · Accepted Answer · 2021-01-21 15:25:12Z

3

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

answered Jan 21, 2021 at 15:25

kenorb

169k95 gold badges712 silver badges796 bronze badges

Comments

Rohit Jindal · Accepted Answer · 2018-06-26 15:30:56Z

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

umbersar · Accepted Answer · 2020-05-22 10:48:42Z

0

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

answered May 22, 2020 at 10:48

umbersar

1,9313 gold badges26 silver badges37 bronze badges

Comments

kenorb · Accepted Answer · 2021-01-21 13:33:54Z

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"

Collectives™ on Stack Overflow

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

5 Answers 5

1 Comment

`Dockerfile`

`startup.sh`

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Dockerfile

startup.sh

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

`Dockerfile`

`startup.sh`