Trouble getting docker compose run with a containers for SQL Server and dacpac - Stack Overflow

admin管理员组

文章数量:1332628

I've been trying to create a SQL Server container and then a container to install a database from a .sqlproj and for that I'm using a dockerfile.

The trouble is that when I run the command docker compose up --build the .dacpac container never starts (but I can start it manually). Then both containers fail. If I just run the SQL Server container separately, it works well.

The contents of the docker compose file is

services:

  sqlserver-2022:
    image: mcr.microsoft/mssql/server:2022-latest
    container_name: sqlserver-2022
    environment:
      - ACCEPT_EULA=true
      - MSSQL_SA_PASSWORD=Strong#Passw0rd1
    ports:
      - "1433:1433" 
    volumes:
      - sqldata2022:/var/opt/mssql
    healthcheck:
      test: /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$${MSSQL_SA_PASSWORD}" -C -Q "SELECT 'Healthcheck OK'" 
      interval: 5s
      timeout: 3s
      retries: 10
      start_period: 15s

  dacpac-runner:
    build:
      context: .
      dockerfile: Dacpac.Dockerfile
    container_name: dacpac-runner
    depends_on:
      sqlserver-2022:
        condition: service_healthy
    volumes:
      - ./projectfolder.Database:/database-src
      - ./dacpac:/dacpac

volumes:
  sqldata2022:

The Dacpac.Dockerfile is

FROM mcr.microsoft/mssql/server:2022-latest

USER root
### Install Unzip
#RUN apt-get update \
#    && apt-get install unzip -y
RUN apt-get update \
    && apt-get install -y unzip wget curl

### Install SQLPackage for Linux and make it executable
# Download and set up sqlpackage
RUN curl -sSL  -o sqlpackage.zip \
    && mkdir -p /opt/sqlpackage \
    && unzip sqlpackage.zip -d /opt/sqlpackage \
    && chmod +x /opt/sqlpackage/sqlpackage \
    && rm sqlpackage.zip


### Add the DACPAC to the image
COPY ./projectfolder.Database.Database/bin/Debug/projectfolder.Database.dacpac /tmp/db.dacpac
COPY ./execute-dacpac.sh /app/execute-dacpac.sh

ENV ACCEPT_EULA=Y

EXPOSE 1433

# Make the script executable
RUN chmod +x /app/execute-dacpac.sh
# Entry point for running the script
ENTRYPOINT ["sh", "-c", "/app/execute-dacpac.sh"]

And the execute-dacpac.sh file is

#!/bin/bash

set -e  # Exit immediately if a command exits with a non-zero status
set -x  # Print commands and their arguments as they are executed

# Wait for SQL Server to be ready
echo "Starting SQL Server in the background..."
( /opt/mssql/bin/sqlservr & )

# Wait for the "Service Broker manager has started" message in the logs
echo "Waiting for SQL Server Service Broker to start..."

until /opt/mssql-tools18/bin/sqlcmd -S "localhost,1433" -U "sa" -P "Strong#Passw0rd1" -Q "SELECT 'Healthcheck OK'" &>/dev/null; do
  echo "SQL Server is not ready yet. Retrying..."
  sleep 5
done

### Configure the required environmental variables
#
#ENV SA_PASSWORD=$SAPASSWORD

# Deploy the DACPAC using sqlpackage
#echo "Deploying the DACPAC..."
/opt/sqlpackage/sqlpackage /a:Publish \
  /tsn:"tcp:sqlserver-2022,1433;Encrypt=True;TrustServerCertificate=True" \
  /tdn:"databasetest" \
  /tu:"sa" \
  /tp:"Strong#Passw0rd1" \
  /sf:/tmp/db.dacpac

# Clean up
#echo "Cleaning up temporary DACPAC file..."
#rm /tmp/db.dacpac

# Terminate SQL Server
#echo "Stopping SQL Server..."
#pkill sqlservr
#
#echo "Script execution completed successfully."

I know lot's of things can be improved but at this point I'm just trying to make it work.

