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.
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.
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.
- Download the MSI installer from https://docs.microsoft.com/en-us/cli/azure/install-azure-cli-windows?view=azure-cli-latest and install it.
Once you install Azure CLI, use below command in the command prompt to login into the Azure account.
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.
- Download the installer from https://kubernetes.io/docs/tasks/tools/install-kubectl/#install-kubectl-on-windows.
To set the environment variable, right click on This PC and click on properties. Click on Advanced system settings -> Environment Variables -> Path -> Edit.
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.
az provider register -n Microsoft.Compute
az provider register -n Microsoft.ContainerService
az provider register -n Microsoft.Network
az provider register -n Microsoft.Storage
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.
az group create -n democlus01 --location eastus
“democlus01” is the name of the resource group and “eastus” is the location. You can replace them as per your choice.
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.
az aks create --resource-group democlus01 --name rbctechdemo --node-count 2 --generate-ssh-keys --node-vm-size=Standard_B2s
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.
Get the credentials of the managed Kubernetes cluster by using the below command. This configures your kubectl to connect to your managed Kubernetes cluster.
az aks get-credentials --resource-group democlus01 --name rbctechdemo
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.
kubectl get nodes
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.
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.
Use kubectl command to apply the VolumeClaim.yaml file.
kubectl apply -f /home/ranga/VolumeClaim.yaml
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.
kubectl get pv
kubectl get pvc
The status of the persistent volume and volume claim should be bound.
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.
kubectl create secret generic mssql --from-literal=SA_PASSWORD="yourownpassword"
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).
- name: mssql
- containerPort: 1433
- name: MSSQL_PID
- name: ACCEPT_EULA
- name: MSSQL_SA_PASSWORD
- name: mssqldb
- name: mssqldb
- protocol: TCP
In this case, I save the above code in sqldeployment.yaml file. Apply the manifest file using below command.
kubectl apply -f /home/ranga/sqldeployment.yaml
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.
kubectl get pods
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.
To know the exact event, use below command with pod name. this command also tells on which node the pod is running.
kubectl describe pods mssql-deployment-5bc9cc6d54-l4pzs
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.
kubectl get services
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.
CREATE DATABASE [testdb]
CREATE TABLE test (id int )
insert into test values (1),(2)
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.
kubectl delete pod mssql-deployment-5bc9cc6d54-cttcm
As soon as the pod is deleted, a new pod is created as per the deployment specification. Please refer to the below image.
After the new pod is created, verify the services by using the below command.
kubectl get services
Login to the SQL server using management studio and query the tables you created earlier.
To delete the managed Kubernetes cluster use below command. This will delete all the resources created by aks create command.
az aks delete --name rbctechdemo --resource-group democlus01
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.
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
Latest posts by Ranga Babu (see all)
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019