How to wait for MSSQL in Docker Compose?

Question

I have a service (an ASP.NET Core Web application) that depends on MSSQL. The services are orchestrated using Docker compose, and I want docker compose to first start the database and wait for it to be ready before running my service. For that, I am defining the docker-compose.yml as:

version: '3.7'

services:

  sql.data:
    container_name: db_service
    image: microsoft/mssql-server-linux:2017-latest
    healthcheck:
      test: ["CMD", "/opt/mssql-tools/bin/sqlcmd", "-S", "http://localhost:1433", "-U", "sa", "-P", "Pass_word", "-Q", "SELECT 1", "||", "exit 1"]

  my_service:
    container_name: my_service_container
    image: ${DOCKER_REGISTRY-}my_service
    build:
      context: .
      dockerfile: MyService/Dockerfile
    depends_on:
      - sql.data

With this health-check, Docker compose does not wait for the database service to be ready, and starts my_service immediately after, and, as expected, my_service fails connecting to the database. Part of the log is:

Recreating db_service ... done
Recreating my_service_container ... done
Attaching to db_service, my_service_container 
my_service_container | info: ...Context[0]
my_service_container |       Migrating database associated with context Context
my_service_container | info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
my_service_container |       Entity Framework Core 3.1.1 initialized 'Context' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: MigrationsAssembly=MyService
my_service_container | fail: Context[0]
my_service_container |       An error occurred while migrating the database used on context Context
my_service_container | Microsoft.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server)
...
exception details
...
my_service_container | ClientConnectionId:00000000-0000-0000-0000-000000000000
my_service_container exited with code 0
db_service | 2020-03-05 05:45:51.82 Server      Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64)
        Nov 30 2018 12:57:58
        Copyright (C) 2017 Microsoft Corporation
        Developer Edition (64-bit) on Linux (Ubuntu 16.04.5 LTS)
2020-03-05 05:45:51.82 Server      UTC adjustment: 0:00
2020-03-05 05:45:51.82 Server      (c) Microsoft Corporation.
2020-03-05 05:45:51.82 Server      All rights reserved.
2020-03-05 05:45:51.82 Server      Server process ID is 4120.
2020-03-05 05:45:51.82 Server      Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'.
2020-03-05 05:45:51.82 Server      Registry startup parameters:
         -d /var/opt/mssql/data/master.mdf
         -l /var/opt/mssql/data/mastlog.ldf
         -e /var/opt/mssql/log/errorlog

As shown in the logs, the docker compose first starts the DB, but does not wait for it become ready before running my service.

I tried different syntax for the healthcheck, e.g.,:

test: /opt/mssql-tools/bin/sqlcmd -S http://localhost:1433 -U sa -P ${SA_PASSWORD} -Q "SELECT 1" || exit 1

But neither worked as expected.

I have checked the following sources online, but using neither I was able to solve the problem:

• stackoverflow answer
• github comment
• github sample

Is this functionality even supported in version 3.7? because of this confusing comment

---

Question

Any thoughts on how best I can wait for MSSQL service to start?

Answer 1

After searching and trying many different scenarios, I was able to add waiting using the following composer file. This is for asp.net core solution. The key is that you have to overwrite entrypoint if it is specified in dockerfile. Also, you need to make sure to save "wait-for-it.sh" LF as line ending instead of CRLF, otherwise you'll get the error of file not found.

