Saturday , July 31 2021

SQL Server on Azure Kubernetes

In this article, I will be explaining how to create a Kubernetes cluster in Azure Kubernetes Service, and how to install SQL server on Azure Kubernetes cluster. This topic will represent the introduction to the Azure Arc Enabled Data Services topic.

What is Kubernetes?

First of all, I think it would be better to start the article by answering the kubernetes question in the mind of those who do not know kubernetes at all. The word Kubernetes comes from Greek and its meaning is helmsman (it means helmsman.Therefore its logo is similar to the helm of a ship). In most sources, it is abbreviated as k8s and this is because there are 8 letters between the letter K and the letter S.

See: Kubernetes

Kubernetes vs Virtualization

If you remember how your workloads worked in the past, you would basically have hardware and an operating system on it and your workloads running on them. Later, we started to run our workloads on virtualization layers with various solutions. This time, there is a hypervisor between our workloads and the operating system, and we can run our workloads on virtual servers independent of each other on a hypervisor, and it has become a widely used and preferred infrastructure management. 
However, with the technology and platforms developing every day, a container distribution model that is one step beyond virtualization was developed. In fact, although it is a solution similar to virtualization structure, they have comfortable isolation features to share the Operating System (OS) between applications. For this reason, containers are considered lightweight.

Similar to a VM, a container has its own filesystem, CPU, memory, process space, and more. Because they are separated from the underlying infrastructure, they can be transported in clouds and operating system deployments.

So where is Kubernetes in this? Kubernetes is a platform that provides you with a flexible environment for managing containers working together and running them seamlessly and flexibly.

We mentioned the meaning and abbreviation of Kubernetes, let’s return to our main topic after sharing the information for what it was developed for.

Kubernetes developed in Which Language?

Kubernetes is a platform developed with an Open source GO language that allows you to manage, expand and make your containerized workloads and services easily portable.

SQL Server on Azure Kubernetes

After this basic knowledge, we can move on to SQL Server installation in Azure Kubernetes Services, which is our main topic.

We will install a version of Microsoft SQL Server adapted to the container structure. Thus, we will ensure the flexible distribution, expansion and management of these containers with Azure Kubernetes service.

As a first step, we need an azure account. Assuming that you have completed this step, I continue my article and login to my Azure account. After logging into the portal, we click on the “>” box on the upper left and shown in Picture-1 and log into the azure cloud shell. If you have not used azure cloud shell before, it will ask you to create a storage address.

Figure1: Running Azure Cloud Shell
Picture1: Running Azure Cloud Shell

But before continuing our operations on Azure, we need to install Azure CLI and Kubectl on our computer that we are using and want to manage Azure resources.

Azure CLI is a command line tool we will use to manage Azure resources.

Kubectl is a command line tool for us to run commands in the Kubernetes cluster in Azure Kubernetes Services.

Both installations will allow us to use both from cmd – command prompt and powershell.

You can use the article below for the Azure CLI setup. I do not want to disrupt the flow of the article by providing more information.

https://docs.microsoft.com/en-us/cli/azure/install-azure-cli-windows?view=azure-cli-latest&tabs=azure-cli Azure CLI

We perform the installation using the address above. Then we use the following command to log in to the Azure account via command line.

Picture2: Login to Azure account with minimal login

Azure CLI opens your default browser and prompts you to sign in using your Azure account.

To run kubectl commands via command line or powershell, you need to download kubectl.exe and define it from environment variables.

To download and install Kubectl;

https://kubernetes.io/docs/tasks/tools/install-kubectl-windows/#install-with-powershell-from-psgallery

To add to environment variables after installation;

Press the Windows Key + S key to open the “View Advanced System Settings” section.

Click on the environment variables part on the screen that appears and click on the path part on the screen that appears, add the path information of the kubectl.exe you downloaded and exit by clicking OK.

Picture4: environment variables

I store the kubectl.exe file in the .kubectl folder in the c directory and I entered this address information in the path information. Thus, we have completed the kubectl installation.

Now it is time to install Azure Kubernetes Service. In order to do this, the following Resource Providers need to be registered in Azure Cloud Shell.

Figure 5: Azure Cloud Shell Register Azure Kubernetes Services Resource Provider

Now that we have registered Resource Providers, let’s create an Azure Resource Group and collect the resources we need in this group.

With the code block above, we created a resource group in the westus2 area named rg-dmc-azure-k8s.

Picture6: Creating rg-dmc-azure-k8s resource group

We have completed the creation of the resource group. Now let’s set up a kubernetes cluster with two nodes in this resource group with the code block below. The feature of Kubernetes cluster is standard_D8_v4, ie 8 vCPU, 32 GM Ram.

