Windocks gives you SQL Server containers you always wanted
I previously blogged about Rapid test instances with Docker and Azure, but I recently came across another product I started using for SQLWATCH testing, Windocks.
What is Windocks
Windocks, as the name may suggest, is a containerisation solution for SQL Server on Windows. It does way more than just containerising SQL Server, including Database Cloning and Orchestration. I am currently only using it to help me test SQLWATCH on a minimum of 100 concurrent instances.
You can get the Community Edition for free.
Getting started
First, install the application. It’s just a standard MSI/EXE, so I will not bore you with how to run the installer. You can also read the details on their website.
Once you have installed it, navigate to the management portal. In the images section, you will see a list of all available images you currently have (same as in images in Docker). One of the pre-requisites for Windocks installation is that you need to have SQL Server installed on the host. Windocks installer will then pick it up and turn it into images as with my SQL Server 2019 example below:
On the other hand, the containers section shows currently deployed and running images (they are called containers). I now have two SQL Server 2019 containers running:
Creating containers
To create a new container from an existing image, go to the Images tab, select the image you want, give it a name, such as HelloWorld and click “Deliver”:
The “Request being processed” banner keeps us assured that things are happening behind the scenes:
And viola:
And when we go to the Containers and clones tab, we can see our HelloWorld Container, each having its port. The HelloWorld Container is running on port 10004:
Connecting to the new Container in SSMS is very similar to connecting to a SQL Server on Docker. Give the IP of the host and the port:
How does it work
I figured I could also connect to the Container in a similar way I would connect to a SQL Server named instances. This would suggest that Windocks is simply creating named instances behind the scenes:
But they are not typical instances recognised by the SQL Server Configuration Manager:
This is because Windocks can run SQL Server containers in two modes:
- As a User Prorcess – where it only runs the database engine
- As a Windows Service – the same way as normal SQL Instance would run and it supports all the SQL Server features such as Distributed Transactions, Linked Serves, Replication etc
Adding different version of SQL Server
Although Windocks supports Docker Compose YAML definitions, I am yet to fully understand how to use it to add different versions of SQL Server. However, what works well is installing another SQL Server on the host as you would typically do, and Windocks then picks it up and makes it into images that can be then turned into containers.
Why Windocks
The biggest advantage for me is that it runs on Windows. I can execute end to end SQLWATCH tests, including the WMI interface in Windows OS. This is not something I could do with Docker on Linux.
Another advantage is that because it runs a single host OS, and that OS is Windows, I can add it to a Windows domain and have all SQL Server instances authenticated via AD. Sure, you can argue I can connect SQL Server on Docker to the Windows Domain too. Yes, I can, but there are a couple of important factors to be aware of:
- Containers are disposable by design.
- Active Directory Objects are not disposable by design.
I am creating 100 Docker containers, I will be adding all 100 of them as AD Objects. When I dispose of those containers in Docker, the Active Directory objects will stay in the AD forever. I would then have to manually (or via script) force-remove them from the AD.
With Windocks, the host stays, and it’s only the “virtual” SQL Instances that are being added and removed, so that’s ideal for Active Directory.
There are many more features that I am not using nor need, but which you may find useful. Go check it out.
Re Windocks interaction with AD: I’m curious how the SPN’s work for Windows-auth in SQL. Do SPN’s get auto-created and auto-deleted in AD?
Hi Tim, in my test environment I am happy with NTLM authentication and haven’t bothered with automatic SPN registration but I will investigate this for you. I am going to guess that the behaviour will be similar to any other SQL Instance and if the SPN is automatically registered, it will need to be manually removed as Windocks does not talk to the AD directly, it just automates the instance creation.
You are correct Marcin. Windocks SQL Server containers act the same as a conventional instance, and work with AD Authentication. SPN’s are created automatically and cleaned up as well.