Two years ago we moved our order database to our cloud provider's managed database service. One of the features we valued most was point in time restore. The service keeps a continuous record of changes, and we can bring the database back as it was at any minute within the retention period. Ours was seven days, the default in the template we used, and at the time it felt generous. Our old nightly backups had given us one restore point a day.
In September a supplier's update to our pricing module introduced a fault. When an order was amended, its discount lines were recalculated from the list price rather than the agreed customer price. Nothing broke. Amended orders were undercharged by between three and eight percent, and the invoices went out as normal.
Finance found it in the month end margin review, eleven days after the update. To work out which orders were wrong and what they should have been, we needed the database as it stood before the change. The earliest point we could restore was four days after it. The daily snapshots were kept for seven days as well, because the same template had set both.
We rebuilt the answer by hand. Two analysts spent three weeks comparing invoices with the audit trail and the customers' own purchase orders. About two thousand three hundred orders were affected, worth sixty one thousand pounds. We recovered most of it with corrected invoices, at some cost to goodwill, and wrote off the rest.
We had chosen the retention period by thinking about crashes. When a server fails or a table is dropped, everyone knows within minutes, and seven days is plenty. But the failures that need old data are usually quiet. They are found at the speed of whatever process notices them: a month end, a quarterly audit, a customer querying an invoice.
For every system we now write down how long a quiet error could go unnoticed, and set retention beyond that. For the order and finance databases that meant the service's maximum of thirty five days, plus a monthly snapshot copied to a separate account and kept for thirteen months. The extra storage costs about three hundred pounds a month.
A restore window should be measured against how long it takes to notice a problem, not how long it takes to fix one.
– Serguey Shinder
Top comments (0)