SQL Server on Kubernetes and MetalLB

SQL Server containers on Kubernetes and Pure Storage with MetalLB

In this Blog I will show you how we can use a combination of Kubernetes, Pure Service Orchestrator ( PSO ) and MetalLB to create a load balanced Kubernetes cluster for SQL Server

 Pure Service Orchestrator allows for smart provisioning of storage to one or many arrays, this provides administrators and developers the ability to  provision storage on demand with cloud like functionality, ease and flexibility.

Pure Service Orchestrator ( PSO ) is used to  automate the provisioning of volumes to Kubernetes which will then be used to create the SQL Server  containers using persistent storage.


MetalLB is a load-balancer implementation for bare metal Kubernetes clusters, using standard routing protocols.

Environment

RoleFQDNIPOSRAMCPUK8 Ver
Masterk8master192.168.111.228Ubuntu 18.048G31.18.2
Worker1k8worker1192.168.111.229Ubuntu 18.0478G41.18.2
Worker2k8worker2192.168.111.230Ubuntu 18.0478G41.18.2

I already have a Ubuntu Cluster up and running, so I wont be going through the install process in this blog

  • Let's check the cluster status
root@k8master:~# kubectl get nodes
NAME        STATUS   ROLES    AGE    VERSION
k8master    Ready    master   140d   v1.18.2
k8worker1   Ready    <none>   140d   v1.18.2
k8worker2   Ready    <none>   140d   v1.18.2
  • Let's check to see if we have any pod or services deployed
root@k8master:~# kubectl get pods
No resources found in default namespace.

oot@k8master:~# kubectl get svc
NAME         TYPE        CLUSTER-IP       EXTERNAL-IP   PORT(S)    AGE
hollowdb     ClusterIP   10.111.175.138   <none>        3306/TCP   140d
kubernetes   ClusterIP   10.96.0.1        <none>        443/TCP    140d

As you can see from the above output, we have no PODS and no SERVICES.

Lets' check to make sure the PSO namespace has been configure

root@k8master:~# kubectl get ns
NAME              STATUS   AGE
default           Active   140d
oradb             Active   139d
pso               Active   140d
  • Now let's create a new SQL Server deployment called mssql-deployment, the deployment will do a few things
  1. Create the persistent volume claim
  2. Deploy SQL Server using the persistent volume
  3. Create the SQL server service
root@k8master:~# kubectl apply -f cbsql.yaml
persistentvolumeclaim/pvc-sql-system unchanged
deployment.apps/mssql-deployment configured
service/mssql-deployment created
Here is a link to the deployment yaml - sql server yaml file
  • Let's confirm our POD is running
root@k8master:~# kubectl get pod
NAME                               READY   STATUS    RESTARTS   AGE
mssql-deployment-df565f596-zkb87   1/1     Running   0          48s
  • Let's have a closer look, as you can see, the POD has started on node K8worker2, its up and running and uses the 2019-CU4-ubuntu-18.04 image
root@k8master:~# kubectl describe pods mssql-deployment
Name:         mssql-deployment-df565f596-zkb87
Namespace:    default
Priority:     0
Node:         k8worker2/192.168.111.230
Start Time:   Tue, 22 Sep 2020 10:03:37 +1000
Labels:       app=mssql
              pod-template-hash=df565f596
Annotations:  <none>
Status:       Running
IP:           10.244.2.18
IPs:
  IP:           10.244.2.18
Controlled By:  ReplicaSet/mssql-deployment-df565f596
Containers:
  mssql:
    Container ID:   docker://db2f3fcfc04b0717db90a146435a2f5476bc88bd415a67710a123ecbe7e1e80e
    Image:          mcr.microsoft.com/mssql/server:2019-CU4-ubuntu-18.04
    Image ID:       docker-pullable://mcr.microsoft.com/mssql/server@sha256:360f6e6da94fa0c5ec9cbe6e391f411b8d6e26826fe57a39a70a2e9f745afd82
    Port:           1433/TCP
    Host Port:      0/TCP
    State:          Running
      Started:      Tue, 22 Sep 2020 10:03:43 +1000
    Ready:          True
    Restart Count:  0
    Environment:
      ACCEPT_EULA:  Y
      SA_PASSWORD:  <set to the key 'SA_PASSWORD' in secret 'mssql'>  Optional: false
    Mounts:
      /var/opt/mssql from mssql-system (rw)
      /var/run/secrets/kubernetes.io/serviceaccount from default-token-kj8td (ro)

 

  • Let's log onto worker node 2 and confirm the container is running
       # docker ps| grep microsoft
CONTAINER ID        IMAGE                                      COMMAND                  CREATED             STATUS              PORTS               NAMES
86b9292ec4c9        mcr.microsoft.com/mssql/server             "/opt/mssql/bin/perm…"   2 hours ago         Up 2 hours                              k8s_mssql_mssql-deployment-df565f596-nnctl_default_9b1076d6-bf4c-4c0a-a977-c4f65ce1a9a3_0
  • Lets check our persistent volumes, we can see that the volume sqldatavol has been create
      # kubectl get pvc
NAME             STATUS   VOLUME                                     CAPACITY   ACCES MODE     STORAGECLASS   AGE 
sqldatavol       Bound    pvc-79bf05e7-4ef7-4355-95e6-76243dded23f   10Gi       RWO            pure-block     45h


  • Let's connect to the container, notice the persistent volume created by PSO 
root@k8worker2:~# docker exec -it 86b9292ec4c9 bash
mssql@mssql-deployment-df565f596-nnctl:/$
mssql@mssql-deployment-df565f596-nnctl:/$
mssql@mssql-deployment-df565f596-nnctl:/$ df -k
Filesystem                                    1K-blocks     Used Available Use% Mounted on
overlay                                       102685624 18154400  79272064  19% /
tmpfs                                             65536        0     65536   0% /dev
tmpfs                                           4084140        0   4084140   0% /sys/fs/cgroup
/dev/sda1                                     102685624 18154400  79272064  19% /etc/hosts
shm                                               65536        0     65536   0% /dev/shm
/dev/mapper/3624a9370a21265762db64ece00092e5c  10475520   197468  10278052   2% /var/opt/mssql
tmpfs                                           4084140       12   4084128   1% /run/secrets/kubernetes.io/serviceaccount
tmpfs                                           4084140        0   4084140   0% /proc/acpi
tmpfs                                           4084140        0   4084140   0% /proc/scsi
tmpfs                                           4084140        0   4084140   0% /sys/firmware

mssql@mssql-deployment-df565f596-nnctl:/$ ps -ef|grep sql
mssql        1     0  0 01:38 ?        00:00:00 /opt/mssql/bin/sqlservr
mssql        9     1  1 01:38 ?        00:02:14 /opt/mssql/bin/sqlservr
mssql     1344     0  0 03:49 pts/0    00:00:00 bash
mssql     1356  1344  0 03:49 pts/0    00:00:00 ps -ef
mssql     1357  1344  0 03:49 pts/0    00:00:00 grep sql

 The serial number of the volume ending in 2e5c, maps to the volume created by PSO



At this point I can connect to the database using the port specified in the service section of the yaml file ( 31433 ), It's a cluster wide port number, however -  we need to specify the worker node IP address, so if the pod moves to another worker node, we need to specify the other nodes IP address. A better way is to create a load-Balanced IP address which allows us to connect to one IP address regardless of which node the service is running on. This is where MetalLB comes into play.

---
apiVersion: v1
kind: Service
metadata:
  name: mssql-deployment
spec:
  selector:
    app: mssql
  ports:
    - protocol: TCP
      port: 31433
      targetPort: 1433
  type: LoadBalancer

Install and configure MetalLB.

  • Download metalLB from https://metallb.universe.tf/

  • Select the installation TAB, select installation manifest, copy the code and run it on your cluster

kubectl apply -f https://raw.githubusercontent.com/metallb/metallb/v0.9.3/manifests/namespace.yaml
kubectl apply -f https://raw.githubusercontent.com/metallb/metallb/v0.9.3/manifests/metallb.yaml
# On first install only
kubectl create secret generic -n metallb-system memberlist --from-literal=secretkey="$(openssl rand -base64 128)"

Ok, let's check to make sure metalLB is installed. First lets check to make sure the namespace has been created.

root@k8master:~# kubectl get ns
NAME              STATUS   AGE
default           Active   140d
metallb-system    Active   12h
oradb             Active   139d
pso               Active   140d

Next, we need to deploy the configuration, we will deploy this using a configmap. Go to the configuration tab on the metallb web page select the Layer 2 configuration




apiVersion: v1
kind: ConfigMap
metadata:
  namespace: metallb-system
  name: config
