SQL Server – Its -MS + SQLServer

I love giving interviews, one of the question asked in one of my interview question was “on which particular edition automatic page repair is possible (in context of database mirroring)”.

That became a nightmare question for me as I had never heard like edition specific features for database mirroring, still I answered standard and enterprise edition and added automatic page repair is not edition specific and we can use it for both synchronous and asynchronous mirroring.

So this time, its automatic page recovery with help of database mirroring. In this demo I am using a tool (Hex editor—not so much new) to corrupt my database and then by enabling mirroring, ill build up my confidence for next interviews in near future.

I wanted to check this feature by using Always On, but I don’t have cluster on system. So lack of resources prompted me to check with database mirroring

Here it goes

I have created a database akash, took full backup and then transaction log backup and restored it on another instance of my machine in norecovery mode.

Created TCP endpoints for database mirroring and to set partner On

I then created a table akash again as I love my name inserted few values and used dbcc ind to get a page number for that table

create table akash
(id int identity)

insert into akash default values
go 2000
 
dbcc ind('akash','akash',-1)

So I wanted to corrupt page number 89 here, important point is if you want to use hex editior you have to search for Offset value with Ctrl+G which is calculated by Page*8192(size of page)

In my case offset was 729088.Now let’s see how Hex editor looks like

I had to stop my Principle instance and now my mirror database was in disconnected, In recovery mode which is very logical

I changed some values and then started my principal instance again

Now dbcc checkdb was throwing error after sometime I executed dbcc checkdb again and got no error..Yuppiee

 
Regards

Akash Gautam

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook