Attach databases automatically upon startup

[twright-msft]

The SQL Server on Windows container image has a startup script that will accept an JSON-formatted environment variable that represents a list of databases including data/log files to attach to the SQL Server instance on container run.

The script is currently based on PowerShell on SQL Server on Windows side: https://github.com/Microsoft/sql-server-samples/blob/master/samples/manage/windows-containers/mssql-server-2016-express-windows/start.ps1

For SQL Server on Linux container image we could wait for PowerShell SQLPS module to be available or use something like sqlcmd to attach the DBs.

This issue should be tracked along with the related issue to create a database on container run.
#2

[arruw]

Maybe good idea. Attach or create empty database if file not exists?

[schmunk42]

Not providing this feature is an Armutszeugnis (there's just no English translation for that) for a billion dollar company like Microsoft. Did someone of your team ever looked at another database Docker image?

For everyone else who struggling with this and does not have a "Power"-Shell at hand: https://hub.docker.com/r/tsgkadot/mssql-tools/

[LuisBosquez]

@schmunk42 Danke schön for the feedback! We actually did look at different databases like mysql and postgres and they use different strategies for initializing the databases, but there isn't an intuitive solution to attaching a database file with initial data automatically. This is why we decided to publish an initial iteration of the image and work with the community to create an optimal solution.

Can you tell us what an ideal solution to this would be?

[schmunk42]

OK, so let me say sorry. So at least you're pretty responsive ;)

Maybe my problem is also that I just don't know how the MSSQL server works in detail.

The background of using the db image is that I want to run this test suite.

All (most) other databases simply work for tests by specifying a database, user and password.
Then you can use that in your config. While I am able to connect from another container with sqlcmd I get this following error during a PHP connection:

root@22e85f294d9c:/app# vendor/bin/phpunit --verbose --group mssql
PHP Warning:  Declaration of yiiunit\framework\db\sqlite\QueryBuilderTest::testBatchInsert() should be compatible with yiiunit\framework\db\QueryBuilderTest::testBatchInsert($table, $columns, $value, $expected) in /app/tests/framework/db/sqlite/QueryBuilderTest.php on line 104
PHPUnit 4.8.27 by Sebastian Bergmann and contributors.

Runtime:	PHP 7.0.8-0ubuntu0.16.04.3
Configuration:	/app/phpunit.xml.dist

PHP Fatal error:  pdo_sqlsrv_db_handle_factory: Unknown exception caught in /app/framework/db/Connection.php on line 629

Since I have no idea what going on may it's related to

but there isn't an intuitive solution to attaching a database file with initial data automatically

"Attaching a file? Just gimme a db, I don't care where it is!" I'll throw it away in 1 minute anyway.

Why is there no MSSQL_DATABASE_NAME, etc... which is processed in a script?

[twright-msft]

Hi @schmunk42
Sounds like you have the sqlcmd command working in a connected tools container. If so you can connect to your SQL Server by running sqlcmd -S <ip_address> -U sa -P <you_sa_login_password>

Once you are connected you can create a database like this:

CREATE DATABASE yii2basic
GO

It looks like once you have the database created, you need to run some initial commands to create some schema and populate some test data:
https://github.com/yiisoft/yii2/blob/master/docs/guide/start-databases.md

We will work on adding support for passing an initial db name as an env variable to the entrypoint script. One step at a time!

Hope that helps!

[schmunk42]

With sqlcmd, I can import data into the server. This has to be part of the image. It's requiring 3,5 GB memory anyway ;)

@LuisBosquez @twright-msft Do you have a prebuilt PHP image with pdo_srvsql, like one that's verified to be working?

[LuisBosquez]

@schmunk42 we haven't published one yet, but it's a great idea. We have a set of working demos for PHP with our newest drivers (we now have a PECL-based pdo_sqlsrv), I can get a draft started of a Dockerfile and send it over so we can improve it together. What do you think?

[LuisBosquez]

@schmunk42 I found some time and put this Dockerfile together: php-mssql/Dockerfile. I also uploaded it to DockerHub under lbosqmsft/php-mssql. Let me know what you think!

[jorgemmsilva]

I'm also struggling with this.

Trying to do something like this on the Dockerfile, but to no avail.
COPY './create_databases.sql' c:\create_databases.sql
RUN sqlcmd -i c:\create_databases.sql

When a container is instantiated with the built image, it does have the *.mdf created, but SQLServer doesn't know that they exist (actually throws an error if we try to re-create them, saying the mdf files already exist).

Right now we are only copying the script to the image, then we have to manually start a container and run:
docker exec -it <CONTAINER_ID> sqlcmd -i c:\create_databases.sql

This does create the databases, but if the container is restarted, they are suddenly gone.

how are we supposed to build an image with a custom DB?

[sb185219]

@jorgemmsilva I've tried something similar by modifying the example from jboesl/docker-mssql-linux, but when the sqlcmd tries to run, it just times out

[twright-msft]

@jorgemmsilva and @sbarne3
There are basically two options for now:

1. Have the application create the database/schema when the application container starts up.
   Examples:
   https://github.com/twright-msft/mssql-aspnet-docker-demo-app (this is the standard ASP.NET Core container app which uses Entity Framework to create the database schema in the database that you connect to)

https://github.com/twright-msft/example-voting-app (the Java container is the one that creates the DB in this case)

2. Create the DB using sqlcmd and a .sql file when the DB container starts up. Example:
   https://github.com/twright-msft/mssql-node-docker-demo-app

Keep in mind that you need to mount a volume to the host or to a container volume to persist your data directory in the container (default: /var/opt/mssql/data).
More info:
https://docs.docker.com/engine/tutorials/dockervolumes/

[Gorbush]

I use the SQL server container for integration testing so i do this in a bit different way:
I detach the DB from original SQL server using
USE master
DBCC SHRINKDATABASE (DB_NAME, TRUNCATEONLY);
ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC sp_detach_db @dbname = 'DB_NAME', @skipchecks = @'true'

Then copy files to the docker's project folder - both DB_NAME.mdf and DB_NAME.ldf
Add these files to the image using
ADD DB_NAME.mdf /var/opt/mssql/data/
ADD DB_NAME.ldf /var/opt/mssql/data/

or copy inside from attached volume using entrypoint.sh
and after server start attach it back:

CREATE DATABASE DB_NAME
ON (FILENAME = '/var/opt/mssql/data/DB_NAME.mdf'),
(FILENAME = '/var/opt/mssql/data/DB_NAME.ldf')
FOR ATTACH
GO
ALTER DATABASE DB_NAME SET MULTI_USER
USE DB_NAME
GO
CREATE LOGIN DATABASE_USER WITH PASSWORD = '1P4ssw0rd'
GO
CREATE USER DATABASE_USER FOR LOGIN DATABASE_USER
GO
ALTER SERVER ROLE sysadmin ADD MEMBER [DATABASE_USER]
GO
ALTER USER DATABASE_USER WITH LOGIN = DATABASE_USER
GO
exec sp_addrolemember 'db_owner', 'DATABASE_USER'

This fixes missing login for the DB, changes the pass and made this user the owner of db - so it works!

Forgot to mention - the command to run sql inside:
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'My@Super@Secret' -i /data/setup-db.sql

[twright-msft]

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.

[henryruhs]

Why not using something that already exists and make it public:
https://github.com/DataGrip/docker-env/tree/master/mssql-server-linux

[twright-msft]

We are planning to have the sqlservr process do these kinds of things on startup. By having sqlservr do it on startup by reading env vars or the mssql.conf file we can have a consistent way of doing these kinds of things in many different deployment scenarios - yum|apt-get|zypper package installs, BOSH, any type of container not just Docker, on Windows or on Linux, etc.

[henryruhs]

I understand, can you give use a roadmap or timetable?

[twright-msft]

Aiming for CTP 2.1 (mid-May) release.

[tap-modernwebapp]

2.1 released without this functionality embedded?

[twright-msft]

We did the first phase of this by having handling more environment variables in CTP 2.1
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-environment-variables

We didnt get the create DB part done yet. I'm not sure we are going to be able to get that done for SQL Server 2017 since we are closing in on the RC milestones pretty soon. You may want to use an alternative approach for DB creation like the examples I shared above for now. We'll continue to work on this but it's probably not going to happen in the next few months.

[tap-modernwebapp]

As far as I have seen, I do not think it's possible to attach DBs using the env variables within Dockerfile? Tried that but somehow the start of the SQL server process ignored that portion. Think it's quite an important aspect to be able to run the instance and attach/create the DBs immediately after..

…

On 24 May 2017 at 12:06, Travis Wright ***@***.***> wrote: We did the first phase of this by having handling more environment variables in CTP 2.1 https://docs.microsoft.com/en-us/sql/linux/sql-server-linux- configure-environment-variables We didnt get the create DB part done yet. I'm not sure we are going to be able to get that done for SQL Server 2017 since we are closing in on the RC milestones pretty soon. You may want to use an alternative approach for DB creation like the examples I shared above for now. We'll continue to work on this but it's probably not going to happen in the next few months. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#4 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AbcU_8l-3HvqhfM-jeHqjBV5pdw5ivRmks5r86zbgaJpZM4LbC-F> .

-- This email is intended only for the named addressee(s) and may contain confidential and/or privileged information. If you are not the named addressee (or have received this e-mail in error), please notify the sender immediately. The unauthorised use, disclosure, distribution or copying of the contents in this e-mail is prohibited. Thank you

[twright-msft]

Attaching DBs via env var is currently only possible on the SQL Server Windows containers.
For example:
https://github.com/Microsoft/mssql-docker/tree/master/windows/mssql-server-windows#run-this-sample

We want to do this on the Linux containers too, but we want to do it as part of sqlservr startup and not in some kind of entrypoint/cmd script. That just is going to take a bit more time. The reason that we want to do this as part of sqlservr startup is so that we have a consistent way of doing this on Windows and on Linux and in all scenarios - native installs, containers, BOSH, etc.

[tap-modernwebapp]

Roger that. Looking forward to the feature on linux.

…

On 25 May 2017 at 09:36, Travis Wright ***@***.***> wrote: Attaching DBs via env var is currently only possible on the SQL Server *Windows* containers. For example: https://github.com/Microsoft/mssql-docker/tree/master/ windows/mssql-server-windows#run-this-sample We want to do this on the Linux containers too, but we want to do it as part of sqlservr startup and not in some kind of entrypoint/cmd script. That just is going to take a bit more time. The reason that we want to do this as part of sqlservr startup is so that we have a consistent way of doing this on Windows and on Linux and in all scenarios - native installs, containers, BOSH, etc. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#4 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AbcU_4bhaZEnb0jwXBkTijx_5znqtUuMks5r9NsUgaJpZM4LbC-F> .

-- This email is intended only for the named addressee(s) and may contain confidential and/or privileged information. If you are not the named addressee (or have received this e-mail in error), please notify the sender immediately. The unauthorised use, disclosure, distribution or copying of the contents in this e-mail is prohibited. Thank you

[jsilberm]

Hi there @twright-msft . We at Portworx would be thrilled at the opportunity of supporting Microsoft with a truly stateful containerized MSSQL server --- one that can be highly available, durable and recoverable. However, we are at an impasse straight out of the gate. I don't know how we can demonstrate any notion of durability, when I can't attach my database on startup. This requirement would seem to be among the most basic requirements for taking 'mssql-docker' out of the pure prototype phase. Can you please offer an ETA for when this will be available? Or any other suggestion rather than "load data on startup everytime"? Appreciate your help. We are happy to collaborate. Thanks.

[twright-msft]

@jsilberm - You can attach or create databases on startup; it just takes a little bit more effort than an ideal solution. The ideal solution is that you provide some configuration information via env variables like DB Name, file locations, etc. and we then process that on start up to create/attach DBs. For now, creating or attaching DBs has to be done via a entry point script that executes sqlcmd to run some queries to attach or create a DB. You can see some examples of these approaches in the links on my Feb 27 post above.

Feel free to reach out to me directly via email at twright a t microsoft d o t youknowwhat if you are interested in exploring a partnership further.

[ijpatricio]

hello everyone. thought I should share this.

sqlops, free closs platform
https://database.guide/what-is-sql-operations-studio-sqlops/

after having your container running, with this app, just connect with 127.0.0.1 (port 1433 will be default), sa and your_sa_password and then you have a gui!!

to create databases, or make your db operations!

Happy coding!

[carljones4]

One note for those pursuing this approach when it may not be needed...

You can always create your own docker image (derived from the dockerhub sql server linux image) that has your sql server database attached, and when instantiating a container, use that image instead of the dockerhub sql server linux image from Microsoft (which obviously doesn't have your database attached).

That may work for some scenarios.

[ntziolis]

Any progress on enabling this?

[mcmoe]

Have you tried my fork at https://github.com/mcmoe/mssqldocker .... It allows you to configure a db and user.

[tracker1]

I'm doing something similar to this... I'm creating my own downstream image, then adding a startup script and an initialize-db sql script expecting certain variables. I'm installing iptables to ensure no external connections can be made until the service is up, intialized and ready... It's a bit of a hack.

startup.sh

#!/bin/bash

export MSSQL_ST_PASS=${MSSQL_ST_PASS:-DEFAULT}
export SA_PASSWORD=${SA_PASSWORD:-DEFAULT}
export TENANT=${TENANT:-DEFAULT}

_term() {
  echo "Stopping SQL Server..."
  pkill -SIGINT -c -e sql
  sleep 2
  pkill -SIGTERM -c -e sql
}

echo "Starting SQL Server..."

# Block outside connections until ready
iptables -A INPUT -p tcp -s 127.0.0.1 --destination-port 1433 -j ACCEPT
iptables -A INPUT -p tcp --destination-port 1433 -j DROP

# run SQL Server in the background
nohup /opt/mssql/bin/sqlservr --accept-eula 2>&1 > /tmp/sqlserver.log &

# capture the child process reference
child=$!

# start outputting the sqlserver.log to stdout (detached)
tail -q --retry -n 1000 -f /tmp/sqlserver.log &

# trap for SIGTERM, forward to child
trap _term SIGTERM

# wait for SQL Server to be ready to accept connections
grep -q "Service Broker manager has started" <(tail -q --retry -n 1000 -f /tmp/sqlserver.log)
sleep 3
echo "SQL Server Started, Initializing Databases..."
# echo "MSSQL_ST_PASS=$MSSQL_ST_PASS"

/opt/mssql-tools/bin/sqlcmd \
    -S tcp:127.0.0.1,1433 \
    -U SA \
    -P "$SA_PASSWORD" \
    -v "MSSQL_ST_PASS=$MSSQL_ST_PASS" \
    -v "TENANT=$TENANT" \
    -i /root/init-st-databases.sql

# Remove existing iptables rules, and allow app 1433 connections for mssql
sleep 1
iptables -D INPUT -p tcp -s 127.0.0.1 --destination-port 1433 -j ACCEPT
iptables -D INPUT -p tcp --destination-port 1433 -j DROP
iptables -I INPUT -p tcp --destination-port 1433 -j ACCEPT

echo "Databases Initialized"


# Unblock/allow connections
wait "$child"