Monthly Archives: March 2019

Presenting with VS Code and Running Commands on Remote Hosts

In this blog post, I’m going to show you how to use VS Code and Azure Data Studio for presentations where you need to execute local code on remote systems over SSH.

The Challenge

When using tools like VS Code and PowerShell ISE these tools give you the ability to highlight code and execute that highlighted code. This generally means the highlighted code you want to execute will execute against the local machine. When presenting, this technique is very useful and impactful for your audience because both the code being executed and its output will be on the screen at the same time as you step through your demos. I do a lot of presenting on topics like Linux, SQL Server on Linux, Kubernetes, and OpenSSH when I’m logged into remote hosts over SSH. I’d like to have the same functionality, so I can highlight the code in VS Code locally and execute that code on a remote host over SSH. Let’s explore how I do that.

The Solution

You can create a custom keyboard shortcut in VS Code (And Azure Data Studio too) that gives you this functionality. Highlight code, press a button and execute that code in the active terminal, which just so happens to be SSH’d into a remote host.

Head over to Preferences->Keyboard Shortcuts (Picture 1) and in there you’ll find a shortcut called “Terminal: Run Selected Text In Active Terminal” (Picture 2). This is exactly what I want. Now, when I’m presenting…I can highlight the code…and what I highlighted gets copied into the terminal below and executed on whatever system is active in the terminal below. This could be either my local computer or a remote system over SSH.

NewImage

Picture 1: Preferences -> Keyboard Shortcuts. This is where you’ll find the Shortcut.

NewImage

Picture 2:  The Run Selected Text in Active Terminal is bound to keys SHIFT+F8

The Output

So, let’s see this in action. In Picture 3, you can see I have VS Code open, the first thing I do is SSH into a remote host, in this case c1-master1 which is a Master Node in a Kubernetes Cluster. Once Logged in, I highlight the command “kubectl get nodes” then I hit SHIFT+F8 which copies my selected text into the active terminal, which is the SSH session on the remote host, and executes the code. This makes me so happy. Being able to do this when presenting provides a lot of value to the attendee/viewer so they can see both the code and its output on the screen at the same time.  Another quick tip, VS Code and Azure Data Studio have excellent font scaling, crank that font up…your attendees/viewers will be happier with you.

DemosInCode

Picture 3:  Here you can see the shortcut in action in an SSH session to a Kubernetes Cluster Master.

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