Building an Always-On SQL Server Platform for an Australian Healthcare Provider

For a healthcare provider, database availability is not an infrastructure statistic. Clinical and administrative systems are read by people making decisions, and when the database behind one of them stops answering, the effect is immediate and visible to patients.

This provider’s SQL Server environment was running on standalone virtual machines. Backups existed and were sound, but a backup answers a different question from the one that matters at nine on a Monday morning: it tells you the data is recoverable, not that the service is available. Losing the production server meant somebody restoring a database while the applications waited.

And availability was only half the requirement. The databases hold health information, which Australian privacy law treats as a special category. A design that delivered resilience by making another unencrypted copy of the data would have solved one problem by creating another.

Two SQL Server Enterprise instances on virtualised Windows Server, joined by Windows Server Failover Clustering — quorum configured with a file-share witness so the cluster can lose a node and still know it has one, distributed transaction support configured, and the cluster properties tuned rather than left at their defaults.

On top of that, an availability group with synchronous commit and automatic failover. That is the change that matters. A standalone model asks "how quickly can we repair the database server?" This one asks "can another server take over?" — and answers it without waiting for an administrator to decide.

Applications connect through an availability group listener rather than a server hostname, so when the primary role moves between replicas the application tier keeps using the same endpoint. Without that, a successful failover still leaves every application pointed at a server that is no longer primary, which is how an HA environment manages to fail over and go down at the same time.

The databases were brought in under a controlled process rather than a switch: a fresh full backup and the required transaction log backup for each, added to the availability group, restored onto the secondary in the correct recovery state, then joined. Repeated per database. It is slower than the automatic seeding option and it means the initial synchronisation is something we watched rather than assumed.

Transparent Data Encryption was applied across the application databases, which is straightforward on a standalone instance and less so inside an availability group: the master key and certificate are created and protected on the primary, and the certificate and its private key have to be present on the secondary as well, or the encrypted database cannot join. Encryption was then enabled per database and the state verified through SQL Server’s own encryption metadata rather than taken on trust.

The point is that the client was never asked to choose between availability and encryption at rest. Both were engineered in, which is the only acceptable answer when the data is health information and the secondary replica is a second complete copy of it.

A standalone production dependency became an availability group. A server hostname became a listener. A passive recovery option became a synchronised secondary that can take the primary role on its own. The databases are encrypted at rest, on both replicas. And monitoring, maintenance, backups, alerts, patching and a documented quality review went in as part of the platform rather than onto a list of things to do later.

For an organisation whose critical systems cannot wait for a failed database server to be repaired, that is the whole difference: the question stopped being how fast the server can be fixed, and became whether anyone outside the IT team needs to know it broke.