AG Failover Testing - No failover when storage disconnected

[dbafromthecold]

Description

During failover testing of the AG operator on a 4-node Kubernetes cluster, automatic failover did not occur after a simulated storage failure affecting the primary replica.

The Kubernetes environment consists of: -

• 1 control plane node
• 3 worker nodes
• All nodes deployed as VMware virtual machines
• Persistent storage is provisioned through PX-CSI.

The environment consisted of the sample Availability Group deployment with: -

• 1 primary replica
• 2 secondary replicas
• Database health enabled

Both secondary replicas configured as: -

• Synchronous commit
• Automatic failover enabled

Test Procedure

Generate transactional workload against the primary replica using HammerDB.
Identify the node hosting the primary AG pod.
Simulate a storage failure by disconnecting the volume provisioned to the primary pod from the underlying node.

Expected Behaviour

When the primary replica becomes unavailable due to storage loss, the Availability Group should automatically fail over to one of the synchronous secondary replicas configured for automatic failover. The application workload should reconnect to the new primary with minimal interruption.

Actual Behaviour

After the primary replica loses access to its storage: -

• The primary replica goes offline.
• No automatic Availability Group failover occurs.
• The AG remains unavailable until manual intervention is performed.

Recovery required: -

• Reattaching the disconnected volume to the original node.
• Bringing the original primary replica back online.
• Manually failing over the Availability Group.

Impact

A storage-level failure affecting the primary replica results in complete AG unavailability, despite synchronous replicas with automatic failover being configured. This prevents the deployment from meeting expected high availability behaviour during node or storage fault scenarios.

Logs

Operator Issue.zip

[nocentino]

Fixed in v0.45: https://github.com/nocentino/sql-on-k8s-operator/releases/tag/v0.45

Root cause: neither the readiness probe nor sp_server_diagnostics can detect storage failure — sp_server_diagnostics's io_subsystem component can only ever report clean/warning per Microsoft's docs, never error. The readiness probe now checks, when the local replica is PRIMARY, whether any AG database has gone SUSPECT/RECOVERY_PENDING/OFFLINE/EMERGENCY — the states a lost/detached volume actually produces — and fails if so, triggering the existing NotReady failover path.

Also added spec.requiredSynchronizedSecondariesToCommit (Pacemaker's REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT equivalent) to reduce the chance of a failover being rejected with error 41142 when the primary dies, plus a FailoverBlocked status condition so a stuck 41142 loop is visible via kubectl get sqlag instead of only in logs.

Live-validated on AKS with Portworx (matching your PX-CSI environment): a storage-node-failure test (px/service=stop under TPC-C load) now triggers automatic failover with zero data loss. Full regression suite (planned failover, preStop hook, unplanned kill x3, SIGSTOP gray failure) passes with no regressions.

Details, including a documented limitation around compound/cascading failures, are in the release notes and README (Automatic Failover section).