Just discovered one bug in sql server healtcheck. It needs to be:

      test: /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$${MSSQL_SA_PASSWORD}" -C -Q "SELECT 'Healthcheck OK'" 

The issue I have now is that the container for dacpac-runner is stuck in an infinite loop

dacpac-runner   | + echo 'SQL Server is not ready yet. Retrying...'
dacpac-runner   | + sleep 5
dacpac-runner   | SQL Server is not ready yet. Retrying...
dacpac-runner   | + /opt/mssql-tools18/bin/sqlcmd -S localhost,1433 -U sa -P Strong#Passw0rd1 -Q 'SELECT '\''Healthcheck OK'\'''

The first container says in the logs

    "Health": {
        "Status": "healthy",

While the second in the labels section says:

            "com.dockerpose.depends_on": "sqlserver-2022:service_healthy:false",

When it runs the sql command to create the database it gives

*** 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: 35 - An internal exception was caught)

What is the correct way to configure the command?

/opt/sqlpackage/sqlpackage /a:Publish
/tsn:"sqlserver-2022,1433"
/tdn:"databasetest"
/tu:"sa"
/tp:"Strong#Passw0rd1"
/sf:/tmp/db.dacpac

I've been trying to create a SQL Server container and then a container to install a database from a .sqlproj and for that I'm using a dockerfile.

The trouble is that when I run the command docker compose up --build the .dacpac container never starts (but I can start it manually). Then both containers fail. If I just run the SQL Server container separately, it works well.

The contents of the docker compose file is

services:

  sqlserver-2022:
    image: mcr.microsoft/mssql/server:2022-latest
    container_name: sqlserver-2022
    environment:
      - ACCEPT_EULA=true
      - MSSQL_SA_PASSWORD=Strong#Passw0rd1
    ports:
      - "1433:1433" 
    volumes:
      - sqldata2022:/var/opt/mssql
    healthcheck:
      test: /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$${MSSQL_SA_PASSWORD}" -C -Q "SELECT 'Healthcheck OK'" 
      interval: 5s
      timeout: 3s
      retries: 10
      start_period: 15s

  dacpac-runner:
    build:
      context: .
      dockerfile: Dacpac.Dockerfile
    container_name: dacpac-runner
    depends_on:
      sqlserver-2022:
        condition: service_healthy
    volumes:
      - ./projectfolder.Database:/database-src
      - ./dacpac:/dacpac

volumes:
  sqldata2022:

The Dacpac.Dockerfile is

FROM mcr.microsoft/mssql/server:2022-latest

USER root
### Install Unzip
#RUN apt-get update \
#    && apt-get install unzip -y
RUN apt-get update \
    && apt-get install -y unzip wget curl

### Install SQLPackage for Linux and make it executable
# Download and set up sqlpackage
RUN curl -sSL https://aka.ms/sqlpackage-linux -o sqlpackage.zip \
    && mkdir -p /opt/sqlpackage \
    && unzip sqlpackage.zip -d /opt/sqlpackage \
    && chmod +x /opt/sqlpackage/sqlpackage \
    && rm sqlpackage.zip


### Add the DACPAC to the image
COPY ./projectfolder.Database.Database/bin/Debug/projectfolder.Database.dacpac /tmp/db.dacpac
COPY ./execute-dacpac.sh /app/execute-dacpac.sh

ENV ACCEPT_EULA=Y

EXPOSE 1433

# Make the script executable
RUN chmod +x /app/execute-dacpac.sh
# Entry point for running the script
ENTRYPOINT ["sh", "-c", "/app/execute-dacpac.sh"]

And the execute-dacpac.sh file is

#!/bin/bash

set -e  # Exit immediately if a command exits with a non-zero status
set -x  # Print commands and their arguments as they are executed

# Wait for SQL Server to be ready
echo "Starting SQL Server in the background..."
( /opt/mssql/bin/sqlservr & )

# Wait for the "Service Broker manager has started" message in the logs
echo "Waiting for SQL Server Service Broker to start..."

