What is SQL Server AlwaysOn?

By:   |   Updated: 2017-02-20   |   Comments (10)   |   Related: > Availability Groups


Problem

SQL Server AlwaysOn is a popular term mentioned in various sources, but what does SQL Server AlwaysOn really mean? This tip will explain the term SQL Server AlwaysOn and its two main technologies.

Solution

SQL Server AlwaysOn is a marketing term which refers to the high availability and disaster recovery solution introduced when SQL Server 2012 was launched.

To be more specific, SQL Server AlwaysOn consists of two technologies:

  • AlwaysOn Failover Clustering Instances (AlwaysOn FCI)
  • AlwaysOn Availability Groups (AlwaysOn AG)

Whilst both technologies have similarities such as requiring Windows Server Failover Clustering (WSFC) as the foundation for its implementation, each is a distinct technology under the AlwaysOn umbrella.

AlwaysOn Failover Clustering Instances (FCI)

AlwaysOn FCI needs shared storage like an iSCSI or Fibre Channel SAN that can be accessed by all of the nodes in the cluster. There is also the option to use third party data replication tools that can assist with the storage requirements if you don't have shared storage or want to do this for virtual machines or in the cloud.

It supports multisite clustering across subnets which enables failover of SQL Server instances across data centers, but this requires replication of the data between the shared storage in each of the data centers.

AlwaysOn FCI is available on both SQL Server Standard and Enterprise Edition, but imposes restrictions on SQL Server Standard Edition such as a 2-node limit.

When you install SQL Server you select the "New SQL failover cluster installation" option.

An implementation of single site two node AlwaysOn FCI (using quorum mode Node and Disk Majority) is depicted below.

AlwaysOn Failover Clustering Instances (FCI)

The quorum mode helps determine which nodes are available and which node should be the primary node.  By having another machine/object involved it can determine if communication between machines is lost are therefore whether a failover should occur. Below are common examples of quorum mode that can be used in an AlwaysOn FCI configuration.

  • Node Majority
  • Node and Fileshare Majority
  • Node and (symmetrical) Disk Majority

A symmetric storage means a cluster disk that is shared between all the WSFC nodes. This allows the shared disk storage to be available to all potential failover nodes in the WSFC cluster.

AlwaysOn Availability Groups

AlwaysOn AG does not require shared disk storage for the server hosting the SQL Server. This SQL Server high availability technology has been an Enterprise feature. This means you cannot configure SQL Server Standard Edition to use AlwaysOn AG with versions prior to SQL Server 2016. There is now an option to create a basic availability group with SQL Server 2016 Standard edition which I discuss below.

When you install SQL Server you select the "New SQL stand-alone installation..." option.

An implementation of AlwaysOn AG for HA and DR (using quorum mode Node Majority) is depicted below.

AlwaysOn Availability Groups

Below are several common examples of quorum mode used in an AlwaysOn AG configuration.

  • Node Majority
  • Node and Fileshare Majority
  • Node and (Asymmetric) Disk Majority

An asymmetric storage means a cluster disk is shared only between a subset of the nodes. Asymmetric disk capability was first introduced on Windows Server 2008. It allows a disk witness to be configured and accessible only to nodes in one site, typically the primary site.

New Features in SQL Server 2016

Now that you understood the differences between AlwaysOn FCI and AlwaysOn AG, SQL Server 2016 introduced two additional varieties of AlwaysOn AG.

  • AlwaysOn Basic Availability Groups (AlwaysOn BAG)
  • AlwaysOn Distributed Availability Group (AlwaysOn DAG)

AlwaysOn Basic Availability Group (AlwaysOn BAG)

AlwaysOn feature is now an included in SQL Server 2016 Standard Edition, but it is referred to as AlwaysOn BAG. It is created and managed similarly to AG, but AlwaysOn BAG is capable to only use a subset of features compared to the more advanced AlwaysOn AG on SQL Server Enterprise Edition. An example limitation is BAG only allows to have two replicas (primary and secondary).

AlwaysOn BAG provides failover support for a single database only, replacing the database mirroring which is deprecated.

AlwaysOn Distributed Availability Group (AlwaysOn DAG)

AlwaysOn DAG are loosely coupled groups of AGs. AlwaysOn DAG runs on top of two distinct AGs meaning that they reside on two distinct WSFCs with their own quorum and voting management.

This configuration allows secondary replicas of an AG to exist in a different geographical region than the primary. An example use case would be to enable read-only workloads for remote regions and at the same time avoid any potential network problem at the secondary site which can affect the primary site.

AlwaysOn Distributed Availability Group (AlwaysOn DAG)

Characteristics of AlwaysOn FCI and AlwaysOn AG

Each of the two technologies differs in its purpose. It is possible to combine AlwaysOn FCI and AlwaysOn AG. Business requirements might require local high availability within a data center using AlwaysOn FCI, and cross data center disaster recovery using AlwaysOn AG. It just means the solution would then consist of a combination of shared storage and non-shared storage in the implementation.

If you're wondering which solution to implement, the table below summarizes the similarity and differences in characteristics between SQL Server AlwaysOn FCI and AlwaysOn AG solutions as a guide when evaluating SQL Server AlwaysOn.

AlwaysOn FCI for HA and DR AlwaysOn AG for HA and DR
Shared Storage solution Non-Shared Storage solution
Instance level HA

Logins, SQL Agent jobs, certificates and other SQL Server instance level objects are in-tact after failover 		Database level HA (can be one or more databases)

Manual adding logins, SQL Agent jobs, certificates and other SQL Server instance level objects to all secondary's
Instance-level protection without data redundancy Each group of secondary AG database(s) are redundant copy of primary
Have Active\Passive nodes. No concept of a secondary database. DR replica can be Active Secondary for backup, read-only workload.
Application connects via virtual server name Application connects via AG listener name
Does not maintain a redundant copy of the data hence does not protect against an I/O subsystem failure Protection against an I/O subsystem failure i.e Automatic Page Repair
No special requirements with respect to database recovery models Database(s) in AG must be in FULL recovery model

Other things to note for both:

  • Every single AlwaysOn deployment is a WSFC deployment
  • Both FCIs and AGs can span multiple data centers, but with different implementations
  • Can be implemented on physical SQL Server systems, or on SQL Server systems that are running as virtual machines

Summary

Whenever SQL Server AlwaysOn is mentioned, it is not specific because it can refer to either AlwaysOn FCI or AlwaysOn AG.

In nutshell:

  • AlwaysOn = {SQL Server Failover Cluster Instances, Availability Groups}
  • AlwaysOn != SQL Server Failover Cluster Instances != Availability Groups
  • Availability Groups != Database Mirroring
  • WSFC != SQL Server Failover Cluster Instances
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Simon Liew Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2017-02-20

Comments For This Article




Monday, May 27, 2019 - 5:53:04 AM - Bala Back To Top (81221)

The best part of always on is having two databases in synchronised state in two different servers which do not need shared storage and once the primary node fails, then the secondary which gets voted by quorum members becomes the new primary. 

I am unable to understand what is the specific use case of having a shared storage for Always On FCI. Asking the same since I believe it increases the overall time and effort for bringing up the primary up ?

Regards,

Bala

Tuesday, October 30, 2018 - 1:57:22 PM - ANNE Back To Top (78115)

Great article, it helps me clarifying some conecepts of AlwaysoOn.

 

Friday, September 7, 2018 - 2:11:41 PM - farhan chowdhury Back To Top (77392)

In our environment we have 3 Nodes

1) Primary Node Synchronous Commit Automatic Failover On site

2) Secondary Node Synchronous Commit Automatic Failover On site

3) Secondary Node Asynchronous Commit Manual Failover Off Site DR

The onsite location will be down for a day and the DR site will be primary, per day transaction log onaverage is 1.5 gb. My Process are below

1) Change the Availability Mode to Syn Comit on the DR then make sure its synchronised and then failover

