SQL 2008 Mirroring, how to failover from the mirror database?

Question

I have configured a database mirroring setup in SQL 2008 using the High-safety, Synchronous mode, without automatic failover. I don't have a Witness instance.

Regarding high availability, I understand Mirroring is a better strategy than Log Shipping (faster and smoother failover), and cheaper than Clustering (because of license and hardware costs).

According to the MS docs, to do the failover you need to access to the Principal database and in the "Mirror" options click the "Failover" button. But I want to do this from the Mirror database, because what would be the benefit as all this setup is being done in case the Principal server knocks down?

Evidently I am missing something. If Mirroring is not a solution for server downtime (as would be Clustering, if I understand correctly), then which practical (i.e. real world examples) cases would benefit from Mirroring for high-availability purposes?

Thank you very much for your response! I really need some enlightment.

Solution

Simply put you must activate the mirror from the principal server in the High Safety mode without automatic fail over.

This method is great for planned outages and maintenance. It is not really beneficial in a disaster or unplanned downtime scenario as you cannot fail over without access to the principal server.

I use a very low cost old computer running MSSQL express as the monitor server. It has windows XP pro on it. I am sure you can find a small computer to carry out the task of the monitor server. Something that can meet minimum requirements for MSSQL express will do fine.

Hope this helps.

Comments

[Admin]

Look at this post serverfault.com/questions/49337/… and read Paul Randal's response. I think it will help.

[Admin]

I thought the only difference between manual and automatic failover was that the first required operator intervention. But there is another important difference, and it is that manual failover requires access to the principal server while automatic does not, as you say. I've just tried installing a witness instance and everything works just like I want. Thank you!

[Admin]

This saved my bacon after I've been up for 36 hours. Go Rich, you unregistered wunderkind.