until /opt/mssql-tools18/bin/sqlcmd -S "localhost,1433" -U "sa" -P "Strong#Passw0rd1" -Q "SELECT 'Healthcheck OK'" &>/dev/null; do
  echo "SQL Server is not ready yet. Retrying..."
  sleep 5
done

### Configure the required environmental variables
#
#ENV SA_PASSWORD=$SAPASSWORD

# Deploy the DACPAC using sqlpackage
#echo "Deploying the DACPAC..."
/opt/sqlpackage/sqlpackage /a:Publish \
  /tsn:"tcp:sqlserver-2022,1433;Encrypt=True;TrustServerCertificate=True" \
  /tdn:"databasetest" \
  /tu:"sa" \
  /tp:"Strong#Passw0rd1" \
  /sf:/tmp/db.dacpac

# Clean up
#echo "Cleaning up temporary DACPAC file..."
#rm /tmp/db.dacpac

# Terminate SQL Server
#echo "Stopping SQL Server..."
#pkill sqlservr
#
#echo "Script execution completed successfully."

I know lot's of things can be improved but at this point I'm just trying to make it work.

Just discovered one bug in sql server healtcheck. It needs to be:

      test: /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$${MSSQL_SA_PASSWORD}" -C -Q "SELECT 'Healthcheck OK'" 

The issue I have now is that the container for dacpac-runner is stuck in an infinite loop

dacpac-runner   | + echo 'SQL Server is not ready yet. Retrying...'
dacpac-runner   | + sleep 5
dacpac-runner   | SQL Server is not ready yet. Retrying...
dacpac-runner   | + /opt/mssql-tools18/bin/sqlcmd -S localhost,1433 -U sa -P Strong#Passw0rd1 -Q 'SELECT '\''Healthcheck OK'\'''

The first container says in the logs

    "Health": {
        "Status": "healthy",

While the second in the labels section says:

            "com.dockerpose.depends_on": "sqlserver-2022:service_healthy:false",

When it runs the sql command to create the database it gives

*** 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: 35 - An internal exception was caught)

What is the correct way to configure the command?

/opt/sqlpackage/sqlpackage /a:Publish
/tsn:"sqlserver-2022,1433"
/tdn:"databasetest"
/tu:"sa"
/tp:"Strong#Passw0rd1"
/sf:/tmp/db.dacpac

• sql-server
• docker
• docker-compose
• dacpac

Share Improve this question Follow edited Jan 19 at 12:26 Nmaster88 asked Jan 17 at 23:49 Nmaster88Nmaster88 1,60522 gold badges3333 silver badges7575 bronze badges 6

• If you docker container inspect sqlserver-2022's-container-id what is the output recorded in the Health > Log > Output property/ies? – AlwaysLearning Commented Jan 18 at 13:02
• Hi @AlwaysLearning I discovered a bug and updated the response. I will now need to check the rest. – Nmaster88 Commented Jan 18 at 15:51
• Have you run sqlcmd -? to see what command line options are available? Where you specified the -TrustServerCertificate option did you instead mean to use -C? – AlwaysLearning Commented Jan 19 at 0:40
• Hi @AlwaysLearning yes I already fixed that part, the healtcheck works well now. The problem now is when I run the command /opt/sqlpackage/sqlpackage – Nmaster88 Commented Jan 19 at 12:22
• I don't think you can add additional properties to the /TargetServerName parameter like you're trying to do. Also, at what point do you create the databasetest database? You might want to re-read the SqlPackage command line syntax. – AlwaysLearning Commented Jan 19 at 12:41

 |  Show 1 more comment

1 Answer 1

Sorted by: Reset to default 0

I was able to fix my issue change the sqlcommand query to be like this:

/opt/sqlpackage/sqlpackage /a:Publish \
  /tsn:"sqlserver-2022,1433" \
  /tdn:"databasetest" \
  /tu:"sa" \
  /tp:"Strong#Passw0rd1" \
  /ttsc:True \
  /sf:/tmp/db.dacpac

本文标签： Trouble getting docker compose run with a containers for SQL Server and dacpacStack Overflow