SQL Server 2019 Availability Groups with Kubernetes

Microsoft first introduced SQL Server on Linux with their 2017 version and has only scraped the surface of many new features to come.  Today we are looking at SQL Server 2019 HA Always on Availability Groups (AG) within a Kubernetes cluster.  This article will assume you have some experience with Kubernetes containers and configuration.

The magic starts with SQL Server 2019 High Availability Operator which provides the components for deployment and management of the group. This operator defines and registers all custom resources and registers itself as a listener ingesting notifications in the Kubernetes cluster.  As you create or update resources in the cluster the operator receives a notification which then interprets and creates or modifies the corresponding object as needed.

The Operator is built from a base Docker image that contains the all the necessary binaries.

Mssql-operator: this binary outfits the Kubernetes operator for the Availability Groups in SQL Server

Mssql-ha-supervisor: outfits the health detection and management logic that will determine the primary replica for the Availability Group. This election is based on the Kubernetes client leader election.

Mssql-server-k8s-health-agent: outfits the logic to determine the health of SQL Server instance.

Mssql-server-k8s-init-sql: outfits the logic for deployment of a desired state configuration to SQL Server instance.

The operator basically instructs the creation of other objects and acts as the coordinator in this process.

 

Enough talk, Lets Give it a Go.

Heads up! If you would like to follow along in this demo, you will need the following before starting.

Docker, Kubernetes, Yaml files

Create the namespace
kubectl create namespace ag1

Kubernetes Namespace

Operator
kubectl apply -f operator.yaml –namespace ag1

Create the secrets for sql server
kubectl create secret generic sql-secrets –from-literal=sapassword=”” –from-literal=masterkeypassword=”” –namespace ag1

Kubernetes Secrets

SQL Server Containers
kubectl apply -f sqlserver2019.yaml –namespace ag1

Load Balance Service
kubectl apply -f ag-service.yaml –namespace ag1

Check to see if the service exists and look at the IPAddresses
kubectl get services

Time to add the database to the AG.

Connect to the primary and add database
Sqlcmd -S [IPADDRESS] -U sa -P “”
CREATE DATABASE [dbShedDemoAG]
GO
BACKUP DATABASE [dbShedDemoAG] TO DISK = N’/var/opt/mssql/data/dbShedDemoAG.bak’
GO
ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [dbShedDemoAG]
GO

Now that we have a db configured in the AG, lets fail it over to the secondary.

Lets deploy the job that will demote the primary replica and promote the specified replica to primary.
kubectl apply -f failover.yaml

Verify the failover
kubectl describe jobs/manual-failover –namespace ag1

 

Warning! You must remove the job before you execute a failover again.  If not, you will receive an error.  Let’s clean this up now.

Kubernetes Jobs

kubectl delete jobs manual-failover –namespace ag1

 

Summary

Playing with SQL Servers Availability Groups and Kubernetes can be fun and challenging at the same time.  This example walked you through how to stand up a cluster and configure Availability Groups using PowerShell.  We dabbled with sqlcmd to create, backup and configure the database in AG, then finished it off by failing over to the secondary replica and calling it a day.   

As I indicated above, we will see a lot more features rolling out using Kubernetes and SQL Server.  Until then, keep learning.