Tag Archives: Kubernetes

Workshop – Kubernetes Zero to Hero – Installation, Configuration, and Application Deployment

Pre-conference Workshop at SQLSaturday Baton Rouge

I’m proud to announce that I will be be presenting an all day pre-conference workshop at SQL Saturday Chicago on March 23rd 2018! This one won’t let you down! 

The workshop is Kubernetes Zero to Hero – Installation, Configuration, and Application Deployment” 

NewImage

Here’s the abstract for the workshop

Modern application deployment needs to be fast and consistent to keep up with business objectives and Kubernetes is quickly becoming the standard for deploying container-based applications, fast. In this day-long session, we will start with an architectural overview of a Kubernetes cluster and how it manages application state. Then we will learn how to build a production-ready cluster. With our cluster up and running, we will learn how to interact with our cluster, common administrative tasks, then wrap up with how to deploy applications and SQL Server. At the end of the session, you will know how to set up a Kubernetes cluster, manage a cluster, deploy applications and databases, and how to keep everything up and running.

Session Objectives

  • Introduce Kubernetes Cluster Components
  • Introduce Kubernetes API Objects and Controllers
  • Installing Kubernetes
  • Interacting with your cluster
  • Storing persistent data in Kubernetes
  • Deploying Applications in Kubernetes
  • Deploying SQL Server in Kubernetes
  • High Availability scenarios in Kubernetes

FAQs

How much does it cost?

The full day training event is $125 per attendee.

What can I bring into the event?
WiFi at the location is limited. The workshop will be primarily demonstration based. Code will be made available for download prior to the event if you would like to follow along during the session.

How can I contact the organizer with any questions?
Please feel free to email me with any questions: aen@centinosystems.com

What’s the refund policy?
7 days: Attendees can receive refunds up to 7 days before your event start date.

Do I need to know SQL Server or Kubernetes to attend this workshop?
No, while we will be focusing on deploying SQL Server in Kubernetes, no prior knowledge of SQL Server or Kubernetes is needed. We will build up our Kubernetes skills using SQL Server as the primary application we will deploy.

What are the prerequisites for the workshop?
All examples will be executed at the command line, so proficiency at a command line is required. Platform dependent (Linux/Windows,Cloud CLIs) configurations and commands will be introduced and discussed in the workshop.  

New Pluralsight Course – Managing the Kubernetes API Server and Pods

New Pluralsight Course – Managing the Kubernetes API Server and Pods

My new course “Managing the Kubernetes API Server and Pods” in now available on Pluralsight here! Check out the trailer here or if you want to dive right in go here! This course offers practical tips from my experiences managing Kubernetes Clusters and workloads for Centino Systems clients.

This course targets IT professionals that design and maintain Kubernetes and container based solutions.The course can be used by both the IT pro learning new skills and the system administrator or developer preparing for using Kubernetes both on premises and in the Cloud.

Let’s take your Kubernetes administration and configuration skills to the next level and get you started now!

The modules of the course are:

  • Using the Kubernetes API – In this module we will dive into the Kubernetes API, looking closely at the architecture of the API Server and how exposes and manages Kubernetes API Objects. Then we will learn about API versioning and object maturity. Next, we’ll look at anatomy of an API request, leading us up to debugging interactions with the API Server.
  • Managing Objects with Labels, Annotations and Namespaces – In this demo-heavy module, we will learn out to organize and interact with resources in Kubernetes using Labels, Annotations, and Namespaces. We will also learn how to use labels to influence Kubernetes operations in Controllers and Pod scheduling.
  • Running and Managing Pods – In this module, we will look at the fundamental unit of work in Kubernetes, the Pod, looking at why the Pod abstraction is needed and design principals for placing your applications in Pods and running those Pods in your cluster.  We’ll examine Pod lifecycle and how its state impacts application health and availability. We wrap up with how Controllers interact with Pods and how Pods report their health status with readiness probes and liveness probes. 

NewImage

Check out the course at Pluralsight!

 

Data Persistency and Advanced SQL Server Disk Topologies in Kubernetes

When working with SQL Server in containers and Kubernetes storage is a key concept. In this post, we’re going to walk through how to deploy SQL Server in Kubernetes with Persistent Volumes for the system and user databases.

One of the key principals of Kubernetes is the ephemerality of Pods. No Pod is every redeployed, a completely new Pod is created. If a Pod dies, for whatever reason, a new Pod is created in its place there is no continuity in the state of that Pod. The newly created Pod will go back to the initial state of the container image defined in the Pod’s spec. This is very valuable for stateless workloads, not so much for stateful workloads like SQL Server.

This means that for a stateful workload like SQL Server we need to store both configuration and data externally from the Pod to maintain state through the recreation of a Pod. Kubernetes give us constructs two constructs to do that, environment variables and Persistent Volumes. 

Using Environment Variables for Container Configuration

Container-based applications use environment variables for configuration at startup. The SQL Server container has a collection of environment variables that can be used to configure it at container startup. We will leverage two of those in this configuration. MSSQL_DATA_DIR and MSSQL_LOG_DIR these allow us to define a file system locations for user database and log files. When the SQL Server container is started inside the Pod, it reads the environment variables at runtime and sets its configuration based on those values. We define these variables as part of the Pod Spec. We will cover that configuration below.

Using Persistent Volumes to Maintain Database State

To persist the state of our SQL Server container, we will configure SQL Server to store its data and log files for both user and system databases on Persistent Volumes.

First, let’s review how SQL Server in a container starts up. During the initial startup, the SQL Server process checks to see if there are any system databases in the default system file location which is, /var/opt/mssql/data. If there are none the system databases are copied there, if they are there no action is taken. 

To add persistently to the system databases, and really all of the other components of SQL Server such as the Error Log and other system files, we will configure /var/opt/mssql so that it is backed by a Persistent Volume.

By placing the system databases on a Persistent Volume, when a Pod is recreated and the Persistent Volumes are attached and mounted in the same location when the SQL Server process starts up it sees the system databases and has what it needs to maintain state between creation.

If there are records for user databases in the system databases, SQL Server will start the process of bringing those databases online as well. We certainly the default location for user databases is /var/opt/mssql/data but we are going to override that with an environment variable for both the data and log directories, placing each on a dedicated Persistent Volumes.

Let’s walk through that configuration together. 

Persistent Volume Claims

In this configuration, we will use dynamic storage provisioning. In dynamic provisioning, a Persistent Volume Claim (PVC) is used to request a Persistent Volume (PV) from a Storage Class. In this case, we’ll be using AKS’s managed-premium Storage Class. 

Here we define three PVCs, one for each place we want Persistent Volume, for the system files and databases and the user database and log files.

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: "pvc-sql-data"
spec:
  accessModes:
  - ReadWriteOnce
  storageClassName: managed-premium
  resources:
    requests:
      storage: 10Gi
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: "pvc-sql-system"
spec:
  accessModes:
  - ReadWriteOnce
  storageClassName: managed-premium
  resources:
    requests:
      storage: 10Gi
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: "pvc-sql-log"
spec:
  accessModes:
  - ReadWriteOnce
  storageClassName: managed-premium
  resources:
    requests:
      storage: 10Gi

Deployment

In the Pod spec for our Deployment, we want to define several elements to support this configuration. 

  • Volumes – define volumes that can be mounted by this Pod. In this case, we’re creating and naming three volumes, backed by the PVCs defined above.
  • volumeMounts – volumes mounted into the container and their mountPath, location. This maps the names from the named Volumes to a location in the filesystem in the container.
  • env – due to the ephemerality of the container in the Pod, we need to tell SQL Server at start up that the data and log files will be stored in a specified directory. We are leaving the system databases and files in the default location which is /var/opt/mssql
The net effect of this storage configuration is that we are mapping the Persistent Volumes into a particular location in the filesystem inside the container. 
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mssql-deployment
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mssql
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: mssql
    spec:
      terminationGracePeriodSeconds: 10
      containers:
      - name: mssql
        image: 'mcr.microsoft.com/mssql/server:2017-latest'
        ports:
        - containerPort: 1433
        env:
        - name: ACCEPT_EULA
          value: 'Y'
        - name: MSSQL_DATA_DIR
          value: '/data'
        - name: MSSQL_LOG_DIR
          value: '/log'
        - name: SA_PASSWORD
          value: 'S0methingS@Str0ng!'
        volumeMounts:
        - name: mssql-system
          mountPath: /var/opt/mssql
        - name: mssql-data
          mountPath: /data
        - name: mssql-log
          mountPath: /log
      volumes:
      - name: mssql-system
        persistentVolumeClaim:
          claimName: pvc-sql-system
      - name: mssql-data
        persistentVolumeClaim:
          claimName: pvc-sql-data
      - name: mssql-log
        persistentVolumeClaim:
          claimName: pvc-sql-log

Service

We’ll front end our SQL Server with a public IP address and a load balancer. 

apiVersion: v1
kind: Service
metadata:
  name: mssql-deployment
spec:
  selector:
    app: mssql
  ports:
    - protocol: TCP
      port: 31433
      targetPort: 1433
  type: LoadBalancer

Apply the Configuration

Save the code above into a YAML file and deploy it into SQL Server.

kubectl apply -f deployment-advanced-disk.yaml

You’ll get this output

persistentvolumeclaim/pvc-sql-data created
persistentvolumeclaim/pvc-sql-system created
persistentvolumeclaim/pvc-sql-log created
deployment.apps/mssql-deployment created
service/mssql-deployment created

Confirm the configuration

We can use kubectl get pv to list the Persistent Volumes (PV) dynamically allocated by our cluster. Here there are three Persistent Volumes. The key here is the status is Bound, which means they are bound to a PVC. I also want to point out the Reclaim Policy is Delete. This means if the PVC is deleted, the PV will be deleted at a cleanup interval sometime in the future. 

kubectl get pv
NAME                                       CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS   CLAIM                    STORAGECLASS      REASON   AGE
pvc-e0b418ef-6e69-11e9-a433-f659caf6a6f5   10Gi       RWO            Delete           Bound    default/pvc-sql-data     managed-premium            11m
pvc-e0cf2345-6e69-11e9-a433-f659caf6a6f5   10Gi       RWO            Delete           Bound    default/pvc-sql-system   managed-premium            11m
pvc-e0ea01a8-6e69-11e9-a433-f659caf6a6f5   10Gi       RWO            Delete           Bound    default/pvc-sql-log      managed-premium            11m

With kubectl get pvc we get a list of the PVCs in our configuration, once for each we defined above. The key here is the status is Bound, or that they are bound to a PV.

kubectl get pvc
NAME             STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS      AGE
pvc-sql-data     Bound    pvc-e0b418ef-6e69-11e9-a433-f659caf6a6f5   10Gi       RWO            managed-premium   12m
pvc-sql-log      Bound    pvc-e0ea01a8-6e69-11e9-a433-f659caf6a6f5   10Gi       RWO            managed-premium   12m
pvc-sql-system   Bound    pvc-e0cf2345-6e69-11e9-a433-f659caf6a6f5   10Gi       RWO            managed-premium   12m 

Now let’s use kubectl describe pods to get the deep dive info about our storage configuration and how it’s mapped into the Pod. 

There are three keep places in the output below I want to point you to

  • Containers: mssql: Environment: you’ll find the two environment variables set for the data and log directories. Configured as /data and /log
  • Mounts: we see the file system location inside the container and the name of the Volumes defined in the Pod Spec
  • Volumes: we see the name of the Volumes, their type, claim name and the read/write status.
  • Events: this is a log of the events for the creation of this Pod. Key here is that sometimes the container will come up prior to the storage being available to the Pod. That’s what the error below is, but it clears itself up and the container is able to start.
kubectl describe pods
Name:               mssql-deployment-df4cf5c4c-nf8lf
Namespace:          default
Priority:           0
PriorityClassName:
Node:               aks-nodepool1-89481420-2/10.240.0.6
Start Time:         Sat, 04 May 2019 07:41:59 -0500
Labels:             app=mssql
                    pod-template-hash=df4cf5c4c
Annotations:
Status:             Running
IP:                 10.244.1.51
Controlled By:      ReplicaSet/mssql-deployment-df4cf5c4c
Containers:
  mssql:
    Container ID:   docker://f2320ae8f94c24fbb04214b903b4a218b82e9548f8d88a95daa7e207eeaa42b4
    Image:          mcr.microsoft.com/mssql/server:2017-latest
    Image ID:       docker-pullable://mcr.microsoft.com/mssql/server@sha256:39554141d307f2d40d2abfc54e3a0eea3aa527e58f616496c6f3ed3245a2e2b1
    Port:           1433/TCP
    Host Port:      0/TCP
    State:          Running
      Started:      Sat, 04 May 2019 07:44:21 -0500
    Ready:          True
    Restart Count:  0
    Environment:
      ACCEPT_EULA:                   Y
      MSSQL_DATA_DIR:                /data
      MSSQL_LOG_DIR:                 /log
      SA_PASSWORD:                   S0methingS@Str0ng!
      KUBERNETES_PORT_443_TCP_ADDR:  cscluster-kubernetes-cloud-fd0c5e-8bca8b54.hcp.centralus.azmk8s.io
      KUBERNETES_PORT:               tcp://cscluster-kubernetes-cloud-fd0c5e-8bca8b54.hcp.centralus.azmk8s.io:443
      KUBERNETES_PORT_443_TCP:       tcp://cscluster-kubernetes-cloud-fd0c5e-8bca8b54.hcp.centralus.azmk8s.io:443
      KUBERNETES_SERVICE_HOST:       cscluster-kubernetes-cloud-fd0c5e-8bca8b54.hcp.centralus.azmk8s.io
    Mounts:
      /data from mssql-data (rw)
      /log from mssql-log (rw)
      /var/opt/mssql from mssql-system (rw)
      /var/run/secrets/kubernetes.io/serviceaccount from default-token-z9sbf (ro)
Conditions:
  Type              Status
  Initialized       True
  Ready             True
  ContainersReady   True
  PodScheduled      True
Volumes:
  mssql-system:
    Type:       PersistentVolumeClaim (a reference to a PersistentVolumeClaim in the same namespace)
    ClaimName:  pvc-sql-system
    ReadOnly:   false
  mssql-data:
    Type:       PersistentVolumeClaim (a reference to a PersistentVolumeClaim in the same namespace)
    ClaimName:  pvc-sql-data
    ReadOnly:   false
  mssql-log:
    Type:       PersistentVolumeClaim (a reference to a PersistentVolumeClaim in the same namespace)
    ClaimName:  pvc-sql-log
    ReadOnly:   false
  default-token-z9sbf:
    Type:        Secret (a volume populated by a Secret)
    SecretName:  default-token-z9sbf
    Optional:    false
QoS Class:       BestEffort
Node-Selectors:
Tolerations:     node.kubernetes.io/not-ready:NoExecute for 300s
                 node.kubernetes.io/unreachable:NoExecute for 300s
Events:
  Type     Reason                  Age   From                               Message
  ----     ------                  ----  ----                               -------
  Normal   Scheduled               13m   default-scheduler                  Successfully assigned default/mssql-deployment-df4cf5c4c-nf8lf to aks-nodepool1-89481420-2
  Normal   SuccessfulAttachVolume  13m   attachdetach-controller            AttachVolume.Attach succeeded for volume "pvc-e0ea01a8-6e69-11e9-a433-f659caf6a6f5"
  Normal   SuccessfulAttachVolume  12m   attachdetach-controller            AttachVolume.Attach succeeded for volume "pvc-e0cf2345-6e69-11e9-a433-f659caf6a6f5"
  Normal   SuccessfulAttachVolume  12m   attachdetach-controller            AttachVolume.Attach succeeded for volume "pvc-e0b418ef-6e69-11e9-a433-f659caf6a6f5"
  Warning  FailedMount             11m   kubelet, aks-nodepool1-89481420-2  Unable to mount volumes for pod "mssql-deployment-df4cf5c4c-nf8lf_default(027c46f7-6e6a-11e9-a433-f659caf6a6f5)": timeout expired waiting for volumes to attach or mount for pod "default"/"mssql-deployment-df4cf5c4c-nf8lf". list of unmounted volumes=[mssql-system mssql-data]. list of unattached volumes=[mssql-system mssql-data mssql-log default-token-z9sbf]
  Normal   Pulled                  11m   kubelet, aks-nodepool1-89481420-2  Container image "mcr.microsoft.com/mssql/server:2017-latest" already present on machine
  Normal   Created                 11m   kubelet, aks-nodepool1-89481420-2  Created container
  Normal   Started                 11m   kubelet, aks-nodepool1-89481420-2  Started container

