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 each candidate in rank order until one succeeds. SQL Server refuses a graceful failover on any replica that could lose data, so trying the next candidate is always safe. If a secondary was recently SYNCHRONIZED, this succeeds with zero data loss. After every replica restarts at once, the former primary (back in RESOLVING) can be the only replica SQL Server accepts, and it is tried as well.

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	Candidates are ranked by AG configuration sequence number, then by last hardened LSN. A forced failover (Tier 3) only targets the highest-ranked candidate; a graceful failover may promote a lower-ranked one, because SQL Server only allows it without data loss.
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 (within about a second) → scans all replicas
5. Operator attempts graceful failover (Tier 1/2) on each SYNCHRONOUS_COMMIT candidate in rank order, stopping at the first that succeeds
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, renews the SQL Server write lease, and re-asserts SET (ROLE = SECONDARY) on the remaining secondaries so they synchronize with the new primary
10. Old primary pod restarts → comes up in RESOLVING state (does not auto-promote with CLUSTER_TYPE=EXTERNAL)
11. Operator detects RESOLVING replica → rejoins it as SECONDARY (see Post-failover recovery)

The operator notices an expired Lease through a watcher that checks the AG primary Leases every second, so failover does not wait for the next periodic reconcile. Connections to an unreachable replica time out within the caller's deadline instead of the SQL driver's 15s dial timeout, so a replica that is cut off from the network does not hold up the failover scan.

Post-failover recovery

With CLUSTER_TYPE = EXTERNAL, a secondary that was not the failover target can keep its database session on the old primary until the cluster manager re-asserts its role. For 60s after a failover the operator reconciles the AG every 3s and tells every secondary that is not healthy from the new primary's view to rejoin, at most every 5s. While no secondary is synchronized with the new primary (commits are blocked in that state), it also restarts the HADR endpoints of the new primary and of each disconnected secondary, at most every 20s.

A secondary that stays NOT SYNCHRONIZING, or a former primary stuck in RESOLVING, is repaired in escalating steps, least disruptive first:

1. Rejoin the replica as SECONDARY, every reconcile.
2. Restart the stuck replica's HADR endpoint.
3. Restart the primary's endpoint as well, only when the primary has no synchronized secondary. Restarting it drops every secondary's session, so while another secondary is synchronized the operator leaves it alone.
4. As a last resort, re-seed the replica: remove it from the AG, drop the AG and its broken databases on the replica, re-add it, and let automatic seeding re-create the databases. The re-seed never drops the AG on a replica that reports PRIMARY.

Recreated replicas and stale connections

A replica pod that is force deleted or loses its node never closes its TCP connections, and it comes back with a new IP. The primary can keep an ESTABLISHED HADR session to the old IP until the kernel gives up on it (about 15 minutes, tcp_retries2), and until then the recreated replica cannot rejoin. When the primary reports a reachable non-primary replica as disconnected for 30s, the operator restarts the primary's HADR endpoint (at most every 2 minutes) to drop the stale session.

After a network partition heals

A primary that was cut off from the network while the operator failed over to another replica still reports PRIMARY once it is reachable again. The Kubernetes Lease decides which one is live:

• While more than one replica reports PRIMARY, the operator skips all other AG maintenance.
• A replica that reports PRIMARY without holding a valid Lease for 30s is demoted with SET (ROLE = SECONDARY). If no replica holds the Lease, nothing is changed.
• If SQL Server refuses the demotion (Msg 41104), the operator restarts SQL Server on the stale replica after 2 minutes, at most every 2 minutes. It comes back RESOLVING and rejoins the live primary as a secondary.
• A replica that has a synchronized secondary is never restarted this way.

The 30s and 2-minute delays leave room for a planned handover, during which the old primary keeps renewing the Lease for a few seconds after the new one is promoted.

With synchronous commit, the cut-off primary cannot acknowledge writes while it has no synchronized secondary, so nothing it holds was committed from the application's point of view.

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.

The kubelet sends SIGTERM to each container as soon as that container's own hook returns, so the mssql container has a matching preStop hook. It keeps SQL Server running until the sidecar's hook has finished (up to preStopFailover.timeoutSeconds + 5): the sidecar writes a marker to a shared in-memory volume once the role is handed over and the other secondaries are synchronizing with the new primary. Until then this replica can be the new primary's only synchronized partner, so stopping it earlier stalls writes. The hook then sends SIGTERM to sqlservr itself: the image's PID 1 is a launcher script that ignores SIGTERM, so without this SQL Server would be killed at the end of the grace period, and the remaining replicas would stall commits for the session timeout (10 s by default) waiting for it.

If the handover does not complete, the operator does not treat the terminating pod as the current primary, even though it still reports PRIMARY while SQL Server shuts down. Once the primary Lease expires, it fails over to a synchronized secondary.

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.

Pausing an Availability Group

An AG can be safely stopped and resumed with spec.paused, without the data-loss risk of scaling every replica to zero at once. See Pausing & Resuming.

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"