Converting a SQL Server Docker image to a WSL2 Distribution

Windows Subsystem for Linux is probably my favourite feature of Windows 10. It gives us the ability to run full blown linux distributions on our Windows 10 desktop. This allows us to utilise the cool features of linux (grep ftw) on Windows 10.

I’ve been playing around a bit with WSL2 and noticed that you can import TAR files into it to create your own custom distributions.

This means that we can export docker containers and run them as WSL distros!

So, let’s build a custom SQL Server 2019 docker image, run a container, and then import that container into WSL2…so that we have a custom distro running SQL Server 2019.

Note…this is kinda cool as WSL2 is not (currently) a supported platform to install SQL on Linux: –

Anyway, let’s run through the process.

Here’s the dockerfile for the custom SQL Docker image: –

FROM ubuntu:20.04

RUN apt-get update && apt-get install -y wget software-properties-common apt-transport-https

RUN wget -qO- https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

RUN add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"

RUN apt-get update && apt-get install -y mssql-server

CMD /opt/mssql/bin/sqlservr

Pretty standard, following the SQL on Linux install instructions here.

OK, let’s build the image: –

docker build -t sqlserver2019 .

Now run a container from the new custom image: –

docker container run -d `
--publish 1433:1433 `
--env ACCEPT_EULA=Y `
--env MSSQL_SA_PASSWORD=Testing1122 `
--name sqlcontainer1 `
sqlserver2019

Confirm that the container is running: –

docker container ls

OK, now we’re going to rename the instance in the container for no other reason that we want the instance name not to be the container ID when we run it as a WSL2 Distro: –

mssql-cli -S localhost -U sa -P Testing1122 -Q "SELECT @@SERVERNAME AS [InstanceName];"

mssql-cli -S localhost -U sa -P Testing1122 -Q "sp_dropserver [8622203f7381];"

mssql-cli -S localhost -U sa -P Testing1122 -Q "sp_addserver [sqlserver2019], local;"

Stop, then start the container and confirm the rename has been successful: –

docker stop sqlcontainer1

docker start sqlcontainer1

mssql-cli -S localhost -U sa -P Testing1122 -Q "SELECT @@SERVERNAME AS [InstanceName];"

Cool! Now, stop the container again: –

docker stop sqlcontainer1

Right, now we can export the container to a tar file: –

docker export sqlcontainer1 -o C:\temp\sqlcontainer1.tar

Once the export is complete we can then import it into WSL2: –

wsl --import sqlserver2019 C:\wsl-distros\sqlserver2019 C:\temp\sqlcontainer1.tar --version 2

Here’s what the code above is doing…

  • sqlserver2019 – the name of the new WSL distro
  • C:\wsl-distros\sqlserver2019 – The path where the new distro will be stored on disk
  • C:\temp\sqlcontainer1.tar – The location of the tar file we are importing
  • version 2 – WSL version of the new distro

Confirm that the new distro is in WSL2: –

wsl --list --verbose

Great stuff, the distro has been imported. Now we need to start it by running SQL. We’re going to use the setsid command to start up SQL here, as if we didn’t…the SQL log would write to our current session and we’d have to open up another powershell window: –

wsl -d sqlserver2019 bash -c "setsid /opt/mssql/bin/sqlservr"

Verify the distro is running: –

wsl --list --verbose

There’s our distro running! And we also execute ps aux against the distro to see if SQL is running: –

wsl -d sqlserver2019 ps aux

Cool! So now we can connect to SQL running in the distro with (using 127.0.0.1 instead of localhost): –

mssql-cli -S 127.0.0.1 -U sa -P Testing1122 -Q "SELECT @@SERVERNAME"

Excellent stuff! We have a new WSL2 distro running the latest version of SQL Server 2019!

So we can do our work and when we’re finished we can close down the distro with: –

wsl -t sqlserver2019

And if we want to get rid of the new distro completely: –

wsl --unregister sqlserver2019

Pretty cool! Ok, I admit…most people would prefer to run SQL in a container for this kind of stuff BUT it does give us another option…and having more options is always a good thing to have…right?

Right?

Thanks for reading!

Migrating SQL Server container images to the Github Container Registry

A couple of months ago Docker announced that they would be implementing a 6 month retention policy for unused images in the Docker Hub.

This was due to kick in on the 1st of November but has now been pushed back until mid 2021.

I’ve had multiple Windows SQL Server container images up on the Docker Hub for years now. It’s been a great platform and I’m very thankful to them for hosting my images.

That being said, I want to make sure that the images that I’ve built are always going to be available for the community so I have pushed my SQL Server images to the Github Container Registry.

In the Docker Hub I have the following public SQL Server images: –

dbafromthecold/sqlserver2012express:rtm
dbafromthecold/sqlserver2012dev:sp4
dbafromthecold/sqlserver2014express:rtm
dbafromthecold/sqlserver2014dev:sp2
dbafromthecold/sqlserver2016dev:sp2

These images won’t be removed (by myself) and I will update this post in the event of them being removed.

But they are now available on the Github Container Registry: –

ghcr.io/dbafromthecold/sqlserver2012:express
ghcr.io/dbafromthecold/sqlserver2012:dev
ghcr.io/dbafromthecold/sqlserver2014:express
ghcr.io/dbafromthecold/sqlserver2014:dev
ghcr.io/dbafromthecold/sqlserver2016:dev

Bit of a disclaimer with these images…they’re LARGE! The 2012 dev image is ~20GB which is gigantic for a container image. So if you going to use them (for dev and test only please 🙂 ) you’ll need to pre-pull them before running any containers.

Thanks for reading!

The SQL Server and Containers Guide

I’ve been blogging about running SQL Server in Docker containers for a while now and, to be honest, my blogs are scattered over a few years and some need to be archived as they’re out of date.

So what I wanted to do was have one place where I could collate all the blogs I’ve written about running SQL Server in a container. This would make it easy for people to access information and make it easy for me to keep it all up-to-date as well.

So introducing, The SQL Server and Containers Guide!

This wiki contains everything needed to get up and running with SQL Server in Docker containers, and it delves a little bit deeper as well.

Topics covered are: –

  • Quick Start – Get up and running with SQL in a container
  • Running SQL in container – Explore the ins and outs of running SQL in a container
  • Persisting data – Have a look at the options for persisting data in a container
  • Docker images – Build your own custom SQL images
  • Container networking – Explore the default docker network and create a custom network
  • Docker Compose – Use Docker Compose to spin SQL up in a container
  • Docker commands – Details of various Docker commands

Each post has code and screenshots to walk through each topic…and the Github repository contains all the code from the posts so that it can be pulled down locally.

I’ll be adding to this some more over the next few weeks…Rob Sewell (b|t) awesomely contributed code to generate Azure Data Studio Notebooks from the wiki which I may (ok, definitely) have broken. But once I’ve (or more likely Rob) has fixed that…they’ll be added into the repository.

If there’s anything missing please feel free to contact me via dbafromthecold@gmail.com or submit a PR!

Thanks for reading!

A kubectl plugin to decode secrets created by Helm

Last week I wrote a blog post about Decoding Helm Secrets.

The post goes through deploying a Helm Chart to Kubernetes and then running the following to decode the secrets that Helm creates in order for it to be able to rollback a release: –

kubectl get secret sh.helm.release.v1.testchart.v1 -o jsonpath="{ .data.release }" | base64 -d | base64 -d | gunzip -c | jq '.chart.templates[].data' | tr -d '"' | base64 -d

But that’s a bit long winded eh? I don’t really fancy typing that every time I want to have a look at those secrets. So I’ve created a kubectl plugin that’ll do it for us!

Here’s the code: –

#!/bin/bash

# get helm secrets from Kubernetes cluster
SECRET=$(kubectl get secret $1 -o jsonpath='{ .data.release }' ) 

# decode the secrets
DECODED_SECRET=$(echo $SECRET | base64 -d | base64 -d | gunzip -c )

# parse the decoded secrets, pulling out the templates and removing whitespace
DATA=$(echo $DECODED_SECRET | jq '.chart.templates[]' | tr -d '[:space:]' )

# assign each entry in templates to an array
ARRAY=($(echo $DATA | tr '} {' '\n'))

# loop through each entry in the array
for i in "${ARRAY[@]}"
do
        # splitting name and data into separate items in another array
        ITEMS=($(echo $i | tr ',' '\n'))

        # parsing the name field
        echo "${ITEMS[0]}" | sed -e 's/name/""/g; s/templates/""/g' | tr -d '/:"'

        # decoding and parsing the data field
        echo "${ITEMS[1]}" | sed -e 's/data/""/g' | tr -d '":' | base64 -d

        # adding a blank line at the end
        echo ''
done  

It’s up in Github as a Gist but to use the plugin, pull it down with curl and drop it into a file in your PATH environment variable. Here I’m dropping it into /usr/local/bin: –

curl https://gist.githubusercontent.com/dbafromthecold/fdd1bd8b7e921075d3d37fcb8eb9a025/raw/afa873b0ef343859ed4119eeb9f41bf733e8cea2/DecodeHelmSecrets.sh > /usr/local/bin/kubectl-decodehelm

Make it executable: –

chmod +x /usr/local/bin/kubectl-decodehelm

Now confirm that the plugin is there: –

sudo kubectl plugin list


N.B. – I’m running this with sudo as I’m in WSL which will error out when checking my Windows paths if I don’t use sudo

Let’s test it out! I’m going to deploy the mysql chart from the stable repository: –

helm install mysql stable/mysql

Once deployed, we’ll have one secret created by Helm: –

kubectl get secrets

Now let’s use the plugin to decode the information in that secret: –

kubectl decodehelm sh.helm.release.v1.mysql.v1

And there’s the decoded secret! Well, just a sample of it in that screenshot as the mysql Chart contains a few yaml files.

The format of the output is: –

  • Filename (in the above example… NOTES.txt
  • Decoded file (so we’re seeing the text in the notes file for the mysql Chart)

Thanks for reading!

Decoding Helm Secrets

Helm is a great tool for deploying applications to Kubernetes. We can bundle up all our yaml files for deployments, services etc. and deploy them to a cluster with one easy command.

But another really cool feature of Helm, the ability to easily upgrade and roll back a release (the term for an instance of a Helm chart running in a cluster).

Now, you can do this with kubectl. If I upgrade a deployment with kubectl apply I can then use kubectl rollout undo to roll back that upgrade. That’s great! And it’s one of the best features of Kubernetes.

What happens when you upgrade a deployment is that a new replicaset is created for that deployment, which is running the upgraded application in a new set of pods.

If we rollback with kubectl rollout undo the pods in the newest replicaset are deleted, and pods in an older replicaset are spun back up, rolling back the upgrade.

But there’s a potential problem here. What happens if that old replicaset is deleted?

If that happens, we wouldn’t be able to rollback the upgrade. Well we wouldn’t be able to roll it back with kubectl rollout undo, but what happens if we’re using Helm?

Let’s run through a demo and have a look.

So I’m on Windows 10, running in WSL 2, my distribution is Ubuntu: –

ubuntu

N.B. – The below code will work in a powershell session on Windows, apart from a couple of commands where I’m using Linux specific command line tools, hence why I’m in my WSL 2 distribution. (No worries if you’re on a Mac or native Linux distro)

Anyway I’m going to navigate to Helm directory on my local machine, where I am going to create a test chart: –

cd /mnt/c/Helm

Create a chart called testchart: –

helm create testchart

Remove all unnecessary files in the templates directory: –

rm -rf ./testchart/templates/*

Create a deployment yaml file: –

kubectl create deployment nginx \
--image=nginx:1.17 \
--dry-run=client \
--output=yaml > ./testchart/templates/deployment.yaml

Which will create the following yaml and save it as deployment.yaml in the templates directory: –

apiVersion: apps/v1
kind: Deployment
metadata:
  creationTimestamp: null
  labels:
    app: nginx
  name: nginx
spec:
  replicas: 1
  selector:
    matchLabels:
      app: nginx
  strategy: {}
  template:
    metadata:
      creationTimestamp: null
      labels:
        app: nginx
    spec:
      containers:
      - image: nginx:1.17
        name: nginx
        resources: {}
status: {}

Now create the deployment so we can run the expose command below: –

kubectl create deployment nginx --image=nginx:1.17 

Generate the yaml for the service with the kubectl expose command: –

kubectl expose deployment nginx \
--type=LoadBalancer \
--port=80 \
--dry-run=client \
--output=yaml > ./testchart/templates/service.yaml

Which will give us the following yaml and save it as service.yaml in the templates directory: –

apiVersion: v1
kind: Service
metadata:
  creationTimestamp: null
  labels:
    app: nginx
  name: nginx
spec:
  ports:
  - port: 80
    protocol: TCP
    targetPort: 80
  selector:
    app: nginx
  type: LoadBalancer
status:
  loadBalancer: {}

Delete the deployment, it’s not needed: –

kubectl delete deployment nginx

Recreate the values.yaml file with a value for the container image: –

rm ./testchart/values.yaml
echo "containerImage: nginx:1.17" > ./testchart/values.yaml

Then replace the hard coded container image in the deployment.yaml with a template directive: –

sed -i 's/nginx:1.17/{{ .Values.containerImage }}/g' ./testchart/templates/deployment.yaml

So the deployment.yaml file now looks like this: –

apiVersion: apps/v1
kind: Deployment
metadata:
  creationTimestamp: null
  labels:
    app: nginx
  name: nginx
spec:
  replicas: 1
  selector:
    matchLabels:
      app: nginx
  strategy: {}
  template:
    metadata:
      creationTimestamp: null
      labels:
        app: nginx
    spec:
      containers:
      - image: {{ .Values.containerImage }}
        name: nginx
        resources: {}
status: {}

Which means that the container image is not hard coded. It’ll take the value of nginx:1.17 from the values.yaml file or we can override it with the set flag (which we’ll do in a minute).

But first, deploy the chart to my local Kubernetes cluster running in Docker Desktop: –

helm install testchart ./testchart

Confirm release: –

helm list


N.B. – That app version is the default version set in the Chart.yaml file (which I haven’t updated)

Check image running in deployment: –

kubectl get deployment -o jsonpath='{ .items[*].spec.template.spec.containers[*].image }{"\n"}'

Great. That’s deployed and the container image is the one set in the values.yaml file in the Chart.

Now upgrade the release, replacing the default container image value with the set flag: –

helm upgrade testchart ./testchart --set containerImage=nginx:1.18

Confirm release has been upgraded (check the revision number): –

helm list

Also, confirm with the release history: –

helm history testchart

So we can see the initial deployment of the release and then the upgrade. App version remains the same as I haven’t changed the value in the Chart.yaml file. However, the image has been changed and we can see that with: –

kubectl get deployment -o jsonpath='{ .items[*].spec.template.spec.containers[*].image }{"\n"}'

So we’ve upgraded the image that’s running for the one pod in the deployment.

Let’s have a look at the replicasets of the deployment: –

kubectl get replicasets

So we have two replicasets for the deployment created by our Helm release. The inital one running nginx v1.17 and the newest one running nginx v1.18.

If we wanted to rollback the upgrade with kubectl, this would work (don’t run this code!): –

kubectl rollout undo deployment nginx

What would happen here is the that the pod under the newset replicaset would be deleted and a pod under the old replicaset would be spun up, rolling back nginx to v1.17.

But we’re not going to do that, as we’re using Helm.

Let’s grab the oldest replicaset name: –

REPLICA_SET=$(kubectl get replicasets -o jsonpath='{.items[0].metadata.name }' --sort-by=.metadata.creationTimestamp)

And delete it: –

kubectl delete replicasets $REPLICA_SET

So we now only have the one replicaset: –

kubectl get replicasets

Now try to rollback using the kubectl rollout undo command: –

kubectl rollout undo deployment nginx

The reason that failed is that we deleted the old replicaset, so there’s no history for that deployment, which we can see with: –

kubectl rollout history deployment nginx

But Helm has the history: –

helm history testchart

So we can rollback: –

helm rollback testchart 1

View release status: –

helm list

View release history: –

helm history testchart

View replicasets: –

kubectl get replicasets

The old replicaset is back! How? Let’s have a look at secrets within the cluster: –

kubectl get secrets

Ahhh, bet you anything the Helm release history is stored in those secrets! The initial release (v1), the upgrade (v2), and the rollback (v3).

Let’s have a closer look at the first one: –

kubectl get secret sh.helm.release.v1.testchart.v1 -o json

Hmm, that release field looks interesting. What we could do is base64 decode it and then run it through decompression on http://www.txtwizard.net/compression which would give us: –

{
"name":"testchart",
"info":
	{
		"first_deployed":"2020-08-09T11:21:20.4665817+01:00",
		"last_deployed":"2020-08-09T11:21:20.4665817+01:00",
		"deleted":"",
		"description":"Install complete",
		"status":"superseded"},
		"chart":{"metadata":
	{
		"name":"testchart",
		"version":"0.1.0",
		"description":"A Helm chart for Kubernetes",
		"apiVersion":"v2",
		"appVersion":"1.16.0",
		"type":"application"},
		"lock":null,
		"templates":[
			{
				"name":
				"templates/deployment.yaml",
				"data":"YXBpVmVyc2lvbjogYXBwcy92MQpraW5kOiBEZXBsb3ltZW50Cm1ldGFkYXRhOgogIGNyZWF0aW9uVGltZXN0YW1wOiBudWxsCiAgbGFiZWxzOgogICAgYXBwOiBuZ2lueAogIG5hbWU6IG5naW54CnNwZWM6CiAgcmVwbGljYXM6IDEKICBzZWxlY3RvcjoKICAgIG1hdGNoTGFiZWxzOgogICAgICBhcHA6IG5naW54CiAgc3RyYXRlZ3k6IHt9CiAgdGVtcGxhdGU6CiAgICBtZXRhZGF0YToKICAgICAgY3JlYXRpb25UaW1lc3RhbXA6IG51bGwKICAgICAgbGFiZWxzOgogICAgICAgIGFwcDogbmdpbngKICAgIHNwZWM6CiAgICAgIGNvbnRhaW5lcnM6CiAgICAgIC0gaW1hZ2U6IHt7IC5WYWx1ZXMuY29udGFpbmVySW1hZ2UgfX0KICAgICAgICBuYW1lOiBuZ2lueAogICAgICAgIHJlc291cmNlczoge30Kc3RhdHVzOiB7fQo="},{"name":"templates/service.yaml","data":"YXBpVmVyc2lvbjogdjEKa2luZDogU2VydmljZQptZXRhZGF0YToKICBjcmVhdGlvblRpbWVzdGFtcDogbnVsbAogIGxhYmVsczoKICAgIGFwcDogbmdpbngKICBuYW1lOiBuZ2lueApzcGVjOgogIHBvcnRzOgogIC0gcG9ydDogODAKICAgIHByb3RvY29sOiBUQ1AKICAgIHRhcmdldFBvcnQ6IDgwCiAgc2VsZWN0b3I6CiAgICBhcHA6IG5naW54CiAgdHlwZTogTG9hZEJhbGFuY2VyCnN0YXR1czoKICBsb2FkQmFsYW5jZXI6IHt9Cg=="}],"values":{"containerImage":"nginx:1.17"},"schema":null,"files":[{"name":".helmignore","data":"IyBQYXR0ZXJucyB0byBpZ25vcmUgd2hlbiBidWlsZGluZyBwYWNrYWdlcy4KIyBUaGlzIHN1cHBvcnRzIHNoZWxsIGdsb2IgbWF0Y2hpbmcsIHJlbGF0aXZlIHBhdGggbWF0Y2hpbmcsIGFuZAojIG5lZ2F0aW9uIChwcmVmaXhlZCB3aXRoICEpLiBPbmx5IG9uZSBwYXR0ZXJuIHBlciBsaW5lLgouRFNfU3RvcmUKIyBDb21tb24gVkNTIGRpcnMKLmdpdC8KLmdpdGlnbm9yZQouYnpyLwouYnpyaWdub3JlCi5oZy8KLmhnaWdub3JlCi5zdm4vCiMgQ29tbW9uIGJhY2t1cCBmaWxlcwoqLnN3cAoqLmJhawoqLnRtcAoqLm9yaWcKKn4KIyBWYXJpb3VzIElERXMKLnByb2plY3QKLmlkZWEvCioudG1wcm9qCi52c2NvZGUvCg=="}]},
				"manifest":"---\n# 
					Source: testchart/templates/service.yaml\n
					apiVersion: v1\n
					kind: Service\nmetadata:\n  
					creationTimestamp: null\n  
					labels:\n    
					app: nginx\n  
					name: nginx\n
					spec:\n  
					ports:\n  
					- port: 80\n    
					protocol: TCP\n    
					targetPort: 80\n  
					selector:\n    
					app: nginx\n  
					type: LoadBalancer\n
					status:\n  loadBalancer: {}\n---\n# 
					
					Source: testchart/templates/deployment.yaml\n
					apiVersion: apps/v1\n
					kind: Deployment\n
					metadata:\n  
					creationTimestamp: null\n  
					labels:\n    
					app: nginx\n  
					name: nginx\nspec:\n  
					replicas: 1\n  
					selector:\n    
					matchLabels:\n      
					app: nginx\n  
					strategy: {}\n  
					template:\n    
					metadata:\n      
					creationTimestamp: null\n      
					labels:\n        
					app: nginx\n    
					spec:\n      
					containers:\n      
					- image: nginx:1.17\n        
					name: nginx\n        
					resources: {}\n
					status: {}\n",
					"version":1,
					"namespace":"default"
			}

BOOM! That look like our deployment and service manifests! We can see all the information contained in our initial Helm release (confirmed as the container image is nginx:1.17)!

So by storing this information as secrets in the target Kubernetes cluster, Helm can rollback an upgrade even if the old replicaset has been deleted! Pretty cool!

Not very clean though, eh? And have a look at that data field…that looks suspiciously like more encrypted information (well, because it is 🙂 ).

Let’s decrypt it! This time on the command line: –

kubectl get secret sh.helm.release.v1.testchart.v1 -o jsonpath="{ .data.release }" | base64 -d | base64 -d | gunzip -c | jq '.chart.templates[].data' | tr -d '"' | base64 -d

Ha! There’s the deployment and service yaml files!

By using Helm we can rollback a release even if the old replicaset of the deployment has been deleted as Helm stores the history of a release in secrets in the target Kubernetes cluster. And by using the code above, we can decrypt those secrets and have a look at the information they contain.

Thanks for reading!