title	Configure a SQL Server Always On availability group in Kubernetes for high availability \| Microsoft Docs
description	This tutorial shows how to deploy a SQL Server always on availability group with Kubernetes on Azure Container Service.
author	MikeRayMSFT
ms.author	mikeray
manager	craigg
ms.date	07/16/2018
ms.topic	tutorial
ms.prod	sql
ms.component
ms.suite	sql
ms.custom	sql-linux,mvc
ms.technology	linux

Configure a SQL Server Always On availability group in Kubernetes for high availability

Learn how to configure a SQL Server Always On availability group on Kubernetes in Azure Kubernetes Service (AKS). This solution provides the high availability (HA) and read-scale benefits of a SQL Server Always On availability group with the container orchestration from Kubernetes.

This diagram represents the solution that you will complete with this tutorial:

In this tutorial, you will:

[!div class="checklist"]

Configure storage

Deploy the SQL Server operator to a Kubernetes cluster

Deploy SQL Server instances and health agents

Verify failure and recovery

Before you begin

You will need a Kubernetes cluster with four nodes. For instructions, refer to Tutorial: Deploy an Azure Kubernetes Service (AKS) cluster.

Install kubctl.

Configure

Create storage

In Kubernetes, a persistent volume is a piece of storage in the cluster. A persistent volume claim (PVC) is a request for storage by a user.

In Azure Kubernetes Service (AKS), you create a persistent volume claim to automatically provision storage based on a storage class.

The following section shows how to create the PVC for this example.

Define the storage

Create a file named pvc.yaml, and copy in the following manifest.

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-1
  annotations:
    volume.beta.kubernetes.io/storage-class: azure-disk
spec:
  accessModes:
  - ReadWriteOnce
  resources:
    requests:
      storage: 8Gi
---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: mssql-data-2
  annotations:
    volume.beta.kubernetes.io/storage-class: azure-disk
spec:
  accessModes:
  - ReadWriteOnce
  resources:
    requests:
      storage: 8Gi
---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: mssql-data-3
  annotations:
    volume.beta.kubernetes.io/storage-class: azure-disk
spec:
  accessModes:
  - ReadWriteOnce
  resources:
    requests:
      storage: 8Gi

Create the PVC with the kubectl apply command.

kubectl apply -f pvc.yaml

Kubernetes creates the persistent volumes automatically as Azure managed storage accounts, and binds them to the persistent volume claims.

Verify the persistent volume claim

To see all of the PVCs in a Kubernetes cluster, run kubectl describe pvc.

kubectl describe pvc

In the preceding step, the persistent volume claim is named mssql-data-<x> where <x> is a number. For example, mssql-data-1. To see metadata about the persistent volume claim for the SQL Server instance mssql-data-1, run the following command:

kubectl describe pvc mssql-data-1

Verify the persistent volumes

kubectl describe pv

kubectl returns metadata about the persistent volumes that were automatically created and bound to the persistent volume claims.

Deploy the operator

The operator is deployed as a one-replica Kubernetes deployment.

Create a file named operator.yaml, and copy in the following manifest.

---
apiVersion: v1

kind: ServiceAccount

metadata:
  name: mssql-operator

---
apiVersion: rbac.authorization.k8s.io/v1

kind: ClusterRole

metadata:
  name: mssql-operator

rules:
# operator permissions
- resources: ["configmaps"]
  apiGroups: [""]
  verbs: ["create"]
- resources: ["configmaps"]
  apiGroups: [""]
  resourceNames: ["sql-operator"]
  verbs: ["get", "update"]
- resources: ["customresourcedefinitions"]
  apiGroups: ["apiextensions.k8s.io"]
  verbs: ["create"]
- resources: ["customresourcedefinitions"]
  apiGroups: ["apiextensions.k8s.io"]
  resourceNames: ["sqlservers.mssql.microsoft.com"]
  verbs: ["delete", "get", "update"]

# sqlserver controller permissions
- resources: ["sqlservers"]
  apiGroups: ["mssql.microsoft.com"]
  verbs: ["get", "list", "watch"]
- resources: ["endpoints"]
  apiGroups: [""]
  verbs: ["create", "delete", "get", "update"]
- resources: ["jobs"]
  apiGroups: ["batch"]
  verbs: ["create", "delete", "get", "update"]
- resources: ["roles", "rolebindings"]
  apiGroups: ["rbac.authorization.k8s.io"]
  verbs: ["create", "delete", "get", "update"]
- resources: ["services"]
  apiGroups: [""]
  verbs: ["create", "delete", "update", "get"]
- resources: ["serviceaccounts"]
  apiGroups: [""]
  verbs: ["create", "delete", "update", "get"]
- resources: ["statefulsets"]
  apiGroups: ["apps"]
  verbs: ["create", "delete", "get", "update"]

# k8s-init-sql role permissions; required by operator to be able to create roles with these permissions
- apiGroups: [""]
  resources: ["pods"]
  verbs: ["get", "list"]
- apiGroups: [""]
  resources: ["secrets"]
  verbs: ["create", "get", "update"]

# k8s-health-agent role permissions; required by operator to be able to create roles with these permissions
- apiGroups: [""]
  resources: ["secrets"]
  verbs: ["get"]

# k8s-ag-agent-supervisor role permissions; required by operator to be able to create roles with these permissions
- resources: ["pod"]
  apiGroups: [""]
  verbs: ["get", "update"]

# k8s-ag-agent role permissions; required by operator to be able to create roles with these permissions
- resources: ["configmaps"]
  apiGroups: [""]
  verbs: ["create", "get", "update"]
- resources: ["endpoints"]
  apiGroups: [""]
  verbs: ["get"]
- resources: ["pods"]
  apiGroups: [""]
  verbs: ["list", "update"]

---
apiVersion: rbac.authorization.k8s.io/v1

kind: ClusterRoleBinding

metadata:
  name: mssql-operator

roleRef:
  name: mssql-operator
  apiGroup: rbac.authorization.k8s.io
  kind: ClusterRole

subjects:
- name: mssql-operator
  namespace: default
  kind: ServiceAccount

---
apiVersion: apps/v1beta2

kind: Deployment

metadata:
  name: mssql-operator

spec:
  selector:
    matchLabels:
      app: mssql-operator
  replicas: 1
  template:
    metadata:
      labels:
        app: mssql-operator
    spec:
      serviceAccount: mssql-operator
      containers:
      - name: mssql-operator
        image: private-repo.microsoft.com/mssql-private-preview/mssql-server-k8s-agents
        command: ["/mssql-server-k8s-operator"]
        env:
        - name: MSSQL_K8S_POD_NAMESPACE
          valueFrom:
            fieldRef:
              fieldPath: metadata.namespace
      imagePullSecrets:
      - name: private-registry-key

Create the operator with the kubectl apply command.

kubectl apply -f operator.yaml

Create the SQL Server AG Deployment

The following example illustrates a SQL Server deployment in an AG configuration. This deployment will result in 3 StatefulSets with one pod each. Every pod will include 3 containers, one for each of the following items:

SQL Server instance
Health agent
AG agent

At deployment time, specify values for these parameters (see below sample configuration file):

Instance root volume mount / PVCT
mssql-server container image name
mssql-server-k8s-agents container image name
SA password. If new, set the SA Password. If mounting an existing instance, root.
desired master key password
acceptEula: true to indicate that EULA is accepted

Optionally, provide additional parameters like the example below (or use the specified default values):

health check monitor policy – default is critical errors only (3)
PID (same value as MSSQL_PID env var for regular container) – default is Developer
additional properties of the mssql-server container
additional properties of the pod, like (anti-)affinity
additional properties for the init-sql job pod, like image pull secrets
AG membership list
post-init T-SQL script

After deployment, only AG membership list and post-init T-SQL script can be updated. Other properties cannot be updated - the resource must be deleted and recreated. Credentials for the auto-generated users can be rotated using a mssql-server-k8s-rotate-creds job.

Create the secrets

To create Kubernetes secrets to store the passwords for the SQL Server SA account and the SQL Server master key, run the following command.

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

In a production environment use a different, complex password.

Deploy the availability group

In this step, create a manifest to describe the SQL Server container based on the mssql-server image, the health agent and AG agent containers based on the mssql-server-k8s-agents Docker image.

Create a file named sqlservers.yaml.

Copy the manifest below into the file.

---
apiVersion: mssql.microsoft.com/v1
kind: SqlServer
metadata:
  name: sql-1
  labels:
    type: sqlservr
spec:
  sqlServerContainer:
    image: private-repo.microsoft.com/mssql-private-preview/mssql-server

  saPassword:
    secretKeyRef:
      name: sql-secrets
      key: sapassword

  masterKeyPassword:
    secretKeyRef:
      name: sql-secrets
      key: masterkeypassword

  instanceRootVolume:
    persistentVolumeClaim:
      claimName: mssql-data-1

  agentsContainerImage: private-repo.microsoft.com/mssql-private-preview/mssql-server-k8s-agents

  acceptEula: true
  
  sqlServerPod:
    imagePullSecrets:
    - name: private-registry-key

  initSQLPod:
    imagePullSecrets:
    - name: private-registry-key

  availabilityGroups:
  - ag1

---
apiVersion: mssql.microsoft.com/v1
kind: SqlServer
metadata:
  name: sql-2
  labels:
    type: sqlservr
spec:
  sqlServerContainer:
    image: private-repo.microsoft.com/mssql-private-preview/mssql-server
  saPassword:
    secretKeyRef:
      name: sql-secrets
      key: sapassword

  masterKeyPassword:
    secretKeyRef:
      name: sql-secrets
      key: masterkeypassword

  instanceRootVolume:
    persistentVolumeClaim:
      claimName: mssql-data-2

  agentsContainerImage: private-repo.microsoft.com/mssql-private-preview/mssql-server-k8s-agents

  acceptEula: true

  sqlServerPod:
    imagePullSecrets:
    - name: private-registry-key

  initSQLPod:
    imagePullSecrets:
    - name: private-registry-key

  availabilityGroups:
  - ag1

---
apiVersion: mssql.microsoft.com/v1
kind: SqlServer
metadata:
  name: sql-3
  labels:
    type: sqlservr
spec:
  sqlServerContainer:
    image: private-repo.microsoft.com/mssql-private-preview/mssql-server

  saPassword:
    secretKeyRef:
      name: sql-secrets
      key: sapassword

  masterKeyPassword:
    secretKeyRef:
      name: sql-secrets
      key: masterkeypassword

  instanceRootVolume:
    persistentVolumeClaim:
      claimName: mssql-data-3

  agentsContainerImage: private-repo.microsoft.com/mssql-private-preview/mssql-server-k8s-agents

  acceptEula: true

  sqlServerPod:
    imagePullSecrets:
    - name: private-registry-key

  initSQLPod:
    imagePullSecrets:
    - name: private-registry-key

  availabilityGroups:
  - ag1

---
apiVersion: v1
kind: Service
metadata:
  name: ag1-primary
spec:
  ports:
  - name: tds
    port: 1433
    targetPort: 1433
  selector:
    type: sqlservr
    role.ag.mssql.microsoft.com/ag1: primary
  type: LoadBalancer

To deploy the SQL Server instances and create the availability group, run the following command.

kubectl apply -f sqlservers.yaml

Connect to the SQL Server instance hosting the primary replica

The configuration file also deploys an ag1-primary service that provides a selector that points to the SQL Server instance hosting the primary replica. Use the external IP address of the service as target server, sa account and the password you created earlier in the mssql secret. Use the password that you configured as the Kubernetes secret.

The sqlservers.yaml manifest file defines a load balancer service named ag1-primary that connects to the availability group primary replica. Use kubectl get services to get this IP address.

For example:

In the image above, ag1-primary service has an external IP address of 104.42-50.138. To connect to SQL Server with SQL authentication, use the sa account, the value for sapassword from the secret you created, and this IP address.

For example:

sqlcmd -S 104.42.50.138 -U sa -P "MyC0m9l&xP@ssw0rd"

Create listener services for secondary replicas

To create services for secondary replicas, create a file named listenerServices.yaml.

Copy the manifest below into the file.

---
apiVersion: v1
kind: Service
metadata:
  name: ag1-primary
spec:
  ports:
  - name: tds
    port: 1433
  selector:
    type: sqlservr
    role.ag.mssql.microsoft.com/ag1: primary
  type: LoadBalancer

---
apiVersion: v1
kind: Service
metadata:
  name: ag1-secondary-sync
spec:
  ports:
  - name: tds
    port: 1433
  selector:
    type: sqlservr
    role.ag.mssql.microsoft.com/ag1: secondary-sync
  type: LoadBalancer


---
apiVersion: v1
kind: Service
metadata:
  name: ag1-secondary-async
spec:
  ports:
  - name: tds
    port: 1433
  selector:
    type: sqlservr
    role.ag.mssql.microsoft.com/ag1: secondary-async
  type: LoadBalancer


---
apiVersion: v1
kind: Service
metadata:
  name: ag1-secondary-config
spec:
  ports:
  - name: tds
    port: 1433
  selector:
    type: sqlservr
    role.ag.mssql.microsoft.com/ag1: secondary-config
  type: LoadBalancer

Verify failure and recovery

To verify failure detection and failover you can delete the pod hosting the primary replica. Do the following steps:

List the pod running SQL Server.
```
kubectl get pods
```
Identify the pod running the primary replica.

Either connect to the primary replica using the external IP and query @@servername or use kubectl to get the appropriate pod. This command will return the name of the pod that includes the container running the primary replica of the AG:
```
kubectl get pod -o json | jq '.items[] | select(.metadata.annotations["role.ag.mssql.microsoft.com/ag1"] == "primary") | .metadata.name' -r
```
Delete the pod.
```
kubectl delete pod <podName>
```

Replace <podName> with the value returned from the previous step for pod name.

Kubernetes automatically fails over to one of the available sync secondary replicas as well as recreates the deleted pod.

Summary

Next steps

[!div class="nextstepaction"] Introduction to Kubernetes

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Configure a SQL Server Always On availability group in Kubernetes for high availability

Before you begin

Configure

Create storage

Define the storage

Verify the persistent volume claim

Verify the persistent volumes

Deploy the operator

Create the SQL Server AG Deployment

Create the secrets

Deploy the availability group

Connect to the SQL Server instance hosting the primary replica

Create listener services for secondary replicas

Verify failure and recovery

Summary

Next steps

FilesExpand file tree

tutorial-sql-server-ag-kubernetes.md

Latest commit

History

tutorial-sql-server-ag-kubernetes.md

File metadata and controls

Configure a SQL Server Always On availability group in Kubernetes for high availability

Before you begin

Configure

Create storage

Define the storage

Verify the persistent volume claim

Verify the persistent volumes

Deploy the operator

Create the SQL Server AG Deployment

Create the secrets

Deploy the availability group

Connect to the SQL Server instance hosting the primary replica

Create listener services for secondary replicas

Verify failure and recovery

Summary

Next steps