MSSQL Server 2019 docker image for windows

MSSQL 2019 not available yet in official images, so…

Dockerfile

The windows server base images must be the same windows version as the host, as per official documentation (¡¿?!). To get the version open cmd and type ver, to get something like 10.0.19042.867.

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

# Copy
COPY ./setupScripts/installSqlServerTools.ps1 /setupScripts/installSqlServerTools.ps1
COPY ./setupScripts/startAndConfigSqlServer.ps1 /setupScripts/startAndConfigSqlServer.ps1

# Copy installers
COPY ./installers /installers

# install Sql Server Developer edition (see readme.md for details)
RUN c:/setupScripts/installSqlServerTools.ps1

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

Installers

The folder installers contains:

Sql Server 2019 installer, like installers\sql2019install\Developer_ENU. Folder Developer_ENU contains the actuall installation files. The documentation is here
SqlPackage binaries (dacpac tool), to be added to PATH. It’s in installers\sqlpackage-win7-x64-en-US-15.0.5084.2. Downloaded from here

Scripts

installSqlServerTools.ps1 installs sql server 2019 and adds SqlPackage tool (dacpac tool) 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)

startAndConfigSqlServer.ps1 starts SqlServer and attaches the mdf/ldf files as per 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 
}