Sql-server – Is “AlwaysOn” not always “Always On?”

availability-groupsfailoversql serversql-server-2016

We created a Windows Failover Cluster, then added two instances of SQL Server as nodes of a SQL Server failover cluster.

We set the servers to use "AlwaysOn Availability Groups" in SQL Configuration Manager.

To test a failover, I loaded up and ran a long query, then brought the active node down by using Failover Cluster Manager to stop the cluster service on the active node.

The query broke with no connection, and the server showed as unavailable for about 20 seconds before the node was drained and the new node took over.

Did I do this wrong? How should I have configured this so that there was little-to-no connectivity loss?

Is AlwaysOn not always on?

Best Answer

You have a bunch of different questions in here.

Q: What is the "Always On" thing?

Microsoft uses that brand name (which was written without a space before 2016) to describe two different features:

  • Failover Clustered Instances (FCIs) - what your grandpa used to call an active/passive cluster
  • Availability Groups (AGs) - like database mirroring, but works with groups of databases in some cases (but not the system databases)

Use those terms to describe which specific Always On feature you're using.

Q: In a failover, will it be always on?

Neither FCIs nor AGs are really always on. During a failover, your running transactions will fail, and connection retries can fail for 5-60 seconds (or more). It's up to you to build in graceful retry logic in your applications, or build in degraded capability tools like Stack Overflow does.

Q: How do I configure Always On?

It varies dramatically based on:

  • Which AO feature you're using (FCIs or AGs)
  • The number of nodes in the cluster
  • How you want to handle quorum (voting)
  • Whether you're using automatic failover via a listener or virtual computer name

These are big decisions that involve a lot of architecture work. For more detailed specifics, include the above details, and we'll be able to tell you more about how to configure it.

Q: Isn't it just a matter of checking the box for Always On?

Nope.

Related Question