// work / database administration

High availability and disaster recovery for a fintech back office

Designed and built a two-site SQL Server Always On setup with verified backups, tested restore drills and the runbooks to go with them, for a company that had never restored a backup.

Client
Payments and lending back office
Industry
Financial services
Year
2024
Role
Database administrator and DR lead
Duration
6 weeks
Server room aisle lined with metal equipment racks
Photo: @ismailenesayhan on Unsplash

Context

A payments and lending company ran its core ledger on a single SQL Server with nightly backups to a share on the same network. The backups had never been restored. A regulator’s questionnaire asked for the recovery point and recovery time objectives, and nobody could answer with a number they had tested.

The problem

Give the business honest, tested answers to “how much can we lose?” and “how long until we are back?”, and make both numbers small enough to satisfy the regulator and the board. The budget allowed a second site but not a second team.

Approach

Agree the targets first. With the operations lead and the compliance officer we settled on a recovery point of five minutes and a recovery time of thirty, written down before any design. Everything that followed was tested against those two numbers.

High availability. A three-node Always On availability group: primary and a synchronous secondary in the main site for automatic failover, and an asynchronous secondary in the second site for disaster recovery. A listener gives the applications one connection string, so failover needs no application change. Read-only reporting was pointed at the secondary, which took load off the primary as a side effect.

Backups. Full backups nightly, differentials every six hours, log backups every five minutes, all copied to immutable off-site blob storage with a 35-day retention. That log cadence is what delivers the five-minute recovery point.

Verification. A backup nobody has restored is a hope, not a plan. Every night an automated job restores the latest chain onto a verification server, runs DBCC CHECKDB, and reports the result to the dashboard and to the on-call channel. A red tile in the morning means someone looks before anyone needs it.

Drills. I ran three full disaster-recovery drills with the operations team: site failover, point-in-time restore to before a simulated bad deployment, and a full rebuild from off-site backups alone. Each drill produced a timed runbook. The full-rebuild drill took 20 minutes end to end, inside the 30-minute target, so that is the number the company now reports.

Monitoring. Replica health, synchronisation lag, backup age and verification results are on one Grafana board, with alerts routed to PagerDuty for the two conditions that matter: lag beyond the recovery point, or a failed verification.

Outcome

The regulator’s questionnaire now has tested numbers in it: five-minute recovery point, twenty-minute recovery time. Patching happens in business hours with an automatic failover instead of a weekend outage window. The drills run quarterly on a calendar, and the operations team runs them without me.

Need something like this?

This is the kind of work I do under database administration. Tell me about your version of the problem.