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

[potatoqualitee]

Hello @amvin87, do you have any updates or a timeline that you can share?

Thank you 🙇🏼

[fardarter]

Honestly find it quite shocking that this isn't considered an obvious feature.

[NGPixel]

[bartelink]

I have only scanned the above for 5/10 mins and realize it duplicates things (as does most of what precedes this). Trust me I will edit and/or delete this post if a better story or workaround appears.

edited to add: I now use the hack from below: #2 (comment)

---

@twright-msft Is the TL;DR on this still that your images do not have a good story for spinning up a DB with docker compose without needing to dig into a pile of version-specific scripting? Literally every other DB has a story for this -- it's 2022 after all

I'm doing pretty unexotic testing; the typical prod database this runs against is an Azure SQL instance.

Can someone be assigned this please, or do you have a good one liner that explains why there's a need to differentiate here? Right now it seems like this is the moral equivalent of JamesNK/Newtonsoft.Json#862, except that's an actual OSS project (and I see lots of annoying drive by commenters on that but feel their pain, as I'm sure some will when they see this (and sorry to those that don't get what drives a human to and-me-three post on a thread like this))

Or is this something the Azure SQL team should be tasked with?

It makes it very hard to include MSSQL in a test matrix alongside sensible children like PG and Mysql as-is, and has thoroughly wasted my Saturday afternoon updating a test rig for an OSS project (with a lot of curse words along the way)

---

Expected reasonable docker-compose fragment for a db called test with a well known SA password

    container_name: equinox-mssql
    image: mcr.microsoft.com/mssql/server:2019-latest
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_DB=test
      - SA_PASSWORD=mssql1Ipw

---

Ugly hack while the world waits

1. docker-compose.yml fragment:

services:
  equinox-mssql:
    container_name: equinox-mssql
    image: mcr.microsoft.com/mssql/server:2019-latest
    ports:
      - 1433:1433
    volumes:
      - ~/apps/mssql/data:/var/lib/mssqlql/data
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=mssql1Ipw

2. Scripted command to run after docker compose up

docker exec -it equinox-mssql /opt/mssql-tools/bin/sqlcmd \
    -S localhost -U sa -P mssql1Ipw \
    -Q "CREATE database test"

3. Connection string:

Server=localhost,1433;User=sa;Password=mssql1Ipw;Database=test

[kasvith]

It's been 6 years and still not done? come on Microsoft

[kolendomichal]

Any sort of update on this?
@amvin87

[potatoqualitee]

I am eager for an update as well 🙏🏼

[abdennour]

6 years and no fix ! wy wy wy

[tracker1]

Give this a look... just dumped it from another project, and minor tweaks... completely untested in current state.

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

[hlovdal]

Updated 2023-03-30 to make the cmd_wrapper.sh script tolerant of sqlcmd failures.

---

@Schlurcher NB, the last command in an entrypoint script should always be executed thourgh exec, e.g.

...
# NB NB The last (long running) command should always be started through exec, see
# https://stackoverflow.com/questions/39082768/what-does-set-e-and-exec-do-for-docker-entrypoint-scripts and
# https://stackoverflow.com/questions/32255814/what-purpose-does-using-exec-in-docker-entrypoint-scripts-serve
# for details.
exec /opt/mssql/bin/sqlservr

---

I generalized the script to support a generic /docker-entrypoint-initdb.d directory instead of a hardcoded setup.sql and also used it as CMD replacement instead of ENTRYPOINT replacement. The docker-compose.yml file then needs the following change:

   db-server:
     image: mcr.microsoft.com/mssql/server:2017-latest
     ...
+    command: [ "/cmd_wrapper.sh" ]
     volumes:
       ...
+      - ./volume/db-server/cmd_wrapper.sh:/cmd_wrapper.sh
+      - ./volume/db-server/docker-entrypoint-initdb.d/:/docker-entrypoint-initdb.d/

with cmd_wrapper.sh being the following:

#!/bin/bash

function finish() {
        local _ret=$?
        if [[ _ret -ne 0 ]]
        then
                echo "========================================================================"
                echo "$0: ERROR: Some command exited with exit code $_ret"
                echo "========================================================================"
        fi
}
trap finish EXIT

# This script cannot unconditionally terminate on command failure because in
# some situations command failures occurs when sqlcmd fails to log in in the
# beginning, but eventually it will succeed. Example log:
# ...
# -03-15 10:18:20.78 spid6s      Starting up database 'msdb'.
# -03-15 10:18:20.78 spid11s     Starting up database 'mssqlsystemresource'.
# -03-15 10:18:20.79 spid11s     The resource database build version is 14.00.3460. This is an informational message only. No user action is required.
# -03-15 10:18:20.84 spid11s     Starting up database 'model'.
# -03-15 10:18:21.08 spid11s     Polybase feature disabled.
# -03-15 10:18:21.08 spid11s     Clearing tempdb database.
# -03-15 10:18:21.57 spid11s     Starting up database 'tempdb'.
# -03-15 10:18:21.78 spid18s     A self-generated certificate was successfully loaded for encryption.
# -03-15 10:18:21.80 spid18s     Server is listening on [ 127.0.0.1 <ipv4> 1433].
# -03-15 10:18:21.80 spid18s     Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, re
# start SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
# -03-15 10:18:21.87 Logon       Error: 18401, Severity: 14, State: 1.
# -03-15 10:18:21.87 Logon       Login failed for user 'sa'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 127.0.0.1]
# Waiting for sys.databases query to return 4 or more rows
# -03-15 10:18:21.94 spid6s      Synchronize Database 'msdb' (4) with Resource Database.
# -03-15 10:18:21.94 spid21s     The Service Broker endpoint is in disabled or stopped state.
# -03-15 10:18:21.94 spid21s     The Database Mirroring endpoint is in disabled or stopped state.
# -03-15 10:18:22.04 spid21s     Service Broker manager has started.
# -03-15 10:18:22.24 spid6s      Database 'master' is upgrading script 'u_tables.sql' from level 234884480 to level 234884484.
# -03-15 10:18:22.25 spid6s      Starting u_Tables.SQL at  15 Mar 2023 10:18:22:253
# -03-15 10:18:22.25 spid6s      This file creates all the system tables in master.
# -03-15 10:18:22.28 spid6s      drop view spt_values ....
# -03-15 10:18:22.34 spid6s      Creating view 'spt_values'.
# -03-15 10:18:22.46 spid6s      drop table spt_monitor ....
# -03-15 10:18:22.50 spid6s      Creating 'spt_monitor'.
# -03-15 10:18:22.52 spid6s      Grant Select on spt_monitor
# -03-15 10:18:22.53 spid6s      Insert into spt_monitor ....
# -03-15 10:18:22.89 spid6s      Finishing at  15 Mar 2023 10:18:22:893
# -03-15 10:18:22.91 Logon       Error: 18401, Severity: 14, State: 1.
# -03-15 10:18:22.91 Logon       Login failed for user 'sa'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 127.0.0.1]
# Waiting for sys.databases query to return 4 or more rows
# -03-15 10:18:23.08 spid6s      Database 'master' is upgrading script 'ProvisionAgentIdentity.sql' from level 234884480 to level 234884484.
# -03-15 10:18:23.08 spid6s      Database 'master' is upgrading script 'no_op.sql' from level 234884480 to level 234884484.
# -03-15 10:18:23.33 spid6s      Database 'master' is upgrading script 'no_op.sql' from level 234884480 to level 234884484.
# -03-15 10:18:23.33 spid6s      -----------------------------------------
# -03-15 10:18:23.33 spid6s      Starting execution of dummy.sql
# -03-15 10:18:23.33 spid6s      -----------------------------------------
# -03-15 10:18:23.34 spid6s      Database 'master' is upgrading script 'repl_upgrade.sql' from level 234884480 to level 234884484.
# -03-15 10:18:23.34 spid6s      Executing replication upgrade scripts.
# -03-15 10:18:23.49 spid6s      Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
# -03-15 10:18:23.53 spid6s      Using 'xpstar.dll' version '2017.140.3460' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
# -03-15 10:18:23.53 spid6s      Executing sp_vupgrade_replication.
# -03-15 10:18:23.57 spid6s      DBCC execution completed. If DBCC printed error messages, contact your system administrator.
# -03-15 10:18:23.93 spid6s      Starting up database 'SomeDatabase1'.
# -03-15 10:18:23.96 Logon       Error: 18401, Severity: 14, State: 1.
# -03-15 10:18:23.96 Logon       Login failed for user 'sa'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 127.0.0.1]
# Waiting for sys.databases query to return 4 or more rows
# -03-15 10:18:24.04 spid6s      Parallel redo is started for database 'SomeDatabase1' with worker pool size [1].
# -03-15 10:18:24.07 spid6s      Parallel redo is shutdown for database 'SomeDatabase1' with worker pool size [1].
# -03-15 10:18:24.15 spid6s      Synchronize Database 'SomeDatabase1' (6) with Resource Database.
# -03-15 10:18:24.19 spid6s      Starting up database 'SomeDatabase2'.
# -03-15 10:18:24.33 spid6s      Parallel redo is started for database 'SomeDatabase2' with worker pool size [1].
# -03-15 10:18:24.75 spid6s      Parallel redo is shutdown for database 'SomeDatabase2' with worker pool size [1].
# -03-15 10:18:24.87 spid6s      Synchronize Database 'SomeDatabase2' (7) with Resource Database.
# ...
# -03-15 10:19:25.75 spid6s      The Utility MDW does not exist on this instance.
# -03-15 10:19:25.75 spid6s      User 'sa' is changing database script level entry 15 to a value of 500.
# -03-15 10:19:25.76 spid6s      Skipping the execution of instmdw.sql.
# -03-15 10:19:25.76 spid6s      ------------------------------------------------------
# -03-15 10:19:25.76 spid6s      execution of UPGRADE_UCP_CMDW_DISCOVERY.SQL completed
# -03-15 10:19:25.76 spid6s      ------------------------------------------------------
# -03-15 10:19:25.84 spid6s      Database 'master' is upgrading script 'ssis_discovery' from level 234884480 to level 234884484.
# -03-15 10:19:25.84 spid6s      ------------------------------------------------------
# -03-15 10:19:25.85 spid6s      Starting execution of SSIS_DISCOVERY.SQL
# -03-15 10:19:25.85 spid6s      ------------------------------------------------------
# -03-15 10:19:25.88 spid6s      Database SSISDB does not exist in current SQL Server instance
# -03-15 10:19:25.88 spid6s      User 'sa' is changing database script level entry 17 to a value of 500.
# -03-15 10:19:25.88 spid6s      Database SSISDB could not be upgraded successfully.
# -03-15 10:19:25.88 spid6s      ------------------------------------------------------
# -03-15 10:19:25.89 spid6s      Execution of SSIS_DISCOVERY.SQL completed
# -03-15 10:19:25.89 spid6s      ------------------------------------------------------
# -03-15 10:19:25.97 spid6s      Database 'master' is upgrading script 'SSIS_hotfix_install.sql' from level 234884480 to level 234884484.
# -03-15 10:19:26.00 spid6s      ------------------------------------------------------
# -03-15 10:19:26.00 spid6s      Starting execution of SSIS_HOTFIX_INSTALL.SQL
# -03-15 10:19:26.01 spid6s      ------------------------------------------------------
# -03-15 10:19:26.01 spid6s      Database SSISDB does not exist in current SQL Server instance
# -03-15 10:19:26.01 spid6s      ------------------------------------------------------
# -03-15 10:19:26.01 spid6s      Execution of SSIS_HOTFIX_INSTALL.SQL completed
# -03-15 10:19:26.01 spid6s      ------------------------------------------------------
# -03-15 10:19:26.01 spid6s      Database 'master' is upgrading script 'provision_ceipsvc_account.sql' from level 234884480 to level 234884484.
# -03-15 10:19:26.02 spid6s      ------------------------------------------------------
# -03-15 10:19:26.02 spid6s      Start provisioning of CEIPService Login
# -03-15 10:19:26.02 spid6s      ------------------------------------------------------
# -03-15 10:19:26.03 spid6s      Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
# -03-15 10:19:26.03 spid6s      Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
# -03-15 10:19:26.05 spid6s      Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
# -03-15 10:19:26.05 spid6s      Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
# -03-15 10:19:26.06 spid6s      Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
# -03-15 10:19:26.06 spid6s      Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
# -03-15 10:19:26.06 spid6s      Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
# -03-15 10:19:26.07 spid6s      Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
# -03-15 10:19:26.07 spid6s      ------------------------------------------------------
# -03-15 10:19:26.07 spid6s      Ending provisioning of CEIPLoginName.
# -03-15 10:19:26.08 spid6s      ------------------------------------------------------
# -03-15 10:19:26.08 spid6s      SQL Server is now ready for client connections. This is an informational message; no user action is required.
# -03-15 10:19:26.08 spid6s      Recovery is complete. This is an informational message only. No user action is required.
# ========================================================================
# Database ready for initialization
# ========================================================================
# Processing create_login_and_user.sql
# ...


# So starting with termination enabled but, selectively turning off later on.
set -euo pipefail

# /opt/mssql/bin/sqlservr ought to match the CMD from the upstream docker image, e.g.
#
# $ docker inspect mcr.microsoft.com/mssql/server:2017-latest | jq '.[0].Config.Cmd'
# [
#   "/opt/mssql/bin/sqlservr"
# ]
# $

function wait_for_server_ready() {
        local rows_affected=0

        # Sql server has 4 system database, so when they are connected and
        # created, SQL server can be assumed to be running and working as
        # expected. Example output from sqlcmd:

        #name
        #--------------------------------------------------------------------------------------------------------------------------------
        #master
        #tempdb
        #model
        #msdb
        #some_other_database_001
        #some_other_database_002
        #some_other_database_003
        #some_other_database_004
        #some_other_database_005
        #
        #(9 rows affected)
        while [[ rows_affected -lt 4 ]]
        do
                echo "Waiting for sys.databases query to return 4 or more rows"
                sleep 1
                set +o pipefail # Turn off termination for sqlcmd failures, see earlier comments.
                rows_affected=$(/opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U sa -P "${MSSQL_SA_PASSWORD}" -Q 'SELECT name FROM sys.databases' 2>/dev/null | sed -n 's/(//; s/ rows affected.*//p;')
                set -o pipefail
        done
}