data:
  config: |
    address-pools:
    - name: default
      protocol: layer2
      addresses:
      - 192.168.111.240-192.168.111.245

  • In the above configmap, I have configured my Load balancer to use the address range 192.168.111.240 - 192.168.111.245

  • Let's create the configmap called config

root@k8master:~# kukectl create -f configmap
configmap/config created

root@k8master:~# kubectl describe configmap config -n metallb-system
Name:         config
Namespace:    metallb-system
Labels:       <none>
Annotations:  <none>

Data
====
config:
----
address-pools:
- name: default
  protocol: layer2
  addresses:
  - 192.168.111.240-192.168.111.245

Now if everything has worked correctly, I should have an external-IP assigned to my mssql-deployment

root@k8master:~# kubectl get svc
NAME               TYPE           CLUSTER-IP       EXTERNAL-IP       PORT(S)           AGE
hollowdb           ClusterIP      10.111.175.138   <none>            3306/TCP          140d
kubernetes         ClusterIP      10.96.0.1        <none>            443/TCP           140d
mssql-deployment   LoadBalancer   10.99.155.157    192.168.111.240   31433:30402/TCP   45m
  • As we can see from the above output, my mssql deployment how has a load balanced IP address assigned to it ( 192.168.111.240) which is from the range I specified

Now I should be able to connect to my MSSQL database using the 192.168.111.240 IP and port 31433

I'm using SQLPro for MSSQL on a MAC, lets setup the connection details using the 192.168.111.240 IP address



Setup database credentials



















Logged into the database










Lets create a new database called demo1









Database created










Summary

  • In this blog I have shown you how we can use MetalLB in our Kubernetes cluster to create a load balanced service for Microsoft SQL Server

Comments

Anonymous said…
Nice article please do visit my website for UNIX & LINUX online training program
2:42 AM
Post a Comment

Popular posts from this blog

SQL Server container with Pure Storage Docker volumes

Image
SQL Server on Docker with Pure Storage Persistent Volumes Docker and containers have become an integral part of virtualisation. Having the ability to spin up containers has almost instantly transformed the way we build, develop and manage applications. Many of us have been using some sort of virtualisation for decades as this is not a new concept. Due to their speed, simplicity and automation, container usage has garnered substantial popularity and are becoming ubiquitous in the field of virtualisations. So why use containers ? Decouple application runtime from infrastructure. Runs instantly. Process and user isolation for application multi-tenancy. Key building blocks for microservices. But what happens when you delete the container ? The main issue in deleting a container is that you delete the writeable layer and any data that was there, hence no data persistence. Docker volumes provide us a way to decouple your application from its state, to the point where you c...
Read more

Oracle 19c Database clones using the Pure Storage Docker plugin

Image
  Docker and containers have become a integral part of virtualization, being able to spin up containers almost instantly have transformed the way be build, develop and manage applications. In this blog I will show you how we can rapidly create Oracle 19c container clones using the Pure Storage Docker plugin with persistent volumes and the new Oracle 19c container image on the Oracle Container Registry https://container-registry.oracle.com/pls/apex/f?p=113:4:2886566670416#     Installing the Pure Plugin ( Latest version is 3.10 ) 12:24:30 root@docker ~ # docker plugin install purestorage/docker-plugin:v3.10 --alias pure --grant-all-permissions Enter the Array Configuration in the /etc/pure-docker-plugin/pure.json file  {      "FlashArrays":[          {              "MgmtEndPoint":“192.168.111.130",              "APIToken":" 476d58bc-3c91-0d10-1b9e-0f31058c462...
Read more

Cloning SQL Server Containers With Pure Storage Docker Volumes

Image
In my first blog  https://bannaych.blogspot.com/2020/04/mssql-server-on-docker-with-pure.html  I showed you how we can create SQL Server containers using the Pure Storage Docker plugin. In this blog I will show you how we can leverage the Pure Storage Docker plugin to instantly create a clone volume from the source volume, then use Pure Storage crash consistent snapshots to refresh the clone volume. Crash consistent snapshots provide us with a point in time copy of the volume/database, because we use metadata pointers to the original blocks on disk, the snapshots are created instantly with no initial space consumption, then those snapshots can be used to refresh volumes or create new ones. The big advantage of using snapshots is that we no longer need to backup and restore large databases to provision and refresh TEST/DEV/UAT environments. In the world of DevOps being agile is imperative and Pure Storage space efficient snapshots are key to maximising productivity of database ...
Read more