Upgrade SQL Server container with Always On availability groups configured on Kubernetes!

Microsoft

May 02, 2023

This is a follow-up to my previous blog post I published a while ago titled "Updating SQL Server containers deployed on Kubernetes!" In that blog post, I discussed two approaches that are OnDelete & Rolling options to update your Kubernetes-deployed independent SQL Server container instances.

In this article, we will go a step further and upgrade SQL Server containers running SQL Server 2019 to SQL Server 2022, with High Availability (HA) configured using Always On availability groups and the cluster stack provided by DH2i. As documented in this article: "Deploy availability group with DH2i for SQL Server containers on AKS." configuring Always On availability group for SQL Server containers with DH2i is a fully supported solution stack for SQL Server HA.

We'll use the "OnDelete” upgrade option today, but you can also use the Rolling upgrade option. As discussed in the previous blog, when the .spec.updateStrategy.type of a StatefulSet is set to RollingUpdate, the StatefulSet controller will delete and recreate each Pod in the StatefulSet. It will update each Pod one at a time, in the same order as Pod termination (from the largest ordinal to the smallest).

As a result, if you have three Pods, namely <yourpodname>-0 (Secondary), <yourpodname >-1 (Primary), and <yourpodname>-2 (Secondary), you must make <yourpodname>-0 the primary because it has the least ordinal index and will be updated last when you choose rolling upgrade. As you may be aware, when performing rolling upgrades for availability groups, the best practice guide requires you to first upgrade the secondaries, then perform a failover from the primary to any of the upgraded secondaries. Finally, upgrade the old primary, so in this case, making <yourpodname>-0 the primary ensures that it is updated after you have updated all the secondaries (<yourpodname>-2 and <yourpodname>-1) and performed the failover.

Deploying SQL Server containers and configure Always On availability group:

As previously stated, we will use the OnDelete upgrade option for this blog. I'm using the script below to deploy three SQL Server replicas as containers and to enable high availability with the DH2i cluster stack. DH2i’s DxEnterprise will be deployed with sidecar architecture as described here: Deploy availability groups with DH2i DxEnterprise sidecar on Kubernetes - SQL Server | Microsoft Learn.

apiVersion: v1
kind: Service
metadata:
  name: mssql-0
spec:
  clusterIP: None
  selector:
    statefulset.kubernetes.io/pod-name: mssql-0
  ports:
  - name: dxl
    protocol: TCP
    port: 7979
  - name: dxc-tcp
    protocol: TCP
    port: 7980
  - name: dxc-udp
    protocol: UDP
    port: 7981
  - name: sql
    protocol: TCP
    port: 1433
  - name: listener
    protocol: TCP
    port: 14033
---
apiVersion: v1
kind: Service
metadata:
  name: mssql-1
spec:
  clusterIP: None
  selector:
    statefulset.kubernetes.io/pod-name: mssql-1
  ports:
  - name: dxl
    protocol: TCP
    port: 7979
  - name: dxc-tcp
    protocol: TCP
    port: 7980
  - name: dxc-udp
    protocol: UDP
    port: 7981
  - name: sql
    protocol: TCP
    port: 1433
  - name: listener
    protocol: TCP
    port: 14033
---
apiVersion: v1
kind: Service
metadata:
  name: mssql-2
spec:
  clusterIP: None
  selector:
    statefulset.kubernetes.io/pod-name: mssql-2
  ports:
  - name: dxl
    protocol: TCP
    port: 7979
  - name: dxc-tcp
    protocol: TCP
    port: 7980
  - name: dxc-udp
    protocol: UDP
    port: 7981
  - name: sql
    protocol: TCP
    port: 1433
  - name: listener
    protocol: TCP
    port: 14033
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mssql
spec:
  serviceName: "mssql"
  replicas: 3
  podManagementPolicy: Parallel
  selector:
    matchLabels:
      app: mssql
  template:
    metadata:
      labels:
        app: mssql       
    spec:
      securityContext:
         fsGroup: 10001
      containers:
      - name: sql
        image: mcr.microsoft.com/mssql/server:2019-latest
        env:
        - name: ACCEPT_EULA
          value: "Y"
        - name: MSSQL_ENABLE_HADR
          value: "1"
        - name: MSSQL_SA_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mssql
              key: MSSQL_SA_PASSWORD
        volumeMounts:
        - name: mssql
          mountPath: "/var/opt/mssql"
      - name: dxe
        image: dh2i/dxe
        env:
        - name: MSSQL_SA_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mssql
              key: MSSQL_SA_PASSWORD
        volumeMounts:
        - name: dxe
          mountPath: "/etc/dh2i"
  volumeClaimTemplates:
  - metadata:
      name: dxe
    spec:
      accessModes:
      - ReadWriteOnce
      resources:
        requests:
          storage: 1Gi
  - metadata:
      name: mssql
    spec:
      accessModes:
      - ReadWriteOnce
      resources:
        requests:
          storage: 1Gi

