Sql-server – Building a custom SQL Server 2017/2019 container on a windows server without internet

SQL Server 2019 Windows containers are still in preview as of this writing. If you don't join the Early Adopter program, you can create a custom image by starting with a Windows Server Core base image and then install SQL Server 2019 (and optionally CUs) to create a custom image.

The build is most easily done on a machine with internet connectivity to pull the base OS image from the public repro. You can then copy the image to the machine without an internet connection (or push it to a local private repro) and run it from there. Alternatively, pull, save, and load the OS images (steps 6-8) and run the build on the server.

Below is an example of the general steps to do this. My appologies for the formatting. I'm mardown-challenged.

1. create an empty directory on your Windows machine (e.g. C:\dockerfiles)

    MKDIR C:\dockerfiles
   

2. create a dockerfile in that directory with the following contents

    #Start with a Windows Server Core base image.
    #Note that the image with tag 'latest' is pulled by default, which could be older than the actual host OS version.
    #In this case, specify the version-specific to pull a later image compatible with the host OS version.
   
    FROM mcr.microsoft.com/windows/servercore:2004
   
    #Parameters for start.ps1:
    ENV sa_password="_" \
        attach_dbs="[]" \
        ACCEPT_EULA="_" \
        sa_password_path="C:\ProgramData\Docker\secrets\sa-password"
   
    #set default shell to PowerShell and set preferences:
    SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';"]
   
    #set current directory to root (C:\)
    WORKDIR /
   
    #copy SQL Server setup folder into build image for installation
    COPY setup setup
   
    #run setup to install
    RUN  \setup\setup.exe /q /ACTION=Install /INSTANCENAME=MSSQLSERVER /FEATURES=SQLEngine /UPDATEENABLED=0 /SQLSVCACCOUNT='NT AUTHORITY\System' /SQLSYSADMINACCOUNTS='BUILTIN\ADMINISTRATORS' /TCPENABLED=1 /NPENABLED=0 /IACCEPTSQLSERVERLICENSETERMS
   
    #remove setup files
    RUN  Remove-Item -Force -Recurse -Path 'setup'
   
    #create empty directory (C:\SqlFiles) for user database files
    RUN "mkdir SqlFiles | Out-Null"
   
    #stop SQL Server service, configure TCP/IP, allow SQL authentication, and set default file locations
    RUN 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 ; \
            set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\' -name BackupDirectory -value 'C:\SqlFiles' ; \
            set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\' -name DefaultData -value 'C:\SqlFiles' ; \
            set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql15.MSSQLSERVER\mssqlserver\' -name DefaultLog -value 'C:\SqlFiles' ;
   
    #set command used for container health check status
    HEALTHCHECK CMD [ "sqlcmd", "-Q", "SELECT 1;" ]
   
    #copy startup script to image
    COPY start.ps1 /
   
    #specify command invoked when container is run from this image
    CMD .\start -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs \"$env:attach_dbs\" -Verbose
   

3. create start.ps1 file in build directory (e.g. C:\dockerfiles\start.ps1). Below is an example script similar to the one I use.

    # 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
    )
   
   
    if($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
   
    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_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."
   
    Write-Verbose "Changing SQL Server server name to $($env:COMPUTERNAME)"
    $sqlcmd = "EXEC sp_dropserver @@SERVERNAME;EXEC sp_addserver N'$env:COMPUTERNAME', 'local';"
    & sqlcmd -Q $sqlcmd
   
    Write-Verbose "Restarting SQL Server."
    Stop-Service MSSQLSERVER
    Start-Service MSSQLSERVER
    Write-Verbose "Restarted SQL Server."
   
    Write-Verbose "Tailing log..."
    Get-Content -Path "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG" -Wait
   

4. copy files from SQL 2019 installation media to a setup subdirectory (e.g. C:\dockerfiles\setup)

5. run docker build in the contest of the build directory (e.g. C:\dockerfiles) to build and tag the custom image

    docker build -t mssql-2019-windows-dev-rtm-example .
   

6. save the image to a tar file

    docker save --output "C:\temp\mssql-2019-windows-dev-rtm-example.tar" mssql-2019-windows-dev-rtm-example
   

7. copy the image to the Windows server

    copy "C:\temp\mssql-2019-windows-dev-rtm-example.tar" "\\YourServer\c$\temp\mssql-2019-windows-dev-rtm-example.tar"
   

8. load the image from the tar file on the Windows server machine:

    docker load --input "C:\temp\mssql-2019-windows-dev-rtm-example.tar"
   

9. example to run the image

    docker run -d -p 14331:1433 -h sql2019 --name sql2019 -e "sa_password=y0ourS@pAssword" -e "ACCEPT_EULA=Y" -e attach_dbs="" --rm mssql-2019-windows-dev-rtm-example:latest
   

Be aware Windows containers are not yet supported on production and you are on your own with SQL 2019 images at this time unless you join the EA preview.