Considerations for Running SQL Server on Kubernetes
Kubernetes is an open-source orchestration and management system for containers. It is an exciting technology to learn once you begin to embrace the cloud platforms. As a DBA, how should you leverage running SQL Servers on containers and deploying them to Kubernetes?
For this tip, we will discuss the different considerations before you begin deploying your SQL Servers images in Kubernetes.
How does Kubernetes work?
Kubernetes is mainly an orchestration tool. You might say you have hundreds of containers running applications and databases. Kubernetes offers a highly scalable, reliable control plane to run these containers. Many SQL Server container images have been published and made public some years back. One very popular container repository is the Docker hub site.
Key Benefits of Running SQL Server in Containers
Here is a great tip that explains the containers for SQL Servers on Windows and Linux. One of the key benefits of having images of SQL Servers on containers is that you skip the installation and configuration part. Basically, you have an image of your database server and its run and availability is orchestrated by the Kubernetes Cluster.
Inside the Kubernetes Cluster, there is a control plane that manages and orchestrates the nodes, which in turn is running the pods. Pods are where your SQL Server container is running and your data and logs are sitting on persistent volumes under the hood.
Ideally, when considering if running SQL Servers in Kubernetes is appropriate for your use case, make sure to test and deploy with a POC or staging environment. Make sure to understand the gears that are running behind the scenes. Understand what happens when the pods, where your SQL container is, die, the data loss acceptable and storage configurations. Here is a good link on SQL internals for containers.
Deploying SQL Container in Azure Kubernetes Cluster
For this tip, I will share how to deploy an empty SQL Server running on Linux to an Azure Kubernetes Cluster. An existing Azure Kubernetes Cluster is required for this tip. If you do not have yet a running Azure Kubernetes Cluster, you can start to create one here.
Once you have a running AKS, connect to it via PowerShell or Azure CLI. To connect to AKS via Azure CLI use the following:
az account set --subscription <subscriptionguid> az aks get-credentials --resource-group <resourcegroupname> --name <kubernetes cluster name>
Also proceed to install kubectl via PowerShell or Azure CLI. Kubectl is the PowerShell command to manage your AKS. To setup kubectl on your server you can do this with these two options below.
Install-Script -Name 'install-kubectl' -Scope CurrentUser -Force
Or via CMD Window if you already have curl:
curl -LO https://dl.k8s.io/release/v1.20.0/bin/windows/amd64/kubectl.exe
First we create the SA password, the SQL login we need to connect to SQL Server after we deploy it. Make sure to make it a bit complex and keep it where you can retrieve for connecting later.
.\kubectl create secret generic mssql --from-literal=SA_PASSWORD=”yourpassword” --namespace=”database”
To validate that our secret mssql password is created, we run the .\kubectl describe secret. The parameter --namespace is optional if you have not created a namespace.
Next we need to create persistent volume. Persistent volumes are independent storage volumes where data is written. When creating a persistent volume, they are like storage pods, that is claimed by your pods. Thus, we need to create a persistent volume claim (pvc). For this tip, we will create a persistent volume claim using Azure disk.
In your local directory, open Notepad, copy the below script and save as pvc.yaml.
kind: StorageClass apiVersion: storage.k8s.io/v1 metadata: name: azure-disk namespace: database provisioner: kubernetes.io/azure-disk parameters: storageaccounttype: Standard_LRS kind: Managed --- kind: PersistentVolumeClaim apiVersion: v1 metadata: name: mssql-data namespace: database annotations: volume.beta.kubernetes.io/storage-class: azure-disk spec: accessModes: - ReadWriteOnce resources: requests: storage: 8Gi
Once you have the file ready, run the below kubectl command.
.\kubectl apply -f pvc.yaml
Verify the pvc with the describe command.
In the Azure portal, the disk is created as below.
On the Azure Kubernetes Cluster, it should appear in the Storage of your AKS.
The next step will prepare the script for the SQL Server deployment. To create the sqldeployment.yaml, open Notepad, and copy the below script and save as sqldeployment.yaml.
apiVersion: apps/v1 kind: Deployment metadata: name: mssql-deployment namespace: database spec: replicas: 1 selector: matchLabels: app: mssql template: metadata: labels: app: mssql spec: terminationGracePeriodSeconds: 30 hostname: mssqlinst securityContext: fsGroup: 10001 containers: - name: mssql image: mcr.microsoft.com/mssql/server:2019-latest ports: - containerPort: 1433 env: - name: MSSQL_PID value: "Developer" - name: ACCEPT_EULA value: "Y" - name: SA_PASSWORD valueFrom: secretKeyRef: name: mssql key: SA_PASSWORD volumeMounts: - name: mssqldb mountPath: /var/opt/mssql volumes: - name: mssqldb persistentVolumeClaim: claimName: mssql-data --- apiVersion: v1 kind: Service metadata: name: mssql-deployment namespace: database spec: selector: app: mssql ports: - protocol: TCP port: 1433 targetPort: 1433 type: LoadBalancer
Once you have created the file, run kubectl to apply the deployment yaml.
.\kubectl apply -f sqldeployment.yaml
To verify that we have the pod created, run the kubectl get pod command. The pod has a status of Running. This status indicates that our SQL Server container is ready.
.\kubectl get pod
On the Azure portal, in the Azure Kubernetes Cluster resource, navigate to the menu for Services and Ingresses. You should be able to see msql-deployment. Our SQL Server service is ready for connections at this point.
In the column for External IP Address (not shown in the image but the column next to Type) note the ExternalIP address for mssql-deployment. The external IP created by the deployment is also created as a public IP address under the same Kubernetes internal resource group.
Use this same ExternalIP to connect the SQL Server via SSMS or Azure Data Studio.
SQL Recovery and Reliability
Suppose in your SQL Server that is running in your AKS, you have the table below and created a couple of rows.
Now to test recovery and reliability for our data, lets delete the pod that mssql-deployment is using.
.\kubectl delete pod mssql-deployment-7cb7b5c689-dt85d
The Kubernetes cluster will see this delete operation and will create a new pod using our msql-deployment.yaml. The persistent volume claim (pvc) created earlier will still exist and attach to the new pod. The operation will be recorded in the Kubernetes Cluster events.
And retaining the exact state of your data when the delete pod operation happened.
At this point, I hope I have helped you understand running SQL Server in containers and applying or deploying them to Azure Kubernetes Cluster. Feel free to leave comments if you have any questions. Happy learning!
- Check out these amazing tips for building SQL Server container images:
About the author
View all my tips
Article Last Updated: 2021-03-09