{"id":368,"date":"2020-05-14T19:31:54","date_gmt":"2020-05-14T19:31:54","guid":{"rendered":"https:\/\/www.dabbler.dk\/?p=368"},"modified":"2020-05-14T19:40:42","modified_gmt":"2020-05-14T19:40:42","slug":"running-microsoft-sql-server-2019-developer-edition-in-a-docker-windows-container","status":"publish","type":"post","link":"https:\/\/www.dabbler.dk\/index.php\/2020\/05\/14\/running-microsoft-sql-server-2019-developer-edition-in-a-docker-windows-container\/","title":{"rendered":"Running Microsoft SQL Server 2019 Developer Edition in a Docker Windows Container"},"content":{"rendered":"\n

Note: This work is based on the “Official Microsoft repository for SQL Server in Docker resources” found here: https:\/\/github.com\/microsoft\/mssql-docker\/tree\/master\/windows\/mssql-server-windows-developer<\/a><\/p>\n\n\n\n

Unfortunately this has not been updated to run Windows ServerCore 1890 and Microsoft SQL Server 2019 – so this is what this blog post is about…<\/p>\n\n\n\n

Note 2: We are only taking about minor changes here – so I’m not in ANY<\/strong> way trying to claim credits for these scripts – all credits goes to the original author for these scripts – see the above mentioned official link!<\/strong><\/p>\n\n\n\n

Note 3: If you try to use the official Microsoft SQL Server Developer Edition image and you get the error “The container operating system does not match the host operating system.”, it is probably because that image is not Windows Server 1809 compatible. This will also solve that as this image is based on the “mcr.microsoft.com\/windows\/servercore:1809” Windows Server image.<\/p>\n\n\n\n

Enough notes for now :-). Let’s roll with this – first you need to create these two files:<\/p>\n\n\n\n

“dockerfile”:<\/p>\n\n\n\n

# 2020-05-11 GL\n#\nFROM mcr.microsoft.com\/windows\/servercore:1809\n\nLABEL maintainer \"Gert Lynge\"\n\n# Download Links:\nENV exe \"https:\/\/download.microsoft.com\/download\/7\/c\/1\/7c14e92e-bdcb-4f89-b7cf-93543e7112d1\/SQLServer2019-DEV-x64-ENU.exe\"\nENV box \"https:\/\/download.microsoft.com\/download\/7\/c\/1\/7c14e92e-bdcb-4f89-b7cf-93543e7112d1\/SQLServer2019-DEV-x64-ENU.box\"\n\nENV sa_password=\"_\" \\\n    attach_dbs=\"[]\" \\\n    ACCEPT_EULA=\"_\" \\\n    sa_password_path=\"C:\\ProgramData\\Docker\\secrets\\sa-password\"\n\nSHELL [\"powershell\", \"-Command\", \"$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';\"]\n\n# make install files accessible\nCOPY start.ps1 \/\nWORKDIR \/\n\nRUN Invoke-WebRequest -Uri $env:box -OutFile SQL.box ; \\\n        Invoke-WebRequest -Uri $env:exe -OutFile SQL.exe ; \\\n        Start-Process -Wait -FilePath .\\SQL.exe -ArgumentList \/qs, \/x:setup ; \\\n        .\\setup\\setup.exe \/q \/ACTION=Install \/INSTANCENAME=MSSQLSERVER \/FEATURES=SQLEngine \/UPDATEENABLED=1 \/SQLSVCACCOUNT='NT AUTHORITY\\NETWORK SERVICE' \/SQLSYSADMINACCOUNTS='BUILTIN\\ADMINISTRATORS' \/TCPENABLED=1 \/NPENABLED=0 \/IACCEPTSQLSERVERLICENSETERMS \/SQLMAXDOP=1 \/SQLBACKUPDIR='C:\\Server\\MSSQL\\Backup' \/SQLUSERDBDIR='C:\\Server\\MSSQL\\DB' \/SQLUSERDBLOGDIR='C:\\Server\\MSSQL\\DB' ; \\\n        Remove-Item -Recurse -Force SQL.exe, SQL.box, setup\n\nRUN stop-service MSSQLSERVER ; \\\n        set-itemproperty -path 'HKLM:\\software\\microsoft\\microsoft sql server\\mssql15.MSSQLSERVER\\mssqlserver\\supersocketnetlib\\tcp\\ipall' -name tcpdynamicports -value '' ; \\\n        set-itemproperty -path 'HKLM:\\software\\microsoft\\microsoft sql server\\mssql15.MSSQLSERVER\\mssqlserver\\supersocketnetlib\\tcp\\ipall' -name tcpport -value 1433 ; \\\n        set-itemproperty -path 'HKLM:\\software\\microsoft\\microsoft sql server\\mssql15.MSSQLSERVER\\mssqlserver\\' -name LoginMode -value 2 ;\n\nHEALTHCHECK CMD [ \"sqlcmd\", \"-Q\", \"select 1\" ]\n\nCMD .\\start -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs \\\"$env:attach_dbs\\\" -Verbose<\/pre>\n\n\n\n
…and “start.ps1”<\/p>\n\n\n\n
# 2020-05-11 GL\n\n# The script sets the sa password and start the SQL Service\n# Also it attaches additional database from the disk\n# The format for attach_dbs\n\nparam(\n[Parameter(Mandatory=$false)]\n[string]$sa_password,\n\n[Parameter(Mandatory=$false)]\n[string]$ACCEPT_EULA,\n\n[Parameter(Mandatory=$false)]\n[string]$attach_dbs\n)\n\nif($ACCEPT_EULA -ne \"Y\" -And $ACCEPT_EULA -ne \"y\")\n{\n\tWrite-Verbose \"ERROR: You must accept the End User License Agreement before this container can start.\"\n\tWrite-Verbose \"Set the environment variable ACCEPT_EULA to 'Y' if you accept the agreement.\"\n\n    exit 1\n}\n\n# start the service\nWrite-Verbose \"Starting SQL Server\"\nstart-service MSSQLSERVER\n\nif($sa_password -eq \"_\") {\n    if (Test-Path $env:sa_password_path) {\n        $sa_password = Get-Content -Raw $secretPath\n    }\n    else {\n        Write-Verbose \"WARN: Using default SA password, secret file not found at: $secretPath\"\n    }\n}\n\nif($sa_password -ne \"_\")\n{\n    Write-Verbose \"Changing SA login credentials\"\n    $sqlcmd = \"ALTER LOGIN sa with password=\" +\"'\" + $sa_password + \"'\" + \";ALTER LOGIN sa ENABLE;\"\n    & sqlcmd -Q $sqlcmd\n}\n\n$attach_dbs_cleaned = $attach_dbs.TrimStart('\\\\').TrimEnd('\\\\')\n\n$dbs = $attach_dbs_cleaned | ConvertFrom-Json\n\nif ($null -ne $dbs -And $dbs.Length -gt 0)\n{\n    Write-Verbose \"Attaching $($dbs.Length) database(s)\"\n\t    \n    Foreach($db in $dbs) \n    {            \n        $files = @();\n        Foreach($file in $db.dbFiles)\n        {\n            $files += \"(FILENAME = N'$($file)')\";           \n        }\n\n        $files = $files -join \",\"\n        $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;\"\n\n        Write-Verbose \"Invoke-Sqlcmd -Query $($sqlcmd)\"\n        & sqlcmd -Q $sqlcmd\n\t}\n}\n\nWrite-Verbose \"Started SQL Server.\"\n\n$lastCheck = (Get-Date).AddSeconds(-2) \nwhile ($true) \n{ \n    Get-EventLog -LogName Application -Source \"MSSQL*\" -After $lastCheck | Select-Object TimeGenerated, EntryType, Message\t \n    $lastCheck = Get-Date \n    Start-Sleep -Seconds 2 \n}<\/pre>\n\n\n\n
…and then you have to run these docker commands (replace “<path to a directory with the above mentioned two files><\/em>” with the path to the above mentioned two files: dockerfile and start.ps1 – and “<password><\/em>” with the sa-password you want):<\/p>\n\n\n\n
docker build --memory 4g --tag dabbler\/mssql-server-windows-developer:winsrv1809-sql2019 \"<path to a directory with the above mentioned two files><\/em>\" \n\ndocker run --name SQLServer2019 -e ACCEPT_EULA=Y -e sa_password=<password><\/em> -p 1433:1433 -d dabbler\/mssql-server-windows-developer:winsrv1809-sql2019<\/pre>\n\n\n\n
If you are lucky,  you will now have a running SQL Server 2019 Developer Edition and you can connect directly to it with Microsoft SQL Server Management Studio directly on the host running Docker (use “SQL Server Authentication” with host: “localhost”, login: “sa” and the password stated above).<\/p>\n","protected":false},"excerpt":{"rendered":"
Note: This work is based on the “Official Microsoft repository for SQL Server in Docker resources” found here: https:\/\/github.com\/microsoft\/mssql-docker\/tree\/master\/windows\/mssql-server-windows-developer Unfortunately this has not been updated to run Windows ServerCore 1890 and Microsoft SQL Server 2019 – so this is what this blog post is about… Note 2: We are only taking about minor changes here … <\/p>\n
Continue reading “Running Microsoft SQL Server 2019 Developer Edition in a Docker Windows Container”<\/span><\/a><\/p>\n","protected":false},"author":3,"featured_media":373,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[46,10,5],"tags":[48,24,12,47],"class_list":["post-368","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-docker","category-microsoft-sql","category-software","tag-docker","tag-microsoft","tag-powershell","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.dabbler.dk\/index.php\/wp-json\/wp\/v2\/posts\/368","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dabbler.dk\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dabbler.dk\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dabbler.dk\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dabbler.dk\/index.php\/wp-json\/wp\/v2\/comments?post=368"}],"version-history":[{"count":4,"href":"https:\/\/www.dabbler.dk\/index.php\/wp-json\/wp\/v2\/posts\/368\/revisions"}],"predecessor-version":[{"id":375,"href":"https:\/\/www.dabbler.dk\/index.php\/wp-json\/wp\/v2\/posts\/368\/revisions\/375"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dabbler.dk\/index.php\/wp-json\/wp\/v2\/media\/373"}],"wp:attachment":[{"href":"https:\/\/www.dabbler.dk\/index.php\/wp-json\/wp\/v2\/media?parent=368"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dabbler.dk\/index.php\/wp-json\/wp\/v2\/categories?post=368"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dabbler.dk\/index.php\/wp-json\/wp\/v2\/tags?post=368"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}