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!

0

Chaos engineering for SQL Server running on AKS using KubeInvaders


UPDATE – March 2022
I have publised an updated guide to deploying KubeInvaders on AKS here: –
https://dbafromthecold.com/2022/03/10/space-invaders-on-kubernetes/


A couple of weeks ago I came across an awesome GitHub repo called KubeInvaders which is the brilliant work of Eugenio Marzo (b|t)

KubeInvaders allows you to play Space Invaders in order to kill pods in Kubernetes and watch new pods be created (this actually might be my favourite github repo of all time).

I demo SQL Server running in Kubernetes a lot so really wanted to get this working in my Azure Kubernetes Service cluster. Here’s how you get this up and running.


Prerequisites

1. A DockerHub repository
2. An Azure Kubernetes Service cluster – I blogged about spinning one up here
3. A HTTPS ingress controller on AKS with a FQDN for the ingress controller IP. I didn’t have to change anything in the instructions in the link but don’t worry if the final test doesn’t work…it didn’t work for me either.


Building the image

First, clone the repo:-

git clone https://github.com/lucky-sideburn/KubeInvaders.git

Switch to the AKS branch:-

cd KubeInvaders
git checkout aks

Build the image:-

docker build -t kubeinvaders .

Once the image has built, tag it with a public repository name and then push:-

docker tag kubeinvaders dbafromthecold/kubeinvaders:aks
docker push

Deploying to AKS

Now that the image is in a public repository, we can deploy to Kubernetes. Eugenio has provided all the necessary yaml files, so it’s really easy! Only a couple of changes are needed.

First one is the the kubeinvaders-deployment.yaml file, the image name needs to be updated:-

    spec:
      containers:
      - image: dbafromthecold/kubeinvaders:aks

And the host in the kubeinvaders-ingress.yaml file needs to be set to the FQDN of your ingress (set when following the MS docs): –

spec:
  tls:
  - hosts:
    - apruski-aks-ingress.eastus.cloudapp.azure.com
  rules:
  - host: apruski-aks-ingress.eastus.cloudapp.azure.com

Cool. So now each of the files can be deployed to your cluster: –

kubectl apply -f kubernetes/kubeinvaders-namespace.yml

kubectl apply -f kubernetes/kubeinvaders-deployment.yml -n kubeinvaders

kubectl expose deployment kubeinvaders --type=NodePort --name=kubeinvaders -n kubeinvaders --port 8080

kubectl apply -f kubernetes/kubeinvaders-ingress.yml -n kubeinvaders

kubectl create sa kubeinvaders -n foobar 

kubectl apply -f kubernetes/kubeinvaders-role.yml

kubectl apply -f kubernetes/kubeinvaders-rolebinding.yml

Finally, set some environment variables: –

TARGET_NAMESPACE='foobar'
TOKEN=`kubectl describe secret $(kubectl get secret -n foobar | grep 'kubeinvaders-token' | awk '{ print $1}') -n foobar | grep 'token:' | awk '{ print $2}'`
ROUTE_HOST=apruski-aks-ingress.eastus.cloudapp.azure.com

kubectl set env deployment/kubeinvaders TOKEN=$TOKEN -n kubeinvaders
kubectl set env deployment/kubeinvaders NAMESPACE=$TARGET_NAMESPACE -n kubeinvaders
kubectl set env deployment/k/ubeinvaders ROUTE_HOST=$ROUTE_HOST -n kubeinvaders

Now navigate to the FQDN of the ingress in a browser and you should see…


Testing the game!

By default KubeInvaders points to a namespace called foobar so we need to create it: –

kubectl create namespace foobar

And now create a deployment running 10 SQL Server pods within the foobar namespace: –

kubectl run sqlserver --image=mcr.microsoft.com/mssql/server:2019-CTP3.1-ubuntu --replicas=10 -n foobar

Now the game will have 10 invaders which represent the pods!

Let’s play! Watch the pods and kill the invaders!

kubectl get pods -n foobar --watch

How awesome is that! You can even hit a to switch to automatic mode!

What a cool way to demo pod regeneration in Kubernetes.

Thanks for reading!

0

Default resource limits for Windows vs Linux containers in Docker Desktop

Docker Desktop is a great product imho. The ability to run Windows and Linux containers locally is great for development and has allowed me to really dig into SQL Server on Linux. I also love the fact that I no longer need to install SQL 2016/2017, I can run it in Windows containers.

However there are some differences between how Windows and Linux containers run in Docker Desktop. One of those differences being the default resource limits that are set.

Linux containers’ resource limits are set in the Advanced section of the Docker Desktop settings: –

These setting control the resources available to the MobyLinuxVM, where the Linux containers run (and that’s how you get Linux containers running on Windows 10): –

This can be confirmed by spinning up a Linux container (I’m running SQL but you can use any image): –

docker run -d -p 15789:1433 `
--env ACCEPT_EULA=Y --env SA_PASSWORD=Testing1122 `
--name testcontainer `
mcr.microsoft.com/mssql/server:2019-CTP3.0-ubuntu

And then running the following to confirm resources available to the container: –

docker exec testcontainer /bin/bash -c 'cat /proc/meminfo | grep MemTotal'

docker exec testcontainer nproc

The container has the memory and CPUs that were set in the Docker settings.

But what about Windows containers? When you switch to Windows containers in Docker, there’s no option to set CPU and memory limits. This is because Windows containers run on the host, not in the MobyLinuxVM. The host I’m running on has 4 cores and 32GB of RAM, so the containers should have all the host resources available to it, right?

This can be checked by spinning up a Windows container:-

docker run -d -p 15789:1433 `
--env ACCEPT_EULA=Y --env SA_PASSWORD=Testing1122 `
--name testcontainer `
microsoft/mssql-server-windows-developer:latest

And then running: –

docker exec testcontainer systeminfo | select-string 'Total Physical Memory'

docker exec testcontainer systeminfo | select-string 'Processor'

Ok, the container can only see the one processor (same as my host), so let’s exec into the container and check the number of cores.

docker exec -i testcontainer powershell

Get-WmiObject -class Win32_processor | Format-Table Name,NumberOfCores,NumberOfLogicalProcessors

Ok, so it looks like Windows containers are limited to 2 cores and 1GB of RAM by default. Not exactly great if we’re running SQL Server, but can we change those limits? As I said earlier, there’s no way to adjust the resources available to Windows containers in the Docker settings.

What we can do is change the resources available to the individual containers at runtime using the –cpus and –memory options:-

docker run -d -p 15799:1433 `
--cpus=3 --memory=8192m `
--env ACCEPT_EULA=Y --env SA_PASSWORD=Testing1122 `
--name testcontainer2 `
microsoft/mssql-server-windows-developer:latest

N.B. – I’m setting 3 cores as when I tried 4, the container crashed (something to watch out for).

Now if we check the resources again: –

docker exec testcontainer2 systeminfo | select-string 'Total Physical Memory'

docker exec testcontainer2 systeminfo | select-string 'Processor'

The memory available to the container has increased, let’s exec into the container and check the number of cores: –

docker exec -i testcontainer2 powershell

Get-WmiObject -class Win32_processor | Format-Table Name,NumberOfCores,NumberOfLogicalProcessors

The core count has also increased.

So the limits of Windows containers running on Docker Desktop can be altered, just not in the settings as with Linux containers.

Thanks for reading!

0

Deploying SQL Server to an Azure Container Instance using Terraform – Part Two

In a previous post I went through how to deploy SQL Server running in an Azure Container Instance using Terraform.

In that post, I used hardcoded variables in the various .tf files. This isn’t great to be honest as in order to change those values, we’d need to update each .tf file. It would be better to replace the hardcoded values with variables whose values can be set in one separate file.

So let’s update each value with a variable in the .tf files.

First file to update is the providers.tf file: –

provider "azurerm" {
version         = "1.24.0"
subscription_id = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
tenant_id       = "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb"
client_id       = "cccccccc-cccc-cccc-cccc-cccccccccccc"
client_secret   = "dddddddd-dddd-dddd-dddd-dddddddddddd"
}

Replace the values in the file with the following: –

provider "azurerm" {
version         = "1.24.0"
subscription_id = "${var.subId}"
tenant_id       = "${var.tenantId}"
client_id       = "${var.clientId}"
client_secret   = "${var.clientSecret}"
}

Now that we have referenced variables in the files we need to define those variables. First create a file to hold the variables: –

mkdir variables.tf

And then drop in the following: –

variable "subId" {
description = "please provide subscription Id"
type        = "string"
}

variable "tenantId" {
description = "please provide tenant Id"
type        = "string"
}

variable "clientId" {
description = "please provide client Id"
type        = "string"
}

