3

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.

  • 1
    Not cheaper than clustering - clustering has serious problems as high availability solution. Mirroring IS BETTER. – TomTom Jun 17 '10 at 13:34
  • Look at this post http://serverfault.com/questions/49337/is-sql-server-mirroring-reliable-high-availability and read Paul Randal's response. I think it will help. – Holocryptic Apr 28 '10 at 18:31

2 Answers2

6

If your Principal goes down, you can force the Mirror database online, but at the risk of losing any transactions that were committed on the Principal that never made it to the Mirror.

ALTER DATABASE YourDatabaseName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Run that query on the Mirror, and the database will now become the new (exposed) Principal. Once your previous principal instance comes back online, mirroring will be suspended between the two. You'll have to manually re-enable mirroring, where you'll get a warning about possible data loss due to the way the previous mirror was brought back online.

4

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.

Campo
  • 1,609
  • 17
  • 33
  • 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! –  Apr 28 '10 at 19:10
  • Good stuff! Glad to help. Mark the answer useful if you could please :) – Campo Apr 28 '10 at 19:36