What happen to the redo queue?

Question

What happen to the redo queue?

mah_j

SSCrazy

Points: 2931
More actions
August 6, 2013 at 1:54 am

#276721

In Mirroring ,If the principal server suddenly fails or crashes when running synchronously
,what happen to the redo queue ?Are these loges hardened to the mirror db?These loges ,on principal do not be harden ,and if we have automatic failover when the session reconnects,it acts as mirror and they run synchronously.
Do I understand correctly?
What about unsent log?If I have unsent log what happen to them ?Do I loose them?
How can I prevent data loss?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The redo queue is the queue of log records on the mirror, log records that have already been hardened on the principal and mirror and only need to be redone (against the data file) on the mirror. The redo queue must be completely redone before the mirror comes online.

You won't have unsent log in synchronous mirroring unless the mirror is not in the synchronised state (due to latency or line failure)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mah_j SSCrazy Points: 2931 More actions · Answer 2

so if I have latency(I can see unsent log when inserting a 10000000 records loop in Launch Database Mirroring Monitor)how can I prevent data loss?Is there any solution?

This is the test script:

DECLARE @i INT

SET @i=1

WHILE @i<10000000

BEGIN

INSERT INTO dbo.Table_1

VALUES( (cast(RAND() AS NVARCHAR(20))),GETDATE() )

SET @i=@i+1

END

If the principal server fails, how can I prevent data loss?manual failover is better or automatic failover?

I am so confused 🙁

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If the principal fails and you have unsent log then it doesn't matter whether you go for automatic or manual failover, failing over manually won't magically make the unsent log records materialise on the mirror. Depending on the type of failure, you might be able to salvage the data after the principal comes back online. Of course, if it was a drive failure and the DB files are gone, then not so much.

Make sure the line can handle the expected load, test, test, test, make sure that the unsent log never gets above the allowable data loss for your system.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HowardW One Orange Chip Points: 29109 More actions · Answer 4

mah_j (8/6/2013)
so if I have latency(I can see unsent log when inserting a 10000000 records loop in Launch Database Mirroring Monitor)how can I prevent data loss?Is there any solution?
This is the test script:
DECLARE @i INT
SET @i=1
WHILE @i<10000000
BEGIN
INSERT INTO dbo.Table_1
VALUES( (cast(RAND() AS NVARCHAR(20))),GETDATE() )
SET @i=@i+1
END
If the principal server fails, how can I prevent data loss?

If Transaction Safety is set to FULL, you shouldn't see latency and therefore shouldn't have data loss. The point of this mode is to delay commit on the primary until the log is hardened on the secondary. What transaction safety mode is set when you see the latency?

http://technet.microsoft.com/en-us/library/ms179344(v=sql.105).aspx

mah_j SSCrazy Points: 2931 More actions · Answer 5

Thanks a lot Gail

Another question,if the principal fails,the way for manual failover is

USE master

go

ALTER DATABASE mirrordb SET PARTNER OFF

GO

RESTORE DATABASE mirrordb WITH RECOVERY

GO

and it will bring the mirror online?

and when the principal is available again,I should configure mirroring again?

mah_j SSCrazy Points: 2931 More actions · Answer 6

The mode is High Performance,but I configure mirroring on my local for test not main server.Is it because of low resources?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

High performance is asynchronous, not synchronous. If you run mirroring in async then you must accept the possibility of data loss.

And no, that's not how you do a manual failover. That's how you remove mirroring entirely. May I suggest you get your hands on a book on mirroring and read up a bit more?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mah_j SSCrazy Points: 2931 More actions · Answer 8

I am so sorry ,I mean High Protection,with safety FULL, but has no witness server .

I know that with set partner off I remove mirroring,but when I run SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS the mirror db is in (in Recovery) mode.