33

I am trying to create my own Docker image with MSSQL DB for development. It's based on microsoft/mssql-server-linux image. During the build I want to copy some .sql files into the container and then run these scripts (to create DB schemas, tables, insert some data etc.). My Dockerfile looks like this:

# use MSSQL 2017 image on Ubuntu 16.04
FROM microsoft/mssql-server-linux:2017-latest

# create directory within SQL container for database files
RUN mkdir -p /opt/mssql-scripts

# copy the database files from host to container
COPY sql/000_create_db.sql /opt/mssql-scripts

# set environment variables
ENV MSSQL_SA_PASSWORD=P@ssw0rd
ENV ACCEPT_EULA=Y

# run initial scripts
RUN /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'P@ssw0rd' -i /opt/mssql-scripts/000_create_db.sql

Content of 000_create_db.sql is not important in my opinion.

The real problem is when I am trying to build this Dockerfile with command docker build -t demo . I always get these errors:

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

But when I delete the last command (running initial scripts), build and run image, and call the same command like this:

docker build -t demo .
docker run -p 1433:1433 --name mssql -d demo
docker exec -it mssql "bash"
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'P@ssw0rd' -i /opt/mssql-scripts/000_create_db.sql

Then everything is going well. Why cannot I run script from Dockefile?

Tomáš Fábry
  • 481
  • 1
  • 5
  • 7

3 Answers3

41

I ended up using a slightly modified version of VDR's solution which waits for the sqlservr to start by checking the logs instead of sleeping 10 seconds:

RUN ( /opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started" \
    && /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'P@ssw0rd' -i /opt/mssql-scripts/000_create_db.sql \
    && pkill sqlservr
robd
  • 9,646
  • 5
  • 40
  • 59
  • This worked, thank you very much! I was able to execute sqlcmd with a -q parameter also. – Vedran Mandić Sep 12 '19 at 20:05
  • 3
    I had to update this a bit. If SQLServer fails to start, it was hard to diagnose. `RUN ( /opt/mssql/bin/sqlservr & ) | grep -q "Service Broker manager has started" && ( echo "SQLServer started" && sleep 10s ) || ( echo "SQLSERVER failed to start" && exit )` – BozoJoe Apr 30 '21 at 20:11
15

From the mssql-server-linux dockerfile, looks like mssql is started on docker run, so you have to modify your last "RUN" command in your dockerfile to start sql-server in the background, run your sql file and stop the sql-server.

RUN /opt/mssql/bin/sqlservr --accept-eula & sleep 10 \
    && /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'P@ssw0rd' -i /opt/mssql-scripts/000_create_db.sql \
    && pkill sqlservr
robd
  • 9,646
  • 5
  • 40
  • 59
VDR
  • 2,663
  • 1
  • 17
  • 13
-2

When you build image main process is not running eat. In your case main process is SQL Server.

Process will run only when you start container with command docker run ...

And you can make database initialization in this phase. microsoft/mssql-server-linux Dockerfile contains some tips

Copy all SQL Server runtime files from build drop into image. COPY ./install /

Just copy into build phase init scripts to ./install dir and it will be executed into start phase.

Bukharov Sergey
  • 9,767
  • 5
  • 39
  • 54
  • Sorry, but now I am really confused. What do you mean under "main process"? And what phases are you talking about? Does it mean that some commands from Dockerfile are not triggered during building image? But instead of that they are triggered during runing container? From you advice I understood that the only thing what I need to do is make an directory with name "install" and put .sql files in it. But it doesn't work for me. – Tomáš Fábry Oct 25 '17 at 15:34
  • I think @bukharov-sergey has misunderstood the point of that particular COPY command. I believe that is in the mssql-server-linux dockerfile because that's the command Mircrosoft uses to copy the actual sql server executable files into the mssql-server-linux docker image that we then all download from Docker Hub. It has nothing to do with your stuff. Copying your own scripts over is left as a separate task for you to do in your own dockerfile based on the microsoft/mssql-server-linux image. – sql_knievel Jul 06 '18 at 17:16