MSSQL Server 2019 Docker Image for Windows

Goal: Build a custom Docker image for MSSQL Server 2019 on Windows (official images don’t include 2019 yet).

Get Your Windows Version

The base image must match your host Windows version. Run:

ver

Output example: 10.0.19042.867

Dockerfile

FROM mcr.microsoft.com/windows/servercore:10.0.19042.867
SHELL [ "powershell" ]

COPY ./setupScripts/installSqlServerTools.ps1 /setupScripts/installSqlServerTools.ps1
COPY ./setupScripts/startAndConfigSqlServer.ps1 /setupScripts/startAndConfigSqlServer.ps1
COPY ./installers /installers

RUN c:/setupScripts/installSqlServerTools.ps1

CMD /setupScripts/startAndConfigSqlServer.ps1 -sa_password \$env:sa_password -attach_dbs "\$env:attach_dbs" -ACCEPT_EULA Y -Verbose

Required Files

Place these in the installers/ directory:

Installation Script

setupScripts/installSqlServerTools.ps1 - Installs SQL Server 2019 with TCP enabled on port 1433 and adds SqlPackage to PATH:

## install sql server
cd c:/installers/sql2019install/Developer_ENU; 
.\setup.exe /q /ACTION=Install /INSTANCENAME=MSSQLSERVER /FEATURES=SQLEngine /UPDATEENABLED=0 /SQLSVCACCOUNT='NT AUTHORITY\NETWORK SERVICE' /SQLSYSADMINACCOUNTS='BUILTIN\ADMINISTRATORS' /TCPENABLED=1 /NPENABLED=0 /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS /SECURITYMODE=SQL /SAPWD="Password_01"

stop-service MSSQLSERVER
set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -name tcpdynamicports -value ''
set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -name tcpport -value 1433
set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\' -name LoginMode -value 2

## add sqlpackage tools to PATH
$current=[System.Environment]::GetEnvironmentVariable('PATH','machine')
$newpath="$current;C:\installers\sqlpackage-win7-x64-en-US-15.0.5084.2"
[System.Environment]::SetEnvironmentVariable('PATH', $newpath,[System.EnvironmentVariableTarget]::Machine)

Startup Script

setupScripts/startAndConfigSqlServer.ps1 - Starts SQL Server, sets SA password, and attaches databases from environment variable:

param(
[Parameter(Mandatory=$false)]
[string]$sa_password,

[Parameter(Mandatory=$false)]
[string]$ACCEPT_EULA,

[Parameter(Mandatory=$false)]
[string]$attach_dbs
)

Write-Verbose "Starting SQL Server..."
start-service MSSQLSERVER

Write-Verbose "Changing SA login credentials..."
$sqlcmd = "ALTER LOGIN sa with password=" +"'" + $sa_password + "'" + ";ALTER LOGIN sa ENABLE;"
& Invoke-Sqlcmd -Query $sqlcmd

$attach_dbs_cleaned = $attach_dbs.TrimStart('\\').TrimEnd('\\')

$dbs = $attach_dbs_cleaned | ConvertFrom-Json

if ($null -ne $dbs -And $dbs.Length -gt 0)
{
    Write-Verbose "Attaching $($dbs.Length) database(s)..."
	    
    Foreach($db in $dbs) 
    {            
        $files = @();
        Foreach($file in $db.dbFiles)
        {
            $files += "(FILENAME = N'$($file)')";           
        }

        $files = $files -join ","
        $sqlcmd = "IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = '" + $($db.dbName) + "') BEGIN EXEC sp_detach_db [$($db.dbName)] END;CREATE DATABASE [$($db.dbName)] ON $($files) FOR ATTACH;"

        Write-Verbose "Invoke-Sqlcmd -Query $($sqlcmd)"
        & Invoke-Sqlcmd -Query $sqlcmd
	}
}

Write-Verbose "Started SQL Server."

$lastCheck = (Get-Date).AddSeconds(-2) 
while ($true) 
{ 
    Get-EventLog -LogName Application -Source "MSSQL*" -After $lastCheck | Select-Object TimeGenerated, EntryType, Message	 
    
    $lastCheck = Get-Date 
    Start-Sleep -Seconds 2 
}