Ygthkb

I got this question about MS SQL Server 2012 administration, two production servers in the same data center. You need to ensure that database remains available if a catastrophic server failure or a disk failure occurs.

You need to maintain transactional consistency of the data across both servers. You need to achieve these goals without manual intervention.

The correct answer for the above question is

>>
Two servers configured on the same subnet
SQL Server Availability Group configured in Synchronous-Commit Availability Mode
<<

But I think the correct answer should be

Two servers configured in Windows Failover Cluster in the same data center SQL Server configured as a clustered instance<<

The tricky part is in this requirement:

You need to ensure that database remains available if a catastrophic
server failure or a disk failure occurs. You need to maintain
transactional consistency of the data across both servers. You need to
achieve these goals without manual intervention.

The disk failure part means a failover cluster alone won't work because the storage is shared with both nodes. If the storage where the data files live fails, then both nodes will be affected.

However, a 2-node synchronous Availability Group isn't the answer either, because as Microsoft's own documentation points out:

If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode.

Read further in that link in the "Factors That Disrupt Data Synchronization" section, and Microsoft elaborates on the reasons why you can't guarantee that a 2-node AG will not lose data on failover.

So what's the right answer for SQL Server 2012?

There isn't one. You can't guarantee zero data loss with 2 independent SQL Server 2012s without third party tools (like SAN replication, and even then, there's a ton of work involved.) I'm guessing the question came from a test or certification written by somebody without real-world experience. That wouldn't be the first time, and it won't be the last.

Is there a right answer for later versions?

Yes, SQL Server 2017 introduced a new REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT setting at the Availability Group level. The default is 0, which means as long as the primary receives the transaction, it's committed. You can change that to 1 (or more), which means that if at least that number of secondaries don't also commit the transaction, then the transaction fails.

The first scenario is actually the more correct implementation. In future versions of SQL Server (starting with 2016), the standard "Failover cluster" model is deprecated. Microsoft is shifting to the HA (High Availability) model via SQL Server Availability groups, which is essentially Mirroring and Clustering mixed together (no need for a witness server), with the data completely replicated to each node in the group.

Failover time is < 1 second, according to Microsoft professionals, as opposed to standard Failover Clustering, which can take 1+ minute for a successful automatic failover. SQL Server Availability Groups would be the way to go from this point forward.