(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

In my previous blog posting I have talked about how to create a SQL Server 2019 Availability Group in Kubernetes. Today I want to continue with that discussion, and I want to show you how you can perform a Failover of such an Availability Group.

Performing a Failover

To perform an Availability Group Failover in a Kubernetes Cluster, you can’t just issue a simple ALTER AVAILABILITY GROUP ag1 FAILOVER command, because everything must be coordinated through Kubernetes.

Therefore, SQL Server provides you the failover.yaml file that starts a Job Object within your Kubernetes Cluster that perform the actual Availability Group Failover.

The idea of a Job Object in Kubernetes is that you perform once a work (like the Failover in our case), and then the Job will be finished and never restarted – in comparison to a Pod that is automatically restarted through Kubernetes when it fails.

Before you initiate the Failover, it is very important that you provide your new Primary Replica in the yaml file. The new Primary Replica is passed through the Environment Variable MSSQL_K8S_NEW_PRIMARY to the Job Object itself.

You can initiate the Failover with the following simple command line:

kubectl apply -f failover.yaml -n ag1

The following picture shows the output of that command.

You can then check with the following command if the Failover Job completed successfully:

kubectl get jobs -n ag1

As you can see from the picture, the manual-failover Job was completed successfully. If you perform afterwards a SELECT @@SERVERNAME, you can see that our Primary Replica is currently hosted on the mssql2-0 Pod.

Summary

In today’s blog posting I have continued the discussion about running SQL Server Availability Groups in Kubernetes. As you have seen it is very easy with the provided yaml file to perform an Availability Group Failover in Kubernetes.

If you want to learn more about SQL Server on Linux, Docker, and Kubernetes, I highly suggest my upcoming Live Online Training on May 13 and May 14, where I will do a more technical deep-dive about all these exiting new technologies that will change our life as SQL Server Developers and DBAs over the next few years.

And if you want to learn more about Availability Groups itself, you can also check-out my On-Demand Online Training about it.

Thanks for your time,

-Klaus