Configuring MSSql Database in Kubernetes

2024-01-25
Kubernetes
MSSql
This document will guide you through configuring a MSSql database in a Kubernetes environment. It will explain the deployment steps in Kubernetes and the corresponding configurations for MSSql.

Creating Relevant Configuration Files

Before creating relevant configuration files, it is recommended to create a folder to centrally store these files. Additionally, create a namespace to store these resources to avoid conflicts with other resources.

In this example, I am creating a folder named 'database' and a namespace named 'database,' and I will create the relevant configuration files in this folder.

Copy Successful 
# Create a folder
mkdir database
Copy Successful 
# Create a namespace
kubectl create namespace database

You can also label nodes and specify nodeSelector in the deployment to deploy the application only on specified nodes.

In this example, I am labeling a node with the 'database' label and specifying nodeSelector in the deployment.

Copy Successful 
# Label the node
# <node-name> is the node server name.
kubectl label node <node-name> worker=database

Create the storage.yaml file with the following content:

Copy Successful 
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: database
provisioner: kubernetes.io/no-provisioner
volumeBindingMode: WaitForFirstConsumer
reclaimPolicy: Retain

Create the volume.yaml file with the following content:

Copy Successful 
# <path> is the path to the database storage
# Example: path: "/mnt/disks/data/database"
apiVersion: v1
kind: PersistentVolume
metadata:
  name: database
  labels:
    app: database
spec:
  storageClassName: database
  capacity:
    storage: 100Gi
  accessModes:
    - ReadWriteMany
  hostPath:
    path: <path>
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: database
  namespace: database
  labels:
    app: database
spec:
  storageClassName: database
  accessModes:
    - ReadWriteMany
  resources:
    requests:
      storage: 100Gi

Create the config.yaml file with the following content:

Copy Successful 
# <pid> is the SQL Server version or product key.
# <password> Set your password.
apiVersion: v1
kind: ConfigMap
metadata:
  name: mssql
  namespace: database
  labels:
    app: mssql
data:
  MSSQL_PID: <pid>
  ACCEPT_EULA: "Y"
  MSSQL_SA_PASSWORD: <password>

Create the deployment.yaml file with the following content:

Copy Successful 
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mssql
  namespace: database
  labels:
    app: mssql
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mssql
  template:
    metadata:
      labels:
        app: mssql
    spec:
      nodeSelector:
        worker: database
      containers:
      - name: mssql
        image: mcr.microsoft.com/mssql/server:2022-latest
        ports:
        - containerPort: 1433
        envFrom:
          - configMapRef:
              name: mssql
        volumeMounts:
        - mountPath: /var/opt/mssql
          name: mssql
          subPath: mssql
      volumes:
      - name: mssql
        persistentVolumeClaim:
          claimName: database

Create the service.yaml file with the following content:

Copy Successful 
# For external connectivity, NodePort is used here. If internal connectivity is sufficient, ClusterIP can be used.
apiVersion: v1
kind: Service
metadata:
  name: mssql
  namespace: database
  labels:
    app: mssql
spec:
  type: NodePort
  ports:
    - name: mssql
      port: 1433
      nodePort: 1433
      targetPort: 1433
  selector:
    app: mssql
Copy Successful 
# Deploy storage
kubectl apply -f storage.yaml
Copy Successful 
# Deploy volume
kubectl apply -f volume.yaml
Copy Successful 
# Deploy config
kubectl apply -f config.yaml
Copy Successful 
# Deploy deployment
kubectl apply -f deployment.yaml
Copy Successful 
# Deploy service
kubectl apply -f service.yaml

Possible Scenarios

During the deployment process, you may encounter the following errors that prevent the pod from starting.

Permission denied

You can use the following command to attempt to modify the permissions of the mounted folders.

Copy Successful 
# <folder> is the path to the mounted folder.
chown 10001:10001 <folder>

GCP Firewall Configuration

If you are using GCP and opening connections to the external world, you need to add a rule in the firewall to allow connections on port 1433.

After logging into the GCP platform, navigate to the left-hand menu:

Virtual Private Cloud > Firewall

0

Click the 'Create Firewall Rule' button at the top and add a rule to allow TCP port 1433 connections.

0

Test the Connection

Since NodePort is used in this example, you can connect directly from outside using the node's IP. You can test the connection using commonly used database tools such as DataGrip, Navicat, etc.

The default account is 'sa', and the password is the one you set in config.yaml.