Creating a Database and Verifying File Location

With this code, we’ll get our IP address for our SQL Server service then we’ll create a database and query master_files for a list of data files. Notice I’m defining my service port as 31443 which is what we defined when creating our service in the earlier step.

SVCIP=$(kubectl get svc mssql-deployment | grep mssql-deployment |  awk '{print $4}')
sqlcmd -S $SVCIP,31433 -U sa -Q 'CREATE DATABASE TestDB1' -P $PASSWORD
sqlcmd -S $SVCIP,31433 -U sa -Q 'SELECT name,physical_name from sys.master_files' -P $PASSWORD


And we’ll get this output, you can see all of the system databases backed by /var/opt/mssql and our user database is on /data and the log is on /log. All backed by Persistent Volumes.

master        /var/opt/mssql/data/master.mdf
mastlog       /var/opt/mssql/data/mastlog.ldf
tempdev       /var/opt/mssql/data/tempdb.mdf
templog       /var/opt/mssql/data/templog.ldf
modeldev      /var/opt/mssql/data/model.mdf
modellog      /var/opt/mssql/data/modellog.ldf
MSDBData      /var/opt/mssql/data/MSDBData.mdf
MSDBLog       /var/opt/mssql/data/MSDBLog.ldf
TestDB1       /data/TestDB1.mdf
TestDB1_log   /log/TestDB1_log.ldf

Confirming Persistency

Let’s go ahead and delete our Pod to confirm that when it’s recreated by our Deployment our data is still there. 

kubectl get pods
NAME                               READY   STATUS    RESTARTS   AGE
mssql-deployment-df4cf5c4c-nf8lf   1/1     Running   0          4d2h

kubectl delete pod mssql-deployment-df4cf5c4c-nf8lf 
pod "mssql-deployment-df4cf5c4c-nf8lf" deleted

Once the Pod is recreated, let’s query master files to see where our databases are located. And you’ll find that your the database created in the previous step persisted between Pod creations.

sqlcmd -S $SVCIP,31433 -U sa -Q 'SELECT name,physical_name from sys.master_files' -P $PASSWORD

master        /var/opt/mssql/data/master.mdf
mastlog       /var/opt/mssql/data/mastlog.ldf
tempdev       /var/opt/mssql/data/tempdb.mdf
templog       /var/opt/mssql/data/templog.ldf
modeldev      /var/opt/mssql/data/model.mdf
modellog      /var/opt/mssql/data/modellog.ldf
MSDBData      /var/opt/mssql/data/MSDBData.mdf
MSDBLog       /var/opt/mssql/data/MSDBLog.ldf
TestDB1       /data/TestDB1.mdf
TestDB1_log   /log/TestDB1_log.ldf

Using PowerShell in Containers

The vision for PowerShell Core is to be able to run PowerShell anywhere. In this article, I’m going to discuss how you can use Docker Containers to enable just that. We’ll look at running PowerShell in a container, running cmdlets, running different versions of PowerShell at the same time, and also how to build our own “serverless” computing platform.

Let’s address a few reasons why you would want to run PowerShell in a container.

  • Speed and agility – this for me is probably the number one reason to run PowerShell in a container.  The PowerShell container images are coming in at around 375MB, this means with a modern Internet connection you’ll be able to pull a PowerShell container image and be up in running in a very small amount of time.
  • Version – there are container images available for every release of PowerShell Core, including preview/release candidate code. With containers, you can run multiple versions of PowerShell Core in a way where they will not conflict with each other.
  • Platform independence – there are container images for Ubuntu, Fedora, Windows Server Core, Nano Server and more. This allows you to be able to consume PowerShell Core regardless of your underlying platform. You can select whichever image you want, pull the container and go. 
  • Testing – if you need to test your scripts across various versions of PowerShell Core you can pull the container, run the script on the exact version you need. You can have multiple containers on your system running multiple versions of PowerShell and be able to run them all at the same time.  
  • Isolation – containers will allow you to have self-contained environments for execution, security, environment, and configuration settings. You can also use this idea to isolate conflicting modules from each other. This is particularly valuable when developing modules and/or cmdlets.

Getting Up and Running

Let’s get started with using PowerShell Core in a container. First up, we will want to pull the Docker Container Image to our local machine. This will pull the image with the latest tag. Which at the time of this post is 6.2.0-ubuntu-18.04.

With the container image local, let’s go ahead and start up the container. In this first go, I’m going to start up the container with the docker run command and with the –interactive and –tty flags. What these flags do is, when the container starts, attach to the terminal of the container so I can use PowerShell Core interactively at the command line.

This will get you a PowerShell prompt. I told you this was going to be fast.

From that prompt, we can do the normal PowerShell things we need to do. Let’s start our journey like all good PowerShell demos do and run Get-Process. You’ll notice that there is only one process running in the container, and that’s your pwsh session. This is due to the isolation concepts of Containers. With this isolation, problems like conflicting modules and settings go away. The container gives you script an isolated execution environment. If you need to have two conflicting versions of a module, DLL or library to run your workload or script…you can use a container to isolate their execution giving them the ability to co-exist on the same system.

We can use exit to get out of PowerShell. When you exit PowerShell the container will stop. You can see that status of your container with docker ps.

 
If you’d like to get back into your container you can use docker start pwsh-latest -i where pwsh-latest is the container name we just created and -i is for interactive (we used –interactive earlier). Run that and you’ll land right back at a PowerShell prompt again. 

Running a cmdlet When Starting a Container

Now, let’s say we wanted to start our container up and non-interactively run a cmdlet right away, we can do that. With the docker run command, we can tell the container that we want it to start pwsh and pass in a cmdlet as a parameter into pwsh, with the -c parameter and that cmdlet will be executed. Let’s check out how.
 
From a performance standpoint, I want to point out the time it takes to do this work, we can use the time command to help us with that. Less than two seconds to start the container, start pwsh and execute our cmdlet and shut down the container.
 
Now let’s say I wanted to test a cmdlet execution against a specific version of PowerShell Core, perhaps even a Release Candidate. Let’s change the tag from latest to preview and docker will pull that container, start it up and we immediately have an environment for testing. This could be leveraged for script testing, cmdlet testing, module testing and so on. In the output below, you can see the preview tag points to the 6.2.0-rc1 version of PowerShell Core.
 
Now, each time we started a container so far in this post and then exited pwsh, the container shut down and was still on our system. We can see the containers with a docker ps -a. We can restart any of these containers and get them back by using the command mentioned previously.
 
We can delete each container by name, using docker rm then specifying the name as a parameter. For example, docker rm pwsh-latest would delete that container.

Running a Script When Starting a Container

When a container is deleted, the data “inside” the container is deleted too. So if we created a script inside a container and then delete the container that means the script would go away too. In Docker, we can use a volume to help us with this. A volume allows us to store our data externally to the container, we can mount the volume inside the container and it looks like it’s part of the container’s file system.
 
With volumes, when we delete the container, the data stays inside the volume. We can then create a new container and attach the volume to that new container and the data will be there for us to work with.
 
Let’s start a container and attach a volume at the /scripts location of the container’s file system. Let’s also add the –detach parameter. This is going to start the container, start pwsh and then stop the container. Then I’m going to copy a script from my local file system into the container. The container does not need to be running for the copy operation to succeed.
 
Here’s the code to copy the script from my local file system into the container where pwsh-script is the container name and /scripts is the location we want to copy the script to inside the container. This is the volume we attached to the container. The script is a simple hello-world script.
 
With that, let’s go ahead and remove the container. We used it just to copy the script into the volume. I kind of feel bad, but we’ll keep moving on.
 
With that, let’s create a new container in interactive mode, with the volume attached. This will put us at a pwsh prompt.
 
Now, since our script is in the volume and we attached that volume when we created this new container, it’s available for us inside the container. Let’s go ahead and run that script inside the container and then delete the container with docker rm when it’s finished. 

Sounds Like…Serverless?

Now let’s take that technique we just stepped through, where we started the container, ran a script and deleted the container and combine all of that into one step. To do so, we’ll use the following command options for docker run. We specify the –rm option which will delete the container when it exits, add the /scripts volume and tell pwsh to run the script that’s in our volume by specifying its location with the parameter -F /scripts/Get-Containers.ps1.
 
Now, with that last technique, we’ve encapsulated the entire lifecycle of the execution of that script into one line of code. It’s like this script execution never happened…or did it ;) All kidding aside, we effectively have a serverless computing platform now. Using this technique in our data centers, we can spin up a container, on any version of PowerShell on any platform, run some workload/script and when the workload finishes, the container just goes away. For this to work well, we will need something to drive that process. In an upcoming blog post, we’ll talk more about how we can automate the running of PowerShell containers in Kubernetes.
 
In this post, we covered a lot, we looked at how you can interactively run PowerShell Core in a container, how you can pass cmdlets into a container at runtime, running different versions of PowerShell Core and also how you can persistently store scripts outside of containers in volumes and run those scripts in your containers. We also looked at how you can encapsulate the whole execution of a script and the containers life cycle into one line of code. Really giving you the ability to run PowerShell Core anywhere on any platform.
 
I hope you enjoyed this and are as excited as I am about how we can leverage this technology to solve new and unique problems in your data center and IT operations.
 

Using Kubernetes Deployments for Updating SQL Server

In Kubernetes we can leverage Controllers to help manage our application state, keeping them in the desired state. In this blog post, we’re going to look at how to use a Deployment Controller to manage the application state of SQL Server in Kubernetes. We’ll look at deploying SQL Server in a Deployment and using that deployment to upgrade SQL Server and rollback our upgrade.

Deploying SQL Server in a Deployment

Let’s start off with deploying SQL Server in Kubernetes. We can do that with the following YAML file to describe our Deployment.

apiVersion: apps/v1beta1
kind: Deployment
metadata:
  name: mssql-deployment
spec:
  replicas: 1
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: mssql
    spec:
      terminationGracePeriodSeconds: 10
      containers:
      - name: mssql
        image: 'mcr.microsoft.com/mssql/server:2017-CU11-ubuntu'
        ports:
        - containerPort: 1433
        env:
        - name: ACCEPT_EULA
          value: "Y"
        - name: SA_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mssql
              key: SA_PASSWORD
        volumeMounts:
        - name: mssqldb
          mountPath: /var/opt/mssql
      volumes:
      - name: mssqldb
        persistentVolumeClaim:
          claimName: pvc-sql-data
---
apiVersion: v1
kind: Service
metadata:
  name: mssql-deployment
spec:
  selector:
    app: mssql
  ports:
    - protocol: TCP
      port: 31433
      targetPort: 1433
  type: NodePort

Listing 1: deployment-sql.yaml

There are a few things I want to point out in our YAML file. First, we’re using a Deployment Controller. This will implement a Replica Set of the desired number of replicas using the container imaged defined. In this case, we’ll have 1 replica using the SQL Server 2017 CU11 Image. A Replica Set will guarantee that a defined set of Pods are running at any given time, here we’ll have exactly one Pod. We’re using a Deployment Controller, which gives us move between versions of Replica Sets based off different container images in a controlled fashion…more on that in a second. I would also like to point out, the volume described in this manifest. Our container’s data directory is /var/opt/mssql which is mounted on a PersistentVolumeClaim. This means our data is external to our Pod, if our Pod is redeployed our databases will be in this directory, they will be mounted and our databases will be made available. We’re also using a Service to provide a fixed IP and Port for access to our SQL Server in this Deployment.

Let’s go ahead and apply the code in Listing 1: deployment-sql.yaml

kubectl create secret generic mssql --from-literal=SA_PASSWORD=OurR&4llyStr0ngP4ssw0rd!
kubectl apply -f deployment-sql.yaml --record

With that applied, our SQL Server Deployment will schedule one Pod, start up the container, expose it as a NodePort Service and our SQL Server is up and running on the 2017 CU11 container image. That –record flag will record the operation as an annotation on the resource. Basically giving us some human-readable information about what we’re doing with that command that we can user later.

Deployments and Replica Sets

In Kubernetes, Deployments are made of Replica Sets. With our SQL Server Pod up in running from our Deployment, let’s start our investigation using kubectl get deployment mssql-deployment. In the output below, we can see the deployment mssql-deployment started a Replica Set based off of the SQL Server 2017 CU11 Image. And the Replica Set started for that container image is mssql-deployment-55bd89b84d.

kubectl get deployment mssql-deployment

Name: mssql-deployment …output omitted
Pod Template:
Containers:
mssql:
Image: mcr.microsoft.com/mssql/server:2017-CU11-ubuntu
…output omitted
NewReplicaSet:   mssql-deployment-55bd89b84d (1/1 replicas created)
Events:
Type Reason Age From Message
---- ------ ---- ---- -------
Normal ScalingReplicaSet 7m10s deployment-controller Scaled up replica set mssql-deployment-55bd89b84d to 1

Screen Shot 2019 03 12 at 6 54 14 AM

Figure 1: SQL Server Deployment

Updating the Deployment with a New Container Image

Now we can use Deployments to easily move between versions of container images. So let’s update this 2017 CU11 container image with a 2017 CU12 container image. We can do that with this code:

kubectl --record deployment set image mssql-deployment mssql=mcr.microsoft.com/mssql/server:2017-CU12-ubuntu

With this block of code, we’re recording the updating of the container image with –record and we’re setting the container image for the mssql container in our Pod Template to 2017-CU12-ubuntu.

Now our container image is being updated using our defined Update Strategy…we defined our update strategy way back in deployment-sql.yaml with the attribute strategy: type: Recreate. The Recreate update strategy will shut down the existing Pod(s) in the Replica Set before starting the new Pod(s) with the new container image in the new Replica Set we’re updating to. This makes sense in an RDBMS since we want to have only one Pod have access to the data files at one point in time. This entire process takes only a few seconds! You may have to wait while SQL Server runs update scripts on the databases.We can check the status with kubectl rollout status deployment mssql-deployment 

kubectl rollout status deployment mssql-deployment

Waiting for deployment "mssql-deployment" rollout to finish: 0 out of 1 new replicas have been updated... Waiting for deployment "mssql-deployment" rollout to finish: 0 of 1 updated replicas are available... deployment "mssql-deployment" successfully rolled out

Now look more closely at our Deployment again with kubectl describe deployment mssql-deployment. In the output below, here we see the original Replica Set (mssql-deployment-55bd89b84d) scaled from 1 to 0 and our new Replica Set (mssql-deployment-6776c966b7) based off of the CU12 image scaled from 0 to 1. I also want to point out that Kubernetes will keep the original Replica Set metadata around for us which we can use to rollback if needed.

kubectl describe deployment mssql-deployment

Name: mssql-deployment …output omitted Pod Template: Containers: mssql: Image: mcr.microsoft.com/mssql/server:2017-CU12-ubuntu
…output omitted
NewReplicaSet: mssql-deployment-6776c966b7 (1/1 replicas created) Events: Type Reason Age From Message ---- ------ ---- ---- ------- Normal ScalingReplicaSet 15m deployment-controller Scaled up replica set mssql-deployment-55bd89b84d to 1 Normal ScalingReplicaSet 114s deployment-controller Scaled down replica set mssql-deployment-55bd89b84d to 0 Normal ScalingReplicaSet 109s deployment-controller Scaled up replica set mssql-deployment-6776c966b7 to 1

Screen Shot 2019 03 12 at 6 54 26 AM

Figure 2: SQL Server Deployment with updated container image

Check out the Revision History

If you want to check the history of your rollouts, with the recorded changes you’ve made, for your Deployment you can use kubectl rollout history deployment mssql-deployment

kubectl rollout history deployment mssql-deployment

REVISION CHANGE-CAUSE 1 kubectl apply --filename=deployment-sql.yaml --record=true 2 kubectl deployment set image mssql-deployment mssql=mcr.microsoft.com/mssql/server:2017-CU12-ubuntu --record=true

With this we can see the history of our changes to our Deployment, specifically Revision number 1 when we created our Deployment. Then Revision number 2 when we changed the image from CU11 to CU12.

Rolling Back our SQL Server Deployment to the Previous Container Image

Now, if we needed to rollback from CU12 to CU11, that’s quite easy in Kubernetes, we can do that with kubectl rollout undo deployment mssql-deployment –to-revision=1  

kubectl rollout undo deployment mssql-deployment --to-revision=1 

Then we can use kubectl describe deployment mssql-deployment to check the status of our Deployment rollback.

kubectl describe deployment mssql-deployment

Name: mssql-deployment
…output omitted
Pod Template:
Containers:
mssql:
Image: mcr.microsoft.com/mssql/server:2017-CU11-ubuntu
…output omitted
NewReplicaSet: mssql-deployment-55bd89b84d (1/1 replicas created)
Events:
Type Reason Age From Message
---- ------ ---- ---- -------
Normal ScalingReplicaSet 7m55s deployment-controller Scaled down replica set mssql-deployment-55bd89b84d to 0
Normal ScalingReplicaSet 7m50s deployment-controller Scaled up replica set mssql-deployment-6776c966b7 to 1
Normal ScalingReplicaSet 18s deployment-controller Scaled down replica set mssql-deployment-6776c966b7 to 0 
Normal ScalingReplicaSet 12s (x2 over 21m) deployment-controller Scaled up replica set mssql-deployment-55bd89b84d to 1 

In the output above you can see our updated Replica Set (mssql-deployment-6776c966b7) is scaled from 1 to 0 and the original Replica Set is scaled from 0 to 1 (mssql-deployment-55bd89b84d). Bringing the Replica Set backed with the CU 11 image back online. Again, similar to the rollout of the image update above, this entire process takes only a few seconds. Again, you may have to wait while SQL Server runs update scripts on the databases.

Summary

Kubernetes offers us many ways to manage our application state. Deployment Controllers give us the ability to easily move between versions of our application and rollback if needed. In SQL Server, this method offers us a way to move between Cumulative Updates in a controlled way with a very quick, and controlled way to rollback if needed. However, in SQL Server, we have to deal with upgrades where we can’t easily roll back as is the case when we update the database version. We can still use this to upgrade SQL Server between database versions, but we lose the ability to rollback. In those scenarios, testing is the best way to ensure you are compatible in the upgraded state. You’ll find this rollout method is amazingly simple and fast when you try it out.

Please feel free to contact me with any questions regarding Linux, Kubernetes or other SQL Server related issues at : aen@centinosystems.com

Speaking at SQLBits 2019

Speaking at SQLBits 2019!

I’m proud to announce that I will be speaking at SQLBits on March 2nd 2019! It’s been a goal of mine to speak at SQLBits for a few years now and I’m VERY excited for the opportunity! This year’s conference won’t let you down. Check out the amazing schedule of Experts and Microsoft MVPs!

If you haven’t been to SQLBits before, what are you waiting for! Sign up now!

SQLBits Logo

Here’s the details on my session!

Inside Kubernetes – An Architectural Deep Dive – March 2 2018 – 15:10 – Room 12

In this session we will introduce Kubernetes, we’ll deep dive into each component and its responsibility in a cluster. We will also look at and demonstrate higher-level abstractions such as Services, Controllers, and Deployments and how they can be used to ensure the desired state of an application and data platform deployed in Kubernetes. Next, we’ll look at Kubernetes networking and intercluster communication patterns. With that foundation, we will then introduce various cluster scenarios such as a single node, single head, and high availability designs. By the end of this session, you will understand what’s needed to put your applications and data platform in production in a Kubernetes cluster

Session Objectives:
Understand Kubernetes cluster architecture
Understand Services, Controllers, and Deployments
Designing Production Ready Kubernetes Clusters

Right before my session, in the same room at 13:45, my good friend Andrew Pruski will be delivering a session on Kubernetes as well! His session is: SQL Server and Kubernetes!

Be sure to come to both sessions, Andrew will get you started on your Kubernetes journey and I’ll dive deep into how Kubernetes works!

New Pluralsight Course – Kubernetes Installation and Configuration Fundamentals

New Pluralsight Course – Kubernetes Installation and Configuration Fundamentals

My new course “Kubernetes Installation and Configuration Fundamentals” in now available on Pluralsight here! If you want to learn about the course, check out the trailer here or if you want to dive right in check it out here! This course offers practical tips from my experiences building Kubernetes Clusters for Centino Systems clients.

This course targets IT professionals that design and maintain Kubernetes and container based solutions.The course can be used by both the IT pro learning new skills and the system administrator or developer preparing for using Kubernetes both on premises and in the Cloud.

Let’s take your Kubernetes administration and configuration skills to the next level and get you started now!

The modules of the course are:

  • Exploring the Kubernetes Architecture – In this module we introduce Kubernetes, deep dive into each component and its responsibility in a cluster. We also look at higher level abstractions such as Services, Controllers, and Deployments and how they can be used to ensure the desired state of an application deployed in Kubernetes
  • Installing and Configuring Kubernetes – In this module, we learn several ways to install a Kubernetes cluster. We start off simple with an installation using kubeadm. Then we head off to the Cloud, we look at the current state of the cloud managed Kubernetes services and installation methods for each of the major cloud providers (Google, AWS, and Azure) and perform an installation using Azure Kubernetes Service (AKS) and Google Kubernetes Engine (GKE).
  • Working with Your Kubernetes Cluster – In this module, we learn how to interact with our cluster. We learn how to use and configure the primary tool for communicating with Kubernetes clusters, kubectl. We then learn how to perform a simple application (pod) deployment both imperatively and declaratively in our Kubernetes cluster. With our cluster up and running and our first application (pod) deployed in our cluster, we will then explore the internals of our cluster, its components and resources.

NewImage

Check out the course at Pluralsight!

 

Speaking at SQLSaturday Nashville – 815!

Speaking at SQLSaturday Nashville – 815!

I’m proud to announce that I will be speaking at SQL Saturday Nashville on January 12th 2019! And wow, 815 SQL Saturdays! This one won’t let you down. Check out the amazing schedule!

If you don’t know what SQLSaturday is, it’s a whole day of free SQL Server training available to you at no cost!

If you haven’t been to a SQLSaturday, what are you waiting for! Sign up now!

NewImage

This year I have TWO sessions!

1. Inside Kubernetes – An Architectural Deep Dive

In this session we will introduce Kubernetes, we’ll deep dive into each component and its responsibility in a cluster. We will also look at and demonstrate higher-level abstractions such as Services, Controllers, and Deployments and how they can be used to ensure the desired state of an application and data platform deployed in Kubernetes. Next, we’ll look at Kubernetes networking and intercluster communication patterns. With that foundation, we will then introduce various cluster scenarios such as a single node, single head, and high availability designs. By the end of this session, you will understand what’s needed to put your applications and data platform in production in a Kubernetes cluster

Session Objectives:
Understand Kubernetes cluster architecture
Understand Services, Controllers, and Deployments
Designing Production Ready Kubernetes Clusters

 

2. Containers – You Better Get on Board

Containers are taking over, changing the way systems are developed and deployed…and that’s NOT hyperbole. Just imagine if you could deploy SQL Server or even your whole application stack in just minutes. You can do that, leveraging containers! In this session, we’ll get your started on your container journey learning container fundamentals in Docker, then look at some common container scenarios and introduce deployment automation with Kubernetes. In this session we’ll look at Container Fundamentals with Docker Common, Container Scenarios and Orchestration with Kubernetes

Workshop – Kubernetes Zero to Hero – Installation, Configuration, and Application Deployment

Pre-conference Workshop at SQLSaturday Chicago – 825

I’m proud to announce that I will be be presenting an all day pre-conference workshop at SQL Saturday Chicago on March 23rd 2018! This one won’t let you down! 

The workshop is Kubernetes Zero to Hero – Installation, Configuration, and Application Deployment” 

NewImage

Here’s the abstract for the workshop

Modern application deployment needs to be fast and consistent to keep up with business objectives and Kubernetes is quickly becoming the standard for deploying container-based applications, fast. In this day-long session, we will start with an architectural overview of a Kubernetes cluster and how it manages application state. Then we will learn how to build a production-ready cluster. With our cluster up and running, we will learn how to interact with our cluster, common administrative tasks, then wrap up with how to deploy applications and SQL Server. At the end of the session, you will know how to set up a Kubernetes cluster, manage a cluster, deploy applications and databases, and how to keep everything up and running.

Session Objectives

  • Introduce Kubernetes Cluster Components
  • Introduce Kubernetes API Objects and Controllers
  • Installing Kubernetes
  • Interacting with your cluster
  • Storing persistent data in Kubernetes
  • Deploying Applications in Kubernetes
  • Deploying SQL Server in Kubernetes
  • High Availability scenarios in Kubernetes

