I have two SQL server 2012 instances and I want to configure mirroring between the 2 servers with database.

My questions are:

How can it automatically change host of one if the other one was dropped ?

Is the performance of mirroring is acceptable and is there another way to make one as host and other is backup then if the main server was dropped the other works automatically better than mirroring?

  • You have duplicate questions. I will leave my answer here as eventually, the mods will merge both questions. Remus has answered your question exactly to the point. – Kin Shah Oct 30 '14 at 21:12

Automatic failover requires a witness. See Database Mirroring Witness:

To support automatic failover, a database mirroring session must be configured in high-safety mode and also possess a third server instance, known as the witness. The witness is an optional instance of SQL Server that enables the mirror server in a high-safety mode session to recognize whether to initiate an automatic failover. Unlike the two partners, the witness does not serve the database. Supporting automatic failover is the only role of the witness.

Your application will also have to use a mirroring aware connection string, see Connect Clients to a Database Mirroring Session:

"Server=Partner_A; Failover_Partner=Partner_B; Database=AdventureWorks; Network=dbmssocn"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.