SQL Server Always On availability group on a AKS cluster

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.

NOTE: You might need change the tags for SQL Server images in operator.yaml and sqlserver.yaml. At time of writing 2019-CTP3.2 was the latest version, this version of SQL Server might not work in future.

Prerequisites

Assuming you have:

• Azure Subscription
• Azure CLI installed
• Logged in to Azure CLI
• Created a Resource Group for AKS cluster
  • Make sure you have at least 3 worker nodes (3 worker + 1 operator = 4 nodes)
• Deployed a AKS cluster
• Created a NameSpace for SQL Server Cluster
  • In this post I am using ag1, make the changes to accordingly if needed.

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 single instance of SQL Server on AKS

Deploy a Operator for SQL Server Cluster

Copy the following to operator.yaml:

apiVersion: v1
kind: Namespace
metadata: {name: ag1}
---
apiVersion: v1
kind: ServiceAccount
metadata: {name: mssql-operator, namespace: ag1}
---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRole
metadata: {name: mssql-operator-ag1}
rules:
- apiGroups: ['']
  resources: [serviceaccounts, services]
  verbs: [create, get, update, delete]
- apiGroups: [batch]
  resources: [jobs]
  verbs: [create, get, update, delete]
- apiGroups: [rbac.authorization.k8s.io]
  resources: [roles, rolebindings]
  verbs: [create, get, update, delete]
- apiGroups: [apps]
  resources: [statefulsets]
  verbs: [create, delete, get, update]
- apiGroups: ['']
  resources: [configmaps, endpoints, secrets]
  verbs: [create, get, update, watch, delete]
- apiGroups: ['']
  resources: [pods]
  verbs: [get, list, update]
- apiGroups: [apiextensions.k8s.io]
  resources: [customresourcedefinitions]
  verbs: [create]
- apiGroups: [apiextensions.k8s.io]
  resourceNames: [sqlservers.mssql.microsoft.com]
  resources: [customresourcedefinitions]
  verbs: [delete, get, update]
- apiGroups: [mssql.microsoft.com]
  resources: [sqlservers]
  verbs: [get, list, watch]
---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRoleBinding
metadata: {name: mssql-operator-ag1}
roleRef: {apiGroup: rbac.authorization.k8s.io, kind: ClusterRole, name: mssql-operator-ag1}
subjects:
- {kind: ServiceAccount, name: mssql-operator, namespace: ag1}
---
apiVersion: apps/v1beta2
kind: Deployment
metadata: {name: mssql-operator, namespace: ag1}
spec:
  replicas: 1
  selector:
    matchLabels: {app: mssql-operator}
  template:
    metadata:
      labels: {app: mssql-operator}
    spec:
      containers:
      - command: [/mssql-server-k8s-operator]
        env:
        - name: MSSQL_K8S_NAMESPACE
          valueFrom:
            fieldRef: {fieldPath: metadata.namespace}
        image: mcr.microsoft.com/mssql/ha:2019-CTP3.2-ubuntu
        name: mssql-operator
      serviceAccount: mssql-operator

Deploy the operator to Kubernetes:

kubectl apply -f operator.yaml --namespace ag1

Create secretes

We will need to create two secrets:

• sapassword password for the SQL Server sa account
• masterkeypassword password used to create the SQL Server master key

You can create the secrets with:

kubectl create secret generic sql-secrets --from-literal=sapassword="MyC0m9l&xP@ssw0rd" --from-literal=masterkeypassword="MyC0m9l&xP@ssw0rd" --namespace ag1

Deploy SQL Server pods

Copy the following to sqlserver.yaml:

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-CTP3.2-ubuntu
  availabilityGroups: [ag1]
  instanceRootVolumeClaimTemplate:
    accessModes: [ReadWriteOnce]
    resources:
      requests: {storage: 5Gi}
    storageClass: default
  saPassword:
    secretKeyRef: {key: sapassword, name: sql-secrets}
  sqlServerContainer: {image: 'mcr.microsoft.com/mssql/server:2019-CTP3.2-ubuntu'}
