0

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<<

migrated from stackoverflow.com Sep 15 '15 at 14:05

This question came from our site for professional and enthusiast programmers.

0

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.

-3

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.

  • 2
    FCI is deprecated? Can you give me the link to where you read this? – James Anderson Sep 15 '15 at 17:08
  • My mistake - 2016 is the last version it will be in. Synchronous mode will only be in the Enterprise version - Asynchronous will come default in Standard edition. I don't have a link, I apologize. I went to a local SQL training for EMC, hosted by a Microsoft SQL MVP who shared this information. – Greg Cardall Sep 15 '15 at 21:56
  • 2
    @GregCardall no, 2016 was definitely not the last version it will be in. Just FYI, MVP is not a technical qualification - you can't just believe everything you hear from MVPs (or anyone else, for that matter.) – Brent Ozar Feb 23 at 14:01

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.