Kubernetes allows you to manage, automate, scale, and deploy containerized workloads. However, all applications are not the same regarding deployment as containers. Some applications do not save permanent data and may only store temporary data during their run. These applications are called stateless applications. One of the main advantages of these applications is that they can be scaled without any issue. Also, data flow is typically transitory and is not required to be maintained. A stateful application is an application that requires persistent storage to store its data, such as if it restarts, it can regain its previous state and identity. There are two different ways to deploy the stateful application pods on Kubernetes as follows:

• StatefulSet—SatefulSet is a method to deploy a workload by maintaining the state of the application. SatefulSet keeps a unique identity for each pod it manages. It uses the same identity whenever it needs to reschedule or restart the same pods.
• Deployments—Kubernetes deployments provide a way to manage a set of pods that are running in more than one container, or a group of duplicate pods knows as ReplicaSets, which allows running multiple pods with the same configuration. 

Microsoft SQL Server is a stateful application, which means that the data is stored on persistent volumes. This reference architecture deploys a Microsoft SQL Server using the deployment method. This deployment hosts a single container image in the pod and deploys a single ReplicaSet. SQL pods are deployed across eight worker nodes. Each node hosts two SQL Server pods. Each pod hosts a single container image of the SQL Server 2019 instance and the data is stored on the persistent volume that is provisioned by the PowerFlex CSI plug-in.

The following table shows the SQL Server pod deployment on an eight node Kubernetes cluster:

Table 6.   SQL Server pods

You can create a deployment using a manifest file containing the following components:

• SQL Server pod configuration
• Persistent volume details
• Service details 

SQL Server pod configuration

 The following code of the deployment manifest file shows the SQL Server pod details containing the resource limits and the container image details:

apiVersion: apps/v1

kind: Deployment

metadata:

name: sql4

spec:

replicas: 1

selector:

matchLabels:

app: mssql

template:

metadata:

labels:

app: mssql

spec:

terminationGracePeriodSeconds: 30

hostname: sql4

securityContext:

fsGroup: 10001

nodeSelector:

host: node1

containers:

command:

- /bin/bash

- -c

- cp /var/opt/config/mssql.conf /var/opt/mssql/mssql.conf && /opt/mssql/bin/sqlservr

image: mcr.microsoft.com/mssql/rhel/server:2019-latest

resources:

limits:

memory: 32Gi

cpu: 16000m

ports:

- containerPort: 1433

env:

- name: MSSQL_PID

value: "Developer"

- name: ACCEPT_EULA

value: "Y"

- name: SA_PASSWORD

valueFrom:

secretKeyRef:

name: mssql

key: SA_PASSWORD

In this deployment, both the name of the pod and hostname is same, named as sql14. This naming is known as labeling and used to quickly identify the pods. The next part of this deployment is the node selector. The node selector decides the Kubernetes node selection for the pods to be deployed. In the preceding figure, sql14 pod is hosted on node5.

The next section of the deployment specifies the command to the pods to start the SQL Server process with the configuration file. The SQL Server configuration file (mssql.conf) is uploaded to the container at path /var/opt/config through configmaps and gets copied to the /var/opt/mssql/mssql.conf path upon pod initialization. This can be seen in the command section of the deployment file. For more information on the SQL configuration file, see Appendix B.

The latest image of the SQL Server container image is pulled from the local private registry. This reference architecture uses a local private Docker registry to store a customized container image that is copied to each pod. The default image is initially pulled from the Microsoft public registry and customized to accommodate Dell Technologies best practices for deploying Microsoft SQL server 2019.

The deployment manifest specifies the resource limit specification for the pods. The pods are restricted to use only 32 GB of memory from the total available memory of the nodes. Similarly, pods are restricted to use only 16 CPU cores from the total available logical CPU of the nodes. Worker Nodes are scaled out to host 3 pods utilizing 48 CPU cores and 129 GB memory of the host.

