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

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>