---
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)](http://docs.microsoft.com/azure/aks/). If you don’t have an Azure subscription, create a [free account](https://azure.microsoft.com/free/?WT.mc_id=A261C142F) before you begin.

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

![kubernetes-ag-cluster](media/tutorial-sql-server-ag-containers-kubernetes/KubernetesCluster.png)

### 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](http://kubernetes.io)
  * [Azure Kubernetes Service (AKS)](http://docs.microsoft.com/azure/aks/)
  * [SQL Server on Docker](quickstart-install-connect-docker.md)
  * [SQL Server Always On availability group](../database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server.md)

* A Kubernetes cluster with four nodes. For instructions, refer to [Tutorial: Deploy an Azure Kubernetes Service (AKS) cluster](http://docs.microsoft.com/azure/aks/tutorial-kubernetes-deploy-cluster).

* Install [`kubctl`](https://docs.microsoft.com/en-us/azure/aks/tutorial-kubernetes-deploy-cluster#install-the-kubectl-cli).

## Create storage

To create the storage, create a manifest that describes:

* The [*Storage Class*](http://kubernetes.io/docs/concepts/storage/storage-classes/)
* Three [*Persistent Volume Claims*](http://docs.microsoft.com/azure/aks/azure-disks-dynamic-pv) as Azure disks

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

[!INCLUDE[ag-pvc-manifest](../includes/kubernetes-ag-pvc-yaml.md)]

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

```azure-cli
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`.

```azurecli
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: 

```azurecli
kubectl describe pvc mssql-data-1
```

### Verify the persistent volumes

```azurecli
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.

[!INCLUDE[kubernetes-ag-operator-yaml](../includes/kubernetes-ag-operator-yaml.md)]

Deploy the operator with the `kubectl apply` command.

```azure-cli
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.

```azurecli
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)](sql-server-linux-kubernetes-spec.md).

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

Copy the manifest below into the file.

[!INCLUDE[kubernetes-ag-sql-statefulset-yaml](../includes/kubernetes-ag-sql-statefulset-yaml.md)]

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

```azurecli
kubectl apply -f sqlservers.yaml
```

### Monitor the deployment

You can use [Kubernetes dashboard with Azure Kubernetes Service (AKS)](https://docs.microsoft.com/en-us/azure/aks/kubernetes-dashboard) 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:

![Get service example](media\tutorial-sql-server-ag-containers-kubernetes\KubernetesGetServices.png)

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:

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

![Connect with sqlcmd](media\tutorial-sql-server-ag-containers-kubernetes\sqlcmdConnect.png)

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

```yaml
---
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.

```azurecli
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.

   ```azurecli
   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:

   ```azurecli
   kubectl get pod -o json | jq '.items[] | select(.metadata.annotations["role.ag.mssql.microsoft.com/ag1"] == "primary") | .metadata.name' -r
   ```

3. Delete the pod.

   ```azurecli
   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](http://docs.microsoft.com/en-us/azure/aks/intro-kubernetes)