2) Change the other two secondaries to asynchronous commit and the failover mode to manual in order to avoid application lag and automatic failover

3) Do the maintenance on the secondaries

4) Change the Availability Mode to Synchrous Commit on the secondaries , make sure its synchornized and then failover

5) Upon failover change the DR to Asynchronous commit and failover mode to Manual.

Tuesday, January 2, 2018 - 11:03:21 AM - Alexis Pabilo Back To Top (74675)

 Hi 

 

Got a question how do you configure AG over FCI: this is the escenario, you have a fci on primary datacenter and fci on secundary datacenter but for DRP you need to configure AG from the primary datacenter to the secundary datacenter. 

 

 

Thursday, August 3, 2017 - 8:38:45 PM - Simon Liew Back To Top (63890)

Hi Kal,

BAG is only available on SQL Server 2016 Standard Edition. Since you're installing SQL Server 2016 Enterprise, you only have a choice of implementing the advanced AG, or the typical AG available on Enterprise Edition.

If you needed to add more nodes to the WSFC, you can do so online without an outage. You do not need to create a new WSFC for this configuration.

 

For example, you can 

1. Install SQL Server 2016 Enterprise (node 3)

2. Whether node 3 is in Loc A or Loc B, join node 3 to existing WFSC

3. Enable AlwaysOn on node 3

4. Expand your AG to node 3, this will be you 2nd secondary AG replica

Wednesday, August 2, 2017 - 4:22:40 AM - Kal Back To Top (63715)

Hi Simon

Thanks for the paper it cleared the mud for some questions i had but i need to ask one thing.

If i want to implement the following across 2 locations using the SAME WSFC then what approach would i use?

Assume locations are called Loc A and Loc B


  1. One windows server cluster include two nodes (Loc A Node & Loc B Node)

  2. 1 SQL server 2016 Enterprise edition Instance in Loc A  (Instance 01)

  3. 1 SQL server 2016 Enterprise edition Instance in Loc B  (Instance 02)

  4. Always on will be enabled between Loc A Database and Loc B DB.

Would you use a BAG if we want to use one node per location in the same WSFC?

What if we want to add more nodes to the AG then can we add them to the same AGs or we need to create a new WSFC?

Kal

 

Monday, July 17, 2017 - 7:23:19 PM - Simon Liew Back To Top (59506)

Hi Stefano,

There a good whitepaper that you can download from microsoft as below. It has a section on networking considerations. The last few pages in the whitepaper has many good links to other references.

http://download.microsoft.com/download/6/1/D/61DDE9B6-AB46-48CA-8380-D7714C9CB1AB/Best_Practices_for_Virtualizing_and_Managing_SQL_Server_2012.pdf

 

Monday, July 17, 2017 - 5:09:57 PM - Stefano Back To Top (59496)

Hi Simon. Great article thanks! I made a 3 Node cluster on Hyper-V 2016. All SQL Server are in VMs. I have on the host a converged 2x10 Gbit NIC Team (Virtual Switch). The 3x 1GB NICs on Server I have disabled as Microsoft recommends to make a 'one-big switch' and then control all with QoS, VLAN.

Do you have a whitepaper where is exactly explained how to configure SQL alwayson on a virtalizied environment? Acutally I have NOT added dedicated NIC for intra-cluster communicaion, because in Guest I have only ONE Hyper-V switch (= network card). I saw that the setup made on every Node an apipa IP adress, which looks not so nice. How exactly do you recommend to handle the network traffic inside the guest-cluster nodes?

 

Regards

Stefano

Wednesday, April 12, 2017 - 7:40:13 PM - GeorgeXiao Back To Top (54751)

This article is really great! Thanks for the simple and precise explaination. I thought alwayson was only AG.

I wish Microsoft does a better job in explaining their technoligies.

Wednesday, April 12, 2017 - 2:33:40 PM - Hichamveo Back To Top (54720)

 Good article . Thanks for this best explanation

 














get free sql tips
agree to terms