Mssql-docker: Healthcheck

[mrfuxi]

Would it be possible to implement Docker's HEALTHCHECK command?

[twright-msft]

Yeah, now that we have sqlcmd in the image this should be much easier. I'll put it on our backlog internally. If you need something sooner, I'd suggest creating your own image FROM ours and specifying a HEALTCHECK that uses sqlcmd with -q to execute a simple query periodically like 'SELECT 1'.

[sangohan]

I too am having an issue where the docker image crashes, but continues to output lines of logging, which prevents my internal hung-build-killer tool to detect that something went wrong.

2017-09-06 09:50:11.21 spid90 Timeout occurred while waiting for latch: class 'LOG_MANAGER', id 00000005ED740BB0, type 4, Task 0x00000005EB49B088 : 0, waittime 71400 seconds, flags 0x1a, owning task 0x00000005EB492CA8. Continuing to wait.
2017-09-06 09:50:25.26 spid89 Timeout occurred while waiting for latch: class 'LOG_MANAGER', id 00000005ED740BB0, type 4, Task 0x00000005EEDE7088 : 0, waittime 71400 seconds, flags 0x1a, owning task 0x00000005EB492CA8. Continuing to wait.
2017-09-06 09:51:25.25 spid84 Timeout occurred while waiting for latch: class 'LOG_MANAGER', id 00000005ED740BB0, type 4, Task 0x00000005E800DC28 : 0, waittime 71400 seconds, flags 0x1a, owning task 0x00000005EB492CA8. Continuing to wait.

having a healthcheck mechanism in the case of this would be great.

[manicmonkey]

I get the same crash / error after using netcat to ascertain when the process has started listening on port 1433

[axelfontaine]

This is super useful in order to avoid having to develop custom poll and wait script to check whether the DB is up in order to be able to start other activities.

[krkabol]

Dockerfile:

FROM microsoft/mssql-server-linux
HEALTHCHECK --interval=10s --timeout=3s --start-period=10s --retries=10 \
    CMD sqlcmd -S localhost -U sa -P ${SA_PASSWORD} -Q "SELECT 1" || exit 1

[djechelon]

It should be

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

Because sqlcmd is not on PATH

[raczeja]

Is there a simple way to use HEALTHCHECK?
I use this piece of code, but maybe you can advise something simpler?
https://github.com/ObjectivityLtd/DBTestCompare/blob/master/restoreBackup.ps1#L7
do { $JSON=docker inspect --format='{{json .State.Health}}' sqlserver-container | Out-String | ConvertFrom-Json Write-Host HEALTHCHECK: $JSON.Status.ToString() sqlserver-container Start-Sleep -s 10 } Until ($JSON.Status.ToString() -eq 'healthy')

[tinohager]

version: "3.4"
services:
    db:
        image: "mcr.microsoft.com/mssql/server:2019-latest"
        environment:
            SA_PASSWORD: "Secure-Password.1234"
            ACCEPT_EULA: "Y"
        healthcheck:
            test: /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "$$SA_PASSWORD" -Q "SELECT 1" || exit 1
            interval: 10s
            timeout: 3s
            retries: 10
            start_period: 10s

[mrlioncub]

Everything that is written above must be changed:
required with the option: -b
and I recommend using the option: -o /dev/null
in docker-compose.yaml

    healthcheck:
      test: /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "$${SA_PASSWORD}" -Q "SELECT 1" -b -o /dev/null
      interval: 10s
      timeout: 3s
      retries: 10
      start_period: 10s

in Dockerfile:

HEALTHCHECK --interval=10s --timeout=3s --start-period=10s --retries=10 \
  CMD /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $${SA_PASSWORD} -Q "SELECT 1" -b -o /dev/null

[raschmitt]

I'm curious here, why do we need to have exit 1 if sqlcmd -Q already has a built-in exit?

 -Q "cmdline query" (and exit)  

https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15#syntax

[mrlioncub]

Yes, exit 1 is not needed. Updated