0

I am in bit of a predicament,and would appreciate any help to move forward from my current situation

I have been given a VM running on Windows Server 2016 with Docker Enterprise installed. My management wants to have SQL Server 2019 with docker on windows containers running within this VM. This is a POC project. The VM does not have any internet connection, and therefore, I am unable to pull the base image from the docker hub. I know that there are SQL Server 2017 developer editions available. What can be done to make sure that I can go ahead with having a container in this situation? Especially when the VM does not have any internet connection?

What I tried doing was to download a SQL Server 2017 Developer edition on my local machine running Windows 10Pro, and then moved the container image onto the VM. But when I tried to load the container, I got an error message saying that "Host OS does not match". That is when I found the website below

https://docs.microsoft.com/en-us/virtualization/windowscontainers/deploy-containers/version-compatibility?tabs=windows-server-2004%2Cwindows-10-2004

Please suggest as to how I can create an image in such a situation

1

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.

7
  • Thank you so much for this! I’ll defiantly try out the steps – buddinggeek Oct 12 '20 at 12:47
  • Can you please let me know what this error signifies? " The command 'powershell -Command $ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue'; \setup\setup.exe /q /ACTION=Install /INSTANCENAME=MSSQLSERVER /FEATURES=SQLEngine /UPDATEENABLED=0 /SQLSVCACCOUNT='NT AUTHORITY\System' /SQLSYSADMINACCOUNTS='BUILTIN\ADMINISTRATORS' /TCPENABLED=1 /NPENABLED=0 /IACCEPTSQLSERVERLICENSETERMS' returned a non-zero code: 1" I see this when trying to create the build. Thanks – buddinggeek Oct 13 '20 at 0:53
  • @buddinggeek, it seems the install erred and returned a non-zero exit code. You can view the Summary.txt file by starting the intermediate build image (the image id that follows the "Step 6/12 : RUN" command) and viewing the Summary.txt file. For example: 1) docker run -it --name debug 7d246d7579bd cmd and 2) type "Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\Summary.txt". Type exit to stop and remove the container. – Dan Guzman Oct 13 '20 at 3:17
  • Thanks so much.. I was told that the VM had an internet connection and I need to use proxy settings to enable this pull.. could you pls point me to the right url on how to configure a proxy for docker pull? – buddinggeek Oct 26 '20 at 17:39
  • @buddinggeek, I have not personally used a proxy with docker but this doc page suggests you can do this on Windows using environment variables. – Dan Guzman Oct 27 '20 at 11:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.