By: Edwin Sarmiento | Updated: 2019-04-11 | Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Containers
Problem
The interest in using Containers to run SQL Server has been growing and as we continue this series, we will look at how to create custom SQL Server on Windows containers using a Dockerfile.
Solution
To continue this series on Introduction to Containers for the SQL Server DBA, you will look at creating your own SQL Server on Windows containers. In Part 1, you have learned how to install Docker on Windows Server 2016. In Part 2, you installed Docker on a Linux CentOS server. Part 3 walked you thru exploring the different docker commands that will help you get started on working with containers. Part 4 introduced you to the container naming conventions and internals of container images, configuration of persistent storage for your SQL Server containers and running multiple SQL Server containers on a single host machine. You created a custom SQL Server on Linux container image using the Dockerfile in Part 5. This time you will look at creating custom SQL Server on Windows containers using the Dockerfile.
Installing SQL Server on Windows Server Core using the Command-Line
If you can automate installation of SQL Server using either an unattended answer file or simply passing parameters to the setup.exe in the command-line, you are half-way there. However, since Windows Containers only support Windows Server Core and Nano Server - SQL Server is not supported on Nano Server – the process will be similar to installing SQL Server on Windows Server Core. This means you can build your own custom SQL Server 2008 R2 on Windows Server 2012 Core container image if your line-of-business applications require it. This is not recommended since both products are nearing their end of support lifecycles. This tip will walk you thru building a SQL Server 2016 Developer Edition on Windows Server 2016 Core container image.
Here's a high-level overview of the process involved in creating a custom SQL Server 2016 Developer Edition on Windows Server 2016 Core container image. These steps will form the instructions in the Dockerfile. Also, download a copy of the SQL Server 2016 Developer Edition installation media using this utility and extract the files to a folder.
- Start from the base Windows Server Core container image
- Create temporary directory to store the SQL Server 2016 Developer Edition installation files
- Copy the SQL Server 2016 Developer Edition installation files from the host to the container image
- Install SQL Server 2016 via command line
- Set SQL Server service to automatic
- Remove SQL Server 2016 Developer Edition installation media folder
- Switch shell to PowerShell in preparation for running script Start.ps1
- Copy Start.ps1 to image on root directory
- Set current working directory for script execution
- Run PowerShell script Start.ps1
Also, create a folder structure on the root drive of the host machine that contains the following:
- dockerBuild – the folder that will contain everything; the path where the Docker client will reference when building the image
- dockerBuild\SQL2016Dev_SP2 – the folder that will contain the SQL Server 2016 Developer Edition installation files
-
Start.ps1 – PowerShell script from Microsoft that will
- process user's response to the End User License Agreement (EULA)
- start the SQL Server service
- sets the sa password
- attach user database samples (OPTIONAL)
- run in an infinite loop to prevent the container from exiting after the last instruction completes
Microsoft uses the Start.ps1 PowerShell script to help developers get started working with SQL Server on Windows containers. No sample user databases are included here but you can do so if necessary. The only parameter required for the script to execute is the -ACCEPT_EULA with a value of Y. The -Verbose parameter used in the script is simply for tracing and troubleshooting in case an issue occurs when running the container.
Building the Dockerfile
Your final Dockerfile should look something like this. Comments are included to represent steps in the build process. The LABEL instructions were also eliminated for brevity.
#Step 1: Start from base image mcr.microsoft.com/windows/servercore FROM mcr.microsoft.com/windows/servercore #Step 2: Create temporary directory to hold SQL Server 2016 installation files RUN powershell -Command (mkdir C:\SQL2016Dev_SP2) #Step 3: Copy SQL Server 2016 installation files from the host to the container image COPY \SQL2016Dev_SP2 C:/SQL2016Dev_SP2 #Step 4: Install SQL Server 2016 via command line RUN C:/SQL2016Dev_SP2/SETUP.exe /Q /ACTION=INSTALL /FEATURES=SQLENGINE /INSTANCENAME=MSSQLSERVER /SECURITYMODE=SQL /SAPWD="y0urSecUr3PAssw0rd" /SQLSVCACCOUNT="NT AUTHORITY\System" /AGTSVCACCOUNT="NT AUTHORITY\System" /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" /IACCEPTSQLSERVERLICENSETERMS=1 /TCPENABLED=1 /UPDATEENABLED=False #Step 5: Set SQL Server service to automatic RUN powershell -Command (Set-Service MSSQLSERVER -StartupType Automatic) #Step 6: Remove SQL Server installation media folder RUN powershell -Command (Remove-Item -Path C:/SQL2016Dev_SP2 -Recurse -Force) #Step 7: Switch shell to PowerShell in preparation for running script Start.ps1 SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';"] #Step 8: Copy Start.ps1 to image on root directory COPY \start.ps1 / #Step 9: Set current working directory for script execution WORKDIR / #Step 10: Run PowerShell script Start.ps1, passing the -ACCEPT_EULA parameter with a value of Y CMD .\start.ps1 -ACCEPT_EULA "Y" -Verbose
Notice the additional instructions in the Dockerfile.
COPY Instruction
The COPY instruction copies new files or directories from the source on the host and adds them to the filesystem of the container at the path. Step #3 copies the SQL2016Dev_SP2 folder from the source on the host (C:\dockerBuild) and adds them to the C:/SQL2016Dev_SP2 folder on the image. On Windows containers, the destination format must use forward slashes. Otherwise, it will not work. Recall that containers were originally designed with Linux in mind. So, things such as folders and path references from Linux must apply.
COPY \SQL2016Dev_SP2 C:/SQL2016Dev_SP2
SHELL Instruction
The SHELL instruction allows the default shell used for the shell form of commands to be overridden. This instruction is more common when using Windows containers since Windows has both cmdand powershell shell environments. Note that you can run cmd commands from within a powershell shell environment. However, you need to be explicit which one you would like to use in a specific instruction.
SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';"]
The $ErrorActionPreference variable determines how PowerShell responds to a non-terminating error (an error that does not stop a cmdlet processing) at the command line or in a script, cmdlet, or provider, such as the errors generated by the Write-Error cmdlet. The $ProgressPreference variable determines how PowerShell responds to progress updates generated by a script, cmdlet or provider, such as the progress bars generated by the Write-Progress cmdlet.
The SHELL instruction above simply means that when a PowerShell script runs at a later instruction, it executes the command but does not display the progress bar. If it encounters an error, it will display the error message and stops executing.
WORKDIR Instruction
The WORKDIR instruction sets the working directory for any instructions that follow it in the Dockerfile. In this example, since the Start.ps1 PowerShell script was copied to the root directory of the container (Step #8), execution of the script should be relative to its location.
WORKDIR /
Building Your Custom SQL Server on Windows Container Image using Dockerfile
Save the Dockerfile inside the C:\dockerBuild folder.
Use the docker build command to build your custom SQL Server 2016 Developer Edition on Windows Server 2016 Core container image from the Dockerfile. Be sure to run the command from within the C:\dockerBuild folder.
docker build -t sql2016sp2dev:1.0 .
Use the docker images command to review the custom image created.
docker images
Test the custom image by running the docker run command below.
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=y0urSecUr3PAssw0rd" -p 1430:1433 --name sql-wincon30 -d -h winsql30 sql2016sp2dev:1.0
Next Steps
- Review the following documentation on Docker
- Review the previous tips on running SQL Server on Docker
About the author
Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-04-11