Another deployment configures the pod to use only 32 CPU core and 32 GB of memory. In this deployment model nodes are scaled up to host two pods utilizing 64 CPU core and 64 GB of the hosts memory. For more information about the node configuration details and Deployment file, see Appendix A.

You must declare the environment variables in the manifest file specifying the SQL Server version details and the login credentials. Use the Kubernetes secret object to store the SQL Server login details. The secret object stores the sensitive information such as username, password, keys, and tokens in an encrypted format. The preceding manifest file calls the secret file that is deployed on the cluster to authenticate the user login into the SQL Server. Use the following command to create the secret file:

kubectl create secret generic mssql –from literal=SA_PASSWORD="XX"
 

Persistent volume specification

The following codes from the deployment manifest file shows the persistent volume details of the SQL Server pod.

As shown in the following code, the deployment manifest file defines three mount points of the SQL pod14 - mssqldb14, mssqltemp14, and mssqllogs14. Deployment files also declare the persistent volume claim for the defined mount points. They are respectively data-sql14, data-logs14, and data-temp14.

kind: PersistentVolumeClaim
apiVersion: v1
metadata:
name: data-sql14
namespace: vxflexos
spec:
accessModes:
- ReadWriteOnce
volumeMode: Filesystem
resources:
requests:
storage: 304Gi
 storageClassName: test-vxflexos-vxflexos-xfs

---

kind: PersistentVolumeClaim
apiVersion: v1
metadata:
name: data-logs14
namespace: vxflexos
spec:
accessModes:
- ReadWriteOnce
volumeMode: Filesystem
resources:
requests:
storage: 200Gi
 storageClassName: test-vxflexos-vxflexos-xfs

---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
name: data-temp14
namespace: vxflexos
spec:
accessModes:
- ReadWriteOnce
volumeMode: Filesystem
resources:
requests:
storage: 300Gi
storageClassName: test-vxflexos-vxflexos-xfs

As shown in the following code, the deployment manifest file displays the persistent volume claim for the pods. It specifies the capacity details, access modes, volume name, and the supporting storage class for the volume. 

volumeMounts:
- name: mssqldb14
mountPath: /var/opt/mssql/data
- name: mssqllogs14
mountPath: /var/opt/mssqlogs/logs
- name: mssql-config-volume
mountPath: /var/opt/config
- name: mssqltemp14
mountPath: /var/opt/mssqltemp/tempdb
volumes:
- name: mssqldb14
persistentVolumeClaim:
claimName: data-sql14
- name: mssqllogs14
persistentVolumeClaim:
claimName: data-logs14
- name: mssqltemp14
persistentVolumeClaim:
claimName: data-temp14
- name: mssql-config-volume
configMap:
name: mssql-config

This deployment manifest file instructs the Kubernetes cluster to communicate with the PowerFlex storage through the CSI plug-in to provision the volume. The PowerFlex CSI plug-in communicates with the PowerFlex Storage to create the specified volumes and present them to the pod.

Service configuration

Before running the deployment, service details are required to access the deployed application. Kubernetes allows access to the deployed application with a service object. The service is responsible for exposing an interface to the pods with the help of a network port. The following code shows the service definition in the deployment to access the SQL Server pod:

apiVersion: v1
kind: Service
metadata:
name: sql14
spec:
selector:
app: mssql
ports:
- protocol: TCP
port: 1433
targetPort: 1433
nodePort: 30020
type: NodePort

There are three types of Kubernetes services:

• ClusterIP—Exposes the service, which is only accessible within the cluster
• NodePort—Exposes the service using a static port of each node with the help of node IP.
• LoadBalancer—Exposes the service with the load balancer of the cloud provider.
• ExternalName—Maps the service to the predefined externalName field identified by DNS.

In this deployment example, the NodePort service is used to expose an interface to the SQL Server pod hosted on the node. The SQL Server container is using port 1433 inside the pod and the deployment exposes port 30020 of the node to access the pod. Kubernetes manages port forwarding internally.