Start SQL Server with Docker & register initial data

Put the initial data in the Docker container of SQL Server and start it. Use the official Microsoft Docker image. In MySQL, the initial data input mechanism is built in from the beginning, but SQL Server does not have it, so I created a shell.

Microsoft SQL Server Docker image (https://hub.docker.com/_/microsoft-mssql-server) Reference: Data input sample linked from the official (https://github.com/twright-msft/mssql-node-docker-demo-app) Use SQL Server Express with Qiita Docker for Windows

Directory structure

|- docker-compose.yml
|- docker
    |- mssqlserver
        |- Dockerfile
        |- initdb.d
            |- entrypoint.sh
            |- import-data.sh
            |- demo.sql
            |- Demo.dbo.DEMO.csv
        |- data
        |- log
        |- secrets

Each file

docker-compose.yml

For SQL Server environment variables set in environment, click here (https://docs.microsoft.com/ja-jp/sql/linux/sql-server-linux-configure-environment-variables?view=sql See -server-ver15).

docker-compose.yml

version: '3'

services:
  mssql:
    build:
      context: ./docker/mssqlserver/
    image: mssql
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=password
      - MSSQL_PID=Express #SQL Server edition or product key
      - MSSQL_LCID=1041 #Locale ID Japanese(https://www.ryadel.com/en/microsoft-windows-lcid-list-decimal-and-hex-all-locale-codes-ids/)
      - MSSQL_COLLATION=Japanese_CI_AS #Collation
    volumes:
      - ./docker/mssqlserver/initdb.d:/docker-entrypoint-initdb.d
      - ./docker/mssqlserver/data:/var/opt/mssql/data
      - ./docker/mssqlserver/log:/var/opt/mssql/log
      - ./docker/mssqlserver/secrets:/var/opt/mssql/sec

Dockerfile

Copy the initial data and the input shell script and call entrypoint.sh. (By the way, EXPOSE seems to be just a document.)

Dockerfile

FROM mcr.microsoft.com/mssql/server:2017-latest

USER root
SHELL ["/bin/bash", "-c"]

WORKDIR /docker-entrypoint-initdb.d
COPY ./initdb.d/ /docker-entrypoint-initdb.d/
RUN chmod -R +x /docker-entrypoint-initdb.d

EXPOSE 1433

ENTRYPOINT ["/bin/bash", "./entrypoint.sh"]

entrypoint.sh

Start the data registration shell and SQL Server at the same time. I'm a little stuck here, but if I start SQL Server first, the container will stop after import-data.sh is executed. If you put SQL Server behind, the container will continue to start. (That is, does Docker decide whether to stop the container based on whether the last executed process is alive?)

Initialize MS SQL in Docker container - create database at startup（https://www.softwaredeveloper.blog/initialize-mssql-in-docker-container）

entrypoint.sh

#!/bin/bash

LOG_OUT=/var/opt/mssql/log/init-stdout.log
LOG_ERR=/var/opt/mssql/log/init-stderr.log

exec 1>>$LOG_OUT
exec 2>>$LOG_ERR

#If SQL Server is in front, the container will stop.
#/opt/mssql/bin/sqlservr & /docker-entrypoint-initdb.d/import-data.sh
/docker-entrypoint-initdb.d/import-data.sh & /opt/mssql/bin/sqlservr

import-data.sh

Since it starts at the same time as SQL Server, it sleeps for 20 seconds at the beginning until the instance starts. Also, when executing SQL or CSV import, if there is an error, wait 1 second and re-execute.

import-data.sh

#!/bin/bash
sleep 20

if [ `ls -U1 /var/opt/mssql/data | grep DEMO | wc -l` -eq 0 ]; then
    cd /docker-entrypoint-initdb.d
    sql_files=`ls *.sql`

    for file in $sql_files;
    do
        for i in {1..30};
        do
            /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $SA_PASSWORD -i $file
            if [ $? -eq 0 ]
            then
                echo "${file} completed."
                break
            else
                echo "${file} failed."
                sleep 1
            fi
        done
    done

    csv_files=`ls *.csv`
    for file in $csv_files;
    do
        for i in {1..30};
        do
            table_name=`basename $file .csv`
            /opt/mssql-tools/bin/bcp $table_name in $file -c -t',' -S localhost -U sa -P $SA_PASSWORD
            if [ $? -eq 0 ]
            then
                echo "${file} completed."
                break
            else
                echo "${file} failed."
                sleep 1
            fi
        done
    done
fi