Database Corruption on the Principal AND the Mirror
Over the past year, I’ve read a number of Paul Randal’s (b|t) blog posts regarding database corruption. I knew he had corrupt databases available for download at sqlskills.com, but I’d never found time to look at them.
That changed last week when the newest member of my team, Nick Schwab (b|t), was looking at mirroring. He asked about Automatic Page Repair, specifically what would happen if a page was corrupted on the principal and the mirror at the same time. I told him I did not know and asked him to run some tests.
Here is what he found. If the page was corrupted on the principal and was read from disk, the initial attempt would error but Automatic Page Repair would be triggered and subsequent access attempts would succeed. If a page was corrupted on the mirror, SQL Server would take no action until a role switch occurred and the page was read from disk on the new principal. At that point Automatic Page Repair would kick in.
That brings us to Nick’s question. If the same page was corrupted on both the principal and the mirror, Automatic Page Repair would pull the mirror copy of the page over and still fail on corruption. I understand that the possibility of this happening in a live environment is extremely small, but it is worth keeping in mind for mirrored applications. At the least, it is a good argument for having a restore strategy (see Myth Number 30-30 here).
Many thanks to Kendra and Paul for making this information available at their blogs.