Docker compose how to initialize SQL Server DB on Windows container?

Question

I run docker for Windows 18.03 on Windows 10. I use the microsoft/mssql-server-windows-express (from Windows Server) image from a docker compose file in my own VS 2017 project. What I try to accomplish here is to initialize the database from a script. I tried using the "command" switch in the docker.compose.yml but without much success...

Here's the docker compose file :

  myscustomservice:
    image: myscustomservice
    build:
      context: .\myscustomservice
      dockerfile: Dockerfile

  db:
    image: microsoft/mssql-server-windows-express
    volumes:
       - ".\\data:C:\\data"
    #command: --init-file C:\\data\\CreateLocalDB.sql
    #command: "sqlcmd -U sa -P sUper45!pas5word -i C:\\data\\CreateLocalDB.sql"
    restart: always
    ports: 
      - "1533:1433"
    environment:
      - "sa_password=sUper45!pas5word"
      - "ACCEPT_EULA=Y"

volumes:
  db-data: 

Note that I have tried the 2 command lines that are commented. First one fails saying it doesn't find the file and second one just replace the normal command line by that one, so the container doesn't start (or doesn't stay up).

On my local drive, I have a C:\myscustomservice\data drive with the file CreateLocalDB.sql in it. It is mounted on the container in the C:\data folder (I see it when I run powershell inside the container).

The sql file looks like this :

USE MASTER

CREATE DATABASE [customDB_test]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'customDB_test', FILENAME = N'C:\data\customDB_test.mdf' , SIZE = 1594752KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'customDB_test_log', FILENAME = N'C:\data\customDB_test.ldf' , SIZE = 3584KB , MAXSIZE = 2048GB , FILEGROWTH = 10240KB )
GO  

Does anyone have an idea how could I do this ? All examples on the net are from linux containers, and this image is from Windows Server container.

Answer 1

OK, just so you know, I finally had to create anothe service that depends on "db" to call a powershell script that check for database existence. If it doesn't exists, I call an mssql script to create it.

Here's the dockerfile :

FROM microsoft/wcf:4.7.1
ARG source
# Creates a directory for custom application
RUN mkdir C:\MyCustomService

COPY . c:\\MyCustomService

# Remove existing default web site
RUN powershell -NoProfile -Command \
Import-module WebAdministration; \
Remove-WebSite -Name "'Default Web Site'"

# Configure the new site in IIS. Binds it to port 80 otherwise it won't work because it needs a default app listening on this port
RUN powershell -NoProfile -Command \
Import-module IISAdministration; \
New-IISSite -Name "MyCustomService" -PhysicalPath C:\MyCustomService -BindingInformation "*:80:";


# Add net.tcp support on the new site and change it to web aplication.
RUN Import-Module WebAdministration; Set-ItemProperty "IIS:\\Sites\\MyCustomService" -name bindings -value (@{protocol='net.tcp';bindingInformation='808:*'},@{protocol='http';bindingInformation='*:80:'});
RUN windows\system32\inetsrv\appcmd.exe set app 'MyCustomService/' /enabledProtocols:"http,net.tcp"
# This instruction tells the container to listen on port 83.
EXPOSE 80
EXPOSE 808

Here's the new docker-compose file :

myscustomservice:
  image: myscustomservice
  build:
    context: .\myscustomservice
    dockerfile: Dockerfile
  ports: 
  - "83:80"
  - "1010:808"
  depends_on: 
    - db
    - db-init

  db:
    image: microsoft/mssql-server-windows-express
    volumes:
     - ".\\data:C:\\data"

    ports: 
      - "1533:1433"
    environment:
      - "sa_password=sUper45!pas5word"
      - "ACCEPT_EULA=Y"
      - 'attach_dbs=[{"dbName":"customDB_test","dbFiles":["C:\\data\\customDB_test.mdf","C:\\data\\customDB_test.ldf"]}]'
  volumes:
    db-data: 


db-init:
  image: microsoft/mssql-server-windows-express
  volumes:
     - ".\\data:C:\\data"

  command: powershell -executionpolicy bypass "C:\\data\\initialize_db.ps1 -insertTestData"

  environment:
    - "sa_password=sUper45!pas5word"
    - "ACCEPT_EULA=Y"


  depends_on: 
    - db

Note the "attach_dbs" environment variable in the db service. This way, it tries to bind to existing files, so the script run by db_init service will find the database and will not recreate it.

The powershell script "initialize_db.ps1" :

param([switch]$insertTestData)

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "db\SQLEXPRESS"
$database = "customDB_test"
$dbs = $server.Databases
$exists = $false

#This sets the connection to mixed-mode authentication 
$server.ConnectionContext.LoginSecure=$false; 

#This sets the login name 
$server.ConnectionContext.set_Login("sa"); 

#This sets the password 
$server.ConnectionContext.set_Password("sUper45!pas5word")

try 
{
    foreach ($db in $dbs) 
    {
        Write-Host $db.Name
        if($db.Name -eq $database) 
        {
            Write-Host "Database already exist"
            $exists = $true
        }
    }

}
catch 
{
    Write-Error "Failed to connect to $server"
}


if(-not $exists)
{
    Write-Host "Database doesn't exist"
    $StopWatch = [System.Diagnostics.Stopwatch]::StartNew()

    sqlcmd -S 'db' -U sa -P 'sUper45!pas5word' -i 'C:\\data\\CreateLocalDB_schema.sql'
    Write-Host "Database created"
    $StopWatch.Elapsed

    if($insertTestData)
    {
        Write-Host "Begining data insertion..." 
        sqlcmd -S 'db' -U sa -P 'sUper45!pas5word' -i 'C:\\data\\CreateLocalDB_data.sql'
        Write-Host "Data inserted"
        $StopWatch.Elapsed
    }

    $StopWatch.Stop()
}

sqlcmd -S 'db' -U sa -P 'sUper45!pas5word' -i 'C:\\data\\CreateLocalDB_user.sql'

Ths script runs at least 1 and up to 3 SQL script :

• CreateLocalDB_user.sql - Recreates the custom login and the db user so that you can connect with this user
• CreateLocalDB_schema.sql - creates the database itself if it doesn't exist
• CreateLocalDB_data.sql - add startup data if you specified the "insertTestData" switch in the call (in your docker compose under db_init service)

The dockerfile of the service exposes ports 80 for http and 808 for net.tcp and my web.config file for "myscustomservice" exposes the wcf service like this :

      <host>
          <baseAddresses>
            <add baseAddress="net.tcp://localhost:1010/myscustomservice/Customer.svc"/>
          </baseAddresses>
        </host>