A 7.5 TB SQL Server estate

Where it started

A single SQL Server 2012 Standard instance carrying every in-house platform in the company. Backups ran. Nobody had restored one in anger. There was no failover story, which in practice means the recovery plan was "the outage lasts as long as it lasts."

That is an extremely common position and it is fine right up until the day it isn't.

The path forward

Four migrations, each one justified on its own terms rather than as a version-chasing exercise:

• 2012 Standard → 2016 Standard. Compatibility-level testing, baseline established, the estate made current enough to have options.
• 2016 → 2019 Enterprise, two-node active-passive. The first real failover capability. Enterprise licensing became defensible once availability was the requirement rather than a nice-to-have.
• 2019 → 2022 Enterprise, two-node active-active. The current topology.

The move to active-active was the interesting one. In active-passive, the second node is a full Enterprise licence sitting idle waiting for a disaster — real money doing nothing most of the year. In active-active each node carries a primary workload and hosts the other's synchronous secondary, so both are earning, and the failover capability comes along with it rather than instead of it.

Applications connect through the availability group listener, which is a single DNS name. When a failover happens, that name resolves elsewhere. Nothing in the application configuration changes, which is what makes planned failovers routine rather than an event.

Decisions that mattered

Synchronous commit, accepted with eyes open. Synchronous replication means every commit waits for the secondary to harden the log. That is a real write-latency cost, and it buys a guarantee that a failover loses zero committed transactions. For this estate — engineering approvals, financial records, document versions — the trade is obviously right. It would not be right for everything.

Patching is a rolling failover. Patch the secondary, fail over, patch the former primary, fail back. The consequence is that high availability gets exercised on a routine cadence instead of being tested for the first time during an actual incident. Most clusters that fail during a disaster fail because nobody had ever actually failed them over.

TDE, plus the certificate discipline it demands. Encryption at rest is straightforward to enable and quietly creates a new way to lose everything: an encrypted backup with no accompanying certificate is not a backup, it is a very durable random number. Certificate and key management is part of the backup procedure, not a side note next to it.

A backup cadence chosen from the recovery point, backwards. Weekly full, six-hourly differential, fifteen-minute transaction log. The log interval is not a habit — it is the recovery point objective. Fifteen minutes of exposure is the number the business agreed to, and the schedule is derived from it rather than the other way round.

Restores are tested, not assumed. Periodic test restores and planned failover exercises. An untested backup is a belief, and the point of all of this is to replace beliefs with evidence.

Monitoring that pages a human. Custom T-SQL diagnostics on a schedule with PowerShell alerting by email, tracking performance and — just as important — database growth, so capacity planning is a trend line rather than a surprise.

The runbooks are written down. Failover, restore and patching procedures live in a maintenance manual. A recovery capability that exists only in one person's head is a single point of failure wearing a convincing disguise.

What this is really evidence of

Anyone can stand up a cluster. The part that takes years to learn is the operational discipline around it: that the licence model shapes the topology, that patching is how you prove availability, that encryption creates new failure modes, and that a procedure nobody has rehearsed is not a procedure.