Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

What is SQL Server AlwaysOn?


By:   |   Read Comments (7)   |   Related Tips: More > Availability Groups

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


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


Last Update:


next webcast button


next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, January 02, 2018 - 11:03:21 AM - Alexis Pabilo Back To Top

 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 03, 2017 - 8:38:45 PM - Simon Liew Back To Top

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 02, 2017 - 4:22:40 AM - Kal Back To Top

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

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

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

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

 Good article . Thanks for this best explanation

 


Learn more about SQL Server tools