Creating a database automatically upon startup

[LuisBosquez]

A couple of comments (4) in the DockerHub page suggest adding parameters to create a database with username and password automatically.

[rburgst]

+1

[rburgst]

Is there a timeline for this? Also the missing tools in this container/image (see #8 ) make this hard to achieve for me on my own.

[potzkovge]

+1

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

[kuncevic]

Was wondering how would you create default db and user just by using docker-compose.yaml file That is what i've got now:

  sqlserver:
    container_name: 'sqlserver'
    image: microsoft/mssql-server-linux:2017-CU1
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_PID=Developer
      - SA_PASSWORD=<pwd>
      - MSSQL_DB=mydb (was expected db to be created)
      - MSSQL_USER=me
      - MSSQL_PASSWORD=<pwd>
    ports:
      - "1433:1433"
    networks:
      - app-network

Using this image for now https://github.com/mcmoe/mssqldocker to create the container and default db

[ghost]

hi @kuncevic did this docker compose work for you? did you get a db and user on the container?

[twright-msft]

Creating databases and users at container run time is not implemented yet.

[kuncevic]

@pjpradeepjha using this image https://github.com/mcmoe/mssqldocker - yes But the mssql docker image is so huge in size also it needs at least 3.5 gb of ram to run (2gb with CU2). So I ended up using PostgresSQL alpine image which is like 14mb size. I wish mssql has a small alpine image just to handle nothing else but basic select, update and delete

[ghost]

@kuncevic @twright-msft thanks for the comments. appreciate the help. :) I was constantly trying to create user and db from docker compose on the mssql docker image to no effect.

[twright-msft]

@kuncevic - We will continue to make the SQL image container smaller. Just takes some time to minify something like SQL Server as you can imagine. :)

[brunooliveiramac]

:) Thanks a lot @kuncevic

[felixhayashi]

Bump. Both postgres and mysql images already support using environment variable to create an initial database when the image is run and the container is created (POSTGRES_DB: "mydb" or MYSQL_DATABASE: "mydb"). Would be great if this were also supported in the official mssql image, otherwise we need to rely on executing sqlcmd to create the db on container startup.

[celamb4]

Any updates? Its been in the backlog for over 2 years. Doesnt seem like that big of a request. If we cant create one based on ENV is there a default DB created when the container boots up?

[0x-2a]

The naming convention in these env vars is inconsistent with industry standard database connectors.

MYSQL_DATABASE here actually refers to the database Instance in connector drivers, not the Database.

For example, MYSQL_DATABASE treated as an Instance variable would have users connect with the following (which is currently what it does): sqlserver://localhost\Foo:1433

Where as MYSQL_DATABASE as a Database variable would have users connect with: sqlserver://localhost:1433;database=Foo, which is currently does not.

I think this is why some folks have connection issues and others don't. It would probably be best to support both.

For others wondering, it looks like you can do this after your docker starts up:

docker exec -i YOUR_MSSQL_DOCKER_CONTAINER /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'YOUR_MSSQL_PASS_HERE' -Q 'CREATE DATABASE YOUR_DATABASE_NAME'

[lkurzyniec]

It is possible, here you are examples: https://github.com/microsoft/sql-server-samples/tree/master/samples/containers

And also my example, IMHO much more straight forward: https://github.com/lkurzyniec/netcore-boilerplate

docker-compose

version: "3.6"

services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2017-latest
    container_name: mssql
    command: /bin/bash ./entrypoint.sh
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_PID=Express
      - SA_PASSWORD=SomeStrongPwd123
    volumes:
      - dbdata:/var/opt/mssql/data
      - ./db/mssql/docker-entrypoint.sh:/entrypoint.sh
      - ./db/mssql/docker-db-init.sh:/db-init.sh
      - ./db/mssql/mssql-cars.sql:/db-init.sql

  netcore-boilerplate:
    image: netcore-boilerplate:local
    container_name: netcore-boilerplate
    build:
      context: .
    ports:
      - 5000:80
    depends_on:
      - mssql

volumes:
  dbdata:

docker-entrypoint.sh

#start SQL Server, start the script to create/setup the DB
 /db-init.sh & /opt/mssql/bin/sqlservr

!!! There is a space in front of.

db-init.sh

#wait for the SQL Server to come up
sleep 30s

echo "running set up script"
#run the setup script to create the DB and the schema in the DB
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P SomeStrongPwd123 -d master -i db-init.sql

db-init.sql

USE [master]
GO

IF DB_ID('cars') IS NOT NULL
  set noexec on               -- prevent creation when already exists

/****** Object:  Database [cars]    Script Date: 18.10.2019 18:33:09 ******/
CREATE DATABASE [cars];
GO

USE [cars]
GO

/****** Object:  Table [dbo].[Cars]    Script Date: 18.10.2019 18:33:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cars](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Plate] [varchar](50) NOT NULL,
	[Model] [varchar](50) NULL,
	[OwnerId] [int] NULL,
 CONSTRAINT [PK_Cars] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Owners]    Script Date: 18.10.2019 18:33:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Owners](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[FullName]  AS (([FirstName]+' ')+[LastName]),
 CONSTRAINT [PK_Owners] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[Cars] ON
GO
INSERT [dbo].[Cars] ([Id], [Plate], [Model], [OwnerId]) VALUES (1, N'JHV 770', N'Mercedes-Benz GLE Coupe', 1)
GO
INSERT [dbo].[Cars] ([Id], [Plate], [Model], [OwnerId]) VALUES (2, N'TAD-3173', N'Datsun GO+', 1)
GO
INSERT [dbo].[Cars] ([Id], [Plate], [Model], [OwnerId]) VALUES (3, N'43-L348', N'Maruti Suzuki Swift', 2)
GO
INSERT [dbo].[Cars] ([Id], [Plate], [Model], [OwnerId]) VALUES (4, N'XPB-2935', N'Land Rover Discovery Sport', 3)
GO
INSERT [dbo].[Cars] ([Id], [Plate], [Model], [OwnerId]) VALUES (5, N'805-UXC', N'Nissan GT-R', NULL)
GO
SET IDENTITY_INSERT [dbo].[Cars] OFF
GO

SET IDENTITY_INSERT [dbo].[Owners] ON
GO
INSERT [dbo].[Owners] ([Id], [FirstName], [LastName]) VALUES (1, N'Peter', N'Diaz')
GO
INSERT [dbo].[Owners] ([Id], [FirstName], [LastName]) VALUES (2, N'Leon', N'Leonard')
GO
INSERT [dbo].[Owners] ([Id], [FirstName], [LastName]) VALUES (3, N'Shirley', N'Baker')
GO
INSERT [dbo].[Owners] ([Id], [FirstName], [LastName]) VALUES (4, N'Nancy', N'Davis')
GO
SET IDENTITY_INSERT [dbo].[Owners] OFF
GO

ALTER TABLE [dbo].[Cars]  WITH CHECK ADD  CONSTRAINT [FK_Cars_Owners] FOREIGN KEY([OwnerId])
REFERENCES [dbo].[Owners] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Cars] CHECK CONSTRAINT [FK_Cars_Owners]
GO

CREATE LOGIN [user]
    WITH PASSWORD = 'simplePWD123!';

CREATE USER [user] FOR LOGIN [user] WITH DEFAULT_SCHEMA=[dbo]
GO

GRANT ALL ON Cars TO [user];
GRANT ALL ON Owners TO [user];
GO

[damien-git]

How do we know sleep 30s will be the right delay ? I have been trying hacks like this, but nothing works... Support for database creation is still needed.

[vin-yu]

@damien-git check this sample out: https://github.com/microsoft/mssql-docker/tree/master/linux/preview/examples/mssql-customize

this will ping the server, and if it is ready, it will run the startup script, setup.sql. This is where you want to put your create DB script

[filiphr]

Sorry @vin-yu, but suggesting to create our own Docker image just so we can create a DB upon startup is not satisfiabile solution.

For me the idea to create a DB upon startup is because I want to use your own Docker image in our CI. I don't want to build a new image everytime you release a new image.

Would really be interested to know what the problem is to add such support to the official docker image? Why not move that example here and parameterise it?

[rburgst]

I totally agree with @filiphr . Also the fact that basically EVERY other db container offers such a feature should be an indication that this is a VERY useful thing to have.

[damien-git]

@vin-yu : thanks for the example, but it does not work for me. In configure-db.sh, ERRCODE is 1 before the server is done starting up, so it fails unless I add a sleep statement before (which makes the whole code useless). Also, there are several syntax errors on line 19.

[kkanellis]

@damien-git I am aware that the previous "solution" is neither complete nor appropriate, but after tweaking the configure-db.sh, I could (partly) replicate the desired behavior:

#!/bin/bash

TIMEOUT=60
DBSTATUS=1
ERRCODE=1
i=0

while [[ $i -lt $TIMEOUT ]] ; do
	i=$i+1
	DBSTATUS=$(/opt/mssql-tools/bin/sqlcmd -h -1 -t 1 -U sa -P $SA_PASSWORD -Q "SET NOCOUNT ON; Select SUM(state) from sys.databases")
	ERRCODE=$?
	sleep 1

	if [[ $DBSTATUS -eq 0 ]] && [[ $ERRCODE -eq 0 ]]; then
		break
	fi
done

if [[ $DBSTATUS -ne 0 ]] || [[ $ERRCODE -ne 0 ]]; then
	echo "SETUP: SQL Server took more than $TIMEOUT seconds to start up or one or more databases are not in an ONLINE state"
	exit 1
fi

sleep 2

# Run the setup script to create the DB and the schema in the DB
/opt/mssql-tools/bin/sqlcmd -S db -U sa -P $SA_PASSWORD -i /usr/config/setup.sql

[damien-git]

I ended up making my own hack. It works in my case, for now, but that is still not satisfying...

#!/bin/bash
# (see https://github.com/microsoft/mssql-docker/issues/2 )
echo "Container initialization: waiting for the server to come up"
while [ ! -f /var/opt/mssql/log/errorlog ]
do
  sleep 1
done
FOUND=0
i=0
while [[ $FOUND -ne 1 ]] && [[ $i -lt 60 ]]; do
  i=$i+1
  FOUND=$(grep -cim1 "Service Broker manager has started" /var/opt/mssql/log/errorlog)
  if [[ $FOUND -ne 1 ]]; then
    sleep 1
  fi
done
if [[ $FOUND -ne 1 ]]; then
  echo "Container initialization: Error: waited for more than 60 seconds for the server to start. Trying to create the database now..."
fi

echo "Container initialization: creating the database if needed"
/opt/mssql-tools/bin/sqlcmd etc...
echo "Container initialization: done"

[agates4]

this shouldn't be a closed issue

are there no plans to implement this extremely useful feature?

if the benefits are not immediately apparent then I would be happy to help explain them

[agates4]

@twright-msft

[martimors]

I recently came into a situation where I had no choice but to use mssql server, and I am honestly completely baffled at how cumbersome it is to use this docker image. Must have feature imo!

[patricklucas]

I'm a bit baffled to come across this three-and-a-half-year-old closed issue for such an extremely obvious and expected feature that is supported by every other major RDBMS Docker image.

@vin-yu do you think you could explain again why you closed this issue? I realize it may have been accidental, since the "Comment" and "Close and comment" buttons are right next to each other.

I would join @agates4 in helping justify the feature, if it's still unclear.

[vin-yu]

@agates4 @patricklucas - Thank you for the feedback. We don't have plans to implement this in the near future, and might have closed this by accident. Reopening but please refer to this work around for now: https://github.com/microsoft/mssql-docker/tree/master/linux/preview/examples/mssql-customize

@damien-git - I'll look into why this doesn't work but got it working before we posted this.

There are multiple workarounds to create a database or run custom .sql scripts post start-up so we are focused on other container improvements/products at the moment.

Thank you.

[patricklucas]

Thanks for the reply @vin-yu. I think the main concern was that the issue was closed without a clear resolution, so I appreciate your quick response and reopening it.

[NGPixel]

@vin-yu I find it extremely baffling that you don't consider this a priority and that you have no plan to implement it in the near future.

All other database vendors (MySQL, Postgres, MariaDB, etc.) offer this feature out-of-the-box. Why? Because it significantly reduce both the time it takes to use it and the learning curve. I shouldn't have to search on google for 30 minutes on how to automatically create a database.

Frankly, I doubt any other container improvements/products you could come up with would be as beneficial as this one, and by a very huge margin. The amount of upvotes on this ticket should be a clear sign.

The very first comment I get from other devs trying to use the mssql docker image the first time is always the same: Why can't I quickly create a database, like the other DB vendors? It doesn't make any sense to ask your users to go customize a startup script and build a new image.

Unless your goal is not to promote the usage of SQL Server, this very basic feature should be added. There's a reason SQL Server isn't the go to database for most users, adding unnecessary barriers to its use is probably the biggest reason.

[davidathompson]

If anybody else had problems with the approaches here, this worked for me:

echo "Starting Sql Server"

( /opt/mssql/bin/sqlservr & ) | grep -q "Service Broker manager has started"
&& /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $SA_PASSWORD -d master -i setup.sql
&& sleep infinity