Running SQL Server in Kubernetes on Azure Container Services (AKS) II

I’ve previously blogged about running SQL Server in ACS but Microsoft has now released a new version still called Azure Container Services (AKS instead of ACS however) but now specifically tailored to building Kubernetes clusters.

There are some differences to the original ACS (making the process simpler) so let’s run through setting up a Kubernetes cluster running SQL Server in AKS.

DISCLAIMER! AKS does seem to be in active development so there may be changes made that will cause the following scripts to fail. Let me know if you run into any issues

Ok, first thing to do is install the CLI (I’m going to work from a Bash shell on my desktop): –

echo "deb [arch=amd64] https://packages.microsoft.com/repos/azure-cli/ wheezy main" | \
     sudo tee /etc/apt/sources.list.d/azure-cli.list
	 
	 
sudo apt-key adv --keyserver packages.microsoft.com --recv-keys 52E16F86FEE04B979B07E28DB02C46DF417A0893
sudo apt-get install apt-transport-https
sudo apt-get update && sudo apt-get install azure-cli

Check the version of the CLI installed (make sure it’s at least version 2.0.20): –

az --version

Then login to Azure in the shell (and follow the instructions): –

az login

As AKS is still in preview a flag needs to be enabled on your Azure subscription.
To do this run: –

az provider register -n Microsoft.ContainerService

You can check that the flag has been successfully enabled by running: –

az provider show -n Microsoft.ContainerService

Cool. Now we’re good to go with setting up a Kubernetes cluster! Same as the original ACS, a resource group needs to be created to hold all the objects in the cloud: –

az group create --name ApResourceGroup1 --location ukwest

And now the cluster can be created. I’m going to create a two node cluster by running: –

az aks create --resource-group ApResourceGroup1 --name mySQLK8sCluster1 --node-count 2 --generate-ssh-keys


EDIT: updated agent-code to node-count has this switch seems to have changed since I wrote this post

What’s cool about this is the amount of objects it’s creating in the background: –

All that from one line of code!

Once that’s complete, Kubectl needs to be installed locally to manage the cluster: –

az aks install-cli

And then I need to connect my local shell to the cluster: –

az aks get-credentials --resource-group ApResourceGroup1 --name mySQLK8sCluster1

Ok, let’s check the nodes in the cluster: –

kubectl get nodes

Awesome, I have two nodes up and running in my cluster!

Next thing to do is spin up SQL Server in a container within the cluster. To do this I’m going to build it from a yaml file: –

nano sqlserver.yml

And drop the following into it: –

apiVersion: apps/v1beta1
kind: Deployment
metadata:
  name: sqlserver
  labels:
    app: sqlserver
spec:
  replicas: 1
  template:
    metadata:
      labels:
        name: sqlserver
    spec:
      containers:
      - name: sqlserver1
        image: microsoft/mssql-server-linux:latest
        ports:
        - containerPort: 1433
        env:
        - name: SA_PASSWORD
          value: "Testing1122"
        - name: ACCEPT_EULA
          value: "Y"
---
apiVersion: v1
kind: Service
metadata:
  name: sqlserver-service
spec:
  ports:
  - name: sqlserver
    port: 1433
    targetPort: 1433
  selector:
    name: sqlserver
  type: LoadBalancer

This will spin up a container within the cluster (as a deployment) and create a load balanced service with an external IP so that I can connect to SQL Server from my desktop. So now run: –

kubectl create -f sqlserver.yml

Once that’s complete we can run some commands to view the objects created. To check the SQL Server container created: –

kubectl get pods

To check on the deployment:-

kubectl get deployments

And finally, to check on the service: –

kubectl get service

Once the service has an external IP, I can use that to connect to SQL Server within my Kubernetes cluster in AKS!

How awesome is that! Microsoft have made this a nice and simple way of getting into running Kubernetes in Azure. I’m going to play around with this some more 🙂

Last thing, to remove all the objects built in this demo you just need to run: –

az group delete --name ApResourceGroup1

Thanks for reading!

12 thoughts on “Running SQL Server in Kubernetes on Azure Container Services (AKS) II

  1. This is really fascinating. I stumbled upon this blog while I was searching for ways to deploy and replicate sql server containers across kubernetes nodes. One thing I’m curious about is would this be useful to solve execution times with long running queries of large databases? Can my application point to the sql server, as a data store, which is deployed in Kubernetes? If this solution can help with running distributed queries, it would be amazing.

    Thank you for the great post

      • Thanks for the reply. In fact, I checked out the link you provided before I run into your blog. My question, to elaborate, was if we deploy sql server as kubernetes cluster, would we be able to run queries that will be executed on each worker node? Can we say that it would be count as distributed database? For example if I run a select query on a supposedly large database, would that execution be shared among the worker nodes hence query being distributed would result in faster response?

      • My understanding is that there’s no auto-distribution of queries. The SQL instances in containers managed by Kubernetes behave as normal SQL instances. Kubernetes just manages the containers (nodes) that SQL is running in.

  2. So can we say that sql server containers are not scalable in Kubernetes? Because one of the awesome advantages of Kubernetes is its scalability but in the sql server container context then this won’t hold true since it would just be replicas of instances. Is that right?

    • Yes, the advantage of running SQL Server in containers is the ease of deployment. Kubernetes provides high availability but not scalability for SQL instances.

      Note – Happy to be corrected if wrong here

  3. Love your article. I have one question about maintenance and upgrades.
    If I would like to deploy new version of SQL container , how can I stop or remove only SQL server service?
    Delete whole resource group is not an option in this scenario
    Thanks.

    • Hi Vlad,

      Individual components of the setup can be deleted. However if you delete the pod that sql is running in a new one will automatically be spun up.

      I think you’re after: kubectl delete deployment sqlserver

      That will drop the pods that sql is running in but keep your resource group and nodes (so that you can redeploy). Just be careful as it won’t drop the service, you’ll have to drop that separately.

      I’d like to work out how to redeploy a pod to an existing service and will post that in a future blog.

  4. Yep, that works:
    kubectl delete deployment sqlserver
    kubectl get service
    kubectl delete service sqlserver-service

    If you don’t mind , I have one more question…
    Do you know how I can change default IP port for SQL let say to 4433 from 1433? I tried to modify your yaml , but it didn’t work fro me.

    • No problem at all!

      There’s a couple of ways you can do it. First one is to change the port that SQL is listening on, open that port in the container, and map the service to that port. The .yml file to set that up is here: https://tinyurl.com/y8yty43l

      Second way is to leave SQL running on its default port, open another port in the service, and map that port back to SQL defaul port in the container. The .yml file to do that is here: https://tinyurl.com/y9b3guhd

      Hope that helps!

  5. Thank you. I am relatively new to K8S and wanted to check how SQL Server can be containerized. This clearly shows how to do it on the Azure platform. Good points made on scale vs HA for SQL Server with AKS.
    So, basically basic SQL Server and not SQL PDW in AKS, right?
    Also, it is a SQL Server Pod, (running on a linux host ), abstracting the container running SQL Server i.e Not supported on windows hosts yet – correct?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s