Ranga Babu

SQL Server in Azure Kubernetes Service (AKS)

May 22, 2019 by

In this article, we will review how to create a Kubernetes cluster in Azure Kubernetes Service, provision the persistent volume to store the database files and deploy SQL server on Kubernetes cluster.

Kubernetes is a container orchestration tool which automates deploying, scaling and operating containers. SQL Server on Kubernetes cluster with persistent volume is like SQL Server failover cluster instance with the shared disk. In case of failure at the container level, a new pod is created on the same node and the volume is attached to the new pod. In case of a node failure, a new pod is created on the other node in the replica set and the persistent volume is attached to the new pod.

Pod and node failover in Kubernetes cluster

Let us see Step by step to deploy SQL Server on a Kubernetes cluster in Azure Kubernetes Service. We need to create an Azure account and login to the Azure portal. Click on the Cloud Shell icon as shown in the image to launch the console. If you are launching it for the first time, it will ask you to create storage for running cloud shell.

Azure Portal

We can also execute commands from our local windows machine. To execute commands from the local machine you need to install azure CLI and kubectl on your machine. Azure CLI is a command line tool to manage Azure resources. Kubectl is a command line interface for running commands against Kubernetes cluster in Azure Kubernetes Service.

Once you install Azure CLI, use below command in the command prompt to login into the Azure account.

Azure CLI login

Azure CLI opens your default browser and asks you to login using your Azure account.

To run the kubectl commands on your local windows machine you have to download the kubectl.exec and set the environment variable.

To set the environment variable, right click on This PC and click on properties. Click on Advanced system settings -> Environment Variables -> Path -> Edit.

kubectl as environment variable

Click on New and add the path of kubectl.exe

Creating a Kubernetes cluster in Azure Kubernetes Service

Now we need to register the resource providers. Execute below commands to register them.

register resource providers

Once you register the required resource providers. We need to create a resource group. Use the below command to create a resource group in your desired location. A resource group is a collection of multiple resources of an Azure solution.

“democlus01” is the name of the resource group and “eastus” is the location. You can replace them as per your choice.

resource group in Azure portal

Create a Kubernetes cluster using the resource group you created above. Below command create a managed Kubernetes cluster with name “rbctechdemo” in the resource group “democlus01” with 2 nodes of size Standard_B2s which has 2 cores of CPU and 4GB RAM.

create Kubernetes cluster in Azure Kubernetes Service

Once you run this command it will take some time around 10 minutes to create the managed Kubernetes cluster. It creates all the resources required for the Kubernetes cluster. To view the resources created, Click on All resources in the Azure portal.

Resources in Azure portal - Kubernetes cluster

Get the credentials of the managed Kubernetes cluster by using the below command. This configures your kubectl to connect to your managed Kubernetes cluster.

set the Kubernetes cluster as current context

Now verify if your nodes in the Kubernetes cluster are ready. Use below kubectl command to get the status of the nodes in the cluster.

nodes in Kubernetes cluster

Configuring Persistent volume and Persistent volume claim

When we deploy SQL server on Kubernetes cluster in Azure Kubernetes Service, the deployment is created in the pod. When the pod is deleted or restarted the data inside the pod is lost. To retain the data on SQL Server we need persistent volume and volume claim. A persistent volume is a piece of storage created by the administrator and used in the Kubernetes cluster.

Open notepad and paste the below code and save as .yaml file. In this case, I saved code in VolumeClaim.yaml file.

We need to upload the file to Azure cloud shell storage. To upload VolumeClaim.yaml to the cloud shell storage, Click on the Upload/Download icon in the console and Upload. Now select the VolumeClaim.yaml file from your drive and click on Open.

Persistent volume in Azure Kubernetes Service

Once the upload is completed it displays the path of the uploaded file. Please note the path which will be used in applying the VolumeClaim.yaml file. Please refer to the below image.

upload file path in Azure cloud shell

Use kubectl command to apply the VolumeClaim.yaml file.

Persistent volume claim - Azure disk

As we are using azure Kubernetes cluster we need to provision Azure disk. Once you apply the VolumeCliam.yaml it creates an azure disk which will be used as persistent volume and a volume claim with name mssql-data.

Below are the commands to check the status of persistent volume and volume claim.

The status of the persistent volume and volume claim should be bound.

Persistent volume in Azure Kubernetes Service

Now we will the volume claim in the SQL Server deployment file.

Deploying SQL Server

First, create a SA password in the Kubernetes cluster using below command which will be used in the deployment.

Create a manifest file for SQL Server deployment, upload it to the cloud shell storage like how we did for VolumeClaim.yaml and apply the manifest file using kubectl.

Below is the sample code to deploy SQL Server Linux container of developer edition which uses the persistent volume claim mssql-data created above. The SQL Server will run on the specified port number (1433).

In this case, I save the above code in sqldeployment.yaml file. Apply the manifest file using below command.

deployment on Kubernetes cluster in Azure Kubernetes Service

Once the manifest file is successfully applied, the pod is created in which SQL Server is deployed. Use below command to get the pod status.

Above command lists all the pods in the Kubernetes cluster and their status. Please refer to the below image. the pod is created, and the status is ContainerCreating.

Deploy SQL Server in Kubernetes Cluster

To know the exact event, use below command with pod name. this command also tells on which node the pod is running.

Status of the pod in cluster

For the first time, it may take some time for the pod to come into the running state as it must pull the container image from docker hub. Subsequent deployments will be fast as the image already exists in the node.

Once the deployment is completed, the status of the pod will be changed to running.

Execute the below command in Azure cloud shell to know the services on Kubernetes cluster in Azure Kubernetes Service, internal and external IP address of the services.

SQL Server service in Kubernetes cluster

Use the external IP address returned from the above command and the SA password you created earlier to connect to the SQL server.

Now open SQL Server management studio and create a database with few tables.

Let us delete the pod so that Kubernetes cluster will create a new pod and deploy the container image and attach the persistent volume to the new pod. Here the service is of load balancer type and the IP does not change even after recovery from failure.

Use the below statement to delete the pod.

As soon as the pod is deleted, a new pod is created as per the deployment specification. Please refer to the below image.

failover in Azure Kubernetes Service

After the new pod is created, verify the services by using the below command.

Login to the SQL server using management studio and query the tables you created earlier.

Failover test

To delete the managed Kubernetes cluster use below command. This will delete all the resources created by aks create command.

Once you execute the above command it will ask for confirmation. On entering “Y” it will start deleting the cluster and its resources. It takes a few minutes to delete the cluster and all the resources.

Delete Kubernetes cluster in Azure

I hope you’ve found this article on SQL Server in Azure Kubernetes Service (AKS) useful. Feel free to post any feedback in the comments below

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with a good experience in SQL server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu