mssql-docker
mssql-docker copied to clipboard
microsoft
Allow pointing to and executing a SQL script on run
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.
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.
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
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.
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.
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.
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
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
@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.
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 :)
@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
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
@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
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 - https://github.com/microsoft/mssql-docker/issues/431
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.
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
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...
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.
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.
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
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?
This would be super useful! There is a solution provided for Linux but does one exists for Windows?
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.
https://github.com/tracker1/mssql-docker-enhanced
very clean and thorough. thank you for sharing
@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.
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.
@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?
