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

Posted on by — No Comments ↓

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

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). 

 

 

 

 

amvin87_0-1683035003100.png

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

amvin87_1-1683035076808.png

amvin87_2-1683035099277.png

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:

  1. 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 .. .. .. ​
  2. 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 .. ..​
  3. 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  

     

    amvin87_4-1683036192946.png
  4. 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 :smile:

    amvin87_4-1683036440310.png

    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.

    amvin87_0-1683036589857.png

     

  5. 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:

    amvin87_0-1683036767047.pngamvin87_1-1683036786662.pngamvin87_2-1683036810609.png

     

     

     

  6. 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

     

    amvin87_1-1683036905965.png

    Once the failover is completed the dashboard looks like this:

     

    amvin87_2-1683036926958.png
  7. 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​amvin87_3-1683036972634.png
  8. 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​amvin87_4-1683037016041.png

    That's it; you've finished upgrading the SQL Server containers deployed on Kubernetes and configured for Always On availability groups :smile:

     

 

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.