Sitecore Docker Development Image for MS SQL Server 2019

Question

The Sitecore container registry contains mssql-developer images for Windows OS targeting only MS SQL Server 2017 (see full list here).

The Microsoft Artifact Registry contains only MS SQL Server images for Linux OS: https://mcr.microsoft.com/en-us/catalog?search=sql

If I want to use an MS SQL Server image for Windows OS targeting an higher version of SQL Server, for example MS SQL server 2019, what should I do?

Answer 1

You should build your own custom Windows based SQL Server image, targeting the desired version.

The following steps describe how to do it, targeting MS SQL Server 2019.

Step 1 - Create a dockerfile spec file:

The dockerfile should define the following build steps:

• Use a Microsoft servercore image as base image
• Define needed environment variables
• Copy entrypoint script (same used in Sitecore mssql-developer images images)
• Download and install the desired MS SQL Server software as a service
• Configure TCP ports and SQL Server login mode
• Define Healthcheck command
• Define image startup command

Example of dockerfile for MS SQL Server 2019 Windows image:

# escape=`
# This Dockerfile is used to generate images for the following roles: mssql

ARG BASE_IMAGE

FROM ${BASE_IMAGE}

ENV sa_password="_" `
    attach_dbs="[]" `
    ACCEPT_EULA="_" `
    sa_password_path="C:\ProgramData\Docker\secrets\sa-password" `
    DATA_PATH="C:\data"

SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';"]

# Overwrite base entrypoint with custom one
COPY \tools\entrypoints .

WORKDIR /

RUN Invoke-WebRequest -Uri "https://download.microsoft.com/download/8/4/c/84c6c430-e0f5-476d-bf43-eaaa222a72e0/SQLServer2019-DEV-x64-ENU.exe" -OutFile SQL.exe; `
    Invoke-WebRequest -Uri "https://download.microsoft.com/download/8/4/c/84c6c430-e0f5-476d-bf43-eaaa222a72e0/SQLServer2019-DEV-x64-ENU.box" -OutFile SQL.box; `
    Invoke-WebRequest -Uri "https://download.microsoft.com/download/f/1/9/f19eaee6-0728-4a0b-9755-9808acc8af0b/EN/x64/DacFramework.msi" -OutFile C:\DacFramework.msi; `
    Start-Process -Wait -FilePath .\SQL.exe -ArgumentList /qs, /x:setup; `
    .\setup\setup.exe /q /ACTION=Install /INSTANCENAME=MSSQLSERVER /FEATURES=SQLEngine /SQLUSERDBDIR=$env:DATA_PATH /UPDATEENABLED=0 /SQLSVCACCOUNT='NT AUTHORITY\NETWORK SERVICE' /SQLSYSADMINACCOUNTS='BUILTIN\ADMINISTRATORS' /TCPENABLED=1 /NPENABLED=0 /IACCEPTSQLSERVERLICENSETERMS /SQLSVCSTARTUPTYPE='Manual'; `
    Start-Process -NoNewWindow -Wait msiexec.exe -ArgumentList /i, C:\DacFramework.msi, /quiet, /norestart; `
    Remove-Item -Recurse -Force SQL.exe, SQL.box, setup, C:\DacFramework.msi;

RUN 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;

HEALTHCHECK CMD ["sqlcmd","-Q","SELECT 1"]

CMD .\start -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs \"$env:attach_dbs\" -DataDirectory $env:DATA_PATH -Verbose

NOTE: Use different download link to target different MS SQL Server software versions. Installation parameters might vary across SQL Server versions as well.

Step 2 - Configure the build command for the mssql service in the docker-compose.override.yml file of your project

Your docker-compose.override.yml file might already have a build command for the mssql service. If it doesn't, add the following section:

mssql:
    ...
    build:
      context: ./docker/build/mssql
      args:
        BASE_IMAGE: mcr.microsoft.com/windows/servercore:${OS_IMAGE_TAG}

The configuration above uses the OS_IMAGE_TAG environment variable to specify the Windows OS targeting version (ie. ltsc-2019).

Step 3 - Copy the Start.ps1 entrypoint script in your host build folder

One of the build steps in the dockerfile described at Step 1 above copies a custom entrypoint start script from the build host machine inside the image. The build step is expecting the start script to be located in the \tools\entrypoints folder relative to the location of the build context (./docker/build/mssql) defined in the docker-compose.override.yml file described at Step 2 above.

The original Start.ps1 script can be copied and exported from one of the Sitecore mssql-developer images, or copied from here below:

# The script sets the sa password and start the SQL Service
# Also it attaches additional database from the disk
# The format for attach_dbs

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

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

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

    [Parameter(Mandatory)]
    [ValidateScript({ Test-Path $_ -PathType Container })]
    [string]$DataDirectory
)


if ($ACCEPT_EULA -ne "Y" -And $ACCEPT_EULA -ne "y") {
    Write-Verbose "ERROR: You must accept the End User License Agreement before this container can start."
    Write-Verbose "Set the environment variable ACCEPT_EULA to 'Y' if you accept the agreement."

    exit 1
}

# start the service
Write-Verbose "Starting SQL Server"
start-service MSSQLSERVER

# Enable Contained Database Authentication
Write-Verbose "Enabling contained database authentication"
& sqlcmd -Q "sp_configure 'contained database authentication', 1; RECONFIGURE;"

if ($sa_password -eq "_") {
    if (Test-Path $env:sa_password_path) {
        $sa_password = Get-Content -Raw $secretPath
    }
    else {
        Write-Verbose "WARN: Using default SA password, secret file not found at: $secretPath"
    }
}

if ($sa_password -ne "_") {
    Write-Verbose "Changing SA login credentials"
    $sqlcmd = "ALTER LOGIN sa with password=" + "'" + $sa_password + "'" + ";ALTER LOGIN sa ENABLE;"
    & sqlcmd -Q $sqlcmd
}

#Attach databases in data directory
Get-ChildItem -Path $DataDirectory -Filter "*.mdf" | ForEach-Object {
    $databaseName = $_.BaseName.Replace("_Primary", "")
    $mdfPath = $_.FullName

    $primaryDbEnding = $_.Name.Replace(".mdf", ".ldf")
    $logDbEnding = $databaseName + "_log.ldf"
    
    $ldfPath = Get-ChildItem -Path $DataDirectory | Where-Object { $_.Name -eq $primaryDbEnding -or $_.Name -eq $logDbEnding }
    $ldfPath = $ldfPath.FullName
    $sqlcmd = "IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = '$databaseName') BEGIN EXEC sp_detach_db [$databaseName] END;CREATE DATABASE [$databaseName] ON (FILENAME = N'$mdfPath'), (FILENAME = N'$ldfPath') FOR ATTACH;"

    Write-Host "INFO: Attaching '$databaseName'..."

    & sqlcmd -Q $sqlcmd
}

#attach additional databases from parameter
$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)"
        & sqlcmd -Q $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 
}

Step 4 - Build the custom image

As last step, build the custom MS SQL image executing the docker-compose build mssql command in Windows Powershell in the folder where the docker-compose.override.yml file is located.