Sql-server – What precautions to take when moving a mirrored server

mirroringsql-server-2008-r2

I have a SQL Server 2008 R2 mirror setup running in high safety with automatic failover (synchronous, witness). Over two different nights, the mirror and the primary will be shut down and re-racked. (Mirror on the first night, Primary on the second night)

My original thought is that maybe I should remove the witness prior to shut down so failover would be avoided during/after this process unless I initiated it. But, I’m wondering whether there are any other precautions I should take. The server move should take ~1 hour which is the frequency of log backups as well, so I’m not worried about the transaction log filling on the primary during this time.

Is there any reason to switch from high safety to high performance mode during them move? I’m a little bit unsure of how these modes might behave differently when the mirroring state is DISCONNECTED.

Best Answer

I would remove the witness from the session. The reason being that if the 1 online partner (principal) loses communication with the witness, the principal will take itself offline because it has no way to know for sure that the mirror isn't online.

There is no need to change the operating mode. When the principal becomes disconnected, it operates asynchronously. When the mirror reconnects, it will continue to operate asynchronously until they are synchronized, and then it will return automatically to operating synchronously.

Related Solutions

Sql-server – Cannot remove Mirroring / nor restore or delete database:Error 3456 (SQL Server 2012)

This is a known issue which Microsoft has resolved in Cumulative Update 6.

Sql-server – Manually moving SQL instance to another node causes immediate database mirror failover

One way to prevent the mirror failover is:

Pause Mirroring with ALTER DATABASE XYZ SET PARTNER SUSPEND
Move the SQL instance
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

Best Answer

Related Solutions

Sql-server – Cannot remove Mirroring / nor restore or delete database:Error 3456 (SQL Server 2012)

Sql-server – Manually moving SQL instance to another node causes immediate database mirror failover

Related Question