variable "clientSecret" {
description = "please provide client secret"
type        = "string"
}

OK, so we have defined the variables that we referenced in the providers.tf file. However, we haven’t set any values for those variables.

If we left the files like this, when we deploy we would be asked to manually enter values for the variables. What we now need to do is create another file where we can set the variable values.

First thing to do is create a directory called vars: –

new-item vars -type directory

And now create a file to hold the values (notice that its extension is .tfvars to distinguish it from the other files): –

cd vars
new-item aci.tfvars

Drop the original hardcoded values in: –

subId = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
tenantId = "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb"
clientId = "cccccccc-cccc-cccc-cccc-cccccccccccc"
clientSecret = "dddddddd-dddd-dddd-dddd-dddddddddddd"

And now we’re good to go! But before we deploy, let’s have another look at the azurecontainerinstance.tf file: –

resource "azurerm_container_group" "testcontainergroup1" {
name                = "testcontainergroup1"
location            = "${azurerm_resource_group.azurecontainerinstances.location}"
resource_group_name = "${azurerm_resource_group.azurecontainerinstances.name}"
ip_address_type     = "public"
dns_name_label      = "testcontainergroup1"
os_type             = "Linux"

container {
name   = "testcontainer"
image  = "mcr.microsoft.com/mssql/server:2019-CTP2.5-ubuntu"
cpu    = "1.0"
memory = "2.0"

ports {
port     = 1433
protocol = "TCP"
}

environment_variables = {
"ACCEPT_EULA" = "Y"
"SA_PASSWORD" = "Testing1122"
}
}
}

We’re already using variables for the resource group and location (which reference the resource group created in the resourcegroup.tf file) but let’s add in variables for the container name, image, and sa password: –

resource "azurerm_container_group" "testcontainergroup1" {
name                = "testcontainergroup1"
location            = "${azurerm_resource_group.azurecontainerinstance2.location}"
resource_group_name = "${azurerm_resource_group.azurecontainerinstance2.name}"
ip_address_type     = "public"
dns_name_label      = "testcontainergroup1"
os_type             = "Linux"

container {
name   = "${var.containerName}"
image  = "${var.containerImage}"
cpu    = "1.0"
memory = "2.0"

ports {
port     = 1433
protocol = "TCP"
}

environment_variables = {
"ACCEPT_EULA" = "Y"
"SA_PASSWORD" = "${var.saPassword}"
}
}
}

Cool, now we need to update the variables.tf file: –

variable "subId" {
description = "please provide subscription Id"
type        = "string"
}

variable "clientId" {
description = "please provide client Id"
type        = "string"
}

variable "clientSecret" {
description = "please provide client secret"
type        = "string"
}

variable "tenantId" {
description = "please provide tenant Id"
type        = "string"
}

variable "saPassword" {
description = "please provide an SA password"
type        = "string"
}

variable "containerImage" {
description = "please provide container image"
type        = "string"
}

variable "containerName" {
description = "please provide container name"
type        = "string"
}

And drop in the values to the aci.tfvars file: –

subId = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
tenantId = "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb"
clientId = "cccccccc-cccc-cccc-cccc-cccccccccccc"
clientSecret = "dddddddd-dddd-dddd-dddd-dddddddddddd"
saPassword = "Testing1122"
containerImage = "mcr.microsoft.com/mssql/server:2019-CTP2.5-ubuntu"
containerName = "testcontainer1"

Before we deploy, let’s format these files: –

terraform fmt

Really love that feature. If you run it at the root of your project it will format all your files for you 🙂

Now we can test the deployment (notice we’re referencing the aci.tfvars file): –

terraform plan -var-file "./vars/aci.tfvars"

If all looks good (the values for the variables are being picked up), we can deploy: –

terraform apply -var-file "./vars/aci.tfvars"

What’s great about using variables like this is that we now don’t have to touch the .tf files where we define our resources. If we want to say, deploy a different image to an ACI we just have to update the containerImage variable in the aci.tfvars file.

This also means that if we push this project to a github repo we can ignore the vars directory, so no sensitive information is exposed (such as the sa password for our SQL instance).

Thanks for reading!

2

Deploying SQL Server to an Azure Container Instance using Terraform – Part One

A couple of weeks ago I attended John Martin’s (t) Terraform pre-con at Data in Devon. I’ve been hearing about Terraform recently so was excited to check it out.

