Home > Corruption, Mirroring, SQL Server, T-SQL > Database Corruption on the Principal AND the Mirror

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.

Using these instructions from Kendra Little’s (b|t) blog, Nick was set to start corrupting pages.

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.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: