Sql-server – Always On Failover Clustering vs Always On Availability Groups

availability-groupsclusteringfailoversql server

I have some concerns over my understanding of SQL Server Always On. Kindly can you correct me (conceptually) where needed:

  1. Always On Clustering and Always On Availability Groups are 2 separate concepts. Clustering is a HA solution and AG is a DR solution. Is the Always On Clustering same as Windows server clustering?

  2. To create an Always On cluster – we have to launch the installer and choose the "New SQL Server failover cluster installation", then on every new node we need to launch the installer and choose "Add Node". The cluster (with many node) operates as a single unit in terms of IP address so applications point to only one IP and if there is instance crash, then the failover is handled by the Clustering technology and no application IP changes are needed. Also, the users/logins are in sync and will continue to work in case of failover. This does not protect against disk failure as all nodes share same disk.

  3. Assuming we have an SQL Server instance already (say server A), then to create Always On Clustering for this we need to follow the same steps as above (create a new failover cluster installation) and then add Server A as a node. Right?

  4. Assuming we have a fresh SQL Server instance (with no HA/DR) and are planning to configure only AG, then we first need to ensure that the "Failover clustering" windows feature is enabled on each participating node. Then right click on the SQL Server service and enable "Always on Availability Groups". Then on the server instance we create the AG and configure databases into the group(s). This ensures availability at database level, however if there is failover then logins wont work any more (orphaned). There is no shared disk and hence disk crash of primary server wont cause any issues for the secondary server database. Also, the client applications will be pointing to the Listener IP and the Listener will ensure that the appropriate working server is utilized by the applications. Right?

  5. In above scenario, "Failover clustering" windows feature is enabled so is clustering a pre-requisite for AG? Is the concept of clustering in Point 2 and Point 4 the same.

  6. If I wish to configure both HA/DR using Always On (AG) and Failover Clustering, then is best practice to follow Point 2 and then point 4 or the opposite way? Also, should we use both the virtual cluster name and Listener or either one if sufficient?

  7. What is difference between "SQL Server failover cluster installation" and Windows clustering?

Best Answer

To start with there is nothing called as Always On or Always ON availability groups it is simply called as Availability groups.

  1. Always On Clustering and Always On Availability Groups are 2 separate concepts. Clustering is a HA solution and AG is a DR solution. Is the Always On Clustering same as Windows server clustering?

Yes you are correct. Always on clustering means you have created availability groups on database(s) which is part of failover cluster instance (FCI) and availability groups simply means two standalone nodes which are part of WSFC or not are configured to form AG, there is no FCI here.

  1. To create an Always On cluster - we have to launch the installer and choose the "New SQL server failover cluster installation", then on every new node we need to launch the installer and choose "Add Node"

That is basically creating an SQL Server failover cluster instance. Yes after you have created an FCI you can go ahead and add the databases residing on FCI to an AG. This is what you are calling as Always On cluster and this is what official MS doc calls it.

For point 3 please refer to above two quotes. Again note the difference between AG and AG residing on FCI.

  1. Assuming we have a fresh SQL instance (with no HA/DR) and are planning to configure only AG, then we first need to ensure that the "Failover clustering" windows feature is enabled on each participating node.

If you are planning to configure ONLY AG you do not need failover cluster instance. AG can be configured with 2 or more nodes which are part of WSFC or not (starting from SQL Server 2017 we have clusterless AG). Remember an FCI will always require some kind of shared storage, OTOH AG does not requires any kind of shared storage.

  1. In above scenario, "Failover clustering" windows feature is enabled so is clustering a pre-requisite for AG? Is the concept of clustering in Point 2 and Point 4 the same.

WSFC is prerequisite for AG but starting from SQL Server 2017 you can have AG without WSFC but that is not a real AG. WSFC is prerequisite for FCI though.

EDIT:

If I wish to configure both HA/DR using Always On (AG) and Failover Clustering, then is best practice to follow Point 2 and then point 4 or the opposite way? Also, should we use both the virtual cluster name and Listener or either one if sufficient?

You need to first configure WSFC then install FCI like you mentioned in point 2 and point 4 then create database and then configure availability groups. Virtual cluster name is for FCI while Listener is for AG. You cannot configure cluster without virtual cluster name you have to have one, but you can have AG without listener. The only work of listener is to direct connections to primary replica which can be on any of the node.

What is difference between "SQL server failover cluster installation" and windows clustering?

Windows server failover cluster(WSFC) is windows server feature on which whole "clustering" technology is based while FCI is at SQL Server level which leverages WSFC for failovers to provide AH to SQL Server instance.

Related Question