---
apiVersion: v1
kind: Service
metadata: {name: mssql1, namespace: ag1}
spec:
  ports:
  - {name: tds, port: 1433}
  selector: {name: mssql1, type: sqlservr}
  type: LoadBalancer
---
apiVersion: mssql.microsoft.com/v1
kind: SqlServer
metadata:
  labels: {name: mssql2, type: sqlservr}
  name: mssql2
  namespace: ag1
spec:
  acceptEula: true
  agentsContainerImage: mcr.microsoft.com/mssql/ha:2019-CTP3.2-ubuntu
  availabilityGroups: [ag1]
  instanceRootVolumeClaimTemplate:
    accessModes: [ReadWriteOnce]
    resources:
      requests: {storage: 5Gi}
    storageClass: default
  saPassword:
    secretKeyRef: {key: sapassword, name: sql-secrets}
  sqlServerContainer: {image: 'mcr.microsoft.com/mssql/server:2019-CTP3.2-ubuntu'}
---
apiVersion: v1
kind: Service
metadata: {name: mssql2, namespace: ag1}
spec:
  ports:
  - {name: tds, port: 1433}
  selector: {name: mssql2, type: sqlservr}
  type: LoadBalancer
---
apiVersion: mssql.microsoft.com/v1
kind: SqlServer
metadata:
  labels: {name: mssql3, type: sqlservr}
  name: mssql3
  namespace: ag1
spec:
  acceptEula: true
  agentsContainerImage: mcr.microsoft.com/mssql/ha:2019-CTP3.2-ubuntu
  availabilityGroups: [ag1]
  instanceRootVolumeClaimTemplate:
    accessModes: [ReadWriteOnce]
    resources:
      requests: {storage: 5Gi}
    storageClass: default
  saPassword:
    secretKeyRef: {key: sapassword, name: sql-secrets}
  sqlServerContainer: {image: 'mcr.microsoft.com/mssql/server:2019-CTP3.2-ubuntu'}
---
apiVersion: v1
kind: Service
metadata: {name: mssql3, namespace: ag1}
spec:
  ports:
  - {name: tds, port: 1433}
  selector: {name: mssql3, type: sqlservr}
  type: LoadBalancer

Deploy the pods with:

kubectl apply -f sqlserver.yaml --namespace ag1

You can verify if the deployment was successful with:

kubectl get pods --namespace ag1

Deploy the Application Group Service

Copy the following to ag-services.yaml:

apiVersion: v1
kind: Service
metadata: {annotations: null, name: ag1-primary, namespace: ag1}
spec:
  ports:
  - {name: tds, port: 1433, targetPort: 1433}
  selector: {role.ag.mssql.microsoft.com/ag1: primary, type: sqlservr}
  type: LoadBalancer
---
apiVersion: v1
kind: Service
metadata: {annotations: null, name: ag1-secondary, namespace: ag1}
spec:
  ports:
  - {name: tds, port: 1433}
  selector: {role.ag.mssql.microsoft.com/ag1: secondary,
    type: sqlservr}
  type: LoadBalancer

Deploy the services with:

kubectl apply -f ag-services.yaml --namespace ag1

You can verify if the deployment was successful with:

kubectl get services --namespace ag1

In the output you will also see the EXTERNAL-IP for the LoadBalancer.

Connect to the Availability Group

Use the ag1-primary to connect to primary replica.

sqlcmd -S "{EXTERNAL-IP}" -U sa -P "{SA-PASSWORD}"

Add a database to Availability Group

In the sqlcmd create a new database with:

CREATE DATABASE [demodb]

Before you can add the database to availability group you need to create a full backup:

USE MASTER
GO
BACKUP DATABASE [demodb] 
TO DISK = N'/var/opt/mssql/data/demodb.bak'

Add the database to availability group with:

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [demodb]