Running MS SQL Server in Kubernetes on AWS involves leveraging AWS services such as EKS (Elastic Kubernetes Service) for the cluster, EBS (Elastic Block Store) for persistent storage, and other AWS resources for networking, security, and scalability. Here's a step-by-step guide tailored for AWS:

---

Step 1: Set Up AWS EKS

1. Provision EKS Cluster:

   • Use the AWS Management Console, CLI, or tools like Terraform to create an EKS cluster.
   • Example CLI command:

     eksctl create cluster --name mssql-cluster --region us-west-2 --nodes 2 --node-type t3.medium

2. Install Kubernetes CLI Tools:

   • Install kubectl and eksctl to manage the cluster.

3. Configure kubectl:

   • Set up your kubeconfig file for the EKS cluster:

     aws eks --region us-west-2 update-kubeconfig --name mssql-cluster

---

Step 2: Set Up Storage (AWS EBS)

1. Create a Storage Class:

   • Define a StorageClass in Kubernetes to use AWS EBS volumes dynamically.

   StorageClass YAML:

   apiVersion: storage.k8s.io/v1
   kind: StorageClass
   metadata:
     name: ebs-sc
   provisioner: kubernetes.io/aws-ebs
   parameters:
     type: gp2 # General Purpose SSD
     fsType: ext4

   Apply it:

   kubectl apply -f ebs-storage-class.yaml

2. Create a PersistentVolumeClaim (PVC):

   • Use the StorageClass to dynamically provision an EBS volume for MS SQL Server.

   PVC YAML:

   apiVersion: v1
   kind: PersistentVolumeClaim
   metadata:
     name: mssql-data-pvc
   spec:
     storageClassName: ebs-sc
     accessModes:
       - ReadWriteOnce
     resources:
       requests:
         storage: 50Gi

   Apply it:

   kubectl apply -f pvc.yaml

---

Step 3: Deploy MS SQL Server StatefulSet

1. Create a Kubernetes Secret for SA Password:

   • Store the SA password securely using Kubernetes Secrets.

   Secret Command:

   kubectl create secret generic mssql-secret --from-literal=SA_PASSWORD='YourSecurePassword123!'

2. Define the StatefulSet:

   • Use a StatefulSet to manage MS SQL Server pods.

   StatefulSet YAML:

   apiVersion: apps/v1
   kind: StatefulSet
   metadata:
     name: mssql
   spec:
     serviceName: "mssql-service"
     replicas: 1
     selector:
       matchLabels:
         app: mssql
     template:
       metadata:
         labels:
           app: mssql
       spec:
         containers:
         - name: mssql
           image: mcr.microsoft.com/mssql/server:2019-latest
           ports:
           - containerPort: 1433
           env:
           - name: ACCEPT_EULA
             value: "Y"
           - name: SA_PASSWORD
             valueFrom:
               secretKeyRef:
                 name: mssql-secret
                 key: SA_PASSWORD
           volumeMounts:
           - name: mssql-data
             mountPath: /var/opt/mssql
     volumeClaimTemplates:
     - metadata:
         name: mssql-data
       spec:
         accessModes: ["ReadWriteOnce"]
         resources:
           requests:
             storage: 50Gi

   Apply it:

   kubectl apply -f statefulset.yaml

---

Step 4: Expose MS SQL Server

• Use a ClusterIP Service to expose MS SQL Server within the Kubernetes cluster.

Service YAML:

apiVersion: v1
kind: Service
metadata:
  name: mssql-service
spec:
  type: ClusterIP
  selector:
    app: mssql
  ports:
  - protocol: TCP
    port: 1433
    targetPort: 1433

Apply it:

kubectl apply -f service.yaml

• The MS SQL Server is now accessible internally at mssql-service:1433.

---

Step 5: Configure External Access (Optional)

If you need to access MS SQL Server from outside the cluster, use a LoadBalancer Service.

LoadBalancer Service YAML:

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

Apply it:

kubectl apply -f loadbalancer.yaml

AWS will provision an external IP address that you can use to connect to MS SQL Server.

---

Step 6: Set Up Backups

• Use EBS Snapshots to back up MS SQL Server data.
• Automate periodic snapshots using AWS Lambda or scheduled tasks.

Example Snapshot Command:

aws ec2 create-snapshot --volume-id <volume-id> --description "SQL Server Backup"

---

Step 7: Monitor and Scale

1. Monitoring:

   • Use CloudWatch and Prometheus to monitor resource usage and SQL Server metrics.

2. Scaling:

   • MS SQL Server typically does not scale horizontally. Use vertical scaling by resizing the EBS volume or increasing pod resources (CPU/RAM).

---

Step 8: Secure the Deployment

1. Restrict Access:

   • Use Network Policies to limit access to the MS SQL Server pod.
   • Example Policy: Only allow backend pods to connect to MS SQL Server.

   apiVersion: networking.k8s.io/v1
   kind: NetworkPolicy
   metadata:
     name: allow-backend-to-mssql
   spec:
     podSelector:
       matchLabels:
         app: mssql
     ingress:
     - from:
       - podSelector:
           matchLabels:
             app: backend
       ports:
       - protocol: TCP
         port: 1433

2. Encrypt Communication:

   • Enable TLS between the application and SQL Server.

---

Summary

By following these steps on AWS, you'll have a production-grade deployment of MS SQL Server in Kubernetes with:

• Dynamic storage using AWS EBS.
• Security with Secrets and Network Policies.
• High availability using StatefulSets.
• Scalability and monitoring using Kubernetes and AWS tools.