Deploying SQL Server on Kubernetes – in Less than 10 lines by Ben Weissman

This article first appeared in the SQLServerGeeks Magazine.
Author: Ben Weissman
Subscribe to get your copy.

You may have heard of Kubernetes – in my opinion the future platform for any kind of modern data platform deployment. But maybe you also heard how complicated it is and that you have to be a Linux Pro to use it?

Well, how about me showing you, how to deploy SQL Server 2019 to a new Kubernetes cluster running in Azure (Azure Kubernetes Services) and all you need is a Windows Workstation and an Azure Subscription (a trial subscription is completely sufficient). And all that with effectively less than 10 lines of code (using PowerShell)!

So, what exactly are we going to do?
First, we’ll get our environment ready as as illustrated:

More precisely, that means:
1. We’ll install chocolatey, package manager for windows, on our workstation

# Install Chocolatey
[Net.ServicePointManager]::SecurityProtocol = [Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls12
Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1')) 

2. Then we’ll install the Azure CLI, the Kubernetes CLI and the SQL Server Command Line tools

# Install Prerequisites
choco install kubernetes-cli sqlserver-cmdlineutils azure-cli -y
$env:Path = [System.Environment]::GetEnvironmentVariable("Path","Machine") + ";" + [System.Environment]::GetEnvironmentVariable("Path","User")  

3. This is – for reasons of code readability – followed by setting a few variables

# Set some variables
$Region="eastus"
$Password="S@LServerGeeks2021!"
$RG_Name="MySQLonAKS"
$Cluster_Name="MyFirstAKS"
# Set this to your individual Azure Subscription ID!
$Subscription=""

4. Using the Azure CLI, we will then log into our Azure account.

# Login to Azure Account and set Subscription
az login  

This will open a Web Browser and prompt you to sign in:

And once you’ve signed in, it’ll confirm a successful login:

If you have multiple subscriptions, set the context to the correct subscription:

az account set -s $Subscription

5. Within our Azure Subscription, we’ll go ahead and create a resource group

# Create Resource Group
az group create --name $RG_Name --location $Region 

Creating resources in Azure through the Azure CLI by default results in a JSON output:

6. Within that resource group, we’ll create an AKS cluster using the default settings which gives us a managed Kubernetes Cluster in Azure with three worker nodes.

# Create AKS Cluster with default settings
az aks create -n $Cluster_Name -g $RG_Name --generate-ssh-keys 

A worker node in a Kubernetes is the compute backbone of your cluster so the number of nodes and their size – in AKS, those are Azure VMs – will determine how your cluster will perform but also how reliably it’ll react to outages.

With our cluster created, all we need to do is retrieve it’s credentials to our workstation to ensure connectivity to the cluster. We can verify the successful connection by, for example, listing all the worker nodes in the cluster through kubectl, the Kubernetes command line tool which we can use to manage any Kubernetes cluster:

# Retrieve credentials and verify connectivity 
az aks get-credentials -n $Cluster_Name -g $RG_Name --overwrite-existing
kubectl get nodes

OK – now we have a Cluster. But what about SQL?

Within that cluster, we’ll now need to create a couple of resources:

1. The first thing we need is a secret which is how we’ll provide the SA password for our new SQL Server instance.

# Create secret on Kubernetes Cluster for SA Password
kubectl create secret generic mssql --from-literal=SA_PASSWORD=$Password 

Kubectl will confirm the creation of this secret:

2. Using that secret, we can then deploy a Persistent Volume Claim (a pointer to actual phyiscal storage in Kubernetes which will also dynamically create a disk in Azure

3. The SQL Server itself, deployed using a container image

4. And a Service – which is Kubernetes‘ way of exposing a deployed application to make it accessible from outside the cluster – in our case from any machine with an internet connection.

And we can deploy all that with just one line of code by pointing kubectl to a YAML manifest:

# Deploy SQL Server 2019
kubectl apply -f https://bookmark.ws/SQLonAKSManifest 

And kubectl will again confirm that it created the PVC, the deployment and the service:

This command however only triggered the creation, so before we can really use this SQL Server, let’s use kubectl again to verify the state of these objects.

# Check result
kubectl get pvc,pods,service 

Your Pod should show as running and the mssql-deployment service should display an EXTERNAL-IP:

If this is not the case yet – give it some time. Kubernetes is an orchestrator for containers so what’s happening here in the background: Our Cluster will automatically download the SQL Server container image (and that can take time) before spinning it up into memory.

Just re-run the previous command after a few minutes until you have the running pod and the external IP showing.

But – that was more than 10 lines of code!

Was it though? While the code we were using was indeed longer than 10 lines, that is only because of the extra checks, variables and formatting I gave you 😊

The only 10 lines that are totally required are:

[Net.ServicePointManager]::SecurityProtocol = [Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls12
Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))
choco install kubernetes-cli sqlserver-cmdlineutils azure-cli -y
$env:Path = [System.Environment]::GetEnvironmentVariable("Path","Machine") + ";" + [System.Environment]::GetEnvironmentVariable("Path","User") 
az login 
az group create --name $RG_Name --location $Region
az aks create -n $Cluster_Name -g $RG_Name --generate-ssh-keys
az aks get-credentials -n $Cluster_Name -g $RG_Name --overwrite-existing
kubectl create secret generic mssql --from-literal=SA_PASSWORD=$Password
kubectl apply -f https://bookmark.ws/SQLonAKSManifest 

Not that hard, right?

And how can I use this SQL Server?

Using the external IP of this service and the port from the manifest – which is 31433, we can now connect to our SQL Server using any SQL Client tool. We’ll simply stick to the command line, using sqlcmd.

To make our commands easier to read and execute, I’ll start by storing the endpoint in a variable.

# Retrieve the (dynamically created) endpoint
$ServerIP=(kubectl get svc mssql-deployment --output jsonpath='{.status.loadBalancer.ingress[0].ip}')

We can use this to, for example, verify the version of our SQL Server:

# Check Version of SQL Server
sqlcmd -S $ServerIP,31433 -U sa -P $Password -Q "SELECT @@VERSION" 

Our manifest was pointing to the latest CU, which was CU11 in my case:

To load some data into this cluster, I’ll go ahead and download a copy of AventureWorks2017.

# Download Adventureworks2017
curl -o AdventureWorks2017.bak 'https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2017.bak' 

To copy this file into our cluster, I need the – again dynamically created – name of our pod so I will – similiarly to what we did with the IP address – use kubectl to retrieve the name, store it in a variable and copy the file to this target:

# Copy Backup to the SQL Pod
$Target=(kubectl get pod -l app=mssql -o jsonpath="{.items[0].metadata.name}")+ ":var/opt/mssql/data/AdventureWorks2017.bak"
kubectl cp AdventureWorks2017.bak $Target -c mssql

With the file sitting in our pod, we can now restore it like any other backup on any other SQL server:

# Restore AdventureWorks2017
sqlcmd -S $ServerIP,31433 -U sa -P $Password `
    -Q "RESTORE DATABASE AdventureWorks2017 FROM DISK = '/var/opt/mssql/data/AdventureWorks2017.bak' WITH `
        MOVE 'AdventureWorks2017' to '/var/opt/mssql/data/AdventureWorks2017.mdf', `
        MOVE 'AdventureWorks2017_Log' to '/var/opt/mssql/data/AdventureWorks2017_log.ldf', NOUNLOAD, STATS = 5" 

And sqlcmd will also confirm, that the database has been successfully created:

# Check that the AdventureWorks2017 database has been created
sqlcmd -S $ServerIP,31433 -U sa -P $Password -Q "SELECT NAME FROM SYS.DATABASES"

And when you’re done?

Don’t forget to delete your resource group again, which will also delete the cluster and all associated resources to avoid any unnecessary or unexpected cost.

# Delete everything
az group delete --name $RG_Name 

Want to learn more?

I know – that was a very high-level article without providing much context. We mentioned a lot, from containers over Kubernetes, Azure CLI, Disks, Deployments, Services… There is no way to explain all this in an article in this magazine. The purpose of this article however was strictly to showcase how simple Kubernetes deployments can be!

If you want to learn more about SQL Server on Kubernetes, join my friend Anthony E. Nocentino and myself for a Webinar on July 17th (https://www.dataplatformgeeks.com/events/)!

In addition to the Webinar, we have a book on the topic coming out (https://bookmark.ws/SQLonAKSBook) as well as a learning path ready for you on Pluralsight on Azure Kubernetes Services (https://www.pluralsight.com/paths/managing-and-orchestrating-containers-with-azure-kubernetes-service-aks).

Also, feel free to reach out to me on LinkedIn or Twitter!

You will find the full script from this article at https://bookmark.ws/SQLonAKSFull.

This article first appeared in the SQLServerGeeks Magazine.
Author: Ben Weissman
Subscribe to get your copy.

Leave a Reply

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