Allow pointing to and executing a SQL script on run about mssql-docker HOT 32 OPEN

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

from mssql-docker.

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

from mssql-docker.

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...

from mssql-docker.

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 :)

from mssql-docker.

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.

from mssql-docker.

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

from mssql-docker.

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.

from mssql-docker.

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

from mssql-docker.

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

from mssql-docker.

pid=$!

needs to be pid=$$!

from mssql-docker.

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

from mssql-docker.

@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.

from mssql-docker.

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.

from mssql-docker.

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.

from mssql-docker.

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

from mssql-docker.

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.

from mssql-docker.

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

from mssql-docker.

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.

from mssql-docker.

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.

from mssql-docker.

@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.

from mssql-docker.

@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

from mssql-docker.

@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

from mssql-docker.

Do we have any update on this issue.

from mssql-docker.

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

from mssql-docker.

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.

from mssql-docker.

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?

from mssql-docker.

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

from mssql-docker.

This would still be a beneficial addition 🙏🏼

from mssql-docker.

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.

from mssql-docker.

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

very clean and thorough. thank you for sharing

from mssql-docker.

@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?

from mssql-docker.

Any updates / recommendations for this?

from mssql-docker.