Latest News
0

Using the GitHub Package Registry to store container images

~

UPDATE – October 2020 – Github has now released the Github Container Registry which can be used to store container images. For more information, see here

The GitHub Package Registry is available for beta testing and allows us to store container images in it, basically giving us the same functionality as the Docker Hub.

However the Docker Hub only allows for one private repository per free account whereas the Github package registry is completely private! Let’s run through a simple demo to create a registry and upload an image.

First thing to do is create a personal access token in GitHub. Go to Settings > Developer Settings > Personal Access Tokens

Ensure that the token has the rights set above and click Generate Token

Now we can use that token to login to the package registry: –

TOKEN=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
docker login docker.pkg.github.com -u dbafromthecold -p $TOKEN

Search for a test image. I’m going to use the busybox image which is 2MB: –

docker search busybox

Then pull the image down: –

docker pull busybox:latest

Tag the image with the repo name to be push to. The format is docker.pkg.github.com/USERNAME/REPOSITORY/IMAGE:TAG

docker tag busybox:latest docker.pkg.github.com/dbafromthecold/testrepository/busybox:latest

N.B. – the repo used has to already exist within your github account

Now push the image to the GitHub Package repository: –

docker push docker.pkg.github.com/dbafromthecold/testrepository/busybox:latest

And then you should be able to see the package in GitHub: –

Thanks for reading!

4

Running SQL Server containers as non-root

~

Recently I noticed that Microsoft uploaded a new dockerfile to the mssql-docker repository on Github. This dockerfile was under the mssql-server-linux-non-root directory and (you guessed it) allows SQL Server containers to run as non-root.

But why is running a container as root bad? Let’s run through an example.

Using a non-root user: –

Run a SQL Server 2019 container with /etc mounted:-

docker run -d -p 15789:1433 \
--volume /etc:/etc \
--env SA_PASSWORD=Testing1122 \
--env ACCEPT_EULA=Y \
--name testcontainer \
mcr.microsoft.com/mssql/server:2019-RC1-ubuntu

Have a look at the logs: –

docker logs testcontainer

So even though I ran the container as a non-root user, the container is running as root.

Here’s the reason that’s bad. Exec into the container: –

docker exec -it testcontainer bash

Now create a user and add to the super user’s group: –

useradd testuser
passwd testuser
adduser testuser sudo

The user has been created and added to the super user’s group within the container. But if we come out of the container and run: –

cat /etc/group | grep sudo

The user is in the super user group on the host! Which means we can do: –

su testuser

Because we mounted the /etc directory into the container, the user created in the container is also created on the host!

And that’s why running containers as root is bad.

EDIT: November 2019

The new SQL Server 2019 run as a non-root user by default, these images are: –

mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
mcr.microsoft.com/mssql/server:2019-GDR1-ubuntu-16.04

So there’s no need to build your own image but the process below will show you how to (if you want to see how it’s done).

Let’s fix this by running SQL Server 2019 in a non-root container. First thing to do is create a mssql user on the host (you’ll have to run this as a user with sudo rights): –

useradd -M -s /bin/bash -u 10001 -g 0 mssql

N.B. – this user is needed as it’s created in the dockerfile, without it on the host the build will complete but any containers created from the image will crash.

Now, build the image from the dockerfile on Github: –

docker build -t 2019-nonroot .

Let’s try to run this container with /etc mounted: –

docker run -d -p 15799:1433 \
--volume /etc:/etc \
--env SA_PASSWORD=Testing1122 \
--env ACCEPT_EULA=Y \
--name testcontainer2 \
2019-nonroot

We can see that the container is running as the user mssql and it’s errored out as it does not have access to the /etc directory that we tried to mount!

So now that we have the option to run SQL Server in containers as a non-root user, I would absolutely recommend that you do so.

Thanks for reading!

7

Use port forwarding to access SQL Server running in Kubernetes

~

A really handy feature in Kubernetes is port forwarding. This can be used to narrow down an issue when connections are failing to SQL Server running in a cluster.

Say we have deployed the following to a Kubernetes cluster: –

apiVersion: apps/v1beta1
kind: Deployment
metadata:
  name: sqlserver
spec:
  replicas: 1
  template:
    metadata:
      labels:
        name: sqlserver
    spec:
      containers:
      - name: sqlserver1
        image: mcr.microsoft.com/mssql/server:2019-RC1-ubuntu
        ports:
        - containerPort: 1433
        env:
        - name: SA_PASSWORD
          value: "Testing1122"
        - name: ACCEPT_EULA
          value: "Y"
---
apiVersion: v1
kind: Service
metadata:
  name: sqlserver-service
