Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a two-node SQL cluster (2008 R2).

Some of the databases within that SQL instance are mirrored to another server on a remote site, using the High safety with automatic failover. The mirroring connection timeout value for those databases is set to 90 seconds.

When I move SQL from one node in the cluster to another node, using the Failover Cluster Manager application's option of "Move this service or application to another node" the databases that are mirrored are instantly failing over to the mirror.

This is undesirable behaviour. My reason for setting the mirroring connection timeout value is that I only want to fail over to the database mirror if the cluster fails completely and there are no functioning nodes.

Is there any way to achieve this? It feels as though it should be possible, otherwise the concept of mixing clustering and automatic failover database mirroring would be unworkable as every node failover within the cluster would trigger a mirror failover.

Thanks.

share|improve this question
    
It sounds to me like you're trying to break the underlying functionality of what you've already set up. If this is what you want, why not use a "High Availability Without Automatic Failover" configuration? msdn.microsoft.com/en-us/library/ms191309(v=SQL.105).aspx –  swasheck Mar 6 '13 at 19:40
    
Because I do want automatic failover of the mirror. The issue is that I only want it when the cluster is dead. –  paulH Mar 7 '13 at 13:08
add comment

2 Answers 2

up vote 4 down vote accepted

One way to prevent the mirror failover is:

  1. Pause Mirroring with ALTER DATABASE XYZ SET PARTNER SUSPEND
  2. Move the SQL instance
  3. Resume mirroring with ALTER DATABASE XYZ SET PARTNER RESUME

The instance is failing over to the mirror because both the witness and the secondary can no longer see the primary instance.

It sounds like you are attempting to recreate SQL Server 2012 Availability Groups by combining mirroring and clustering.

Database mirroring only times-out on so-called "soft" errors. Hard errors, like a cluster failing over are reported to the mirroring session immediately causing the immediate failover. Read more at http://msdn.microsoft.com/en-us/library/ms190913.aspx

Possible causes of hard errors include (but are not limited to) the following conditions:

A broken connection or wire

A bad network card

A router change

Changes in the firewall

Endpoint reconfiguration

Loss of the drive where the transaction log resides

Operating system or process failure

Conditions that might cause mirroring time-outs include (but are not limited to) the following:

Network errors such as TCP link time-outs, dropped or corrupted packets, 
    or packets that are in an incorrect order.

A hanging operating system, server, or database state.

A Windows server timing out.

Insufficient computing resources, such as a CPU or disk overload, the transaction 
    log filling up, or the system is running out of memory or threads. In these 
    cases, you must increase the time-out period, reduce the workload, or change 
    the hardware to handle the workload. 

For more information about mirroring and potential issues, you might want to see my question What can cause a mirroring session to timeout then failover? SQL Server 2005

share|improve this answer
    
Thanks, this is a useful workaround for manually moving nodes, but it wouldn't cover an instance of a node failing and triggering an automatic cluster failover. –  paulH Mar 7 '13 at 12:13
    
Correct you are, Sir. In that case, you'd want to have two or more servers using SQL Server 2012, and call it an Availability Group. –  Max Vernon Mar 7 '13 at 14:34
add comment

This is happening because the server is responding but the mirroring endpoint is down. The partner and witness get a response from the OS that the port that the endpoint is on is closed and immediately starts failover. The timeout applies to when no response is received from the host as opposed to, "the port you're looking for isn't listening anymore," response.

To get around this you'll either need to suspend the mirroring session as Max suggested or, if you're also concerned about unplanned cluster failovers, disable automatic failover as swasheck suggested and manually fail the mirror over when needed.

share|improve this answer
    
The problem with the workaround of disabling automatic failover is that I WANT automatic failover, but ONLY if the cluster is dead with no reasonable hope of recovery. To be honest I'm struggling to see the point of the timeout value now, because it presumably would only have an effect if the entire cluster was offline (and hence unable to move to another node), which would be the only time that you would NOT want the timeout value to be used? –  paulH Mar 7 '13 at 12:10
2  
No need to yell. You're looking for a workaround to a by-design behavior. –  swasheck Mar 7 '13 at 13:11
    
Is that yelling? Sorry, just meant to emphasise! –  paulH Mar 7 '13 at 16:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.