Creating a database automatically upon startup
LuisBosquez opened this issue · 60 comments
A couple of comments (4) in the DockerHub page suggest adding parameters to create a database with username and password automatically.
+1
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.
+1
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.
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
hi @kuncevic did this docker compose work for you? did you get a db and user on the container?
Creating databases and users at container run time is not implemented yet.
@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
@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.
@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. :)
:) Thanks a lot @kuncevic
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.
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?
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'
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.sqldb-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];
GOHow 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.
@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
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?
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.
@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.
@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.sqlI 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"
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
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!
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.
@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.
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.
@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.
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
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);
}
}
}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"
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: testNote 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.
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.sqlinit.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.
Any plans to change this? Auto DB creation on startup is the standard for every other database container.
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 | ✅ |
| InfluxDB | ✅ |
| MariaDB | ✅ |
| MongoDB | ✅ |
| MS SQL Server | ❌ |
| MySQL | ✅ |
| Neo4j | ✅ |
| PostgreSQL | ✅ |
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.
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.
I'm chipping in with a slightly modified entrypoint.sh which plays nicely with wait-for-it, dockerize, etc. The idea is to only make the server available to other containers once the init/setup scripts ran.
#!/bin/bash
_setup() {
for _ in {1..60}; do
if /opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U sa -P "$SA_PASSWORD" -d master -i setup.sql; then
break
fi
echo "Server is not ready..."
sleep 1
done
}
echo "Starting SQL-Server on 127.0.0.1"
/opt/mssql/bin/mssql-conf set network.ipaddress 127.0.0.1
/opt/mssql/bin/sqlservr &
_setup
echo "Setup finished. Stopping SQL-Server..."
pid=$(pgrep -o sqlservr)
kill "$pid"
tail --pid="$pid" -f /dev/null
echo "Starting SQL-Server on 0.0.0.0"
/opt/mssql/bin/mssql-conf set network.ipaddress 0.0.0.0
/opt/mssql/bin/sqlservrOther containers then can simply dockerize -wait tcp://db:1433 ... for example and be sure that the database is only available once it's fully initialised.
@amvin87 its been a couple more months now and still no update, what is the status? i'm likewise amazed that this is still an open issue after almost four years...
what could possibly have a greater cost/benefit than this?
You can put "microsoft heart linux/containers/docker" on slides all day long but not supporting basic features like this what really tells the story :/ Dont ruin the good will dotnet is creating and make it that much harder for us to convince people Microsoft is not just full of hot air when it comes to this stuff
@amvin87 I don't understand why you going silent. A small feedback will be appreciated and a basic feature implementation will be honoured. Thnx in advance!
Hello @amvin87, do you have any updates or a timeline that you can share?
Thank you 🙇🏼
Honestly find it quite shocking that this isn't considered an obvious feature.
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
docker-compose.ymlfragment:
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
- 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"
- Connection string:
Server=localhost,1433;User=sa;Password=mssql1Ipw;Database=test
It's been 6 years and still not done? come on Microsoft
Any sort of update on this?
@amvin87
I am eager for an update as well 🙏🏼
6 years and no fix ! wy wy wy
Give this a look... just dumped it from another project, and minor tweaks... completely untested in current state.
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/sqlservrI 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/sqlservrStill no updates? This is turning into an irony now!
Does:
apt-get install sqlcmd
sqlcmd create mssql --accept-eula --user-database foo
sqlcmd query "SELECT DB_NAME()"
Help with this scenario?
@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).
wiring happens to be OS-specific).
I'm not tracking what is OS-specific, can you flesh this out a bit more.
@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)
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.
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.
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:
- dbHere we do not use an arbitrary sleep timeout, we loop until the sql command ends up in success (and wait 1s between retries).
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_healthySetup 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