Kubernetes has become a popular choice for managing containerized applications, and SQL Server 2022 is no exception. This guide will walk you through deploying and managing SQL Server 2022 on Kubernetes, offering examples and screenshots to illustrate the process.

---

🛠️ Prerequisites

Before diving into the deployment, ensure you have the following:

1. Kubernetes Cluster: A running Kubernetes cluster (e.g., Minikube, Azure Kubernetes Service, Amazon EKS).
2. kubectl: The Kubernetes command-line tool, installed and configured.
3. Docker: Installed for container image management.

🏗️ Step-by-Step Deployment

1. Create a Namespace

Namespaces in Kubernetes help organize your resources. Let’s create one for SQL Server:

kubectl create namespace sqlserver

2. Persistent Storage Setup

SQL Server requires persistent storage for data. We’ll use Persistent Volume (PV) and Persistent Volume Claim (PVC).

Persistent Volume (PV) Definition:

apiVersion: v1
kind: PersistentVolume
metadata:
  name: sql-pv
  namespace: sqlserver
spec:
  capacity:
    storage: 20Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: /mnt/sqlserver

Persistent Volume Claim (PVC) Definition:

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: sql-pvc
  namespace: sqlserver
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 20Gi

Apply these configurations:

kubectl apply -f sql-pv.yaml
kubectl apply -f sql-pvc.yaml

3. Deploying SQL Server 2022

Create a Deployment manifest for SQL Server:

Deployment YAML:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: sqlserver-deployment
  namespace: sqlserver
spec:
  replicas: 1
  selector:
    matchLabels:
      app: sqlserver
  template:
    metadata:
      labels:
        app: sqlserver
    spec:
      containers:
      - name: sqlserver
        image: mcr.microsoft.com/mssql/server:2022-latest
        ports:
        - containerPort: 1433
        env:
        - name: ACCEPT_EULA
          value: "Y"
        - name: MSSQL_SA_PASSWORD
          value: "YourStrongPassword!"
        volumeMounts:
        - name: mssql-data
          mountPath: /var/opt/mssql
      volumes:
      - name: mssql-data
        persistentVolumeClaim:
          claimName: sql-pvc

Apply the deployment:

kubectl apply -f sqlserver-deployment.yaml

4. Exposing SQL Server

To access SQL Server externally, create a Service:

Service YAML:

apiVersion: v1
kind: Service
metadata:
  name: sqlserver-service
  namespace: sqlserver
spec:
  type: LoadBalancer
  ports:
  - port: 1433
    targetPort: 1433
  selector:
    app: sqlserver

Apply the service configuration:

kubectl apply -f sqlserver-service.yaml

🔍 Managing SQL Server on Kubernetes

1. Scaling

To scale SQL Server instances, modify the replicas field in the Deployment YAML:

spec:
  replicas: 3

Apply the changes:

kubectl apply -f sqlserver-deployment.yaml

2. Monitoring

Monitor the SQL Server pods and services using kubectl:

kubectl get pods -n sqlserver
kubectl get svc -n sqlserver

For detailed logs:

kubectl logs <pod-name> -n sqlserver

3. Updating SQL Server Image

To update the SQL Server container image, modify the image field in the Deployment YAML and apply the changes:

image: mcr.microsoft.com/mssql/server:2022-latest

kubectl apply -f sqlserver-deployment.yaml

4. Backup and Restore

Backup: Use the sqlcmd tool or any SQL Server Management tool to perform a backup.

Restore: Similarly, use sqlcmd or another tool to restore from a backup.

Example backup command:

BACKUP DATABASE [YourDatabase] TO DISK = '/var/opt/mssql/backup/YourDatabase.bak'

🏁 Conclusion

Deploying and managing SQL Server 2022 on Kubernetes provides flexibility and scalability for your containerized environments. By following the steps outlined in this guide, you can set up SQL Server, scale it, monitor performance, and perform backups and updates with ease.

Kubernetes and SQL Server 2022 together form a powerful combination for modern cloud-native applications. If you have any questions or run into issues, feel free to explore the official documentation or community forums. Happy deploying! 🚀

For more tutorials and tips on SQL Server, including performance tuning and database management, be sure to check out our JBSWiki YouTube channel.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.