SQL Server on docker

Docker images for custom SQL Server Installations

I no longer install SQL Server on my workstation, I use Docker to spin up instances as and when I need them. This means I don’t have to go through the installation, my machine is cleaner and performance is not reduced by having multiple versions of SQL Server installed.

The SQL Server images that Microsoft publish are very basic installs of the database engine. I wanted to take a look at the new Python functionality (Advanced Analytics) in SQL Server 2017. So I pulled the latest SQL Server image, spun up a container from the image, and connected to it with SSMS (see how here). Lastly, I tried to enable the Advanced Analytics feature.

EXEC sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE

I got the following error:

Msg 39020, Level 16, State 1, Procedure sp_configure, Line 166 [Batch Start Line 0]
Feature ‘Advanced Analytics Extensions’ is not installed. Please consult Books Online for more information on this feature.

The basic install in this image does not include the Advanced Analytics feature.

I could have connected to the container and ran the installer to add the feature, but I would have to do that every time.

I could have edited the microsoft/mssql-server-windows docker file to include the Advanced Analytics feature. This would be better because every container I spun up would have the setting, but when a new version of the image became available I would have to go and edit it’s docker file.

It’s far better to create a new docker file that inherits from the latest microsoft/mssql-server-windows docker file.

FROM microsoft/mssql-server-windows

LABEL maintainer "James Anderson"

SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';"]

WORKDIR /

RUN Invoke-WebRequest -Uri $env:box -OutFile SQL.box ; \
        Invoke-WebRequest -Uri $env:exe -OutFile SQL.exe ; \
        Start-Process -Wait -FilePath .\SQL.exe -ArgumentList /qs, /x:setup ; \
        .\setup\setup.exe /q /ACTION=Install /INSTANCENAME=MSSQLSERVER /FEATURES=AdvancedAnalytics /UPDATEENABLED=0 /SQLSVCACCOUNT='NT AUTHORITY\System' /SQLSYSADMINACCOUNTS='BUILTIN\ADMINISTRATORS' /TCPENABLED=1 /NPENABLED=0 /IACCEPTSQLSERVERLICENSETERMS ; \
        Remove-Item -Recurse -Force SQL.exe, SQL.box, setup

The FROM statement declares that we want to lay some instructions on top of the microsoft/mssql-server-windows image. The beauty of this approach is that when I pull down a new version of the microsoft/mssql-server-windows image, my image will be updated too. The microsoft/mssql-server-windows Dockerfile does the same thing with the microsoft/windowsservercore image.

The rest of the Dockerfile sets some meta data, downloads the installer and adds the Advanced Analytics feature.

SSIS, SSAS, SSRS or any other SQL Server feature could be added to a containerised SQL Server deployment in the same way.

Docker Build

We then execute the Docker build command to create a new image.

docker build C:\projects\mssql-server-analytics -t mssql-server-analytics

docker build sql server advanced analytics

The output above shows that I called the Dockerfile in the C:\Projects\mssql-server-analytics directory to create an image named mssqlserver-server-analytics. To execute each command in the Dockerfile, a container is spun up, the command is executed and an intermediate image is created from it. These layers are cached and can be shared between containers of similar images.

I was then able to spin up a container from my new mssql-server-analytics image, connect to it and enable the Advance Analytics feature.

Time to play with Python!

About the author

James Anderson

I'm a freelance SQL Server DBA in Southampton, UK. Performance tuning code and improving development processes are what I love to do for work. Outside of work, I live to travel to as many places as possible with my wife Sarah.

8 thoughts on “Docker images for custom SQL Server Installations”

  1. Being lazy here – have you actually tried running SSRS in a container? Was there any hurdles?

    Thanks
    Christian

  2. OK. If you get some time to post about your experiences with SSRS and containers, that would be great.

Leave a Reply

Your email address will not be published. Required fields are marked *

Sign Up

James Anderson SQL Server DBA

Hi, I'm James Anderson. When I'm not blogging about SQL Server, I do this.

The SQL People

DBA Stackexchange Profile