Mssql-docker: 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?

[y3sh]

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

[dingobar]

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

[NO1225]

For those using entity framework with asp.net core
I'm doing like this right now and I think this may help someone

Calling this before (EnsureCreatedAsync or MigrateAsync) will make sure that you have a database to work on that also accept your password

private static async Task InitDatabase(IConfiguration config)
        {
            string hostServer = config["HOST_SERVER"] ?? "(localdb)\\MSSQLLocalDB";
            string serverPort = config["HOST_PORT"] ?? "1433";
            string databaseName = config["DATABASE_NAME"] ?? "testdb";
            string userName = config["USERNAME"];
            string passward = config["SA_PASSWORD"];
            string connectionString;
            if (!string.IsNullOrEmpty(userName) && !string.IsNullOrEmpty(passward))
            {
                connectionString = $"Server={hostServer};User Id={userName};Password={passward};";
                var optionsBuilder = new DbContextOptionsBuilder<DataAccess>();
                optionsBuilder.UseSqlServer(connectionString);
                using (var dbContext = new DataAccess(optionsBuilder.Options))
                {
                    var query = @$"IF DB_ID('{databaseName}') IS NOT NULL
set noexec on
CREATE DATABASE [{databaseName}];";
                    await dbContext.Database.ExecuteSqlRawAsync(query);
                }
            }
        }

[MaxHorstmann]

Using docker-compose, this works for me:

version: "3"
services:
        sql:
                image: "mcr.microsoft.com/mssql/server"
                container_name: sql
                ports:
                        - "1433:1433"
                environment:
                        - ACCEPT_EULA=Y
                        - MSSQL_PID=Enterprise
                        - SA_PASSWORD=Password1
                command: /bin/sh -c "(/opt/mssql/bin/sqlservr &) && sleep 10s && /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Password1 -d master -Q 'CREATE DATABASE [MyDatabaseName]' && sleep infinity"

[NickCraver]

Chiming in here from our conversation on Twitter. I think the main use case for Docker here, at least where this matters, is people want to spin this up as a service. For me and my teams, we are generally doing this in Actions or locally. For things like integration tests it's a primary use case - you're using Docker in the first place because you want to quickly spin it up and run. One of the critical components in doing this is to have a database.

We have developers, who may or may not have experience _creating_ a database, but that shouldn't be a barrier to entry here. With an environmental-variable-based approach, we can lower that barrier. Please consider that even though this issue has workarounds with scripting to workaround this issue, some amount of users will give up, never having made it to this issue, or far enough through it for a working solution. Given the volume of input we have here, I'd suggest this be given some priority, since adoption is greatly hindered for any technology if users hit large barriers early.

If it helps, our main use cases are integration tests (e.g. for libraries like MiniProfiler, StackExchange.Exceptional, and Dapper), and also less public ones like internal software (also integration tests), or per-PR build environments we spin up services in for some time period.

Here's an example GitHub Actions config from a project spinning up services for some integration tests:

    services:
      mongo:
        image: mongo
        ports:
        - 27017/tcp
        env:
          MONGO_INITDB_DATABASE: test
      postgres:
        image: postgres
        ports:
        - 5432/tcp
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: test
      sqlserver:
        image: mcr.microsoft.com/mssql/server:2019-latest
        ports:
        - 1433/tcp
        env:
          ACCEPT_EULA: Y
          SA_PASSWORD: Password1!
      mysql:
        image: mysql
        ports:
        - 3306/tcp
        env:
          MYSQL_ROOT_PASSWORD: root
          MYSQL_DATABASE: test

Note that MySQL (and MariaDB) use MYSQL_DATABASE and PostgreSQL uses POSTGRES_DB. When we're consuming SQL Server, we're either scripting this out manually, or resorting to abusing tempdb overall.

[ah1508]

In addition to @NickCraver comment, postgresql also consider files present in /docker-entrypoint-initdb.d/ directory (in the container). It helps to create tables and fill them when container is created.

Exemple :

services:
  pg:
    image: postgres
    ports:
    - 5432/tcp
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: test
    volumes:
    - ./pg.sql:/docker-entrypoint-initdb.d/init.sql

init.sql is not a keyword, multiple files can exist in this directory (postgres execute them in alphabetical order). Without this feature, an external script would be required to create tables and insert data.

[coryechobind]

Any plans to change this? Auto DB creation on startup is the standard for every other database container.

[NGPixel]

Come on @vin-yu, @twright-msft , how much longer are you guys going to ignore this very important issue? It's literally the single most requested feature and should be very quick to implement on your side.

| DB Engine | Has init DB variables? |
|------------|------------------------|
| DB2 | :white_check_mark: |
| InfluxDB | :white_check_mark: |
| MariaDB | :white_check_mark: |
| MongoDB | :white_check_mark: |
| MS SQL Server | :x: |
| MySQL | :white_check_mark: |
| Neo4j | :white_check_mark: |
| PostgreSQL | :white_check_mark: |

[mpern]

If this feature is ever tackled, please add support for init.d directory to the entrypoint script.
i.e. run all shell / sql scripts in the init.d directory in order once the DB is up and ready to receive commands.

[amvin87]

Thank you all for the suggestions and feedback. I am working on triaging this internally and will update this thread on the next action we take. Until then the options mentioned by @vin-yu is something that can be used as a workaround. While we work on this feedback internally.