John’s pre-con was absolutely fantastic, he provided a great insight into Terraform and I highly recommend it to anyone who’s looking at learning it to sign up if you see it at a future event.

So armed with my new found knowledge, I wanted to go about deploying SQL Server into an Azure Container Instance using Terraform. This blog is the first in a series where I’ll go through how to use Terraform. This post will get SQL Server up and running in an ACI and then future posts will go a bit deeper.

To follow along with this blog you will need the following tools: –

Terraform
Azure CLI
VSCode
Terraform extension for VSCode
Azure Terraform extension for VSCode

OK, let’s go through building the required Terraform files to deploy SQL Server to an Azure Container Instance.

First, log into your Azure account: –

az login

az-login.JPG

Make a note of the id and tenantId.

Next, create a service principal which we will reference to allow Terraform to create resources in Azure. Make a note of the appid and secret: –

az ad sp create-for-rbac --role="Contributor" --scopes="/subscriptions/aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

Create service principal.JPG

Ok, now we can start creating Terraform files.

Let’s create a directory for the terraform files and navigate to it: –

mkdir Terraform-ACI

cd Terraform-ACI

The first file to create is a providers file. This tells Terraform that we will be working in Azure so it will pull down the relevant plugin: –

new-item providers.tf

Now drop the following into the providers.tf file (using the values you noted above): –

provider "azurerm" {
version = "1.24.0"
subscription_id = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
tenant_id = "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb"
client_id = "cccccccc-cccc-cccc-cccc-cccccccccccc"
client_secret = "dddddddd-dddd-dddd-dddd-dddddddddddd"
}

The first resource we’ll create in Azure is a resource group to hold the ACI: –

new-item resourcegroup.tf

Drop the following into the resource group file

resource "azurerm_resource_group" "azurecontainerinstances" {
name = "azurecontainerinstances"
location = "eastus"
}

Note the format of the file, it’s laid out as: –

resource "RESOURCE_TYPE" "RESOURCE_NAME" {
attribute_name = "attribute_value"
}

The RESOURCE_NAME is the name of the resource that will be referenced in Terraform. It can look a little confusing as in the code above I’ve called the resource name in Terraform the same name as the name of the resource group in Azure (the name attribute).

What’s cool is with the Azure Terraform VSCode extension, we can click on the RESOURCE_TYPE field and it will take us to the documentation for that resource. Makes things a lot easier!

Resource Group.JPG

Ok, now that we have a resource group we can create the Terraform file to deploy the Azure Container Instance into it: –

new-item azurecontainerinstance.tf

Drop the following into the file: –

resource "azurerm_container_group" "testcontainergroup1" {
name = "testcontainergroup1"
location = "${azurerm_resource_group.azurecontainerinstances.location}"
resource_group_name = "${azurerm_resource_group.azurecontainerinstances.name}"
ip_address_type = "public"
dns_name_label = "testcontainergroup1"
os_type = "Linux"

container {
name = "testcontainer"
image = "mcr.microsoft.com/mssql/server:2019-CTP2.5-ubuntu"
cpu = "1.0"
memory = "2.0"

ports {
port = 1433
protocol = "TCP"
}

environment_variables = {
"ACCEPT_EULA" = "Y"
"SA_PASSWORD" = "Testing1122"
}
}
}

What this is going to do is create an Azure Container Instance Group with one container it in, running SQL Server 2019 CTP 2.5. It’ll be publicly exposed to the internet on port 1433 (I’ll cover fixing that in a future post) so we’ll get a public IP that we can use to connect to.

Notice that the location and resource_group_name are set using variables that retrieve the values of the resource group are going to create.

Cool! We are ready to go!

Initialise the directory: –

terraform init

Terraform init.JPG

One cool feature of Terraform is the ability to format all the files (seriously love this): –

terraform fmt

And now we can test the deployment (also love this feature): –

terraform plan

Terraform Plan.JPG

If all looks good, we can deploy!

terraform apply

Awesome stuff. Terraform will give us updates on how the deployment is doing: –

Terraform-ACI-1.gif

To get the IP address of the new Azure Container Instance: –

az container show --name testcontainergroup1 --resource-group azurecontainerinstances

Show Container Details.JPG

Drop that IP Address into SSMS or ADS: –

Azure Data Studio.JPG

And boom! We are connected to SQL Server 2019 CTP2.5 running in an Azure Container Instance deployed with Terraform.

If you want to tear down what’s just been built, you can run: –

terraform destroy

Thanks for reading!