Sql-server – SQL Server 2008 R2 – Moving nodes in mirrored environment

mirroringrestoresql-server-2008-r2

Was hoping that I would find this documented somewhere, but I've searched and searched. Finally asking the question myself:

I have SQL Server 2008 R2 in a HA setup (primary/mirror/witness)

I want to move the mirror and witness servers to different hardware from what they're currently on.

It's only 1 db and about 20GB in size, it uses SQL authentication for the db (not Windows auth) and the Windows SQL service will run with the same domain user on all nodes.

I assume I start by installing SQL Server on the new nodes, and adding the endpoints to each instance.

From there would I just remove mirroring from the primary instance and re-create mirroring, adding the new nodes? Would I need to restore from a backup on the new mirror instance?

Hopefully someone can lay out the steps involved, thank you.

Best Answer

You are correct..

Install SQL Server on the new nodes
Use SP_HELP_REVLOGIN to copy the user account to the secondary node
Take a full backup and a single log backup of the primary server and restore on the secondary with no_recovery
Break the mirror and remove the secondaries from the primary server
Create the endpoints with TSQL or use the Mirroring wizard to configure them and endpoint security
start mirroring

Related Solutions

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

Sql-server – Should we periodically ‘refresh’ the mirrored copy of a SQL Server database

I don't trust mirroring 100%, but I do trust it about 99%, same with everything else. It never hurts to periodically check your mirror database and DR scheme. We test our DR plan across the board several times a year, and it would not hurt your scenario to set aside a DR test time and simulate a failure of your primary database to fully test the mirror if you have concerns.

Best Answer

Related Solutions

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

Sql-server – Should we periodically ‘refresh’ the mirrored copy of a SQL Server database

Related Question