spec:
  ports:
  - name: sqlserver
    port: 1433
    targetPort: 1433
  selector:
    name: sqlserver
  type: LoadBalancer

This will create the following in the Kubernetes cluster: –

The load balanced service’s IP can be usually be used to connect into the SQL instance running in the pod, but what if we’re unable to connect? Does the issue lie with the service or the pod?

In order to narrow this down, port forwarding can be used to directly connect to the pod: –

kubectl port-forward pod/sqlserver-889b56d7b-nb2b4 15789:1433

This will allow us to use 127.0.0.1,15789 (localhost won’t work) and connect from our local machine to the pod running in the Kubernetes cluster (in a separate window): –

mssql-cli -S 127.0.0.1,15789 -U sa

We can use the same port to connect via ADS and SSMS as well: –

If a connection can be established to the pod via the forwarded port then we know that the issue doesn’t lie with the pod but with the service or the connection from the service to the pod.

Thanks for reading!

0

Data Relay 2019

~

My favourite UK Data Platform event is back for 2019 and has just opened for registration!

If you haven’t heard of Data Relay before, it’s pretty unique. It’s a 5 day conference but each of those 5 days is in a different city. Typically cities that don’t host their own data event.

This means that after each day, the event packs up and moves on. As you can imagine this poses quite a few challenges but it’s extremely satisfying to see it all come together.

The reason I like Data Relay so much is that it really fosters a sense of camaraderie between everyone involved. We’re all in it together to get each event up and running every day.

That’s how I got involved in the first place. I got selected to speak at each event in 2017 and ended up helping out with packing swag bags, moving boxes, and generally doing anything else that needed to be done. Have to admit, it was exhausting but really really good fun.

So if you’d like to come to one of the best community events on the calendar, this year’s schedule is: –

  • Newcastle – October 7th – Register here
  • Leeds – October 8th – Register here
  • Nottingham – October 9th – Register here
  • Birmingham – October 10th – Register here
  • Bristol – October 11th – Register here

Thanks for reading and hope to see you at Data Relay 2019!

0

Converting SQL Server docker compose files for Kubernetes with Kompose

~

Docker compose is a great tool for easily deploying docker container without having to write lengthy docker run commands. But what if I want to deploy my docker-compose.yaml file into Kubernetes?

Kompose is a tool that can convert docker compose files so that they can be deployed to a Kubernetes cluster.

Here’s a typical docker-compose.yaml file I use: –

version: '3'
 
services:
    sqlserver1:
        image: mcr.microsoft.com/mssql/server:2019-CTP3.1-ubuntu
        ports:  
          - "15789:1433"
        environment:
          SA_PASSWORD: "Testing1122"
          ACCEPT_EULA: "Y"
          MSSQL_DATA_DIR: "/var/opt/sqlserver/data"
          MSSQL_LOG_DIR: "/var/opt/sqlserver/log"
          MSSQL_BACKUP_DIR: "/var/opt/sqlserver/backup"
        volumes: 
          - sqlsystem:/var/opt/mssql/
          - sqldata:/var/opt/sqlserver/data
          - sqllog:/var/opt/sqlserver/log
volumes:
  sqlsystem:
  sqldata:
  sqllog:

This will spin up one container running SQL Server 2019 CTP 3.1, accept the EULA, set the SA password, and set the default location for the database data/log/backup files using named volumes created on the fly.

Let’s convert this using Kompose and deploy to a Kubernetes cluster.

To get started with Kompose first install by following the instructions here. I installed on my Windows 10 laptop so I downloaded the binary and added to my PATH environment variable.

Before running Kompose I had to make a slight change to the docker-compose.yaml file because when I deploy SQL Server to Kubernetes I want to create a LoadBalanced service so that I can connect to the SQL instance remotely. To get Kompose to create a LoadBalanced service I had to add the following to my docker-compose.yaml file (under the first volumes section): –

        labels:
          kompose.service.type: LoadBalancer

Then I navigated to the location of my docker-compose.yaml file and ran: –

kompose convert -f docker-compose.yaml

Which created the corresponding yaml files!

Looking through the created files, they all look good! The PVCs will use the default storage class of the Kubernetes cluster that you’re deploying to and the deployment/service don’t need any adjustment either.

So now that I have the yaml files to deploy into Kubernetes, I simply run:-

kompose up

And the files will be deployed to my Kubernetes cluster!

OK, kubectl describe pods will show errors initially when the pod is first created as the PVCs haven’t been created but it will retry.

Once the pod is up and the service has an external IP address, the SQL instance can be connected to. Nice and easy!

Cleaning up is also a cinch, just run:-

kompose down

And the objects will be deleted from the cluster.

Thanks for reading!