kohera-logo-regular.svg

Make your own SQL Server Docker Container

Vector_BG.png

Make your own SQL Server Docker Container

Vector_BG.png

Recently I needed to have a SQL Server 2008 R2 instance to do some testing. I had to install it on my laptop, but I wasn’t a big fan of installing such an old version locally. A couple of weeks ago at Dataminds Connect  I saw Andrew Prusky building customized Docker containers. Because most of the public SQL Server Docker images are only for the most recent SQL Server versions, it seemed like a great opportunity to create my own SQL Server 2008 R2 Docker image.

You can use below instructions for other applications as well. In this example you will see how to build a SQL Server container (I didn’t streamline the SP on purpose) together with one of it’s prerequisites, the .Net feature.

Some preparations:

  • Docker needs to be in Windows Container mode.
  • We will be building a SQL Server 2008 R2 SP3 image, so we need source files for both SQL Server and the Service pack.
  • I decided to use an existing Windows Docker image, so that means we will be working with Windows Server (core) 2016.
  • We also need the .Net 3.5 feature in package form.
  • Powershell script start.ps1

Here we go

  • Create the following directory structure

A root folder, containing our dockerfile, start.ps1 and subfolders for SQL Server 2008 R2 , Service Pack 3  and the .Net package

In my case:
D:\temp\docker\
Dockerfile
Start.ps1
\mssql
\sql2008r2sp3
\dotnet

  • Place the extracted SQL Server source files in respectively \mssql and \sql2008r2sp3 folders.
  • Place the extracted .Net cab file microsoft-windows-netfx3-ondemand-package.cab in the dotnet folder. You can extract it from a Windows ISO or DVD. The actual file is located in the \sources\sxs folder.
  • Put the file start.ps1 in the root folder.
    Start.ps1 is a script from Microsofts Github repository. It accepts they EULA and sets the sa password. Otherwise you need too add these actions to your Docker Run command each time you create a container. At the end of the script an infinite loop is created, which keeps the container running. Without this the container would start, and stop immediately. Bit of a weird solution, but it works. I hope this will be replaced by a cleaner solution in the future.
  • We will be working from the root folder. I had some issues with full paths, using relative paths solved this issue.Also, remember that in a dockerfile we use a forward slash instead of a backslash when working in the Docker context. Example: COPY \dotnet C:/source/dotnet
  • Still in the root folder, create a new text file, name it dockerfile. These are the contents for that file:

 

# using Microsoft Windows Server Core image

FROM microsoft/windowsservercore

 
#install dotnet 3.51 (prereq for SQL Server 2008 R2)

RUN powershell -Command (mkdir C:/source/dotnet)

COPY \dotnet C:/source/dotnet

run powershell Install-WindowsFeature -name NET-Framework-Core -source C:/source/dotnet

# this is ok as well : run Dism /online /enable-feature /featurename:NetFX3 /All /Source:C:/source/dotnet

RUN powershell -Command (rm C:/source/dotnet -recurse)

 
# create temporary directory to hold SQL Server sources

RUN powershell -Command (mkdir C:/source/sqlserver2008r2)

RUN powershell -Command (mkdir C:/source/sqlserver2008r2SP3)

 
# copy sources to image

COPY \mssql C:/source/sqlserver2008r2

COPY \sql2008r2sp3 C:/source/sqlserver2008r2sp3

 
# install SQL Server 2008 R2

RUN C:/source/sqlserver2008r2\setup.exe /q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SECURITYMODE=SQL /SAPWD="S1mbaS@mbA" /SQLSVCACCOUNT="NT AUTHORITY\System" /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" /TCPENABLED=1 /IACCEPTSQLSERVERLICENSETERMS /SKIPRULES=PerfMonCounterNotCorruptedCheck /AGTSVCACCOUNT="NT AUTHORITY\System"

 
# install SP3 for SQL 2008 R2

RUN C:/source/sqlserver2008r2sp3/setup.exe /q /IAcceptSQLServerLicenseTerms /Action=Patch /Instanceid=mssqlserver

 
# remove the sources

RUN powershell -Command (rm C:/source/sqlserver2008r2 -recurse)

RUN powershell -Command (rm C:/source/sqlserver2008r2SP3 -recurse)

 
# make sure service is set to automatic

RUN powershell -Command (set-service MSSQLSERVER -StartupType Automatic)

 
# switch shell to powershell

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

COPY \start.ps1 /

WORKDIR /

ENV SA_PASSWORD "S1mbaS@mbA"

ENV ACCEPT_EULA "Y"

 
# run start.ps1

CMD .\start -sa_password $env:SA_PASSWORD -ACCEPT_EULA $env:ACCEPT_EULA -Verbose

 

  • Open a Powershell session, go the root folder and build the image.

! Don’t forget the dot at the end of the command.

Cd d:\temp\docker
docker build -t sql2008r2sp3df .

 

Download the screen output here. 

 

  • Build the container.

Port 14567 will be mapped to the SQL Server port 1433, and a container named dckrcntsql2008r2 will be created.

docker run -d -i -p 14567:1433 –name dckrcntsql2008r2 sql2008r2sp3df

PS D:\temp\docker> docker run -d -i -p 14567:1433 –name dckrcntsql2008r2 sql2008r2sp3df
7f4a6ac5eb069370c7fdf83d37c67f8c84265ef5f2c78852765769ef48368fe0

 

  • Start the container

docker start dckrcntsql2008r2

PS D:\temp\docker> docker start dckrcntsql2008r2
dckrcntsql2008r2

 

-In SSMS, connect to localhost,14576

  • And that’s it. Happy Dockering.
2319-blog-database-specific-security-featured-image
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
kohera-2312-blog-sql-server-level-security-featured-image
SQL Server security on server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
blog-security_1
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...
DSC_1388
Aggregaties
Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
blog-2303
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...