Figure7: Creating Azure Kubernetes Service

Our kubernetes cluster will be created in about ten minutes. After the process is completed, we can see all the resources in the resource group we created on the Azure Portal.

Figure8: Azure Portal – Viewing all resources

Let’s get the credentials of the cluster using the following command.

Picture9: get the credentials of the cluster

After this process, we can view all pods in the cluster with the following command.

Picture10: Viewing All Pods

Persistent volume (Pv) and Persistent volume Claim (Pvc) in Kubernetes

Now we will configure Persistent volume (Pv) and Persistent volume Claim (Pvc), which we can call Kubernetes backbone, which is perhaps one of the most important issues. But first of all, I think it would be better to explain what these two terms are.

The importance of structuring Pv and Pvc concepts; Pv and Pvc configuration determines whether or not the data it contains is lost when a pod crashes or gets up again.

Save the following code block in a notebook as dmc_pv_pvc.yaml. After saving, let’s upload our yaml file via Azure Cloud Shell as shown in Picture11.

Picture11: Azure Cloud Shell Upload File

After the upload process is finished, we will see a notification as shown in Picture12.

Picture12: Yaml transfer successful.

Let’s run our yaml file that we transferred with the code block below.

Figure13: Running dmc_pv_pvc.yaml

As seen in Picture 13, we have completed the pv and pvc configuration. We can check pv and pvc with the following code blocks.

Picture14: Seeing pv-pvc information with kubectl

Now that we have completed our operations on the Kubernetes side, we can now install Microsoft SQL Server on our Cluster.

First of all, the password of the SA user that we will use in Kubernetes Cluster must be determined. We do this with the code block below. My password is “DMC-CaglarOZENC*!”

Figure15: Setting the SA password

As we use in Pv and Pvc definitions, I create a yaml file as below in order to be able to install SQL Server and upload it. The port information will be 1433. I am installing SQL Server 2019 Developer Edition and the SA password will be the password I set in Picture15.

After successfully uploading the Yaml file, I implement my yaml file with the code block below.

 

Figure16: SQL Server Installation

You can run the following query to query the status of the installation.

Figure17: SQL Server Pods

As can be seen in Picture17, the installation has been successfully completed and our SQL Server installed Pod has been running for 3 minutes and 10 seconds. If you want to get more details about the Running Pod, you can use the code block below.

Picture18: Detailed information about Pod

As you can see in Picture 18, we can even see the address of Pulling and the Pvc information it attaches, in detailed information.

As the next step, we need to learn the IP address information of the running SQL Server. For this process, we can use the following code block.

Figure19: Learning the SQL Server IP address

As can be seen in Picture19, we have learned both the Cluster’s internal IP address and External IP address. Now, let’s connect to the SQL Server with SQL Server Management Studio or Azure Data Studio.

Figure20: SQL Server Connection successful

As you can see, we did not have any problems accessing the SQL Server we installed. Now we’re connected, let’s create a new DB and add a few records.

We created a database named DMC, created a table named BLOG and insert four row into it.

Figure21: Creating the DMC database

Now, we have actually come to the most important part of the work, and let’s delete the Pod we created using the command below, let’s see what will happen 😊

Picture22: We are deleting the Pod where SQL Server is installed.

The deletion was also successful. We are checking Pods again.

Picture23: New Pod created automatically even if you delete the pod

As can be seen in Resim23, although I deleted the existing Pod, a new Pod was created for me. I’m running the following code to check the Pod details created.

Picture24: SQL IP Address

As can be seen in Picture24, the old Pod IP address has been assigned to the new Pod and the service names are also the same. But we deleted this pod. I want to check the BLOG table in the DMC database that I created by reconnecting with SQL Server Management Studio or Azure Data Studio to see if the data is still.

Picture25: Bingo! The data are still available.

As you can see, if you have successfully configured the Pv and Pvc configurations, the Pod’s crash or deletion or reboot does not cause any data loss for us. Even our IP address does not change.

Now that we have successfully installed SQL Server on Azure Kubernetes Service, let’s not forget to delete the resource group we created on the Azure side and the resources in it, so that Microsoft does not invoice us 😊

Now that we are done with dmc-azure-k8s, we can delete our azure kubernetes cluster service with peace of mind. If you want, you can delete the resource group we created for this operation after deleting the cluster. Remember, if the Resource Group is empty, you will not be billed separately.

See you in our next article, goodbye.

DMC Bilgi Teknolojileri
Çağlar ÖZENÇ

 

 

 

About Çağlar Özenç

Leave a Reply

Your email address will not be published. Required fields are marked *