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

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!

The post Docker images for custom SQL Server Installations appeared first on The Database Avenger.