Here is how the cluster looks after I deploy the above script and run the commands described in the document to configure SQL Server Always On availability groups. In total, I have three pods, each pod running two containers, one SQL Server container and the other DH2i sidecar container to provide the required cluster stack for Always On availability groups. I have three SQL Server instances: mssql-0 (Primary), mssql-1 (Secondary), and mssql-2 (Secondary).

When you look at the resources using SSMS and the Always On dashboard here is how the resources are configured:

As we used the sidecar architecture during the deployment, we will now upgrade the SQL Server instances from SQL Server 2019 to SQL Server 2022 independently of the DH2i containers running on the same pod.

Upgrade the SQL Server 2019 instances to SQL Server 2022:

To begin the upgrade process, use the command below to change the update strategy on the statefulset deployment from Rolling Upgrade to OnDelete upgrade.

C:\>kubectl patch statefulset mssql -p "{\"spec\": {\"updateStrategy\": {\"type\": \"OnDelete\",\"rollingUpdate\": null}}}"
statefulset.apps/mssql patched
 
# Let us now confirm that the preceding command was successful and that the Update strategy has been changed to the OnDelete option.

C:\>kubectl describe statefulset mssql
Name:               mssql
Namespace:          default
CreationTimestamp:  Tue, 25 Apr 2023 16:48:58 +0530
Selector:           app=mssql
Labels:             <none>
Annotations:        <none>
Replicas:           3 desired | 3 total
Update Strategy:    OnDelete
Pods Status:        3 Running / 0 Waiting / 0 Succeeded / 0 Failed
Pod Template:
  Labels:  app=mssql
  Containers:
   sql:
    Image:      mcr.microsoft.com/mssql/server:2019-latest
..
..
..

With this, we can now update the statefulset to use the SQL Server 2022 image by running the command below. Please keep in mind that running this command will not trigger the upgrade; the upgrade will occur only when you delete the pod and kubernetes recreates the deleted pod.

C:\>kubectl set image statefulset mssql sql=mcr.microsoft.com/mssql/server:2022-latest
statefulset.apps/mssql image updated
 
# Verify that the image is updated.
C:\>kubectl describe statefulset mssql
Name:               mssql
Namespace:          default
CreationTimestamp:  Tue, 25 Apr 2023 16:48:58 +0530
Selector:           app=mssql
Labels:             <none>
Annotations:        <none>
Replicas:           3 desired | 3 total
Update Strategy:    OnDelete
Pods Status:        3 Running / 0 Waiting / 0 Succeeded / 0 Failed
Pod Template:
  Labels:  app=mssql
  Containers:
   sql:
    Image:      mcr.microsoft.com/mssql/server:2022-latest
..
..

Now that we are ready to upgrade the SQL Server containers, we will begin by updating all our secondary servers in accordance with best practices for rolling upgrades for SQL Server Always On availability groups. Before we begin the upgrade, we must ensure that our secondary replicas are in synchronized state, as shown below
I perform the upgrade on the mssql-2 instance by deleting the pod with the following command:
```
C:\>kubectl delete pod mssql-2
```
and you'll notice that the pod is recreated by the Kubernetes cluster, as shown below; the upgrade took only 6 seconds

And a few more seconds for the mssql-2 instance to rejoin the AG; as shown below, the mssql-2 instance is now upgraded to SQL 2022 version, and the database has rejoined the AG in "Synchronized/In-recovery" mode. At all times, the primary pod mssql-0 was still operational and accepting connections, and the secondary pod (mssql-1) allowed read-only connections.
Now that we are ready to upgrade the other secondary, mssql-1, we use the same command to delete the pod:
```
C:\>kubectl delete pod mssql-1
```
And you'll see the same thing happen: the pod is deleted and recreated with the new image, rejoining the AG with the database state "Synchronized/In-Recovery", as shown below:
It is now time to upgrade the primary pod, mssql-0, but before we do so, we must first failover the AG from the current primary, mssql-0, to one of the upgraded secondaries; in this case, I will failover to mssql-1 and make it the primary. To perform the failover, I use the DxAdmin and follow the steps below

Once the failover is completed the dashboard looks like this:
As you can see, mssql-1 is now the new primary, and the old primary (mssql-0) cannot sync because it is running an older version of SQL Server. We upgrade the old primary mssql-0 by deleting the pod, as shown below.
```
C:\>kubectl delete pod mssql-0
```
When the old primary mssql-0 rejoins the AG as secondary, the database will be in the "Not synchronizing/In-recovery" state, which is normal. You will need to resume the AG by running the T-SQL command on the mssql-0 instance as shown below, and then the database will join as secondary and should be in sync:
```
alter database [test] set hadr resume
```
That's it; you've finished upgrading the SQL Server containers deployed on Kubernetes and configured for Always On availability groups