Automatic Page Repair With SQL Server Database Mirroring - Part II

Please have a look to the Part I for better understanding.

In our previous part, there was lots of steps to configure the Mirroring and Manual Failover thing. I hope you would have enjoyed that. Thanks for your time on that!!

Here we will be discussing about - How the corrupted Page will be repaired automatically by the Mirroring

Just recap about the environments participated on Mirroring. We have failover it again to being back to the below state

Let me make sure the Mirroring environments are in Sync

Everything is in Sync

1. let me corrupt some Data Page of Principal Database "PrimaryDB1" (In PANDIAN\SQL2012)

2. Here is the Data Page we trying to corrupt
:CONNECT PANDIAN\SQL2012
SELECT %%LockRes%% [Page Allocation],* FROM PrimaryDB1.dbo.TABLE1

Ok. I try to corrupt the page id "232" (232 x 8192 = 1900544)

Disclaimer: Please don't try to corrupt the data page manually in any of the PRODUCTIONS Or other live Databases environments. It may lead to corrupt and non-operational the whole database If something goes wrong!!

3. let me use XVI32.exe tool to corrupt the Page

Actually, The plan is to open the data file(.mdf) in this tool in administrative mode to make the change on it's internal allocation bits

Let me get the File path of the Database
:CONNECT PANDIAN\SQL2012
USE PrimaryDB1
GO
Exec sp_helpfile

Normally, The database should be in OFFLINE to access the underlying Data/Log files.

But, We can't do that right now since the Database is in Mirroring. let's try once

:CONNECT PANDIAN\SQL2012
USE [Master]
GO
ALTER DATABASE PrimaryDB1 SET OFFLINE

Connecting to PANDIAN\SQL2012...
Msg 1468, Level 16, State 1, Line 10
The operation cannot be performed on database "PrimaryDB1" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 10
ALTER DATABASE statement failed.
Disconnecting connection from PANDIAN\SQL2012...

So, What to we do now ?

let me Stop the Mirror Now
:CONNECT PANDIAN\SQL2012
ALTER DATABASE PrimaryDB1 SET PARTNER SUSPEND

Let me STOP the SQL Service of the Primary database Instance(PANDIAN\SQL2012)

4. Let's corrupt the page id (232) using the Tool (XVI32.exe)
Open the Tool in Administrative mode

File --> Open --> Locate the data file (.mdf)

Address --> Goto --> 1900544

Change some of the Bits

Save the File. Its' done. We just corrupted the Data page

START the SQL Service

5. Let me access the Table

:CONNECT PANDIAN\SQL2012
SELECT %%LockRes%% [Page Allocation],* FROM PrimaryDB1.dbo.TABLE1

Yeah. See, The Page ID: 232 got corrupted as shown below

Connecting to PANDIAN\SQL2012...
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xefd30b4e; actual: 0x67db0b46). It occurred during a read of page (1:232) in database ID 5 at offset 0x000000001d0000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PrimaryDB1.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

let me check at the suspect pages system table 

:CONNECT PANDIAN\SQL2012

SELECT * FROM msdb.dbo.suspect_pages

See, The event_type column has 2 (Bad Checksum)

For detailed Info on msdb..suspect_pages

How do we know, The Mirroring repaired the corrupted page automatically Or Not ?

We have a system VIEW sys.dm_db_mirroring_auto_page_repair which will have a row for every automatic page-repair attempt on any mirrored database on the server instance

:CONNECT PANDIAN\SQL2012
SELECT * FROM sys.dm_db_mirroring_auto_page_repair

There is no records in this system View yet which means - Mirroring not started to repair the page yet!

Let me start the Mirror now and see...

:CONNECT PANDIAN\SQL2012
ALTER DATABASE PrimaryDB1 SET PARTNER RESUME

Wait for some time and Let the Mirroring repair the corrupted page in Principal Database

6. Let me access the corrupted Table again in Principal Database

:CONNECT PANDIAN\SQL2012

SELECT %%LockRes%% [Page Allocation],* FROM PrimaryDB1.dbo.TABLE1

It's done!!! - The corrupted page got repaired and The table back to operational

Let's make sure

:CONNECT PANDIAN\SQL2012

SELECT * FROM msdb.dbo.suspect_pages

See, The event_type column has 5 now (Repaired)

:CONNECT PANDIAN\SQL2012

SELECT * FROM sys.dm_db_mirroring_auto_page_repair

See, The page_status column has 5 (5 = Automatic page repair succeeded and the page should be usable)

What just happening when the page corrupted at Principal source ?

According to MSDN

a) When a read error occurs on a data page in the principal/primary database, the principal/primary inserts a row in the suspect_pages table with the appropriate error status. the principal then requests a copy of the page from the mirror.

b) The request specifies the page ID and the LSN that is currently at the end of the flushed log.

c) The page is marked as restore pending. This makes it inaccessible during the automatic page-repair attempt. Attempts to access this page during the repair attempt will fail with error 829 (restore pending)

d) After receiving the page request, the mirror/secondary waits until it has redone the log up to the LSN specified in the request. Then, the mirror/secondary tries to access the page in its copy of the database. If the page can be accessed, the mirror/secondary sends the copy of the page to the principal/primary. Otherwise, the mirror/secondary returns an error to the principal/primary, and the automatic page-repair attempt fails.

7. What will happen, If the Principal and Mirror versions are different ?

If I use SQL Server 2012 for Principal and SQL Server 2014 for Mirror

It can be able to failover from Principal to Mirror (2012 to 2014)

But, 

If I try to failover back 2014 to 2012, There will be a problem with below Err 

Error: 948, Severity: 20, State: 2.
The database 'PrimaryDB1' cannot be opened because it is version 782. This server supports version 706 and earlier. A downgrade path is not supported.

Here are the list of SQL Server Database Versions