tutorial-sql-server-ag-kubernetes.md

title	Configure a SQL Server Always On availability group on Docker containers 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 on Docker containers in Kubernetes for high availability with Azure Kubernetes Service (AKS)

In this tutorial, you learn how to:

[!div class="checklist"]

• Configure storage
• Deploy the SQL Server operator to a Kubernetes cluster
• Deploy SQL Server instances and health agents
• Configure the availability group
• Connect to the primary replica

This tutorial demonstrates the architecture in Azure Kubernetes Service (AKS). If you don’t have an Azure subscription, create a free account before you begin.

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

Deployment methodology for Kubernetes

For each step in this task, you will create a manifest and then deploy the manifest to the cluster. The manifest is a .yaml file with the description of the Kubernetes objects that you deploy. The objects include storage, operators, pods, containers, and services.

Prerequisites

• General familiarity with these technologies

  • Kubernetes
  • Azure Kubernetes Service (AKS)
  • SQL Server on Docker
  • SQL Server Always On availability group

• A Kubernetes cluster with four nodes. For instructions, refer to Tutorial: Deploy an Azure Kubernetes Service (AKS) cluster.

• Install kubctl.

Create storage

To create the storage, create a manifest that describes:

• The Storage Class
• Three Persistent Volume Claims as Azure disks

To create the manifiest for the storage, make a file named pvc.yaml, and copy in the following .yaml code.

[!INCLUDEag-pvc-manifest]

Next, apply the manifest to the Kubernetes cluster. The following example applies the manifest:

kubectl apply -f pvc.yaml

AKS 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

mssql-operator is a Kubernetes operator that deploys the instances of SQL Server and configures the availability group in the Kubernetes cluster. Deploy the operator as a one-replica Kubernetes deployment.

To deploy the operator, create a file named operator.yaml, and copy in the following manifest.

[!INCLUDEkubernetes-ag-operator-yaml]

Deploy the operator with the kubectl apply command.

kubectl apply -f operator.yaml

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.

Create the SQL Server AG Deployment

The next step creates the SQL Server instances and the availability group in one Kubernetes deployment. After you apply this deployment to the cluster, the operator will deploy the SQL Server instances as Docker containers. This deployment will result in three StatefulSets with one pod each. Every pod will include two containers.

• SQL Server instance in a container based on the mssql-server image
• AG agent

In addition, the deployment describes a load balancer service for the availabiltiy group listener

For more information about the contents of the deployment file, see Configure SQL Server specification (YAML).

To create the specification, create a file named sqlservers.yaml.

Copy the manifest below into the file.

[!INCLUDEkubernetes-ag-sql-statefulset-yaml]

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

kubectl apply -f sqlservers.yaml

Monitor the deployment

You can use Kubernetes dashboard with Azure Kubernetes Service (AKS) to monitor the deployment.

Use az aks browse to launch the dashboard.

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.

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

To create the Kubernetes services and for the secondary replicas, run the following command.

kubectl apply -f secondaryListeners.yaml

Verify failure and recovery

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

1. List the pod running SQL Server.

   kubectl get pods
   

2. 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
   

3. 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