FAQs

What can I bring into the event?
WiFi at the location is limited. The workshop will be primarily demonstration based. Code will be made available for download prior to the event if you would like to follow along during the session.

How can I contact the organizer with any questions?
Please feel free to email me with any questions: aen@centinosystems.com

What’s the refund policy?
7 days: Attendees can receive refunds up to 7 days before your event start date.

Do I need to know SQL Server or Kubernetes to attend this workshop?
No, while we will be focusing on deploying SQL Server in Kubernetes, no prior knowledge of SQL Server or Kubernetes is needed. We will build up our Kubernetes skills using SQL Server as the primary application we will deploy.

What are the prerequisites for the workshop?
All examples will be executed at the command line, so proficiency at a command line is required. Platform dependent (Linux/Windows,Cloud CLIs) configurations and commands will be introduced and discussed in the workshop.  

Deploying SQL Server Availability Groups in Kubernetes

In this blog post, we’re going to work on deploying a SQL Server Availability Group in a Kubernetes Cluster in on-premises virtual machines. I’m going to walk you through the process as it’s documented by Microsoft at this link here. This document is very good but only shows you how to do it in Azure, we’re going to do it in VMs. I’m going to follow Microsoft’s documentation as much as possible, deviating only when necessary for on-premises deployments. I’m also going to explain the key Kubernetes concepts that you need to know to understand how all these pieces together. This is a long one, buckle up.

Creating Your Cluster

In my last blog post, I showed you how to create a three-node Kubernetes on premise, virtual machine based cluster. I’m going to use the same setup for this blog post. Check out how to create this cluster at this link here. There’s a reason I wrote that post first :)

Process Overview

Here’s the big picture of all the steps we will perform in this demonstration

  • Create a Namespace
  • Create Secrets
  • Create a Storage Class and mark it default
  • Create Persistent Volumes
  • Create a ServiceAccount, ClusterRole, ClusterRoleBinding and a Deployment
  • Deploy the SQL Server Pods
  • Expose the Availability Group Services as a Kubernetes Service
  • Connect to our Availability Group from outside our cluster
  • Create a database
I’m going to be running these commands from the Cluster Master, you can run these from any host that can talk to your API Server. We’re going to execute the first few commands using kubectl directly then we’re going to switch to loading our configurations from “deployment manifests” which describe our configuration in YAML files and in this case, describe the Availability Group Deployment.

Create a Namespace

First up, we’ll create a Namespace. In Kubernetes you can use namespaces to put boundaries around resources for organizational or security reasons.  

demo@k8s-master1:~/ag$ kubectl create namespace ag1

namespace/ag1 created


demo@k8s-master1:~/ag$ kubectl get namespaces

NAME          STATUS   AGE

ag1           Active   11m

default       Active   28h

kube-public   Active   28h

kube-system   Active   28h

Create Secrets

In Kubernetes, the cluster store can hold Secrets…in other words sensitive data like passwords. This is valuable because we don’t want to store this information in our containers and we certainly don’t want to have our passwords as clear text in our deployment manifests. So in those manifests, we can reference these values and then upon deployment the Pods will retrieve the secrets when they’re started and pass the secret into the container for the application to use. In this case, we’re creating two secrets to be used by our Pods. The first is the SA password we’ll use for our SQL Server Instance, the second is the password for our Service Master Key which is behind the certificates that are used to authenticate the Availability Group (*cough*) Database Mirroring endpoints.

Let’s create the secrets with kubectl.

demo@k8s-master1:~/ag$ kubectl create secret generic sql-secrets –from-literal=sapassword=”1-S0methingS@Str0ng” –from-literal=masterkeypassword=”2-S0methingS@Str0ng”  –namespace ag1

secret/sql-secrets created

 
Want to know how to read a secret out of the cluster store?
 
Well here you go, just change the masterkeypassword string to the name of the secret you want to decode. 
 

demo@k8s-master1:~/ag$ kubectl get secret sql-secrets -o yaml –namespace ag1 | grep masterkeypassword  | awk ‘{ print $2 }’ | base64 –decode 

2-S0methingS@Str0ng
demo@k8s-master1:~/ag
 

Create a Storage Class and Mark it Default

OK, now it’s time to work on our storage configuration. This is where we’re going to deviate most from the Microsoft documentation. We’re going to start off by creating a StorageClass. A StorageClass is a way to group storage together based on the storage’s attributes. In this case, we’re going to use a StorageClass because later on in the YAML files provided by Microsoft they are going to make storage requests of the cluster referencing the default storage class in the namespace. So that’s what this code does, creates the local-storage StorageClass and marks it as default.

Here is the YAML definition of our StorageClass, save this into a file called StorageClass.yaml.

File 1 – StorageClass.yaml

Once you have that code saved into StorageClass.yaml, go ahead and run the following command to pass the StorageClass manifest to the API server for it to create the resource for you.

demo@k8s-master1:~/ag$ kubectl apply -f StorageClass.yaml –namespace ag1

storageclass.storage.k8s.io/local-storage created
 

demo@k8s-master1:~/ag$ kubectl get storageclass –namespace ag1

NAME                      PROVISIONER                    AGE

local-storage (default)   kubernetes.io/no-provisioner   3h20m

Create Persistent Volumes

Next up is creating our actual storage. In Kubernetes, the cluster provides storage to Pods and the Pods request the storage. Cluster storage can be many different types. You can have NFS, virtual disks from your cloud provider, local storage and many more. Local storage is the storage that’s attached to the Kubernetes Node itself. In this demonstration, we’re going to use local storage. Since we’re deploying Availability Groups that will put a Pod on each Node in our cluster, we’re going to need to define three PersistentVolumes, one on each Node.. Looking at the code in File 2 – PV.yaml (below) you will see three PersistentVolumes each with a different name, all pointing to /var/opt/mssql. This will be local on each Node in our cluster. So we will need to make a /var/opt/mssql directory on each node in our cluster. So go ahead and do that now. Storage in HA systems requires understanding of what data is living where in your system and layering the appropriate data protections to meet your recovery objectives. This configuration places the storage on each Node in your Kubernetes Cluster. For more information on the local storage type in Kubernetes check out this post here. In that post they specifically call out that this Persistent Volume type is appropriate for “Distributed storage systems that shard or replicate data across multiple nodes” and Availability Groups fall into that category.

In an effort to loosely couple Pods and their storage, the cluster administrator defines Persistent Volumes, that what you’ll do when you run the code in File 2 – PV.yaml below. Then the pods will use PersistentVolumeClaim to attach the Pods to the PersistentVolumes.

File 2 – PV.yaml

Once you have that code saved into PV.yaml, go ahead and run the following command to pass the PersistentVolume manifest to the API server for it to create the resource for you.

demo@k8s-master1:~/ag$ kubectl apply -f pv.yaml –namespace ag1

persistentvolume/ag1-pv-volume-node1 created

persistentvolume/ag1-pv-volume-node2 created

persistentvolume/ag1-pv-volume-node3 created


demo@k8s-master1:~/ag$ kubectl get PersistentVolume –namespace ag1

NAME                  CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS      CLAIM   STORAGECLASS    REASON   AGE

ag1-pv-volume-node1   10Gi       RWO            Retain           Available           local-storage            29s

ag1-pv-volume-node2   10Gi       RWO            Retain           Available           local-storage            29s

ag1-pv-volume-node3   10Gi       RWO            Retain           Available           local-storage            29s

Create a ServiceAccount, ClusterRole, ClusterRoleBinding and a Deployment

Now that we have our storage created it’s time to get started creating our resources for our cluster. In the Samples provided by Microsoft, they provide operator.yaml. For our deployment, we’ll use that file just as is from Microsoft. I have the contents below for you. The core details of the operator.yaml manifest file are that it creates a ServiceAccount, a ClusterRole, a ClusterRoleBinding and also defines the Deployment for the Kubernetes Operator Pod for our Availability Group. Basically, this sets up the security contexts in the cluster for our Pods and also kicks off the Deployment of the Availability Group Kubernetes Operator Pod. A Deployment is a Kubernetes object that will ensure that the defined Pod is up and running at all times. Looking towards the bottom of the file you’ll see replicas: 1 this defines that there must be exactly one Operator pod using the 2019-CTP2.1-ubuntu container at all times. 

File 3 – operator.yaml 

Now let’s go ahead and deploy operator.yaml and create the resources. 

demo@k8s-master1:~/ag$ kubectl apply -f operator.yaml –namespace ag1

Warning: kubectl apply should be used on resource created by either kubectl create –save-config or kubectl apply

namespace/ag1 configured

serviceaccount/mssql-operator created

clusterrole.rbac.authorization.k8s.io/mssql-operator-ag1 created

clusterrolebinding.rbac.authorization.k8s.io/mssql-operator-ag1 created

deployment.apps/mssql-operator created

 
If you want to inspect the resources further here are some commands to do that. I’ll leave this as an exercise for the reader.
 

kubectl get ServiceAccount –namespace ag1

kubectl describe ClusterRole mssql-operator-ag1  –namespace ag1

kubectl describe ClusterRoleBinding mssql-operator-ag1  –namespace ag1

kubectl get deployment –namespace ag1

kubectl describe deployment –namespace ag1 

Before moving on, we’ll want to make sure our Deployment is finished and Ready. We can use kubectl get deployment for that, the key here is the DESIRED and CURRENT are both 1. This means our Operator is online and ready.

demo@k8s-master1:~/ag$ kubectl get deployment –namespace ag1

NAME             DESIRED   CURRENT   UP-TO-DATE   AVAILABLE   AGE

mssql-operator   1         1         1            1           4m38s


If you want to double check that, you can make sure your Pods online with kubectl get pods. There you can see the operator Pod’s Status is Running.
 

demo@k8s-master1:~/ag$ kubectl get pods –namespace ag1

NAME                              READY   STATUS    RESTARTS   AGE

mssql-operator-6d88564d97-hqz8r   1/1     Running   0          7m8s

Deploy the SQL Server Pods

Ok, now that the Storage, Security and the Operator are deployed. Now it’s time to deploy the Pods for our SQL Server Availability Group Replicas. In File 3 – sqlserver.yaml (below) we define the resources needed to get our Pods up and running. But, this file is a little bit different…it’s using Custom Resources. Your first hint to that is look at the apiVersion, it references mssql.microsoft.com/v1 and the kind: SqlServer. Microsoft has chosen to implement a custom object likely so that it can abstract away some of the complexity of creating this configuration. 

In this file, you are going to see a lot of things come together from the other configurations we’ve made so far in this post. You’ll find that the ag1 namespace is referenced, you’ll see the storageClass is default, and the saPassword being read from the Cluster’s secrets.

In addition to the custom SqlServer resource, in this file, you will also find the Service object defined for each Pod replica. The Service provides persistent access to the Pod’s application. In this file, we create three services all on 1433. This port is the port listening on the cluster network. 

I did make one change to this file from the original Microsoft file. I changed the Service Type from LoadBalancer to NodePort. Doing this exposes the Service which is running on 1433 inside the Pod and also on the Pod network to the Node’s real IP address and bind the Pods internal 1433 port to an available ephemeral port on the Node. This means I can connect to each of the SQL Instances outside of the Kubernetes cluster using the IP address of the virtual machine (Node) and the NodePort ephemeral port. The type LoadBalancer is used in cloud scenarios where the cloud provider stands up and load balancer resource for you. We’re doing this On-Prem, so this had to change.

File 3 – sqlserver.yaml

Let’s now deploy this Pods and Services by passing sqlserver.yaml into our API Server. This might take a minute or two as the containers are downloaded from the Internet and started. In the output below you can see the three Pods are Created and the three services are created. I do want to call out when you create the Pods, you will see 6 Pods when you use the kubectl get pods command. The “initialize” Pods are managed by the Deployment as Kubernetes Jobs that are used to configure the Availability Group Replicas, these run only once and complete. When running the deployment, a successful deployment will have three mssqlN-0 Pods up with a status of Running and the  initialize pods are Completed.

demo@k8s-master1:~/ag$ kubectl apply -f sqlserver.yaml –namespace ag1

sqlserver.mssql.microsoft.com/mssql1 created

service/mssql1 created

sqlserver.mssql.microsoft.com/mssql2 created

service/mssql2 created

sqlserver.mssql.microsoft.com/mssql3 created

service/mssql3 created
 

demo@k8s-master1:~/ag$ kubectl get pods –namespace ag1

NAME                              READY   STATUS      RESTARTS   AGE

mssql-initialize-mssql1-klhzh     0/1     Completed   0          7m

mssql-initialize-mssql2-8vd6r     0/1     Completed   0          6m57s

mssql-initialize-mssql3-4tjvd     0/1     Completed   0          6m54s

mssql-operator-6d88564d97-hqz8r   1/1     Running     0          30m

mssql1-0                          2/2     Running     0          7m

mssql2-0                          2/2     Running     0          6m58s

mssql3-0                          2/2     Running     0          6m55s

If you want to get a peek at what’s been written to standard out in a Pod you can use kubectl logs. Let’s do that for one of the initialize Pods above to see what they did for us. In the output below you can see it’s the job of the initialize container to configure the Availability Group on the Pod that’s running our AG Replica on that same Node. There’s an initialize Pod for each replica on each Node.

demo@k8s-master1:~/ag$ kubectl logs mssql-initialize-mssql1-klhzh –namespace ag1

2018/11/13 02:26:14 Using randomly generated master key password

2018/11/13 02:26:14 Statefulset has 1 replicas but could only find 0

Found pod [mssql1-0] with owner [7d5ef98d-e6eb-11e8-816e-000c296ac327]2018/11/13 02:26:15 [192.168.3.5] Setting sa pasword…

ERROR: 2018/11/13 02:26:15 [192.168.3.5] Could not connect: Unresponsive or down Unable to open tcp connection with host ‘192.168.3.5:1433’: dial tcp 192.168.3.5:1433:

…output omitted…

ERROR: 2018/11/13 02:26:28 [192.168.3.5] Could not connect: Unresponsive or down Unable to open tcp connection with host ‘192.168.3.5:1433’: dial tcp 192.168.3.5:1433: getsockopt: connection refused

ERROR: 2018/11/13 02:26:28 [192.168.3.5] Both old and new sa password failed

ERROR: 2018/11/13 02:26:29 [192.168.3.5] Could not connect: Unresponsive or down Login error: mssql: Login failed for user ‘sa’.

ERROR: 2018/11/13 02:26:29 [192.168.3.5] Could not connect: Unresponsive or down Login error: mssql: Login failed for user ‘sa’.

ERROR: 2018/11/13 02:26:29 [192.168.3.5] Both old and new sa password failed

ERROR: 2018/11/13 02:26:30 [192.168.3.5] Could not connect: Unresponsive or down Login error: mssql: Login failed for user ‘sa’.

2018/11/13 02:26:31 [192.168.3.5] sa password updated

2018/11/13 02:26:31 [192.168.3.5] Creating master key

2018/11/13 02:26:31 [192.168.3.5] Creating sql login dbm-mssql1 if it does not already exist

2018/11/13 02:26:31 [192.168.3.5] Creating sql user dbm-mssql1 if it does not already exist

2018/11/13 02:26:31 [192.168.3.5] Granting dbm-mssql1 availability group permissions

2018/11/13 02:26:31 [192.168.3.5] Granting dbm-mssql1 control to the database mirroring endpoint, dbm, to if it already exists

2018/11/13 02:26:31 [192.168.3.5] Granting  dbm-mssql1 control to the certificates used on the database mirroring endpoint, dbm

2018/11/13 02:26:31 [192.168.3.5] Granting dbm-mssql1 control to availibility group ag1

2018/11/13 02:26:31 Updating secret to note that the initialization is complete

2018/11/13 02:26:31 Uploading cert secret for local instance…

2018/11/13 02:26:31 Initialization complete

 
Here’s some more commands for you to further investigate the Availability Group’s Kubernetes Configuration. Poke around in there and see what’s up. 
 

demo@k8s-master1:~/ag$ kubectl describe pods  -n ag1

demo@k8s-master1:~/ag$ kubectl describe statefulset –namespace ag1

 

Expose the Availability Group Services as a Service

Ok, so we’ve created the AG Replicas in our Kubernetes Cluster. Now it’s time to create a Kubernetes Service for access to the Primary and Read Only Read-only Replicas. Kubernetes handles networking for us. It’s responsible for adding persistency to the ephemeral container world. So these services are going to be our application’s primary access points to connect to the SQL Server. For our On-Prem scenario, we’re going to deviate from Microsoft’s documentation, similar to what we had to do when we exposed each individual replica as a Service. In the ag-services.yaml file provided in the Microsoft examples, the AG Services are Type LoadBalancer. This is Service type is used in cloud scenarios and we don’t have a that in our On-Prem configuration. So we’re going to change this again to NodePort. The reason I’m choosing NodePort here is to expose the service outside the cluster. This way I will be able to use my own tools to access the SQL Instance from outside the cluster.

Here’s the code for our ag-services.yaml Services manifest.
File 4 – ag-services.yaml

Now let’s apply that manifest to create our AG Services in our cluster.  

demo@k8s-master1:~/ag$ kubectl apply -f ag-services.yaml –namespace ag1

service/ag1-primary created

service/ag1-secondary created


And take a quick peek at our Service configuration. You’ll see we don’t have an External-IP as described in Microsoft walk-through. This would come from our LoadBalancer. Since we’re using NodePort, our IP address will be that of the actual Nodes. 

demo@k8s-master1:~/ag$ kubectl get service –namespace ag1

NAME            TYPE        CLUSTER-IP       EXTERNAL-IP   PORT(S)             AGE

ag1             ClusterIP   None             <none>        1433/TCP,5022/TCP   9h

ag1-primary     NodePort    10.110.116.77    <none>        1433:30625/TCP      2m6s

ag1-secondary   NodePort    10.100.132.228   <none>        1433:30380/TCP      2m6s

mssql1          NodePort    10.103.126.32    <none>        1433:32572/TCP      9h

mssql2          NodePort    10.102.175.227   <none>        1433:30001/TCP      9h

mssql3          NodePort    10.107.215.217   <none>        1433:31539/TCP      9h


What IP and Port do we connect our applications to for the Availability Group Primary Replica? For the IP address, it’s the real address of the Node, in other words the actual IP address of your virtual machine. For the port, well, let’s use kubectl describe for that. Here you can see the NodePort is 30625. The port is allocated by the cluster, yours will vary from this as this can be any port in the ephemeral port range. We can point our applications to any node in our cluster on THAT port and the cluster will redirect the traffic to the correct Node internal. So for me, it’s k8s-master1:30625.

demo@k8s-master1:~/ag$ kubectl describe service  ag1-primary -n ag1

Name:                     ag1-primary

Namespace:                ag1

Labels:                   <none>

Annotations:              kubectl.kubernetes.io/last-applied-configuration:

                            {“apiVersion”:”v1″,”kind”:”Service”,”metadata”:{“annotations”:{},”name”:”ag1-primary”,”namespace”:”ag1″},”spec”:{“ports”:[{“name”:”tds”,”p…

Selector:                 role.ag.mssql.microsoft.com/ag1=primary,type=sqlservr

Type:                     NodePort

IP:                       10.110.116.77

Port:                     tds  1433/TCP

TargetPort:               1433/TCP

NodePort:                 tds  30625/TCP

Endpoints:                192.168.3.5:1433

Session Affinity:         None

External Traffic Policy:  Cluster

Events:                   <none>


Let’s use sqlcmd to test external connectivity to our SQL Instance. The first one we will connect to k8s-master1 on Port 30625, the second we’ll connect to k8s-node1’s IP address (remember that’s coming from DHCP so you’ll have to go get if for your network) on port 30625. In the output below you can see we always hit the primary replica for our AG, mssql1-0. To use the name k8s-master1, I have an entry in my local hosts file to resolve the name to the IP of the server. You may want to do the same.

Anthonys-MacBook-Pro:~ aen$ sqlcmd -S k8s-master1,30625 -Q “SELECT @@SERVERNAME” -U sa -p

Password: 

                                                                                                                                

——————————————————————————————————————————–

mssql1-0                                                                                                                        

 

(1 rows affected)

 

Network packet size (bytes): 4096

1 xact[s]:

Clock Time (ms.): total         2  avg   2.0 (500.0 xacts per sec.)


Anthonys-MacBook-Pro:~ aen$ sqlcmd -S 172.16.94.136,30625 -Q “SELECT @@SERVERNAME” -U sa -p

Password: 

                                                                                                                                

——————————————————————————————————————————–

mssql1-0                                                                                                                        

 

(1 rows affected)

 

Network packet size (bytes): 4096

1 xact[s]:

Clock Time (ms.): total         1  avg   1.0 (1000.0 xacts per sec.)

In figure 1, you can find the connection information to connect to our AG primary with Azure Data Studio. Remember, I have that host name in a host file. So create that entry if you need one.

NewImage

Figure 1 – Connection Parameters for Azure Data Studio

Create an Availability Group Database

Now with everything up and running, let’s create an Availability Group database. We’re going to depend on direct seeding to copy the database to each Replica in our Availability Group, pretty nice feature, eh? Take the text below and use sqlcmd  or Azure Data Studio to create the database, Hey, in the code below, see how I’m backing up to nul, yea if this is a real database…that’s a bad idea. I’m just doing that to get the database ready to be added to the AG.
Listing 5 – Create your Availability Group Database
 
Now let’s check the status of our AG configuration with Azure Data Studio. Here’s the query to pull the AG configuration information:
Query 1 – Availability Group Configuration Query
 
 
NewImage
 
Figure 2 – Availability Group Configuration 
 
One thing that’s interesting to call out in this output in Figure 2 is you see the endpoint_url is on the 192.168.x.y network. This is the Pod network. Remember way back when we created our cluster together in my previous blog post, we needed to ensure that our Pods have the ability to communicate with each other…so we deployed the Calico Pod network. The network range on that what 192.168.0.0/16. That’s this network, our Pods’ network.

Wrap up

If you want to get a peek at all the resources you created today, use the following. This will generate this output for you to look at your configuration.
 

demo@k8s-master1:~/ag$ kubectl get all –namespace ag1

NAME                                  READY   STATUS      RESTARTS   AGE

pod/mssql-initialize-mssql1-klhzh     0/1     Completed   0          12h

pod/mssql-initialize-mssql2-8vd6r     0/1     Completed   0          12h

pod/mssql-initialize-mssql3-4tjvd     0/1     Completed   0          12h

pod/mssql-operator-6d88564d97-hqz8r   1/1     Running     0          12h

pod/mssql1-0                          2/2     Running     0          12h

pod/mssql2-0                          2/2     Running     0          12h

pod/mssql3-0                          2/2     Running     0          12h

 

NAME                    TYPE        CLUSTER-IP       EXTERNAL-IP   PORT(S)             AGE

service/ag1             ClusterIP   None             <none>        1433/TCP,5022/TCP   12h

service/ag1-primary     NodePort    10.110.116.77    <none>        1433:30625/TCP      176m

service/ag1-secondary   NodePort    10.100.132.228   <none>        1433:30380/TCP      176m

service/mssql1          NodePort    10.103.126.32    <none>        1433:32572/TCP      12h

service/mssql2          NodePort    10.102.175.227   <none>        1433:30001/TCP      12h

service/mssql3          NodePort    10.107.215.217   <none>        1433:31539/TCP      12h

 

NAME                             DESIRED   CURRENT   UP-TO-DATE   AVAILABLE   AGE

deployment.apps/mssql-operator   1         1         1            1           12h

 

NAME                                        DESIRED   CURRENT   READY   AGE

replicaset.apps/mssql-operator-6d88564d97   1         1         1       12h

 

NAME                      DESIRED   CURRENT   AGE

statefulset.apps/mssql1   1         1         12h

statefulset.apps/mssql2   1         1         12h

statefulset.apps/mssql3   1         1         12h

 

NAME                                COMPLETIONS   DURATION   AGE

job.batch/mssql-initialize-mssql1   1/1           19s        12h

job.batch/mssql-initialize-mssql2   1/1           18s        12h

job.batch/mssql-initialize-mssql3   1/1           17s        12h


 Want to dump your entire configuration to yaml, use this
 

kubectl get all –namespace ag1 -o yaml

 
Well, I hope you enjoyed this lengthy walkthrough on how to create a SQL Server Always On Availability Group using On-Premises virtual machines. I tried to do two things with this post, first show you how to set this up, and second introduce the SQL Server pro to core Kubernetes constructs.

Please feel free to contact me with any questions regarding Linux, Kubernetes or other SQL Server related issues at : aen@centinosystems.com