Allow pointing to and executing a SQL script on run #11

twright-msft · 2017-01-09T22:56:29Z

For scenarios where you might want to create logins, run some test/initial data population script, etc. it would be nice to be able to point to a sql script file and have that be executed by the entrypoint script after the sqlservr process is started.

rfaaberg · 2017-02-05T08:22:01Z

That would be awesome, I can already see a great use-case for this image for spinning up dev databases. I hacked in that functionality with a modified start.ps1 script that I'd open a PR for but sounds like this repo isn't used to generate the image.

twright-msft · 2017-02-05T19:51:04Z

Right, we don't build out of this repo. Feel free to share what you would do by creating a new Dockerfile and entrypoint.sh on your github and shoot us a link to that and the image on Docker Hub.

Here is an implementations that I did last week for a demo that I did in a presentation at a conference.
https://github.com/twright-msft/mssql-node-docker-demo-app

twright-msft · 2017-02-06T19:14:09Z

Here is an example of executing a SQL script at run time.
https://github.com/twright-msft/mssql-node-docker-demo-app

That's really a lot more complicated than it needs to be though.

twright-msft · 2017-03-17T23:06:23Z

FYI - We released CTP 1.4 today. This release of the mssql-server-linux image now includes the mssql-tools package (sqlcmd and bcp) in it.

Executing sqlcmd as part of the entrypoint.sh script can be used for this kind of scenario for now. Since this is such a commonplace requirement we want to make it easier in the future, but sqlcmd will provide a reasonable option until then.

shanegenschaw · 2017-04-04T22:36:55Z

I created a pull request based on the example in the demo mentioned above, implemented in a similar fashion as the mysql image.

The genschsa/mssql-server-linux image on Docker Hub is an extension of the official image using this technique.

Hope this is useful and considered for inclusion.

jvitor83 · 2018-07-11T23:22:17Z

As a workaround, maybe this can be helpfull:

  flyway:
    image: boxfuse/flyway
    command: -c 'sleep 30; flyway migrate -user=sa -password=$${SA_PASSWORD} -url="jdbc:sqlserver://sqlserver:1433;databaseName=$${DATABASE}"'
    entrypoint: sh
    volumes:
      - ./sql:/flyway/sql
    environment:
      SA_PASSWORD: P@ssw0rd
      DATABASE: MyDatabase

  sqlserver:
    image: microsoft/mssql-server-linux:2017-latest
    ports:
      - 1433:1433
    entrypoint: sh
    command: -c '/opt/mssql/bin/sqlservr & /opt/mssql-tools/bin/sqlcmd -l 30 -S localhost -U sa -P $${SA_PASSWORD} -d tempdb -q "CREATE DATABASE $${DATABASE}"; wait'
    environment:
      ACCEPT_EULA: 'Y'
      SA_PASSWORD: P@ssw0rd
      DATABASE: MyDatabase

rmoff · 2019-01-08T11:52:26Z

Here's another workaround, this one will execute all the SQL files in a mounted folder:

  mssql:
    image: mcr.microsoft.com/mssql/server:2017-latest
    environment: 
      - SA_PASSWORD=Admin123
      - ACCEPT_EULA=Y
    volumes:
     - ./data/mssql:/scripts/
    command:
      - /bin/bash
      - -c 
      - |
        # Launch MSSQL and send to background
        /opt/mssql/bin/sqlservr &
        # Wait 30 seconds for it to be available
        # (lame, I know, but there's no nc available to start prodding network ports)
        sleep 30
        # Run every script in /scripts
        # TODO set a flag so that this is only done once on creation, 
        #      and not every time the container runs
        for foo in /scripts/*.sql
          do /opt/mssql-tools/bin/sqlcmd -U sa -P $$SA_PASSWORD -l 30 -e -i $$foo
        done
        # So that the container doesn't shut down, sleep this thread
        sleep infinity

twright-msft · 2019-01-16T02:22:20Z

@rmoff - This is a tidy solution. Thanks for sharing it. Hopefully, we'll get something like this built into the SQL Server engine and configurable via an env variable (MSSQL_STARTUP_SCRIPTS_DIR or something) in a future release, but for now, this is a good way to handle it.

rmoff · 2019-01-16T16:40:43Z

thanks @twright-msft. I enhanced it a bit further with trying to detect if the engine has started or not:

    command:
      - /bin/bash
      - -c 
      - |
        # Launch MSSQL and send to background
        /opt/mssql/bin/sqlservr &
        # Wait for it to be available
        echo "Waiting for MS SQL to be available ⏳"
        /opt/mssql-tools/bin/sqlcmd -l 30 -S localhost -h-1 -V1 -U sa -P $$SA_PASSWORD -Q "SET NOCOUNT ON SELECT \"YAY WE ARE UP\" , @@servername"
        is_up=$$?
        while [ $$is_up -ne 0 ] ; do 
          echo -e $$(date) 
          /opt/mssql-tools/bin/sqlcmd -l 30 -S localhost -h-1 -V1 -U sa -P $$SA_PASSWORD -Q "SET NOCOUNT ON SELECT \"YAY WE ARE UP\" , @@servername"
          is_up=$$?
          sleep 5 
        done
        # Run every script in /scripts
        # TODO set a flag so that this is only done once on creation, 
        #      and not every time the container runs
        for foo in /scripts/*.sql
          do /opt/mssql-tools/bin/sqlcmd -U sa -P $$SA_PASSWORD -l 30 -e -i $$foo
        done
        # So that the container doesn't shut down, sleep this thread
        sleep infinity

I'd be interested to know if this is a valid technique or not :)

vin-yu · 2019-01-16T19:13:13Z

@rmoff - this is great, and I think we can take it one step further.
SQL Server starts listening to port 1433 before start up completely finishes - you can see this in the /var/opt/mssql/log/errorlog in the start up section. After shortly after 'model' db starts up, SQL Server starts listening to 1433. There are still user databases that need to finish recovery along with tempdb starting up and several other services. A better way would be to evaluate if all databases are online before running start up scripts.

select case when exists(select * from sys.databases where state <> 0) then 0 else 1 end;

if 1, all databases are online
if 0, not all databases are online

aharpervc · 2019-01-17T23:44:27Z

Here's a healthcheck that I wrote that waits for dbs to come online, might be useful.

/opt/mssql-tools/bin/sqlcmd -H localhost -U sa -P "$MSSQL_SA_PASSWORD" -l 1 -t 1 -Q "select name from sys.databases where state_desc != 'ONLINE'" | grep --quiet '0 rows affected' || exit 1

Save that as healthcheck.sh. My dockerfile looks like this (it's a base image for other db containers to build off of):

FROM mcr.microsoft.com/mssql/server:2017-latest-ubuntu

ENV ACCEPT_EULA=Y MSSQL_COLLATION=Latin1_General_CI_AI

COPY ./healthcheck.sh /healthcheck.sh
RUN chmod +x /healthcheck.sh

HEALTHCHECK --interval=1s --timeout=10s --start-period=60s --retries=3 CMD /healthcheck.sh

craigjar · 2019-12-12T19:48:54Z

@twright-msft @rmoff instead of the sleep infinity at the end, you can capture the pid and use wait.

    command:
      - /bin/bash
      - -c 
      - |
        # Launch MSSQL and send to background
        /opt/mssql/bin/sqlservr &
        pid=$!
        # Wait for it to be available
        echo "Waiting for MS SQL to be available ⏳"
        /opt/mssql-tools/bin/sqlcmd -l 30 -S localhost -h-1 -V1 -U sa -P $$SA_PASSWORD -Q "SET NOCOUNT ON SELECT \"YAY WE ARE UP\" , @@servername"
        is_up=$$?
        while [ $$is_up -ne 0 ] ; do 
          echo -e $$(date) 
          /opt/mssql-tools/bin/sqlcmd -l 30 -S localhost -h-1 -V1 -U sa -P $$SA_PASSWORD -Q "SET NOCOUNT ON SELECT \"YAY WE ARE UP\" , @@servername"
          is_up=$$?
          sleep 5 
        done
        # Run every script in /scripts
        # TODO set a flag so that this is only done once on creation, 
        #      and not every time the container runs
        for foo in /scripts/*.sql
          do /opt/mssql-tools/bin/sqlcmd -U sa -P $$SA_PASSWORD -l 30 -e -i $$foo
        done
        # Wait on the sqlserver process
        wait $pid

govipul · 2020-04-28T08:45:29Z

Do we have any update on this issue.

Fosol · 2020-05-13T20:21:23Z

I get the following error when I attempt to do use the above example.

Error: Microsoft ODBC Driver 17 for SQL Server : Driver's SQLAllocHandle on SQL_HANDLE_HENV failed

Has anyone solved this massive bug in this image? This is issue is better described here - #431

PilotBob · 2020-05-16T22:15:39Z

Add my voice to this suggestion. The way MySQL does it is, any .sql scripts you mount into /docker-entrypoint-initdb.d/ get run in the order they are mounted. It's really nice way to seed test data in my dev docker compose.

jeffreyschultz · 2020-07-03T23:58:27Z

Add my voice to this suggestion. The way MySQL does it is, any .sql scripts you mount into /docker-entrypoint-initdb.d/ get run in the order they are mounted. It's really nice way to seed test data in my dev docker compose.

This is also how Postgresql handles it as well. I was sort of expecting this same convention, but shocked that I also had to control the execution of sqlservr as well.

I had to update this to get this to work in my case. There were a couple $ that were not doubled up. I also added a message to let you know when the scripts had finished executing.

command:
      - /bin/bash
      - -c
      - |
        # Launch MSSQL and send to background
        /opt/mssql/bin/sqlservr &
        pid=$$!
        # Wait for it to be available
        echo "Waiting for MS SQL to be available ⏳"
        /opt/mssql-tools/bin/sqlcmd -l 30 -S localhost -h-1 -V1 -U sa -P $$SA_PASSWORD -Q "SET NOCOUNT ON SELECT \"YAY WE ARE UP\" , @@servername"
        is_up=$$?
        while [ $$is_up -ne 0 ] ; do
          echo -e $$(date)
          /opt/mssql-tools/bin/sqlcmd -l 30 -S localhost -h-1 -V1 -U sa -P $$SA_PASSWORD -Q "SET NOCOUNT ON SELECT \"YAY WE ARE UP\" , @@servername"
          is_up=$$?
          sleep 5
        done
        # Run every script in /scripts
        # TODO set a flag so that this is only done once on creation,
        #      and not every time the container runs
        for foo in /scripts/*.sql
          do /opt/mssql-tools/bin/sqlcmd -U sa -P $$SA_PASSWORD -l 30 -e -i $$foo
        done
        echo "All scripts have been executed. Waiting for MS SQL(pid $$pid) to terminate."
        # Wait on the sqlserver process
        wait $$pid

KvistA-ELS · 2020-09-07T15:22:45Z

I struggle a bit to understand why entrypoint.d isn't just a part of the image - it's so standard in the docker community. Why force everyone to make odd solutions when it would be so easy to do a standard implementation for this and not having to handle issues for years about it...

jeffreyschultz · 2020-09-08T03:06:08Z

I struggle a bit to understand why entrypoint.d isn't just a part of the image - it's so standard in the docker community. Why force everyone to make odd solutions when it would be so easy to do a standard implementation for this and not having to handle issues for years about it...

That would just make too much sense, apparently. This thread has been open since 2017.

knutwannheden · 2020-09-08T04:26:02Z

Also note that a lot of other databases already offer this option in their Docker images and tools like https://www.testcontainers.org/ then even provide a standardized API to use this feature.

paul-barnes · 2020-11-11T14:06:20Z

One issue with the suggestions here is that the container does not shut down cleanly. Since sql server is not running in PID 1, it does not get the SIGTERM signal, and when you try to shut down the container, it times out and kills the container. One solution is to trap SIGTERM and forward it on to the sql server process:

command:
      - /bin/bash
      - -c 
      - |
        # Launch MSSQL and send to background
        /opt/mssql/bin/sqlservr &
        pid=$!
        # Wait for it to be available
        echo "Waiting for MS SQL to be available ⏳"
        /opt/mssql-tools/bin/sqlcmd -l 30 -S localhost -h-1 -V1 -U sa -P $$SA_PASSWORD -Q "SET NOCOUNT ON SELECT \"YAY WE ARE UP\" , @@servername"
        is_up=$$?
        while [ $$is_up -ne 0 ] ; do 
          echo -e $$(date) 
          /opt/mssql-tools/bin/sqlcmd -l 30 -S localhost -h-1 -V1 -U sa -P $$SA_PASSWORD -Q "SET NOCOUNT ON SELECT \"YAY WE ARE UP\" , @@servername"
          is_up=$$?
          sleep 5 
        done
        # Run every script in /scripts
        # TODO set a flag so that this is only done once on creation, 
        #      and not every time the container runs
        for foo in /scripts/*.sql
          do /opt/mssql-tools/bin/sqlcmd -U sa -P $$SA_PASSWORD -l 30 -e -i $$foo
        done
        # trap SIGTERM and send same to sqlservr process for clean shutdown
        trap "kill -15 $$pid" SIGTERM
        # Wait on the sqlserver process
        wait $$pid
        exit 0

jurij · 2020-12-05T00:35:00Z

Also note that a lot of other databases already offer this option in their Docker images and tools like https://www.testcontainers.org/ then even provide a standardized API to use this feature.

do you know any solution for .NET/C# environment?

ramonsmits · 2020-12-10T15:17:39Z

This would be super useful! There is a solution provided for Linux but does one exists for Windows?

sks2141 · 2021-09-02T04:23:26Z

pid=$!

needs to be pid=$$!

potatoqualitee · 2022-09-15T21:21:56Z

This would still be a beneficial addition 🙏🏼

tracker1 · 2022-09-19T18:38:21Z

https://github.com/tracker1/mssql-docker-enhanced

potatoqualitee · 2022-09-20T08:51:38Z

Thank you @tracker1, I also have some advanced containers and can do it on my own but I am aiming for official containers from Microsoft to do this instead of pointing people to community containers.

ci-vamp · 2022-11-05T23:53:01Z

https://github.com/tracker1/mssql-docker-enhanced

very clean and thorough. thank you for sharing

hlovdal · 2022-12-06T10:48:16Z

@tracker1 Tip: rather than using and depending on an external tool like iptables to restrict access you could use /opt/mssql/bin/mssql-conf set network.ipaddress 127.0.0.1 (and 0.0.0.0) instead, example here.

hlovdal · 2022-12-07T09:51:46Z

And for a solution to have a generic /docker-entrypoint-initdb.d directory to populate with *.sql scripts, see this answer which does not require wrapping the upstream docker container with a new, custom built container, only requires a few changes to the docker-compose.yml file.

gkhedekar5758 · 2023-06-19T05:31:50Z

@rmoff and all others, I landed up in a situation where I have used the above logic to run the sql scripts (it creates a DB and tables in it), but now whenever I run the container it runs and then I get errors like the DB already exists,table xxxx already exist. How can i stop that, I have been searchinng answer or stackoverflow but have not been succesful yet. Can anyone help?

ci-vamp · 2023-06-20T11:12:17Z

@gkhedekar5758 you can make the scripts idempotent using the sql server equivalent of IF NOT EXISTS clause

jlazzeri-altair · 2024-01-23T16:59:24Z

Any updates / recommendations for this?

dermeister0 mentioned this issue Jan 13, 2017

Use ServiceMonitor instead of infinite loop #16

Closed

twright-msft added the mssql-server-linux label Feb 6, 2017

twright-msft mentioned this issue Feb 28, 2017

Run scripts in /docker-entrypoint-initdb.d #36

Closed

twright-msft mentioned this issue Mar 17, 2017

Tools are not installed in the container #8

Closed

This was referenced Apr 4, 2017

Add initdb logic shanegenschaw/mssql-docker#2

Open

Allow for running initialization scripts #60

Closed

twright-msft mentioned this issue Aug 21, 2018

Run scripts if exists in directory #349

Closed

pattisdr mentioned this issue Jan 7, 2022

Fidesops 78 mssql support ethyca/fidesops#151

Merged

2 tasks

potatoqualitee mentioned this issue Sep 15, 2022

Enable env var for auto creation of MSSQL_DB if var exists and db does not dataplat/docker#17

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Allow pointing to and executing a SQL script on run #11

Allow pointing to and executing a SQL script on run #11

twright-msft commented Jan 9, 2017

rfaaberg commented Feb 5, 2017

twright-msft commented Feb 5, 2017

twright-msft commented Feb 6, 2017

twright-msft commented Mar 17, 2017

shanegenschaw commented Apr 4, 2017 •

edited

Loading

jvitor83 commented Jul 11, 2018 •

edited

Loading

rmoff commented Jan 8, 2019

twright-msft commented Jan 16, 2019

rmoff commented Jan 16, 2019

vin-yu commented Jan 16, 2019 •

edited

Loading

aharpervc commented Jan 17, 2019

craigjar commented Dec 12, 2019 •

edited

Loading

govipul commented Apr 28, 2020

Fosol commented May 13, 2020 •

edited

Loading

PilotBob commented May 16, 2020 •

edited

Loading

jeffreyschultz commented Jul 3, 2020 •

edited

Loading

KvistA-ELS commented Sep 7, 2020

jeffreyschultz commented Sep 8, 2020

knutwannheden commented Sep 8, 2020

paul-barnes commented Nov 11, 2020 •

edited

Loading

jurij commented Dec 5, 2020

ramonsmits commented Dec 10, 2020

sks2141 commented Sep 2, 2021

potatoqualitee commented Sep 15, 2022

tracker1 commented Sep 19, 2022

potatoqualitee commented Sep 20, 2022

ci-vamp commented Nov 5, 2022

hlovdal commented Dec 6, 2022

hlovdal commented Dec 7, 2022

gkhedekar5758 commented Jun 19, 2023

ci-vamp commented Jun 20, 2023

jlazzeri-altair commented Jan 23, 2024

Allow pointing to and executing a SQL script on run #11

Allow pointing to and executing a SQL script on run #11

Comments

twright-msft commented Jan 9, 2017

rfaaberg commented Feb 5, 2017

twright-msft commented Feb 5, 2017

twright-msft commented Feb 6, 2017

twright-msft commented Mar 17, 2017

shanegenschaw commented Apr 4, 2017 • edited Loading

jvitor83 commented Jul 11, 2018 • edited Loading

rmoff commented Jan 8, 2019

twright-msft commented Jan 16, 2019

rmoff commented Jan 16, 2019

vin-yu commented Jan 16, 2019 • edited Loading

aharpervc commented Jan 17, 2019

craigjar commented Dec 12, 2019 • edited Loading

govipul commented Apr 28, 2020

Fosol commented May 13, 2020 • edited Loading

PilotBob commented May 16, 2020 • edited Loading

jeffreyschultz commented Jul 3, 2020 • edited Loading

KvistA-ELS commented Sep 7, 2020

jeffreyschultz commented Sep 8, 2020

knutwannheden commented Sep 8, 2020

paul-barnes commented Nov 11, 2020 • edited Loading

jurij commented Dec 5, 2020

ramonsmits commented Dec 10, 2020

sks2141 commented Sep 2, 2021

potatoqualitee commented Sep 15, 2022

tracker1 commented Sep 19, 2022

potatoqualitee commented Sep 20, 2022

ci-vamp commented Nov 5, 2022

hlovdal commented Dec 6, 2022

hlovdal commented Dec 7, 2022

gkhedekar5758 commented Jun 19, 2023

ci-vamp commented Jun 20, 2023

jlazzeri-altair commented Jan 23, 2024

shanegenschaw commented Apr 4, 2017 •

edited

Loading

jvitor83 commented Jul 11, 2018 •

edited

Loading

vin-yu commented Jan 16, 2019 •

edited

Loading

craigjar commented Dec 12, 2019 •

edited

Loading

Fosol commented May 13, 2020 •

edited

Loading

PilotBob commented May 16, 2020 •

edited

Loading

jeffreyschultz commented Jul 3, 2020 •

edited

Loading

paul-barnes commented Nov 11, 2020 •

edited

Loading