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?
Best Answer
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.