function process_init_scripts() {
        local old_pwd=$PWD
        for dir in "$@"
        do
                cd "$dir" || { echo "$0; Error: unable to cd to $dir" 1>&2; exit 1;}
                for file in *.sql
                do
                        case "$file" in
                                "*.sql")
                                        echo "No *.sql files in $dir."
                                        ;;
                                *)
                                        echo "Processing $file"
                                        /opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U sa -P "${MSSQL_SA_PASSWORD}" -d master -i "$file"
                                        sync
                                        ;;
                        esac
                done
        done
        cd "$old_pwd"
}

echo "Starting SQL-Server on 127.0.0.1"
# Ignoring output from mssql-conf since it will complain
#   SQL Server needs to be restarted in order to apply this setting. Please run
#   'systemctl restart mssql-server.service'.
# which is not an issue here in this script.
/opt/mssql/bin/mssql-conf set network.ipaddress 127.0.0.1 > /dev/null
/opt/mssql/bin/sqlservr &
pid=$!

wait_for_server_ready
echo "========================================================================"
echo "Database ready for initialization"
echo "========================================================================"
process_init_scripts /docker-entrypoint-initdb.d

sync

echo "Setup finished. Stopping SQL-Server..."
kill "$pid"
wait "$pid"

sync
sleep 1

echo "Starting SQL-Server on 0.0.0.0"
/opt/mssql/bin/mssql-conf set network.ipaddress 0.0.0.0 > /dev/null

# NB NB The last (long running) command should always be started through exec, see
# https://stackoverflow.com/questions/39082768/what-does-set-e-and-exec-do-for-docker-entrypoint-scripts and
# https://stackoverflow.com/questions/32255814/what-purpose-does-using-exec-in-docker-entrypoint-scripts-serve
# for details.
exec /opt/mssql/bin/sqlservr

[waqas-syed]

Still no updates? This is turning into an irony now!

[stuartpa]

Does:

apt-get install sqlcmd
sqlcmd create mssql --accept-eula --user-database foo
sqlcmd query "SELECT DB_NAME()"

Help with this scenario?

https://aka.ms/sqlcmd

