Over the last few months I have done a lot of work and research in the area of Docker Containers and Kubernetes. One of the coolest new features in SQL Server 2019 is the ability to deploy an Availability Group into a Kubernetes Cluster. Therefore, I want to show you in today’s blog posting how you can achieve that. Let’s get started…

Creating a Kubernetes Cluster

Before we can actually deploy our SQL Server 2019 Availability Group, you need of course a Kubernetes Cluster. You can run a Kubernetes Cluster in an On-Premises Installation, or you can use your preferred Cloud Vendor to deploy everything into the sky. For today’s deployment I have chosen Microsoft Azure, because it offers a native Kubernetes support through Azure Kubernetes Services (AKS). So let’s configure and create our first Kubernetes Cluster.

As you can see in the picture, I have chosen to use the Kubernetes version 1.12.5, and my Kubernetes Cluster consists of 4 nodes. AKS will create for each node in the background a Virtual Machine that will run Ubuntu Linux. In my case I have chosen the Standard DS2 v2 VM size, which gives each VM 2 vCPUs and 7 GB of RAM. The name of my Kubernetes Cluster is k8s-sqlserver2019-ag. I have left all the other settings as it, so let’s create now our first Kubernetes Cluster in Microsoft Azure!

It takes now a few minutes, and afterwards the creation of the Kubernetes Cluster is successfully completed.

In the next step you can configure now your local workstation, so that kubectl connects to your Kubernetes Cluster in Microsoft Azure. You have to run here the following command on the command line (please change the name of your Resource Group accordingly):

In the next step you can configure now your local workstation, so that kubectl connects to your Kubernetes Cluster in Microsoft Azure. You have to run here the following command on the command line (please change the name of your Resource Group accordingly):

az aks get-credentials –resource-group SQLpassionResourceGroup –name k8s-sqlserver2019-ag

Let’s check now if kubectl can connect to your AKS deployment by running a simple command like:

kubectl get nodes -o wide

As you can see, kubectl returns you the 4 nodes of your Kubernetes Cluster. You can now also view and explore the Kubernetes Dashboard by issuing the following command on the command line (again, please change the name of your Resource Group accordingly):

az aks browse –resource-group SQLpassionResourceGroup –name k8s-sqlserver2019-ag

As soon as you are running that command, the Kubernetes Dashboard appears in your default Internet Browser:

Our whole Kubernetes Infrastructure is now up and running, so it’s time to deploy our SQL Server 2019 Availability Group into the Kubernetes Cluster.

Deploying a SQL Server 2019 Availability Group

To be able to deploy a SQL Server 2019 Availability Group into your Kubernetes Cluster, you need to download the necessary manifest files from Microsoft:

• operator.yaml
• sqlserver.yaml
• ag-services.yaml

Let’s create now a new Kubernetes Namespace for our SQL Server deployment:

kubectl create namespace ag1

The next step is now to deploy the Kubernetes Operator for SQL Server:

kubectl apply -f operator.yaml -n ag1

Now we need to create the necessary secrets that stores the password of the sa user and the password of the SQL Server Master Key. That secret is referenced within the yaml files.

kubectl create secret generic sql-secrets –from-literal=sapassword=”Passw0rd1″ –from-literal=masterkeypassword=”Passw0rd1″ –namespace ag1

And finally, you are able to deploy SQL Server Replicas itself:

kubectl apply -f sqlserver.yaml -n ag1

Let’s check now if all the SQL Server Pods are up and running:

kubectl get pods –n ag1 -o wide

It will take some time until the mssql-initialize-mssql Pods are in the Completed state, and until the mssql1-0, mssql2-0, and mssql3-0 Pods are in the Running state. To be able to connect to your deployed SQL Server 2019 Availability Group Replicas, you have to deploy finally a Load Balancing Service:

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

After the deployment of the Load Balancing Service, you can retrieve the IP address of the Primary Replica with the following command:

kubectl get services -n ag1 -o wide

From that output, you can use now the External IP address to connect to your Primary Replica (through SQL Server Management Studio, or Azure Data Studio).

Adding a Database into the Availability Group

By now you have an Availability Group spread across 3 replicas where each replica is hosted on a different Kubernetes node. But the Availability Group doesn’t contain yet any databases. Therefore, let’s add now a new database into the Availability Group.

First of all, I want to use an existing database backup from an On-Premises SQL Server installation. Books Online only describes the necessary steps how to create a new database and add it into the Availability Group, but this is not really a realistic scenario. In my case I just want to use a backup from the AdventureWorks2014 database and restore it into the Kubernetes Cluster.

In the first step we have to copy the local database backup to the Primary Replica. To be able to find out which Kubernetes Node currently runs the Primary Replica, you can query the log of the mssql-ha-supervisor Container of one of the Replica Pods:

kubectl logs mssql1-0 -c mssql-ha-supervisor -n ag1

The output tells you, which Kubernetes Pod is the current leader – the Primary Replica. In my case the mssql2-0 Pod was the leader. Therefore, I have executed the following command to perform a simple file copy from my local file system into the correct Kubernetes Pod:

kubectl cp AdventureWorks2014.bak ag1/mssql2-0:/var/backups/AdventureWorks2014.bak -c mssql-server

That command just copies the local AdventureWorks2014.bak file into the folder /var/backups of the mssql-server Container of the mssql2-0 Pod. After we have copied the backup file, we perform a simple RESTORE DATABASE command:

RESTORE DATABASE AdventureWorks2014 FROM DISK = '/var/backups/AdventureWorks2014.bak'
WITH
MOVE 'AdventureWorks2014_Data' TO '/var/opt/mssql/data/Adventureworks2014.mdf',
MOVE 'AdventureWorks2014_Log' TO '/var/opt/mssql/data/Adventureworks2014.ldf'
GO

And then let’s add the database AdventureWorks2014 into the Availability Group:

ALTER AVAILABILITY GROUP ag1 ADD DATABASE AdventureWorks2014
GO

And now let’s check the Dashboard within SQL Server Management Studio:

Perfectly, we have now a fully working SQL Server 2019 Availability Group deployed into a Kubernetes Cluster running in Microsoft Azure.

It smells like a bug

By now everything was fine with our SQL Server 2019 Availability Group deployment, but please be aware that SQL Server 2019 is currently only available as an CTP version (CTP 2.2), and therefore there are some bugs in the product. One of the biggest bugs and show stopper is the fact that the Availability Group setup is not working anymore as soon as you have stopped the underlying Kubernetes Nodes.

When you stop and restart the Ubuntu Linux VMs, you will see that your Availability Group is not working anymore. The Kubernetes Pods are constantly crashing and restarting, and they are forever in the CrashLoopBackOff state.

The same applies when you try to deploy a SQL Server 2019 AG into a On-Premises Kubernetes Deployment. It seems that Microsoft didn’t tested that functionality quite well… They are aware of this bug, and hopefully we will get a fully working implementation in one of the next upcoming CTP versions of SQL Server 2019.

Summary

In today’s blog posting I have shown you how you can deploy a SQL Server 2019 Availability Group into a Kubernetes Cluster. As you have seen the whole process is quite straightforward, and you only need to deploy a few yaml files provided by Microsoft. Restoring an existing database is also not a big deal, because you can copy with the kubectl command local backup files into a Kubernetes Pod.

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