General rules for deployment would have kinks worked out when deploying to a TEST, QA or DEV system so there are no issues with the PROD deployment. If you are already running a cluster you can add an instance to the cluster at no licensing cost and use that as your TEST environment to iron out the deployment process. If the deployment to TEST goes south then you simply restore the database from the PROD backup, change the scripts and try again.
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
Mirroring is a technology that increases the high availability for user databases and creates a copy of the source db that is maintained in real time. This is done by delivering all the active transaction log records to the mirror server.
From MSDN:
"Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Database mirroring is supported in SQL Server Standard and Enterprise. "
Clustering is a technology that provides high availability for the entire SQL Server instance. This means that it's possible to have two servers with identical structure (OS, applications, services, service packs, patches...etc) and with shared storage (the data itself).
From MSDN:
"SQL Server failover clustering provides high-availability support for an entire SQL Server instance. SQL Server failover clusters are built on top of Windows Server failover clusters. To create a SQL Server failover cluster, you need to first create the underlying Windows Server failover cluster."
Here's a course from the Microsoft Virtual Academy about High Availability as offered today by MS: Understanding Microsoft’s High-Availability Solutions.