Updating SQL Server containers deployed on Kubernetes!

I'm sure you've thought about how to update SQL Server containers running on a Kubernetes cluster at some point. So, this blog attempts to answer the question. According to the Kubernetes documentation, there are two Update strategies for statefulset workloads. For your convenience, I'm quoting the summary below:

1.  OnDelete update : When a StatefulSet's .spec.updateStrategy.type is set to OnDelete, the StatefulSet controller will not automatically update the Pods in a StatefulSet. Users must manually delete Pods to cause the controller to create new Pods that reflect modifications made to a StatefulSet's .spec.template.
2. Rolling update : When a statefuleset's .spec.updateStrategy.type is set to RollingUpdate, the StatefulSet controller will delete and recreate each Pod in the StatefulSet. It will proceed in the same order as Pod termination (from the largest ordinal to the smallest), updating each Pod one at a time. This is the default update strategy.

Before, we get into the SQL Server update strategy, let's go over the guidelines for deploying SQL Server containers as statefulset workloads, which are documented here: Deploy SQL Server Linux containers on Kubernetes with Statefulsets.

As mentioned in the article, you could have chosen to deploy only one SQL Server instance per statefulset deployment script by setting the .spec.replica parameter to 1. In this case, the rolling update strategy does not make sense because you only have one SQL Server instance (one pod) deployed using the single statefulset deployment yaml, so you would normally choose the OnDelete update option to update the SQL Server instance.

Alternatively, if you set the .spec.replicas to more than one, you will deploy that many SQL Server instances with co-related names. In this case, you can choose the Ondelete strategy if you want to control the order in which the updates are pushed to each pod or Rolling update strategy for Kubernetes-controlled phased updates of SQL Server pods.

I’d like to clarify that .spec.replicas here refers to Replicas as defined in Kubernetes and this does not refer to the SQL Server Always On availability groups replicas. What I am alluding to here is that these are all standalone (independent) SQL Server instances that are not part of the Always On availability groups replicas. (I'm working on a blog about the update strategy for SQL Server containers as part of the AGs, but for now let's refocus on this blog )

OnDelete Update for SQL Server container:

In my opinion, you can use this update option for your SQL Server deployment if any of the following conditions are met:

• You’ve set the .spec.replicas to 1 and are deploying one pod per statefulset deployment script.
• If you’ve set the .spec.replicas to a value greater than 1 and want to precisely control the order in which the update is pushed to each pod in the statefulset deployment indexed by the ordinal ID.

The downside of this option is:

•  An accidental delete of the pod can cause the pod to be started with the updated image.

Consider the following scenario to better understand this. The initial script used to deploy SQL Server as described in this article is shown below, and as you can see, we are deploying SQL Server 2022 RTM with the .spec.replicas set to 3. Here's an illustration of the scenario and I want to make sure that I upgrade the pod mssql-1 first. I've depicted how the pod mssql-1 is upgraded, the same is true for next SQL Server pod, I decide to update.

Note: All the commands are being run from a cmd shell running kubectl from a Windows client machine connected to an Azure Kubernetes Service cluster that serves as the kubernetes platform for SQL Server container deployments.

apiVersion: apps/v1
kind: StatefulSet
metadata:
 name: mssql 
spec:
 serviceName: "mssql" 
 replicas: 3
 selector:
  matchLabels:
   app: mssql  
 template:
  metadata:
   labels:
    app: mssql 
  spec:
   securityContext:
     fsGroup: 10001
   containers:
   - name: mssql 
     image: mcr.microsoft.com/mssql/server:2022-RTM-ubuntu-20.04
     resources:
      limits:
       memory: 2Gi
       cpu: '2'
     ports:
     - containerPort: 1433
       name: tcpsql
     env:
     - name: ACCEPT_EULA
       value: "Y"
     - name: MSSQL_ENABLE_HADR
       value: "1"
     - name: MSSQL_AGENT_ENABLED
       value: "1"
     - name: MSSQL_SA_PASSWORD
       valueFrom:
         secretKeyRef:
          name: mssql
          key: MSSQL_SA_PASSWORD
     volumeMounts:
     - name: mssql
       mountPath: "/var/opt/mssql"
 volumeClaimTemplates:
   - metadata:
      name: mssql
     spec:
      accessModes:
      - ReadWriteOnce
      resources:
       requests:
        storage: 8Gi

Now let's say we want to upgrade all the pods to run SQL Server 2022 latest image, I can do this using the following steps, followed in the same order:

• Let's run 'kubectl describe statefulset mssql` command to see what the current update strategy is, by default the Update Strategy is set to RollingUpdate, we need to update the statefulset and change the update strategy from RollingUpdate to OnDelete. We have to use the rollingupdate null option due to an issue as described here Error when trying to update StatefulSet updateStrategy.type to "OnDelete" from default value. To do this run the below command:

  kubectl patch statefulset mssql -p "{\"spec\": {\"updateStrategy\": {\"type\": \"OnDelete\",\"rollingUpdate\": null}}}"​

• Once you set the Update Strategy to OnDelete, you can verify this by running the 'kubectl describe statefulset mssql' command again.

  Name:               mssql
  Namespace:          default
  CreationTimestamp:  Fri, 12 Mar 2023 20:45:20 +0530
  Selector:           app=mssql
  Labels:             <none>
  Annotations:        <none>
  Replicas:           3 desired | 3 total
  Update Strategy:    OnDelete
  ..
  ..
  ..​

• We now update the image version in the statefulset to use the desired SQL Server version. In this case, I am updating SQL Server 2022 RTM to the most recent SQL 2022 version, which is SQL Server 2022-CU1. Please keep in mind that running this command will not immediately update the SQL Server pod. Only when you delete and recreate the pod will the update take place.

  kubectl set image statefulset mssql mssql=mcr.microsoft.com/mssql/server:2022-latest​

• You can confirm that the image for the statefulset has been updated by running the same "'kubectl describe statefulset mssql"' command again, and you will see that the Image value is now set to: mcr.microsoft.com/mssql/server:2022-latest instead of mcr.microsoft.com/mssql/server:2022-RTM-ubuntu-20.04.
• When you are ready to update the SQL Server container, most likely during a scheduled downtime, you can run the following command, which will immediately trigger the SQL Server update. Once the pod is online, the upgrade scripts will run, and you can inspect the errorlog for the "Recovery is complete. This is an informational message only. No user action is required" message indicating you that SQL Server is now ready for connections post the update.

  Kubectl delete pod mssql-1
  
  #Check the logs to see the version updated
  kubectl logs mssql-1

• Now, if you have more than one replica defined in the statefulset yaml, as in this case, you can delete the other pods one at a time with the kubectl delete <yourpodname> command. This way, you have control over which pods and when they are updated.

Rolling Update for SQL Server Containers:

I consider this is an advanced option, as you can perform a phased roll out of the SQL Server update. Read partitioned rolling updates to know more.

You can choose to update the highest ordinal pod first and then push the updates in a phase, with the pod with the lowest ordinal updated last. The benefit of choosing this option is that an accidental deletion of a pod does not result in the SQL Server instance being updated.

Let’s consider the same script as used above and this time, lets update the pods using the rolling update option to see how this works:

If you have used the same script as shared in this article above, you should have mssql-0, mssql-1 and mssql-2 pods deployed in the cluster. Now, lets push the updates in stages, where you first update mssql-2 then mssql-1 and finally the mssql-0 pods from SQL 2022 RTM version to SQL 2022-latest which as of today is SQL 2022-CU1 as shown below.  Please remember this order cannot be changed when using the phased rolling updated.

To get started with this update strategy, make sure you follow the steps below in the correct order:

• Let's run 'kubectl describe statefulset mssql' command to see what the current update strategy is, by default the Update Strategy is set to RollingUpdate and the partition is set to 0. Look at the .spec.updateStrategy.type that should be set to RollingUpdate and the .spec.updateStrategy.rollingUpdate.partition should be set to 0.
• Now update the statefulset to set the partition to 2 (in this case, highest ordinal), which ensures that only pods with ordinal indexes equal to or greater than 2 are updated. Only mssql-2 will be updated in this case.

  kubectl patch statefulset mssql -p "{\"spec\": {\"updateStrategy\": {\"type\": \"RollingUpdate\",\"rollingUpdate\": {\"partition\": 2}}}}"
  
  #output when you run the 'kubectl describe statefulset mssql' command:
  Name:               mssql
  Namespace:          default
  CreationTimestamp:  Fri, 12 Mar 2023 20:45:20 +0530
  Selector:           app=mssql
  Labels:             <none>
  Annotations:        <none>
  Replicas:           3 desired | 3 total
  Update Strategy:    RollingUpdate
    Partition:        2
  ..
  ..
  ..​

• We are now ready to push our updates. To do so, we must first update the statefulset to use the most recent SQL Server image, as shown below. The mssql-2 pod will be deleted and recreated automatically by the cluser with the upgraded image as soon as you run the below command; the other pods, mssql-0 and mssql-1, will remain untouched and running the older version. As a result, you should only run the following command when you are ready to update the pod mssql-2. Please note, I did not have to manually delete the pod as I did with the Ondelete option to update the image.

  kubectl set image statefulset mssql mssql=mcr.microsoft.com/mssql/server:2022-latest​

• If you are ready to update the mssql-1 pod, you must update the statefulset and set the partition to 1. As soon as you set the partition to 1, the update is pushed to all the pods with ordinal values equal or greater than 1. In this case, because the mssql-2 pod has already been updated, it is not deleted and recreated, but the mssql-1 pod is. So, only run the following command when you are ready to update the mssql-1 pod.

  kubectl patch statefulset mssql -p "{\"spec\": {\"updateStrategy\": {\"type\": \"RollingUpdate\",\"rollingUpdate\": {\"partition\": 1}}}}"
  
  #output when you run the 'kubectl describe statefulset mssql' command:
  
  Name:               mssql
  Namespace:          default
  CreationTimestamp:  Fri, 12 Mar 2023 20:45:20 +0530
  Selector:           app=mssql
  Labels:             <none>
  Annotations:        <none>
  Replicas:           3 desired | 3 total
  Update Strategy:    RollingUpdate
    Partition:        1
  ..
  ..​

• Similarly, when you're ready to update the mssql-0 pod, set the partition value to 0, and all pods with ordinal index values greater than or equal to 0 will be updated. Because mssql-2 and mssql-1 were previously updated, they are not deleted in this case, and only the mssql-0 pod is deleted and recreated. Again, only run the following command when you are ready for the pod to be updated.

  kubectl patch statefulset mssql -p "{\"spec\": {\"updateStrategy\": {\"type\": \"RollingUpdate\",\"rollingUpdate\": {\"partition\": 0}}}}"​
  
  #output when you run the 'kubectl describe statefulset mssql' command:
  
  Name:               mssql
  Namespace:          default
  CreationTimestamp:  Fri, 12 Mar 2023 20:45:20 +0530
  Selector:           app=mssql
  Labels:             <none>
  Annotations:        <none>
  Replicas:           3 desired | 3 total
  Update Strategy:    RollingUpdate
    Partition:        0
  ..
  ..

That’s it you have now completed the phased rollout of the SQL Server update.

I hope you find this blog helpful in planning and executing SQL Server container updates on the Kubernetes platform. Happy learning!!