The dockerfile should have the following (download it from here: https://raw.githubusercontent.com/vishnubob/wait-for-it/master/wait-for-it.sh, make sure to save the file):

COPY ./wait-for-it.sh /wait-for-it.sh
RUN chmod +x wait-for-it.sh

docker-compose.yml

version: '3.7'

services:

  vc-db:
    image: mcr.microsoft.com/mssql/server:latest
    ports:
      - "${DOCKER_SQL_PORT:-1433}:1433"
    expose:  
      - 1433  
    environment: 
      - ACCEPT_EULA=Y
      - MSSQL_PID=Express
      - SA_PASSWORD=v!rto_Labs!
    networks:
      - virto

  vc-platform-web:
    image: virtocommerce/platform:${DOCKER_TAG:-latest}
    ports:
      - "${DOCKER_PLATFORM_PORT:-8090}:80"
    environment:
      - ASPNETCORE_URLS=http://+
    depends_on:
      - vc-db
    entrypoint: ["/wait-for-it.sh", "vc-db:1433", "-t", "120", "--", "dotnet", "VirtoCommerce.Platform.Web.dll"]
    networks:
      - virto

Answer 2

When you use depends_on, docker-compose will just launch your base service with more priority and never wait for start services.

There are some useful external program that help you to wait for specific service (port), then run another service.

vishnubob/wait-for-it is one of them which blocks execution flow until your specific port(s) get ready.
Another good choice is eficode/wait-for which already prepared for docker-compose.

Example usage (according to eficode/wait-for docs)

version: '2'

services:
  db:
    image: postgres:9.4

  backend:
    build: backend
    # Blocks execution flow util db:5432 is ready (Or you can use localhost instead)
    command: sh -c './wait-for db:5432 -- npm start'
    depends_on:
      - db

-- UPDATE --

Consider you have a Python application that depend on a database like PostgreSQL, and also your application will run with this command: python app.py
As Official Docker Document said, Put vishnubob/wait-for-it in your image (inside of your other project files like app.py)

Now just put this lines in your docker-compose.yml:

version: "3"
services:
  web:
    build: .
    ports:
      - "80:8000"
    depends_on:
      - "db"
    # This command waits until `db:5432` respond (5432 is default PostgreSQL port)
    # then runs our application by this command: `python app.py`
    command: ["./wait-for-it.sh", "db:5432", "--", "python", "app.py"]
  db:
    image: postgres

Note: Don't forget to put this commands in your Dockerfile inside your image files:

# Copy wait-for-it.sh into our image
COPY wait-for-it.sh wait-for-it.sh
# Make it executable, in Linux
RUN chmod +x wait-for-it.sh

Answer 3

Create two separate dockerfiles (e.g):

1. Mssql.Dockerfile
2. App.Dockerfile

Set up the sequence within docker-compose.yml

Mssql.Dockerfile

FROM mcr.microsoft.com/mssql/server AS base

ENV ACCEPT_EULA=Y
ENV SA_PASSWORD=Password123

COPY . .
COPY ["Db/Scripts/*", "Db/Scripts/"]
VOLUME ./Db:/var/opt/mssql/data

HEALTHCHECK --interval=10s --timeout=5s --start-period=10s --retries=10 \
    CMD /opt/mssql-tools/bin/sqlcmd -S . -U sa -P Password123 -i Db/Scripts/SetupDb.sql || exit 1

App.Dockerfile:

    FROM mcr.microsoft.com/dotnet/core/aspnet:3.1-buster-slim AS base
    WORKDIR /app
    EXPOSE 80
    EXPOSE 443

    FROM mcr.microsoft.com/dotnet/core/sdk:3.1-buster AS build
    WORKDIR /src
    COPY ["AspNetCoreWebApplication/AspNetCoreWebApplication.csproj", "AspNetCoreWebApplication/"]
    COPY ["WebApp.Data.EF/WebApp.Data.EF.csproj", "WebApp.Data.EF/"]
    COPY ["WebApp.Service/WebApp.Service.csproj", "WebApp.Service/"]

    RUN dotnet restore "AspNetCoreWebApplication/AspNetCoreWebApplication.csproj"
    COPY . .
    WORKDIR "/src/AspNetCoreWebApplication"
    RUN dotnet build "AspNetCoreWebApplication.csproj" -c Release -o /app/build
    FROM build AS publish
    RUN dotnet publish "AspNetCoreWebApplication.csproj" -c Release -o /app/publish

    FROM base AS final
    WORKDIR /app
    COPY --from=publish /app/publish .
    ENTRYPOINT ["dotnet", "AspNetCoreWebApplication.dll"]

Docker-compose.yml:

version: '3.7'

services:
    api:
        image: aspnetcore/mentoring_api
        container_name: mentoring_api
        build:
            context: .
            dockerfile: App.Dockerfile
        ports:
            - 8081:80
        expose: 
            - 8081
        environment:
            ASPNETCORE_ENVIRONMENT: Development
        depends_on:
            - sqlserver
    sqlserver:
        image: aspnetcore/mentoring_db
        container_name: mentoring_db
        build:
            context: .
            dockerfile: Mssql.Dockerfile
        ports:
            - "1433:1433"
        expose: 
            - 1433
        environment:
            - ACCEPT_EULA=Y
            - SA_PASSWORD=Password123
        volumes:
            - ./Db:/var/opt/mssql/data

Note: The connection string will look like: "Server=sqlserver;Database=Northwind;Trusted_Connection=False;User Id=sa;Password=Password123;MultipleActiveResultSets=true"

Answer 4

Here is a complete example

version: "3.8"

services:
  ms-db-server:
    image: mcr.microsoft.com/mssql/server
    environment: 
      - SA_PASSWORD=P@ssw0rd
      - ACCEPT_EULA=Y
    volumes:
      - ./data/db/mssql/scripts:/scripts/
    ports:
      - "1433:1433"
    #entrypoint: /bin/bash
    command:
      - /bin/bash
      - -c
      - |
        /opt/mssql/bin/sqlservr &
        pid=$$!

        echo "Waiting for MS SQL to be available ⏳"
        /opt/mssql-tools/bin/sqlcmd -l 30 -S localhost -h-1 -V1 -U sa -P $$SA_PASSWORD -Q "SET NOCOUNT ON SELECT \"YAY WE ARE UP\" , @@servername"
        is_up=$$?
        while [ $$is_up -ne 0 ] ; do
          echo -e $$(date)
          /opt/mssql-tools/bin/sqlcmd -l 30 -S localhost -h-1 -V1 -U sa -P $$SA_PASSWORD -Q "SET NOCOUNT ON SELECT \"YAY WE ARE UP\" , @@servername"
          is_up=$$?
          sleep 5
        done

        for foo in /scripts/*.sql
          do /opt/mssql-tools/bin/sqlcmd -U sa -P $$SA_PASSWORD -l 30 -e -i $$foo
        done
        echo "All scripts have been executed. Waiting for MS SQL(pid $$pid) to terminate."

        wait $$pid

  tempo:
    image: grafana/tempo:latest
    command: ["-config.file=/etc/tempo.yaml"]
    volumes:
      - ./etc/tempo-local.yaml:/etc/tempo.yaml
      - ./data/tempo-data:/tmp/tempo
    ports:
      - "14268"      # jaeger ingest, Jaeger - Thrift HTTP
      - "14250"      # Jaeger - GRPC
      - "55680"      # OpenTelemetry
      - "3100"       # tempo
      - "6831/udp"   # Jaeger - Thrift Compact
      - "6832/udp"   # Jaeger - Thrift Binary   

  tempo-query:
    image: grafana/tempo-query:latest
    command: ["--grpc-storage-plugin.configuration-file=/etc/tempo-query.yaml"]
    volumes:
      - ./etc/tempo-query.yaml:/etc/tempo-query.yaml
    ports:
      - "16686:16686"  # jaeger-ui
    depends_on:
      - tempo

  loki:
    image: grafana/loki:2.1.0
    command: -config.file=/etc/loki/loki-local.yaml
    ports:
      - "3101:3100"                                   # loki needs to be exposed so it receives logs
    environment:
      - JAEGER_AGENT_HOST=tempo
      - JAEGER_ENDPOINT=http://tempo:14268/api/traces # send traces to Tempo
      - JAEGER_SAMPLER_TYPE=const
      - JAEGER_SAMPLER_PARAM=1
    volumes:
      - ./etc/loki-local.yaml:/etc/loki/loki-local.yaml
      - ./data/loki-data:/tmp/loki

  nodejs-otel-tempo-api:
    build: .
    command: './wait-for.sh ms-db-server:1433 -- node ./dist/server.js'
    ports:
      - "5555:5555"
    environment:
      - OTEL_EXPORTER_JAEGER_ENDPOINT=http://tempo:14268/api/traces
      - OTEL_SERVICE_NAME=nodejs-opentelemetry-tempo
      - LOG_FILE_NAME=/app/logs/nodejs-opentelemetry-tempo.log
      - DB_USER=sa
      - DB_PASS=P@ssw0rd
      - DB_SERVER=ms-db-server
      - DB_NAME=OtelTempo
    volumes:
      - ./data/logs:/app/logs
      - ./etc/wait-for.sh:/app/bin/wait-for.sh   #https://github.com/eficode/wait-for
    depends_on:
      - ms-db-server
      - tempo-query

  promtail:
    image: grafana/promtail:master-ee9c629
    command: -config.file=/etc/promtail/promtail-local.yaml
    volumes:
      - ./etc/promtail-local.yaml:/etc/promtail/promtail-local.yaml
      - ./data/logs:/app/logs
    depends_on:
      - nodejs-otel-tempo-api
      - loki

  prometheus:
    image: prom/prometheus:latest
    volumes:
      - ./etc/prometheus.yaml:/etc/prometheus.yaml
    entrypoint:
      - /bin/prometheus
      - --config.file=/etc/prometheus.yaml
    ports:
      - "9090:9090"
    depends_on:
      - nodejs-otel-tempo-api

  grafana:
    image: grafana/grafana:7.4.0-ubuntu
    volumes:
      - ./data/grafana-data/datasources:/etc/grafana/provisioning/datasources
      - ./data/grafana-data/dashboards-provisioning:/etc/grafana/provisioning/dashboards
      - ./data/grafana-data/dashboards:/var/lib/grafana/dashboards
    environment:
      - GF_AUTH_ANONYMOUS_ENABLED=true
      - GF_AUTH_ANONYMOUS_ORG_ROLE=Admin
      - GF_AUTH_DISABLE_LOGIN_FORM=true
    ports:
      - "3000:3000"
    depends_on:
      - prometheus
      - tempo-query
      - loki

Answer 5

I think your initial attempt isn't actually far off. Using a health check seems the most appropriate route, so I would continue with that approach, however you'll want to take advantage of the condition functionality of depends_on. With this, you can use service_healthy condition, which will wait for your SQL Server health check to report healthy.

See this article on the Docker website which mentions this: https://docs.docker.com/compose/startup-order/

Your docker-compose.yml would look like this:

version: '3.7'

services:

  sql.data:
    container_name: db_service
    image: microsoft/mssql-server-linux:2017-latest
    healthcheck:
      test: ["CMD", "/opt/mssql-tools/bin/sqlcmd", "-S", "http://localhost:1433", "-U", "sa", "-P", "Pass_word", "-Q", "SELECT 1", "||", "exit 1"]

  my_service:
    container_name: my_service_container
    image: ${DOCKER_REGISTRY-}my_service
    build:
      context: .
      dockerfile: MyService/Dockerfile
    depends_on:
      sql.data:
        condition: service_healthy

You could also take advantage of the healthcheck options, as below, and a slightly neater syntax:

version: '3.7'

services:

  sql.data:
    container_name: db_service
    image: microsoft/mssql-server-linux:2017-latest
    healthcheck:
      test: /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "Pass_word" -Q "SELECT 1" -b -o /dev/null
      interval: 10s
      timeout: 3s
      retries: 10
      start_period: 10s

  my_service:
    container_name: my_service_container
    image: ${DOCKER_REGISTRY-}my_service
    build:
      context: .
      dockerfile: MyService/Dockerfile
    depends_on:
      sql.data:
        condition: service_healthy

Answer 6

You can write a simple script, which will be launched in container with your app. For example, you can just set a delay, using sleep N ( where N it is a time which is needed for starting your DB), or you can use a until cycle in which you to can try to connect to your DB, and when it will be possible, then you can start your app.

I know this is not a perfect solution, but it helped me when I had a similar problem

Answer 7

Using Kubernetes Deployment, the following probe successfullly identified the ready state:

      containers:
        - name: mssql
          image: mcr.microsoft.com/mssql/server:2019-latest
          ports:
            - containerPort: 1433
          env:
            - name: ACCEPT_EULA
              value: 'Y'
          startupProbe:
            exec:
              command:
                - /bin/sh
                - '-c'
                - '/opt/mssql-tools/bin/sqlcmd -U sa -P "${SA_PASSWORD}" -Q "SELECT \"READY\"" | grep -q "READY"'
            failureThreshold: 15
            periodSeconds: 10

You could code this as a livenessProbe with initialDelay if your Kubernetes version is <1.20.

Explanation: sqlcmd returns a '0' status irrespective of whether the query returns anything or not. However, grep -q returns 0 or 1 depending on presence of the word "READY".

I have not used docker-compose but I suspect this command would work as a healthcheck test (assuming SA_PASSWORD is injected into the environment), ie:

    healthcheck:
      test:
        - /bin/sh
        - '-c'
        - '/opt/mssql-tools/bin/sqlcmd -U sa -P "${SA_PASSWORD}" -Q "SELECT \"READY\"" | grep -q "READY"'

• Configure Liveness, Readiness and Startup Probes
• Pod Lifecycle

Answer 8

you can also delay the docker startup until mssql is up:

docker-compose.yaml

  mssql:
    image: mcr.microsoft.com/mssql/server:2017-latest
    ports:
      - 1433:1433
    environment:
      SA_PASSWORD: "t9D4:EHfU6Xgccs-"
      ACCEPT_EULA: "Y"
    networks:
      - backend
    command:
      - /bin/bash
      - -c
      - |
        /opt/mssql/bin/sqlservr
        curl -s https://raw.githubusercontent.com/vishnubob/wait-for-it/master/wait-for-it.sh | bash /dev/stdin localhost:1433