For what it’s worth, SQL should be a little bit easier than solr. This Container really just the base, SQL Image, then the Sitecore SQL Image on top of it. Let’s get cracking! Looking at the docker file in \windows\dependencies\mssql-developer-2017:

# escape=`
ARG BASE_IMAGE

FROM $BASE_IMAGE

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

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

RUN [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12; `
    New-Item -Path 'C:\\install' -ItemType 'Directory' | Out-Null; `
    Invoke-WebRequest -Uri 'https://go.microsoft.com/fwlink/?linkid=840945' -UseBasicParsing -OutFile 'C:\\install\\SQL.exe'; `
    Invoke-WebRequest -Uri 'https://go.microsoft.com/fwlink/?linkid=840944' -UseBasicParsing -OutFile 'C:\\install\\SQL.box'; `
    Start-Process -FilePath 'C:\\install\\SQL.exe' -ArgumentList '/qs', '/x:C:\install\setup' -NoNewWindow -Wait; `
    Start-Process -FilePath 'C:\\install\\setup\\setup.exe' -ArgumentList '/q', '/ACTION=Install', '/INSTANCENAME=MSSQLSERVER', '/FEATURES=SQLEngine', '/UPDATEENABLED=0', '/SQLSVCACCOUNT=\"NT AUTHORITY\System\"', '/SQLSYSADMINACCOUNTS=\"BUILTIN\ADMINISTRATORS\"', '/TCPENABLED=1', '/NPENABLED=0', '/IACCEPTSQLSERVERLICENSETERMS' -NoNewWindow -Wait; `
    Stop-Service MSSQLSERVER; `
    Set-ItemProperty -Path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -Name 'tcpdynamicports' -Value ''; `
    Set-ItemProperty -Path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -Name 'tcpport' -Value 1433; `
    Set-ItemProperty -Path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver' -Name 'LoginMode' -Value 2; `
    Invoke-WebRequest -Uri 'https://download.microsoft.com/download/9/2/2/9228AAC2-90D1-4F48-B423-AF345296C7DD/EN/x64/DacFramework.msi' -UseBasicParsing -OutFile 'C:\\install\\DacFramework.msi'; `
    Start-Process -FilePath 'msiexec.exe' -ArgumentList '/i', 'C:\\install\\DacFramework.msi', '/quiet', '/norestart' -NoNewWindow -Wait; `
    Remove-Item -Path 'C:\\install' -Recurse -Force;

COPY Start.ps1 .

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

CMD .\Start.ps1 -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs \"$env:attach_dbs\" -Verbose

You’ll notice a new command in here: HEALTHCHECK. This is used to ensure the Container is running in a valid state. Essentially this does a simple SQL Query to ensure the SQL processes are running. And since this is the base SQL Image, every child image is going to get the same benefit.

docker image build --memory 4GB --build-arg BASE_IMAGE=mcr.microsoft.com/windows/servercore:1903 --tag mssql-developer:2017-windowsservercore-1903 --isolation 'hyperv' .\windows\dependencies\mssql-developer-2017\

Let’s create that image now:

docker image build --memory 4GB --build-arg BASE_IMAGE=mcr.microsoft.com/windows/servercore:1903 --tag mssql-developer:2017-windowsservercore-1903 --isolation 'hyperv' .\windows\dependencies\mssql-developer-2017\

One addition here is the ability to set the memory allocation for the image. This is SQL, so we’ll want to grab some headroom.

Now that we have a SQL Base, let’s throw on some Sitecore SQL on top of it. The Dockerfile is found in \windows\9.x.x\sitecore-xm-sqldev and looks like the following:

# escape=`
ARG BASE_IMAGE
ARG ASSETS_IMAGE

FROM $ASSETS_IMAGE as assets
FROM $BASE_IMAGE

ARG ASSETS_USE_WDP

ENV ACCEPT_EULA='Y' `
    sa_password='HASH-epsom-sunset-cost7!' `
    INSTALL_PATH='C:\\install\\' `
    DATA_PATH='C:\\data\\'

COPY --from=assets ["${ASSETS_USE_WDP}", "${INSTALL_PATH}"]
COPY . ${INSTALL_PATH}

RUN New-Item -Path $env:DATA_PATH -ItemType Directory | Out-Null; `
    & (Join-Path $env:INSTALL_PATH "\\Extract-Databases.ps1") -Path $env:INSTALL_PATH; `
    & (Join-Path $env:INSTALL_PATH "\\Install-Databases.ps1") -InstallPath $env:INSTALL_PATH -DataPath $env:DATA_PATH; `
    Copy-Item -Path (Join-Path $env:INSTALL_PATH "\\Boot.ps1") -Destination 'C:\\'; `
    Get-ChildItem -Path $env:INSTALL_PATH -Exclude "*.mdf", "*.ldf" | Remove-Item -Force; 

CMD C:/Boot.ps1 -InstallPath $env:INSTALL_PATH -DataPath $env:DATA_PATH

Nothing fancy! This is decidedly Sitecore due to the DacPac and WDP references. Also, you’ll see the two custom PS1 files getting copied in.

We’ve got to take note though: the sa password is being set in the sa_password variable. We’ll need the latter to log into SQL if we so desire. Throw it in your password vault, or put it on a sticky note on your monitor. Let’s create this image

docker image build --memory 4GB --build-arg BASE_IMAGE=mssql-developer:2017-windowsservercore-1903 --build-arg ASSETS_IMAGE=sitecore-assets:9.2.0-nanoserver-1903 --build-arg ASSETS_USE_WDP='C:\\packages\\Sitecore 9.2.0 rev. 002893 (XM) (OnPrem)_cm.scwdp.zip' --tag sitecore-xm-sqldev:9.2.0-windowsservercore-1903 --isolation 'hyperv' .\windows\9.x.x\sitecore-xm-sqldev\

Again with the memory option. Also, noting the ASSET_USE_WDP. The path here doesn’t seem to match the local filesystem, so where are they coming from? Look at the ASSET_IMAGE variable there. Notice it points to our original Asset Image? Those files come from that base. Sweet? Hell yeah!

Once that is all said and done, you’ve got your local SQL Image ready to go. Let’s take a look!

Let’s spin up a container using that image:

docker run -d --publish 44010:1433 --mount type=bind,source=c:/Dev/docker/sitecore/dummy/sql,target=c:/data --name sql_xm_920  sitecore-xm-sqldev:9.2.0-windowsservercore-1903

Similar concepts here compared to solr. We’re opening up port 1433 to our local 44010. We’re mounting a drive, and we’re giving it a name that makes sense for us.

How do you we test? Open up SSMS and connect away! Note below the screenshot. The port is separated from the server name by a comma.

Entering your login information (that lovely Password you snagged from earlier) and viola:

If you’re greeted by an error of “The certificate chain was issued by an authority that is not trusted”, then click the “Options >>” Button, click “Connection Properties” and then check the “Trust server certificate” box like so:

Once you’ve done that, click Connect. You should see your new server up and running with your Sitecore Databases!

Congrats on your new SQL 2017 Container. If you want to phone mom and dad, that’s great. If you’re Batman, maybe Alfred. Either way, next up is the final piece of the puzzle: Creating the Sitecore CM. Head on over to Part 8: It’s Sitecore Time.

The TL;DR Code

#create our SQL base, and then sitecore SQL Images
docker image build --memory 4GB --build-arg BASE_IMAGE=mcr.microsoft.com/windows/servercore:1903 --tag mssql-developer:2017-windowsservercore-1903 --isolation 'hyperv' .\windows\dependencies\mssql-developer-2017\
docker image build --memory 4GB --build-arg BASE_IMAGE=mssql-developer:2017-windowsservercore-1903 --build-arg ASSETS_IMAGE=sitecore-assets:9.2.0-nanoserver-1903 --build-arg ASSETS_USE_WDP='C:\\packages\\Sitecore 9.2.0 rev. 002893 (XM) (OnPrem)_cm.scwdp.zip' --tag sitecore-xm-sqldev:9.2.0-windowsservercore-1903 --isolation 'hyperv' .\windows\9.x.x\sitecore-xm-sqldev\
#create the container
docker run -d --publish 44010:1433 --mount type=bind,source=c:/Dev/docker/sitecore/dummy/sql,target=c:/data --name sql_xm_920  sitecore-xm-sqldev:9.2.0-windowsservercore-1903

---

This is a post in the “Yet Another Sitecore Docker Series.” Other Posts include

• Part 1: The Beginning
• Part 2: Running Docker
• Part 3: Layers are Yum
• Part 4: Composing an Image
• Part 5: Deconstructing a Dockerfile
• Part 6: Simply Solr
• Part 7: Smooth as SQL
• Part 8: It’s Sitecore Time
• Part 9: Compose all the Things

---