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
As long as the path names remain the same for your database files, this approach should work. This assumes that when you say "copying all files", your are ONLY moving the database files (data/log) and not the SQL Server binaries. If your SQL binaries and/or resourcedb need to be moved, you have a much stickier task ahead of you.
Your biggest gotcha should be maintaining the directory and database file permissions. I would handle this using ROBOCOPY
with the /copyall
switch to move the files while keeping all perms and attributes intact.
While outside of scope, if your end goal is to move the database to your full cluster and you're hurdle is client connectivity, have you considered using a DNS Alias to redirect traffic? I understand that the connectivity change could be larger in scope, but it could give you a path to consolidating your databases to where you want them.
Best Answer
Rename New Server
Is ServerA\reports running existing software that will break if the name changes? If not, after decommissioning Server1\inst1 you could rename the entire failover cluster instance and network name to reflect the decommissioned instance.
Alias
You can create an alias in SQL Server Configuration Manager on the server that is running SSRS with the alias name Server1\inst1 pointing to ServerA\reports. SSRS should be able to use this to connect with reports.
Shared Data Source
Alternatively, if you alter your reports to use a single shared data source per database you can have a single data source for each in SSRS that you can update. I do this to alter between a primary and backup set of reporting data as a form of local redundancy.
Edit: Reference for creating an alias: https://msdn.microsoft.com/en-us/library/ms190445.aspx
Note that you'll need to create the alias on the 'client' server rather than the server hosting SQL, so in your case on the server running SSRS where the connection is initiated from.