SQL Server for Linux on Azure Kubernetes Services

Ishan jain July 27, 2019 09:01

Running a single instance of SQL might not what you want, but it is a start. And you still have Hight Availability.

Disclaimer: Currently I am employed by Microsoft, but my views and thoughts are still my own. The reason I joined Microsoft was, the work Microsoft have been doing for last couple of years in Open Source Space. Today I am a advocate for Open Source representing Microsoft.

In my last post I talked about how to run SQL Server in ACI. ACI is good, if you need a Database for a single and small application, but in enterprise you might several instances of it.

Prerequisites

Assuming you have:

NOTE: You can use any Kubernetes cluster for running SQL Server, this post is not limited to just running on AKS.

This post builds on top of my previous post about running SQL Server in ACI

Create a secret for SA Password

kubectl create secret generic mssql --from-literal=SA_PASSWORD="MyC0m9l&xP@ssw0rd" --namespase mssql

Create a Persistent Storage

Copy the following to pvc.yaml:

kind: StorageClass
apiVersion: storage.k8s.io/v1beta1
metadata:
     name: azure-disk
provisioner: kubernetes.io/azure-disk
parameters:
  storageaccounttype: Standard_LRS
  kind: Managed
---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: mssql-data
  annotations:
    volume.beta.kubernetes.io/storage-class: azure-disk
spec:
  accessModes:
  - ReadWriteOnce
  resources:
    requests:
      storage: 8Gi

Deploy the PVC to AKS:

kubectl apply -f pvc.yaml --namespace mssql

Deploy SQL to AKS

Copy the following to mssql.yaml:

apiVersion: apps/v1beta1
kind: Deployment
metadata:
  name: mssql-deployment
spec:
  replicas: 1
  template:
    metadata:
      labels:
        app: mssql
    spec:
      terminationGracePeriodSeconds: 10
      containers:
      - name: mssql
        image: mcr.microsoft.com/mssql/server:2017-latest
        ports:
        - containerPort: 1433
        env:
        - name: MSSQL_PID
          value: "Express"
        - name: ACCEPT_EULA
          value: "Y"
        - name: MSSQL_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
spec:
  selector:
    app: mssql
  ports:
    - protocol: TCP
      port: 1433
      targetPort: 1433
  type: LoadBalancer

Deploy the SQL Server to AKS:

kubectl apply -f mssql.yaml --namespace mssql

This will deploy a SQL Server 2017 Express to AKS.

Get external IP

Now we can ask AKS for the external IP to the mssql service:

kubectl get services --namespace mssql

NOTE: It can take sometime to get the external IP, just run the above command again if kubectl return <pending> after a minute or two.

Now you can see the external IP:

NAME              TYPE          CLUSTER-IP   EXTERNAL-IP    PORT(S)       AGE
mssql-deployment  LoadBalancer  10.0.192.33  <EXTERNAL-IP>  80:31257/TCP  53s

Connect to SQL Server

I usually prefer to use SQL Server Management Studio, but you can also use sqlcmd, Visual Studio or any other SQL Server Client.

You can access SQL Server using sqlcmd with:

sqlcmd -S "{EXTERNAL-IP}" -U sa -P "MyC0m9l&xP@ssw0rd"

Failure and Recovery

Kubernetes will recover a failed instance of container.

Get the list of running pods:

kubectl get pods --namespace mssql

It will return a list of running pods in the namespace:

NAME                                READY   STATUS    RESTARTS   AGE
mssql-deployment-5b74bdb6f7-c6z5b   1/1     Running   0          3d20h

You can kill the running pod with:

kubectl delete pod mssql-deployment-0

You can verify the pod has been recreated by running:

kubectl get pods --namespace mssql

The output should return something like:

NAME                                READY   STATUS    RESTARTS   AGE
mssql-deployment-5b74bdb6f7-hvkwq   1/1     Running   0          3d20h