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:
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 )
In my opinion, you can use this update option for your SQL Server deployment if any of the following conditions are met:
The downside of this option is:
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:
kubectl patch statefulset mssql -p "{\"spec\": {\"updateStrategy\": {\"type\": \"OnDelete\",\"rollingUpdate\": null}}}"
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
..
..
..
kubectl set image statefulset mssql mssql=mcr.microsoft.com/mssql/server:2022-latest
Kubectl delete pod mssql-1
#Check the logs to see the version updated
kubectl logs mssql-1
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:
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
..
..
..
kubectl set image statefulset mssql mssql=mcr.microsoft.com/mssql/server:2022-latest
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
..
..
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!!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.