Deploy SQL Server on Azure Kubernetes Service cluster via Helm Charts - on a windows client machine!

A brief snapshot of the conversation that my colleague and I had a couple of weeks ago:

Colleague:  "I am new to Kubernetes and Linux world and was hoping if I could deploy SQL Server using helm chart from my Helm on my windows client and a managed kubernetes cluster service like Azure kubernetes service cluster (AKS)?"

Me:  "Yes, you can!"

Colleague: "Great! Can you share articles or guidance to do that so I could follow those?"

Me: "hmm.. For Helm refer this.. for Kubernetes refer this.. For SQL Server deployment using helm on kubernetes refer this.. "

and that's when I noticed there wasn't a single stop end to end guidance. This is what prompted me to write this blog. And I hope this helps anyone who is new to Kubernetes/helm charts or even SQL Server and want to get started with the deployment of SQL Server on Kubernetes.

 

By the end of this blog, you should have the SQL Server deployed via the helm charts on the managed kubernetes service which in this case is Azure Kubernetes Service (AKS). For more information on Kubernetes, Helm chart please refer to articles mentioned below to get you started.

1. What is Kubernetes ?
2. What is helm chart ?
3. The sample helm chart that we will use for this SQL Server deployment is available here. Please go through the readme file, to understand the files and values that you can chance and customize for your SQL Server deployment.

With the pre-read done, now lets start with deployment.

 

The follow along plan-

 

Please deploy an AKS (Azure Kubernetes Service) cluster.  If you do not want to use AKS and want to create your custom kubernetes cluster you could do that as well.

 

On your windows client system, from where you will connect to the AKS cluster and deploy SQL Server using the helm chart below, the tools that need to be installed on the windows client are:

 

• You first install "az", you can follow the steps documented here. From the link, download the current version of "az cli" exe and install the exe. If the installation is successful, you can type the command az in command prompt and if it is installed you should see output similar to the image shown below:

 

 

• Once you have the Azure CLI installed the next step is to install chocolatey, which is required to install helm on the windows client. To install chocolatey, follow the chocolatey install documentation. Follow the "Install with cmd.exe" section for a installation via the cmd.exe. Once the installation of the chocolatey is complete to confirm, open cmd.exe and run the command : choco as shown below:

 

 

• With chocolatey installed, it is now time to install helm. To install helm, open cmd.exe and run the command: 

  Choco install kubernetes-helm​

         Post the successful installation, when you run helm command in the cmd.exe you should see the output as shown below

 

 

• Now it is time to install the "kubectl" tool to interact and work with the kubernetes cluster, to install kubectl in the command prompt run the command

  az aks install-cli

         Post the successful install of "kubectl" you should add the path to "kubectl" as one of the  environment variables. In my case the path to kubectl is at "C:\Users\amitkh\.azure-kubectl" so I add the path to the environment variable as shown below:

 

 

With all the required tools installed on the windows client, we need to merge the context of the AKS cluster with the kubectl, so when we run kubectl or helm commands the operation takes place on that specific AKS cluster. To merge, run the command as described in the connect to aks cluster which is:

 

 

 

az aks get-credentials --resource-group <resourcegroupname> --name <aks clustername>

 

 

 

 

Once this is done, when you run a kubectl command the execution happens in the context of the aks cluster that we merged our tools with in the above step. An example shown below:

 

 

You are now ready to deploy the SQL Server on AKS cluster via the helm chart. You can download the sample helm-chart from this github location. Please go through the readme file to ensure you understand the options that you need or can change as per your requirement and customization.

 

Once you have the helm chart and all its file download to your windows client, switch to the directory where you have downloaded and after you have done modification to the downloaded helm chart to ensure it is as per your requirement and customization, deploy SQL Server using the command as shown below, you can change the deployment name ("mssql-latest-deploy") to anything that you'd like.

 

 

 

helm install mssql-latest-deploy . --set ACCEPT_EULA.value=Y --set MSSQL_PID.value=Developer

 

 

 

Here is a example for reference, I have the chart and its files downloaded to the mssql-latest directory and this is how it looks and then I run the helm install command to deploy SQL Server.

 

 

After a few minutes, you should see SQL Server deployed and ready to be used as shown below:

 

 

Connect to the SQL Server running on AKS:

1. Using any of the familiar SQL Server tools like the SSMS (SQL Server Management Studio) or SQLCMD or ADS (Azure Data Studio), etc. You can connect to the instance of the SQL Server using the External-IP address for the "mssql-latest-deploy" service. In this case it is 20.40.0.145.
2. The sa_password is the value you provide to the Values.sa_password in the values.yaml file in the helm chart.

 

 

Changing the tempdb path:

As you would have noticed in the helm chart, we provide a specific location for tempdb files to ensure that the tempdb files are stored in those specific location. Here are the steps you can follow:

 

1. To change the tempdb location to the specific path you can connect to the SQL Server instance and then run the below T-SQL queries:

 

 

 

-- Get the tempdb specific files
select * from sysaltfiles where dbid=2
--We want to move the tempdb files to this specific location : /var/opt/mssql/tempdb/, ---so here are the commands:
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = '/var/opt/mssql/tempdb/tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev2, FILENAME = '/var/opt/mssql/tempdb/tempdb2.ndf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = '/var/opt/mssql/tempdb/templog.ldf');
GO
--check and confirm that the locations for tempdb files are updated
select * from sysaltfiles where dbid=2

 

 

 

     2. Now for the change to take in affect, you will have to restart the SQL Server container and you can do the same using the commands below:

 

 

 

kubectl scale deployment mssql-latest-deploy --replicas=0

 

 

 

         Once you run the above command, wait for the container to be deleted and once it is, run the below command for the new container to be deployed

 

 

 

kubectl scale deployment mssql-latest-deploy --replicas=1

 

 

 

         Here you can now connect to the pod and check the tempdb files are now located to the new location : /var/opt/mssql/tempdb/

 

 

Hope you enjoyed it !! Happy learning!!

 

Amit Khandelwal

Sr. Program Manager