Create SQL Server on Windows Docker Container using Dockerfile - Part 6

By:   |   Updated: 2019-04-11   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | More > 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.

  1. Start from the base Windows Server Core container image
  2. Create temporary directory to store the SQL Server 2016 Developer Edition installation files
  3. Copy the SQL Server 2016 Developer Edition installation files from the host to the container image
  4. Install SQL Server 2016 via command line
  5. Set SQL Server service to automatic
  6. Remove SQL Server 2016 Developer Edition installation media folder
  7. Switch shell to PowerShell in preparation for running script Start.ps1
  8. Copy Start.ps1 to image on root directory
  9. Set current working directory for script execution
  10. 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
DockerBuild directory

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 . 
Building Your Custom SQL Server on Windows Container Image using Dockerfile
Building Your Custom SQL Server on Windows Container Image using Dockerfile

Use the docker images command to review the custom image created.

docker images
docker images command to review the custom image created

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 

SQL Server Version and Edition
Next Steps


Last Updated: 2019-04-11


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools