The example in this article deploys a SQL Server Always On availability group on a Kubernetes cluster with three replicas. The secondary replicas are in synchronous commit mode.

On Kubernetes the deployment includes a SQL Server operator, the SQL Server containers, and load balancer services. The operator orchestrates the availability group automatically. This article explains how to:

• Deploy the operator, SQL Server containers, and load-balancing services
• Connect to the availability group with the services
• Add a database to the availability group

• A Kubernetes cluster

• Kubernetes version 1.11.0 or higher

• At least three nodes

• kubectl.

• Access to the sql-server-samples github repository

  [!NOTE] You can use any type of Kubernetes cluster. To create a Kubernetes cluster on Azure Kubernetes Service (AKS), see Create an AKS cluster. The following script creates a four node Kubernetes cluster in Azure.

  az aks create --resource-group myResourceGroup --name myAKSCluster --node-count 4 --kubernetes-version 1.11.1 --generate-ssh-keys

1. Create a namespace.

This example uses a namespace called ag1. Run the following command to create the namespace.

kubectl create namespace ag1

All objects belonging to this solution are in the ag1 namespace.

1. Configure and deploy the SQL Server operator manifest.

Copy the SQL Server operator.yaml file from sql-server-samples.

The operator.yaml file is the deployment manifest for the Kubernetes operator.

Apply the manifest to the Kubernetes cluster.

kubectl apply -f operator.yaml --namespace ag1

1. Create Kubernetes a secret with passwords for the sa account, and the SQL Server instance master key.

Create the secret with kubectl.

The following example creates a secret named sql-secrets in the ag1 namespace. The secret stores two passwords:

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

Copy the script to your terminal. Replace each <> with a complex password, and run the script to create the secret.

kubectl create secret generic sql-secrets --from-literal=sapassword="<>" --from-literal=masterkeypassword="<>"  --namespace ag1

1. Deploy the SQL Server custom resource.

Copy the SQL Server manifest sqlserver.yaml from sql-server-samples.

Apply the manifest to the Kubernetes cluster.

kubectl apply -f sqlserver.yaml --namespace ag1

The following image shows successful application of kubectl apply for this example.

After you apply the SQL Server manifest, the operator deploys the SQL Server containers.

Kubernetes places the containers in pods. Use kubectl get pods --namespace ag1 to see the status of the pods. The following image shows the example deployment after the SQL Server pods are deployed.

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

Use az aks browse to launch the dashboard.

The ag-services.yaml from sql-server-samples example describes load-balancing services that can connect to availability group replicas.

• ag1-primary provides an endpoint to connect to the primary replica.
• ag1-secondary provides an endpoint to connect to any secondary replica.

When you apply the manifest file, Kubernetes creates the load-balancing services for each type of replica. The load-balancing service includes an IP address. Use this IP address to connect to the type of replica you need.

To deploy the services, run the following command.

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

After you deploy the services, use kubectl get services --namespace ag1 to identify the IP address for the services.

With the IP address, you can connect to the SQL Server instance that hosts each type of replica.

The following image shows:

• The output from kubectl get services for the namespace ag1.

The services the load-balancing services that are created for each SQL Server container. Use these IP addresses as endpoints to connect directly to the instances of SQL Server in the cluster.

• The sqlcmd connection to the primary replica, with the sa account via the load-balancer endpoint.

After Kubernetes creates the SQL Server containers, complete the following steps to add a database to the availability group:

1. Connect to a SQL Server instance in the cluster.

2. Create a database.

CREATE DATABASE [demodb]

1. Take a full backup of the database to start the log chain.

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

1. Add the database to the availability group.

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [demodb]

The availability group is created with automatic seeding so SQL Server will automatically create the secondary replicas.

You can view the state of the availability group from the SQL Server Management Studio Availability Group dashboard.

Connect to SQL Server availability group on Kubernetes cluster

Manage SQL Server availability group on Kubernetes cluster

SQL Server availability group on Kubernetes cluster