SqlServer On Kubernetes Cluster not able to deploy

9/25/2019

Im trying to deploy a SQLServer Always on into a kubernetes cluster on premise with bases os ubuntu server 19 latest patch

Here is Setup

Kubeadm v1.16.0

Docker 18.09.7

Nodes
NAME          STATUS   ROLES    AGE     VERSION
master-node   Ready    master   6d19h   v1.16.0
slave-node1   Ready    <none>   6d18h   v1.16.0
slave-node2   Ready    <none>   6d19h   v1.16.0

[Version Table][1]

Im unable to deploy the sqlserver pods i dont get an error but no pod is deployed, when i check my pvc it is not binded yet it says waiting for first consumer to be created before binding.

kubectl describe pvc mssql-data1-claim -n ag1
Name:          mssql-data1-claim
Namespace:     ag1
StorageClass:  local-storage
Status:        Pending
Volume:
Labels:        <none>
Annotations:   kubectl.kubernetes.io/last-applied-configuration:
                 {"apiVersion":"v1","kind":"PersistentVolumeClaim","metadata":{"annotations":{},"name":"mssql-data1-claim","namespace":"ag1"},"spec":{"acce...
Finalizers:    [kubernetes.io/pvc-protection]
Capacity:
Access Modes:
VolumeMode:    Filesystem
Mounted By:    <none>
Events:
  Type    Reason                Age                  From                         Message
  ----    ------                ----                 ----                         -------
  Normal  WaitForFirstConsumer  109s (x42 over 12m)  persistentvolume-controller  waiting for first consumer to be created before binding

Can you please point me into what im doing wrong im new to the Kubernetes and currently learning. All help is appreciated

This is the YAML definition im using

Storage Class

apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: local-storage
provisioner: kubernetes.io/no-provisioner
reclaimPolicy: Retain
allowVolumeExpansion: true
volumeBindingMode: WaitForFirstConsumer

PV and PV Claim

kind: PersistentVolume
apiVersion: v1
metadata:
  name: ag1-pv-volume-node1
  labels:
    type: local
spec:
  storageClassName: default
  capacity:
    storage: 3Gi
  accessModes:
    - ReadWriteOnce
  local:
    path: "/var/opt/mssql"
  nodeAffinity:
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - slave-node1
---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: mssql-data1-claim
  namespace: ag1
spec:
  storageClassName: default
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 3Gi
  selector:
    matchLabels:
      name: ag1-pv-volume-node1

Operator File

The one from microsoft https://github.com/Microsoft/sql-server-samples/blob/master/samples/features/high%20availability/Kubernetes/sample-manifest-files/operator.yaml

SqlServer Deploy

apiVersion: mssql.microsoft.com/v1
kind: SqlServer
metadata:
  labels: {name: mssql1, type: sqlservr}
  name: mssql1
  namespace: ag1
spec:
  acceptEula: true
  agentsContainerImage: mcr.microsoft.com/mssql/ha:2019-CTP2.1-ubuntu
  availabilityGroups: [ag1]
  instanceRootVolumeClaimTemplate:
    accessModes: [ReadWriteOnce]
    resources:
      requests: {storage: 3Gi}
    storageClass: default
  saPassword:
    secretKeyRef: {key: sapassword, name: sql-secrets}
  sqlServerContainer: {image: 'mcr.microsoft.com/mssql/server:2019-CTP2.1-ubuntu'}
  volumes:
    - name: sql-server-storage1
      persistentVolumeClaim:
        claimName: mssql-data1-claim
  nodeAffinity:
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - slave-node1
---
apiVersion: v1
kind: Service
metadata: {name: mssql1, namespace: ag1}
spec:
  ports:
  - {name: tds, port: 1433}
  selector: {name: mssql1, type: sqlservr}
  type: NodePort

Here is the Result of the kubectl get pods -A

NAMESPACE              NAME                                         READY   STATUS    RESTARTS   AGE
ag1                    mssql-operator-5c85589dfb-j2t6f              1/1     Running   0          3d23h
kube-system            coredns-5644d7b6d9-dh9fg                     1/1     Running   2          6d20h
kube-system            coredns-5644d7b6d9-p84nl                     1/1     Running   2          6d20h
kube-system            etcd-master-node                             1/1     Running   2          6d19h
kube-system            kube-apiserver-master-node                   1/1     Running   3          6d19h
kube-system            kube-controller-manager-master-node          1/1     Running   5          6d19h
kube-system            kube-flannel-ds-amd64-cpsf9                  1/1     Running   1          6d19h
kube-system            kube-flannel-ds-amd64-d5sj4                  1/1     Running   2          6d18h
kube-system            kube-flannel-ds-amd64-jg6pd                  1/1     Running   2          6d19h
kube-system            kube-proxy-2cq5m                             1/1     Running   2          6d20h
kube-system            kube-proxy-8rc4m                             1/1     Running   1          6d19h
kube-system            kube-proxy-rh27f                             1/1     Running   1          6d18h
kube-system            kube-scheduler-master-node                   1/1     Running   4          6d19h
kubernetes-dashboard   dashboard-metrics-scraper-566cddb686-dmns8   1/1     Running   1          6d18h
kubernetes-dashboard   kubernetes-dashboard-7b5bf5d559-6nqsm        1/1     Running   7          6d18h
-- Ricardo Granados
kubernetes
sql-server-2019

1 Answer

10/23/2019

Welcome on StackOverflow @Ricardo,

From my own experience there are few things that you need to sort out first in order to make HA MSSQL Server working with 'mssql-operator' on Linux workers w/o making big changes in 'deploy-ag.py' script.

I'm assuming that you are running './deploy-ag.py deploy' in --dry-run mode, so that you have a chance to adjust some manifests before applying them with kubectl.

  1. Ensure that 'PVC' can bound to previously created 'PV' of 'local' type

    • Especially make sure that 'storageClassName' defined in PVC matches the one in 'PV' (I specified it first manually in the 'Kubernetes/sample-deployment-script/templates/pvc.yaml' before running the script), e.g.

PV_1.yaml

apiVersion: v1
kind: PersistentVolume
metadata:
  labels: {storage: ag1}
  name: ag1-mssql1-pv
spec:
  accessModes: [ReadWriteOnce]
  capacity: {storage: 2Gi}
  local:
    path: "/mnt/data"
  storageClassName: gp2
  nodeAffinity:
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - node-1.region.compute.internal
  1. Adjust sqlServerContainer

The code samples on github contains outdated version of sqlServerContainer image, which results in following error msg. on sqlservr process startup:

Error: The evaluation period has expired. This program has encountered a fatal error and cannot continue running.

Adjust the following in 'Kubernetes/sample-deployment-script/templates/sqlserver.yaml' template:

<!Before>

  sqlServerContainer:
    image: mcr.microsoft.com/mssql/server:2019-CTP2.1-ubuntu

 <!After> 

  sqlServerContainer:
    image: mcr.microsoft.com/mssql/server:2019-CTP3.2-ubuntu

At this point you should be able to talk to your HA-SQL Server instance

Quick verification procedure with command:

kubectl exec po/mssql1-0 -c mssql-server -n ag1 -- /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <your password> -Q "SELECT name FROM sys.databases

Expected output:

name

-------- master tempdb
model
msdb

(4 rows affected)

-- Nepomucen
Source: StackOverflow