Most people know they need backups. Far fewer know they need integrity checks, and the difference between the two matters more than it sounds.
Backups protect you from losing data. Integrity checks tell you whether you have some data corruption.
What’s actually going on
Your data sits on disk, and disk has a lifespan. At some point a data page can go corrupt. When that happens, the data on that page is inaccessible. You can’t read it, and you can’t get it back unless you hold a backup from before the corruption occurred.
A database integrity check doesn’t prevent any of that. I want to be clear about it, because it’s often sold as though it does. What it does is give you early warning that corruption has occurred, so you can deal with it while dealing with it is still possible.
Some corruption is repairable in place. Some of it needs a backup to go back to. Which of those you’re facing is something you want to find out early.
Why the timing is the whole problem
Here’s the scenario that catches people out.
If you’re not running integrity checks regularly, you don’t find out when corruption happens. And if it’s older data that the application doesn’t touch often, historical records, archived transactions, anything that sits there being retained rather than being used, you won’t find out at all. Not until somebody runs a query that needs it and gets an error back.
That data may have been corrupt for months. It may have been corrupt for years.
And that’s when the real question arrives: do you still have a backup from before it went? If enough time has passed, almost certainly not. Your retention policy has long since rolled over the good copies. So you have a backup regime that worked exactly as designed, and it can’t help you.
“We moved to the cloud, so this doesn’t apply”
It’s a reasonable assumption. It’s also wrong.
Behind any cloud platform there’s still storage, and storage still fails. The provider manages it well, but managed storage isn’t immune storage.
I know of an environment that was upgraded onto a brand new server with brand new disk. Within a week the storage had problems, crashed, and had to be replaced. You can run for years on ageing disk without a moment’s trouble, or lose brand new disk in days. There’s no pattern to it and no mean time you can plan around.
Which means the checks and balances need to be running either way. On-premises, in Azure, in AWS, it makes no difference to this particular risk.
What good looks like
Integrity checks running on a regular schedule, with the results actually going somewhere a human sees. A backup retention policy set with the possibility of undetected corruption in mind, not just the possibility of a server failure. And someone who knows what to do with the answer when a check comes back bad, because that’s a stressful morning to be working out from first principles.
None of this is exotic. It’s routine database maintenance. It just has to be happening.
—
Warwick Rudd is a Microsoft Certified Master and Data Platform MVP, and the founder of SQL Masters Consulting. More on how we approach this: Backup, Recovery & Data Integrity.
Leave a Reply