[bartelink]

@stuartpa The point is that every other similar image has a way to request the creation of a database in a declarative manner.
It's the need to have a separated step (and being able to trust that it's been done) that's the problem.
Nobody is suggesting that the individual step is in any way hard (though your wiring happens to be OS-specific).

[stuartpa]

wiring happens to be OS-specific).

I'm not tracking what is OS-specific, can you flesh this out a bit more.

[bartelink]

@stuartpa Just saying that I'm not disputing that your scripting will work (create a database), but it does have a dependency on a specific environment and permissions, i.e. if the container simply did it, one would not need to have something that has apt-get (and the associated required permissions) in the context of the specific consumption scenario.

The table in #800 summarises the situation well

Perhaps you can flesh out what you're hoping to accomplish with your suggestion? e.g. in my suggestion, I have a command thats functionally equivalent, leaning on the container's contents, instead of assuming we want/can install a package - but I am calling out that this is pretty useless as it is not integrated, and as a result I have to maintain special case documentation

(Happy to remove this and my preceding message from the thread if we can agree that the point was the integration, not how one might do the step that people want/need/expect to be integrated into the container image)

[fardarter]

wiring happens to be OS-specific).

I'm not tracking what is OS-specific, can you flesh this out a bit more.

The issue is I don't want to script it out, I just want to pass a file for the container to understand. I can script it out, but I can write assembly too and don't want to.

Every other DB container has this feature. It's entirely possible.

MS simply doesn't care.

[tracker1]

I don't have a stake in one direction or another... but the container really should have the options to use/specify environment variables to create a database with a non-sa user that has full access only for that database. This would align closer to other Database containers.

[vbueb]

A workaround using docker-compose :

version: "3"
services:
  db:
    image: mcr.microsoft.com/mssql/server:2022-latest
    ports:
      - "1433:1433"
    environment:
      ACCEPT_EULA: y
      MSSQL_SA_PASSWORD: 01pass_WORD
  db-init:
    image: mcr.microsoft.com/mssql/server:2022-latest
    network_mode: service:db
    command: bash -c 'until /opt/mssql-tools/bin/sqlcmd -U sa -P 01pass_WORD -Q "CREATE DATABASE mydb"; do echo retry && sleep 1; done'
    depends_on:
      - db

Here we do not use an arbitrary sleep timeout, we loop until the sql command ends up in success (and wait 1s between retries).

[LadislavBohm]

Just another version of what @vbueb wrote that waits for db to properly start and executes all sql scripts in certain directory. It also exits because I believe that db-init contain does not need to keep running after db has been setup:

  db:
    image: mcr.microsoft.com/mssql/server:2022-latest
    restart: always
    ports:
      - "1433:1433"
    environment:
      ACCEPT_EULA: y
      SA_PASSWORD: $SA_PASSWORD
    healthcheck:
      test:
        [
          "CMD",
          "/opt/mssql-tools/bin/sqlcmd",
          "-U",
          "sa",
          "-P",
          "$SA_PASSWORD",
          "-Q",
          "SELECT 1",
        ]
      interval: 10s
      retries: 5
      start_period: 20s
  db-init:
    image: mcr.microsoft.com/mssql/server:2022-latest
    network_mode: service:db
    command: >
      bash -c '
        for file in ./sql-scripts/*.sql; do
          echo "Executing SQL file $$file"
          until /opt/mssql-tools/bin/sqlcmd -U sa -P $SA_PASSWORD -i "$$file" > /dev/null; do
            echo "Retry SQL execution for $$file"
            sleep 1
          done
        done
        exit 0
      '
    volumes:
      - ./sql-scripts:/sql-scripts
    depends_on:
      db:
        condition: service_healthy

Setup above executes all scripts inside sql-scripts directory after sqlserver becomes active.

If you want to execute another service after db init has finished a simple setup like this worked fine for me:

  api:
    build:
      context: ../
      dockerfile: ./someapi/Dockerfile
    ports:
      - "8080:80"
    environment:
      ConnectionStrings:MyDatabase: "Server=db;Database=MyDatabase;User Id=sa;Password=$SA_PASSWORD;"
    depends_on:
      db-init:
        condition: service_completed_successfully