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?

Solution 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

Solution 2

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"

Solution 3

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

Comments

[Admin]

Checking connection inside our application is a good idea, but docker document itself recommends using wait-for-it and alternative scripts.

[Yurii Paneiko]

Of course, I do not argue with this, I just offered a simple ( not perfect) solution

[Andrew Nos]

How to connect internal mssql DB threw docker? I have server with MSSQL DB in one network with host machine when docker will running. In my simple flask app i use pyodbc connection to DB. On other app i use sqlite db file with docker and it was simple to connect it.

[Evgeny Gorbovoy]

I feel that this "wait-for" and similar are semi-solution: what if container/sql-container can not be started due a bug or, really, any reason. Are we going to wait forever? Or if we are using timeout, how can we be sure this timeout is enough in a given particular environment?

[Woland]

@EugeneGorbovoy I think it is reasonable for it to wait until related service becomes available, it is a job of a scheduler at that point to throw the error. Timeout is a hack since we really don't know how long would it take for service to start.

[caduceus]

You set ENV SA_PASSWORD but then write out the password in the HEALTHCHECK

[Shahar Shokrani]

Hey @caduceus, why is that a problem?

[caduceus]

@ShaharShokrani why set a variable if you're not going to use it?

[Shahar Shokrani]

In your previous comment did you intend that the password isn't necessary in ENV or in CMD?

[Shahar Shokrani]

Not sure the health-check is going to work without the password provided via health check's CMD, maybe I'm wrong but I think both are necessary, worth to check it.