0

We have two servers each one occupied by a dedicated storage. I am looking for the a way to setup these two servers to provide a database on MS SQL Server with automatic fail-over support.

Using Database Mirroring seems to be ineffective for automatic fail-over when only two servers are available.

More info on why: For automatic fail-over, Database Mirroring needs a third element called witness to coordinate Principal/Mirror roles between two DB instances and assist switching during fail-over. However, I only have two servers available so I am forced to install witness on one of the servers that can be either Primary (Principal) or Secondary (Mirror) DB instance. So when a hardware failure occurs on the server that both principal DB and witness are running, the mirror DB cannot become online and automatic fail-over doesn't happen.

A recent feature of SQL Server called Always On Availability Groups seems to provide the similar functionality for mirroring plus more. Can automatic fail-over be achieved using Always On Availability Groups having only two servers?

Do you have any other recommendation for having automatic fail-over support, particularly against power failures, by two servers?

Improve this question

1 Answer 1

Reset to default
6

For automatic failover, you need a tie-breaker.

Otherwise, what would happen when the two servers had a network split and couldn't see each other? You wouldn't want them both automatically promoting themselves to primary, and both accepting writes. Think about what would happen on a table with an identity field, for example: both servers could quickly end up with two different records with the same identity field.

This is called a split brain scenario, and you always want SQL Server to fail into a non-available state when that happens.

To break the tie, database mirroring can use a witness server, but it can be an absolutely free SQL Server Express Edition.

Always On Availability Groups is different than mirroring in that it's built atop Windows clustering, which means it relies on quorum voting to achieve the tiebreaker. Normally, you would want a third Windows server involved, but good news: it doesn't have to have SQL Server installed. You can use a file share to act as a quorum witness, or even a cloud witness.

You could even get fancy and remove the quorum votes from one of your servers - but if that server goes down, your entire cluster will go down.

Improve this answer
4
  • How about using a replication? For instance, Peer-to-Peer Transactional Replication
    – Isaac
    Nov 28, 2017 at 7:01
  • P2P Transactional Replication allows two masters to be accessible for read and write hence no need for a witness entity. One major issue that must be handled is the identity fields, but besides that, I don't see any limitations to replication.
    – Isaac
    Nov 28, 2017 at 7:13
  • @Isaac no limitations? What happens with write conflicts? When schemas undergo major changes?
    – Brent Ozar
    Nov 28, 2017 at 10:54
  • Schema changes are rare in our use-case and quiesce can be tolerated. The writes to DB are mainly INSERTs that can be handled by assigning different ranges to identity fields. While UPDATEs are rare in our use-case, you are right about conflict whenever it happens. I guess I should give more thought one the available solutions and consider the trade-offs
    – Isaac
    Nov 28, 2017 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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