Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What happen to the redo queue? Expand / Collapse
Author
Message
Posted Tuesday, August 6, 2013 1:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 3:29 AM
Points: 108, Visits: 1,098
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?
Post #1481198
Posted Tuesday, August 6, 2013 3:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:13 AM
Points: 39,976, Visits: 36,336
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 2008, MVP
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

Post #1481209
Posted Tuesday, August 6, 2013 4:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 3:29 AM
Points: 108, Visits: 1,098
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 :-(
Post #1481222
Posted Tuesday, August 6, 2013 4:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:13 AM
Points: 39,976, Visits: 36,336
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 2008, MVP
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

Post #1481223
Posted Tuesday, August 6, 2013 4:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:31 AM
Points: 1,191, Visits: 9,882
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
Post #1481224
Posted Tuesday, August 6, 2013 6:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 3:29 AM
Points: 108, Visits: 1,098

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?



Post #1481272
Posted Tuesday, August 6, 2013 6:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 3:29 AM
Points: 108, Visits: 1,098

The mode is High Performance,but I configure mirroring on my local for test not main server.Is it because of low resources?
Post #1481274
Posted Tuesday, August 6, 2013 6:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:13 AM
Points: 39,976, Visits: 36,336
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 2008, MVP
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

Post #1481276
Posted Tuesday, August 6, 2013 6:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 3:29 AM
Points: 108, Visits: 1,098

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.
Post #1481282
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse