2

In trying to determine the most appropriate High Availability option we are focused on reducing downtime (either planned or unplanned). I've been able to gather stats (from reading lots of MSDN documentation and blogs) on Failover Cluster Instances (FCI).

What I haven't found is documentation around the failover stats/times of Availability Groups.

As a comparison (FCI):
Failing over a 2008R2 cluster can range from 30 seconds to 5 minutes (depending on traffic volumes as well as hardware/network setup):

When doing a manual failover, it is completing its writes to the LUNs, switching the LUNs to the new Active node and starting up the SQL Server instance on the new Active node.

When doing an automatic failover, after starting up the node it will do a consistency check on the databases and roll back any in-flight transactions.

Availability Groups
I understand that Availability Groups should be able to fail over quicker, when you compare the same hardware and traffic volumes.

What I haven't been able to find is any real world actual metrics comparing the two.

Specifically does anyone have any metrics on how long it takes to fail over the primary write node in an Availability Group that is being actively used?
(fail over to a synchronous secondary) Ideally this would include references to any Microsoft or trustworthy sources. Not opinion based please, only metrics.

closed as too broad by Philᵀᴹ, mustaccio, Tom V, Marco, LowlyDBA Mar 16 '17 at 18:14

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    That step about writing last bits of data to the LUN's is only for a graceful shutdown and switchover. FCI's share storage so when Node B starts up it has to do a consistency check on the databases and roll back any in-flight transactions. An AG doesn't have to do any of that and failover happens as quickly as the listener can update and the client can reconnect. You still lose any in-flight transactions. – Jonathan Fite Mar 15 '17 at 14:47
  • 1
    @JonathanFite An AG does need to complete redo and undo - so yes, it has to do some of that. – Sean Gallardy Mar 16 '17 at 13:25
5

Personal observation from experience with both FCI and AG failovers, with reasonable high volume transactional system (40k trx/sec). For each consider 6 databases ranging in size from 500MB to 4TB in size. Times listed for failover are what it takes for the database to up and in a writeable state on the new node. Your mileage can, and will vary, but this is at least a data point for you.

Cluster failover: 47 seconds (avg)

AG Failover: 10 seconds

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