Always-On Availability Groups

The operator configures a SQL Server Always-On Availability Group (AG) across all replicas of a MSSQLAvailabilityGroup custom resource.

MSSQLAvailabilityGroup is a dedicated CRD, fully decoupled from MSSQLInstance — there is no availabilityGroup.enabled toggle. Every MSSQLAvailabilityGroup is always an AG and therefore requires size >= 2 (a primary plus at least one secondary). Use MSSQLInstance for standalone, non-replicated servers and MSSQLAvailabilityGroup when you want Always-On replication and failover.

Configuration

apiVersion: mssql.solanica.io/v1alpha1
kind: MSSQLAvailabilityGroup
metadata:
  name: my-mssql-ag
spec:
  size: 3  # at least 2 replicas required for an AG
  acceptEULA: true
  saPasswordSecret: my-mssql-ag-secret
  storage:
    size: 20Gi
  availabilityGroup:
    name: mssql-ag              # defaults to <cr-name>-ag; immutable once set
    availabilityMode: synchronous
    # readableSecondaries: all        # all | readOnly | no — read scale-out via <name>-replicas Service
    # contained: false                # SQL Server 2022 Contained AG (replicates master/msdb); immutable
    # endpointPort: 5022
    # leaseDurationSeconds: 10
    # leaseRenewIntervalSeconds: 3
    # allowDataLoss: false            # never force a data-losing failover (default)
    # forceFailoverGracePeriodSeconds: 30  # only used when allowDataLoss: true
    # databases:
    #   - mydb

How it works

On each reconcile, the operator runs the following steps:

1. Failover check — uses the Kubernetes Lease to detect primary failure (runs even when not all pods are ready)
2. Stale secondary repair — detects NOT SYNCHRONIZING or partner-suspended databases and triggers automatic re-seeding
3. Wait for pods — all replicas must be ready before initial setup proceeds
4. Discover primary — finds the current AG primary (defaults to pod-0 for initial setup)
5. Certificates — ensures an AG certificate Secret exists (auto-generated)
6. HADR endpoint — on each replica: creates a master key, imports the certificate, and creates the database mirroring endpoint (ENCRYPTION = REQUIRED ALGORITHM AES, port 5022 by default)
7. Create AG — on the primary: CREATE AVAILABILITY GROUP ... WITH (CLUSTER_TYPE = EXTERNAL, DB_FAILOVER = ON)
8. Join secondaries — each secondary runs ALTER AVAILABILITY GROUP ... JOIN
9. Add databases — listed databases are added to the AG with SEEDING_MODE = AUTOMATIC

All steps are idempotent (IF NOT EXISTS guards), so reconcile loops are safe.

Architecture

The operator uses CLUSTER_TYPE = EXTERNAL, which tells SQL Server that an external cluster manager (this operator) is responsible for failover decisions. This is the same mode used by the Microsoft Pacemaker resource agent on Linux. The operator's failover logic is modeled after the battle-tested ag-helper binary that ships with SQL Server on Linux.

Each MSSQL pod runs an AG sidecar container that:

• Polls the local SQL Server instance every 2 seconds for its AG role and health
• Renews a Kubernetes Lease (<cr-name>-ag-primary) when the local replica is PRIMARY
• Labels the pod with mssql.solanica.io/role=primary or replica for service routing

The operator watches the Lease — when it expires (primary pod is lost), the operator initiates failover.

Automatic failover

Tiered failover model

The operator uses a three-tier failover model to minimize data loss:

Tier 1/2 — Graceful failover (zero data loss):

When the primary Lease expires, the operator scans all replicas, applies pre-checks (see below), and attempts ALTER AVAILABILITY GROUP ... FAILOVER on the best candidate. If the secondary was recently SYNCHRONIZED, this succeeds with zero data loss.

Grace period — wait before escalating:

If graceful failover fails (e.g. the secondary is NOT SYNCHRONIZING because the primary just crashed), the operator keeps retrying graceful failover on every reconcile. During this window, the primary may return or the secondary may resynchronize — either avoids data loss.

Tier 3 — Forced failover (potential data loss, opt-in):

Forced failover is disabled by default (allowDataLoss: false). When no synchronized secondary is available for a graceful failover, the operator never issues FORCE_FAILOVER_ALLOW_DATA_LOSS on its own. Instead it keeps retrying graceful failover and surfaces a FailoverBlocked condition so an operator/DBA can intervene (recover the primary, wait for a secondary to resynchronize, or explicitly opt in to data loss).

Only when allowDataLoss: true is set does the operator escalate: after forceFailoverGracePeriodSeconds expires and graceful failover still fails, it issues ALTER AVAILABILITY GROUP ... FORCE_FAILOVER_ALLOW_DATA_LOSS. This recovers service availability but may lose committed transactions.

This mirrors Microsoft's mssql-server-ha ag-helper, which never falls back to a forced failover on its own.

FailoverBlocked condition

When automatic failover cannot proceed safely and allowDataLoss is false, the operator sets a FailoverBlocked status condition on the CR:

kubectl get mssqlavailabilitygroup my-mssql-ag -o jsonpath='{.status.conditions[?(@.type=="FailoverBlocked")]}'

Field	Value
status	True while blocked, False otherwise
reason	NoSynchronizedSecondary
message	Explains the situation and how to resolve it

The condition clears automatically once a primary is present again (a secondary resynchronizes and graceful failover succeeds, or the original primary recovers). To resolve a blocked AG you can: wait for a secondary to resynchronize, recover the failed primary, or set allowDataLoss: true to permit a forced failover.

Pre-checks (inspired by the Pacemaker ag-helper)

Before promoting any replica, the operator verifies:

Check	Description
SYNCHRONOUS_COMMIT	Only synchronous-commit replicas are eligible for promotion. Async replicas are never promoted.
Sequence number	The candidate must have the highest AG configuration sequence number across all reachable replicas.
Quorum	A majority of replicas must be reachable.
No existing primary	If any replica is already PRIMARY, failover is skipped (the sidecar will renew the Lease).

Failover timeline (primary pod deleted)

1. Kubernetes deletes the primary pod → StatefulSet recreates it
2. AG sidecar on the primary stops renewing the Kubernetes Lease
3. Lease expires after leaseDurationSeconds (default: 10s)
4. Operator detects expired Lease → scans all replicas
5. Operator attempts graceful failover (Tier 1/2) on the best SYNCHRONOUS_COMMIT candidate
6. If graceful failover succeeds → new primary serves reads/writes immediately, zero data loss
7. If graceful failover fails → operator keeps retrying graceful failover every reconcile
8. If no synchronized secondary is available:
   • allowDataLoss: false (default) → operator sets the FailoverBlocked condition and waits for manual intervention (never loses data)
   • allowDataLoss: true → after the grace period, operator issues FORCE_FAILOVER_ALLOW_DATA_LOSS (Tier 3)
9. Post-promote: operator sets REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT and renews the SQL Server write lease
10. Old primary pod restarts → comes up in RESOLVING state (does not auto-promote with CLUSTER_TYPE=EXTERNAL)
11. Operator detects RESOLVING replica → tears down stale AG, re-joins it as SECONDARY
12. Automatic seeding re-creates databases on the re-joined secondary

Post-failover recovery

After failover, some secondaries may be stuck in NOT SYNCHRONIZING (their database was seeded from the old primary's log fork). The operator automatically detects and repairs these:

1. Removes the stale replica from the AG on the new primary
2. Drops the AG on the stale secondary
3. Drops broken databases so automatic seeding can re-create them
4. Re-adds the replica to the AG on the primary
5. Re-joins the AG on the secondary → automatic seeding starts

Data loss considerations

Availability mode	Data loss risk on failover
synchronous (default)	None with Tier 1/2 — graceful failover preserves all committed transactions. Forced failover (Tier 3) is opt-in via allowDataLoss and, even with synchronous commit, rarely loses data — only uncommitted in-flight transactions.
asynchronous	Possible — transactions committed on the old primary but not yet replicated may be lost. Async replicas are never automatically promoted by the operator.

Configuration

Field	Default	Description
leaseDurationSeconds	10	How long the Kubernetes Lease is valid. Primary failure is detected after this expires.
leaseRenewIntervalSeconds	3	How often the sidecar renews the Lease. Must be less than leaseDurationSeconds.
allowDataLoss	false	Whether the operator may perform a forced, data-losing failover (FORCE_FAILOVER_ALLOW_DATA_LOSS) when no synchronized secondary is available. When false (default), the operator never loses data and instead surfaces a FailoverBlocked condition.
forceFailoverGracePeriodSeconds	30	How long to retry graceful failover before resorting to FORCE_FAILOVER_ALLOW_DATA_LOSS. Only applies when allowDataLoss: true. Set to 0 to force-failover immediately.

Planned failover: preStop hook

For planned pod terminations — rolling updates, node drains, kubectl delete pod — the ag-sidecar's preStop lifecycle hook promotes a healthy synchronous secondary before the primary pod is stopped. This avoids the ~2×leaseDurationSeconds write outage that the unplanned failover path would incur while it waits for the primary Lease to expire.

The hook is enabled by default whenever the Availability Group is enabled. On the primary pod it:

1. Queries the local AG role — if not PRIMARY, exits immediately.
2. Selects the best synchronous, connected, healthy secondary from sys.dm_hadr_availability_replica_states.
3. Connects to that secondary's FQDN and issues ALTER AVAILABILITY GROUP … FAILOVER (graceful, zero data loss).
4. Waits up to preStopFailover.timeoutSeconds for the promotion to complete.

Any failure (no eligible target, timeout, SQL error) is logged and the hook exits successfully so the kubelet still proceeds with pod deletion. The operator's tiered unplanned-failover path is still the safety net.

Pod terminationGracePeriodSeconds is set automatically to preStopFailover.timeoutSeconds + 40 so the kubelet gives the hook enough time before sending SIGTERM.

Field	Default	Purpose
preStopFailover.enabled	true	Set to false to disable — useful for chaos-style tests that intentionally kill the primary without pre-failover.
preStopFailover.timeoutSeconds	20	Hard deadline for the hook. Must be less than the pod's terminationGracePeriodSeconds (which the operator sizes accordingly).

Frozen-primary detection: sp_server_diagnostics

The unplanned-failover path in the operator triggers when the primary Lease expires. But a frozen primary — SQL Server process alive, scheduler wedged, network sockets still open — will keep the pod "running" and, if we did nothing more, the sidecar would keep renewing the Lease from Go code even though no SQL work is actually making progress.

To catch this, the sidecar runs EXEC sp_server_diagnostics @repeat_interval = 0 against the local instance on a configurable cadence with a hard client-side timeout. Each probe returns a component state (clean / warning / error / unknown) or, if the query itself times out, timeout — which is the primary signal we're looking for.

When N consecutive probes fail (error or timeout), the sidecar stops renewing its Lease. The primary Lease expires and the operator's existing tiered failover path promotes a healthy secondary. The sidecar also stops renewing the SQL Server internal write lease so app writes fail fast against the wedged primary rather than hanging.

The last observed state is published on the AG Lease as the mssql.solanica.io/primary-health annotation and surfaced on status.availabilityGroup.primaryHealth.

The check runs regardless of role (SECONDARIES also probe themselves), so a newly-promoted primary has a warm health signal from the very first tick.

Field	Default	Purpose
healthCheck.enabled	true	Set to false to disable — useful for chaos-style tests that intentionally freeze SQL Server (SIGSTOP, paused cgroup) to exercise other detection mechanisms.
healthCheck.threshold	system	Which sp_server_diagnostics component determines primary health. system (default; matches FAILURE_CONDITION_LEVEL 3), resource (also memory / tempdb pressure — level 4), query_processing (also non-yielding scheduler — level 5).
healthCheck.intervalSeconds	10	How often to probe. Range 2–30.
healthCheck.timeoutSeconds	10	Hard client-side timeout for a single probe. A timeout counts as a failure — this is how the frozen-primary case is caught. sp_server_diagnostics samples over an interval and takes several seconds to return, so this must be generous. Must be <= intervalSeconds. Range 2–30.
healthCheck.maxConsecutiveFailures	3	Consecutive failures required before the sidecar stops renewing the Lease. Absorbs transient blips. With the defaults an unhealthy primary is detected in ~30s, matching SQL Server's own HealthCheckTimeout. Range 1–10.

Admission validation (optional)

An opt-in ValidatingWebhookConfiguration enforces admission-time invariants that the reconciler cannot safely repair on a live AG. When enabled, the following mutations are rejected at kubectl apply time rather than causing reconciliation loops:

Create/Update checks:

• spec.acceptEULA must be true
• spec.saPasswordSecret must be non-empty
• spec.size >= 2 (an MSSQLAvailabilityGroup is always an AG; also enforced by the CRD schema)
• healthCheck.timeoutSeconds <= healthCheck.intervalSeconds
• leaseRenewIntervalSeconds < leaseDurationSeconds
• Duplicate database names in availabilityGroup.databases
• Either spec.storage.size or spec.storage.volumes must be set

Update-only (immutability) checks:

• spec.size cannot decrease (scale-down would strand replicas)
• spec.port, spec.saPasswordSecret, spec.storage.storageClassName, spec.storage.volumes (single-PVC ↔ separate-volume switch)
• availabilityGroup.name, availabilityGroup.endpointPort, availabilityGroup.availabilityMode, availabilityGroup.contained

Enabling the webhook:

1. Install cert-manager in the cluster.
2. Uncomment the [WEBHOOK] and [CERTMANAGER] sections in config/default/kustomization.yaml.
3. Pass -webhook-cert-path=/tmp/k8s-webhook-server/serving-certs (already in the manager's default flags) — the operator only registers the webhook when this flag is non-empty, so leaving cert-manager off is a supported, non-breaking mode.
4. make deploy IMG=<your-image>.

The webhook is validation-only (no defaulting, no conversion). All defaults come from CRD schema markers.

Certificates

AG replicas authenticate to each other using certificate-based authentication on the HADR endpoint. All replicas share the same certificate (SQL Server uses the cert to authenticate the mirroring endpoint, not to identify individual hosts).

The operator creates the certificate inside SQL Server on the primary (pod-0) using CREATE CERTIFICATE, then exports it via CERTENCODED() / CERTPRIVATEKEY() and stores the binary data in a Secret named <cr-name>-ag-certs. Secondaries import the certificate from this binary export using CREATE CERTIFICATE ... FROM BINARY.

This approach avoids external file format issues — SQL Server manages the certificate natively.

Certificate rotation

AG certificates are stored inside SQL Server's master database. Updating the Secret alone does not replace the already-imported certificate.

To rotate AG certificates, run the following on each replica after the Secret is updated:

DROP ENDPOINT hadr_endpoint;
DROP CERTIFICATE [ag_cert];

Then trigger a reconcile to re-import and recreate the endpoint:

kubectl annotate mssqlavailabilitygroup my-mssql reconcile=$(date +%s) --overwrite

Services

For a CR named my-mssql:

Service	Name	Type	Ports	Created When
Headless	my-mssql	ClusterIP: None	1433, 5022	Always
Primary	my-mssql-primary	ClusterIP	1433	Always — routes to the current primary replica
Replicas	my-mssql-replicas	ClusterIP	1433	Always — routes to readable secondaries (see readableSecondaries)
Exposed	my-mssql-exposed	Configurable	1433	expose.enabled=true

The operator automatically provisions the my-mssql-primary and my-mssql-replicas Services so applications can target the writable primary or fan reads out to readable secondaries without extra configuration. The AG sidecar labels each pod with mssql.solanica.io/role=primary or mssql.solanica.io/role=replica, which back these Services. You can reuse the same labels to build your own custom Services — for example one that routes only to the primary under a different name:

apiVersion: v1
kind: Service
metadata:
  name: my-mssql-write
spec:
  selector:
    app.kubernetes.io/instance: my-mssql
    mssql.solanica.io/role: primary
  ports:
    - port: 1433

Availability modes

Mode	Description
synchronous (default)	Zero data loss. Primary waits for secondary acknowledgement before committing.
asynchronous	Faster, but potential data loss on failover. Async replicas are never automatically promoted.

Status

AG health is reported in .status.availabilityGroup:

status:
  availabilityGroup:
    state: healthy          # healthy | degraded | pending | configuring | error
    primary: my-mssql-0.my-mssql.default.svc.cluster.local
    synchronizedReplicas: 3
    message: "3/3 replicas synchronized"