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
Using mirroring alone won't be sufficient since the mirrored secondary is not available for querying. You have to create and maintain snapshots, which can be annoying.
Your options are, in no particular order:
- Mirroring with snapshots : effective but has management overhead.
- Backup and restore : Are you restoring from full backups rather than differential or logs? If so, you may be able to reduce the time spent on restores.
- Log shipping : management overhead, database is unavailable when log backups are being applied
- Replication : challenging from a management perspective, not really intended for syncing full databases
- Report from transactional database - This option is all too often discarded over unsubstantiated performance concerns. Those concerns can also be mitigated somewhat through the use of Resource Governor, snapshot isolation, etc. This is an underutilized option, IMO.
- ETL with SSIS or a similar tool. You would have to do your own schema changes, however.
- Availability Groups, if you can upgrade to 2012 (I assume you're on 2008 R2). This is really the best option available these days.
- Idera makes a tool called Virtual Database that allows you to mount and query a backup file. It's an interesting option in some cases.
It's difficult to make a clear recommendation without knowing a lot more information about your environment. I've used most of these methods to varying degrees of success. Note that most places end up building out more robust data marts / data warehouses for reporting and analytics, so you'll probably end up with the ETL route one day.
Oh, and make sure you have licensed the secondary server ;)
Best Answer
No automatic failover does not happen in this scenario as the current principal will still have a quorum forming a partner-to-partner quorum. Without a witness, automatic failover is not possible - ONLY Manual failover is possible.
This is explained in more detailed at How a Witness Affects Database Availability ?