Setup SQL Always-On Availability Groups with Failover Cluster Instance

Here we are with the next step about the high availability of SharePoint Databases by leveraging the SQL AlwaysOn Availability Groups feature. In the previous articles we covered the deployment of a Single-Farm based on SharePoint 2016 on a SQL Failover Cluster Instance (FCI).  This setup is essentially providing redundancy and therefore protection for the SQL Services accessing the Instance and the Instance itself which can be failed over an available node of the cluster. SQL Data is conveniently stored on a SAN/NAS which already includes mechanisms to protect data from potential hardware failures. But what if data gets corrupted due to viruses or other forms of malwares? Or what if we want to make data available for other purposes like reporting ,backups or remote sites for Disaster Recovery scenarios without necessarily affecting the performances of the production SQL cluster? In these and other cases we can definitely leverage the AlwaysOn Availability Groups (AAG) to address these scenarios. This article is proposing to integrate FCI with AAG offering not just that peace of mind with available servers always up and running but also the high availability of the data should the primary storage location fail. This scenario is also perfect for guaranteeing no data loss when failing over to a new node of the SQL FCI.

So let’s take a look at a sample scenario we want to accomplish first and then the shopping list:

  • 3 total nodes Fail-over Cluster Installation of SQL 2014

  • 1 node for High Availability of SQL Data

  • Integration of FCI with AAG

  • Creation of 1 Test AAG

The scenario will look similar to something like the one below

And now for the shopping list we need:

  • 2x Windows Server 2012 R2 with SQL 2014 for FCI

  • 1x Windows Server 2012 R2 with SQL 2014 for AAG (new SQL instance)

  • 1x Network Location where to save the AAG Full and T-Log backups accessible from all nodes

So here we go. First thing we need to enable the AlwaysOn Group feature on both SQL instances (Production and HA). To do this essentially we need to select the properties for the intended instance (if more than one on the same server) and select the  Enable the AlwaysOn Availability Groups checkbox.  To make this change effective we also need to restart the running SQL Server services. It is also important to notice this operation needs to be done on the following nodes:

  • The active SQL node in FCI

  • The active SQL node on the HA instance

We are almost ready to start with the AAG wizard. There is still some preparation work which includes:

  • Install WSFC on HA node

    • More info on how to install Failover cluster feature can be found here

    • Add the node the existing Windows Cluster (WINClu-2012 in my case)

  • Adding WSFC Resources to WSFC node

    • Add Quorum disk (from all eligible storage)

    • Create share Location for AAG Backups (Backup Disk in my case)

    • Add AAG Backup location for AAG Cluster resource as File Share

 

  • Install SQL standalone for HA instance

    • Create and assign the same letters as per servers on the WSFC cluster for SQL Data and SQL Logs drives creating the same folder paths

    • Use iscsi to mount Quorum disk and assign the same letter as per servers on the WSFC cluster

    • Install new SQL named instance (eg. AAG)

    • Enable FCI SQL property

  • Run Full/T-Log Backup

    • This is a prerequisites to meet the minimum criteria to add a database to the AAG. The backups can also be saved in a different location from the one created in the previous step. I will run a Full/T-Log Backup for just one Database: WSS_Content. It is also essential for the Database recovery model to be set as Full Recovery mode

  • Create DNS entry for Listener

    • This will be a Host(A) Record and using default port. In my case the DNS record is

    • hostname: AAG_Listener

    • IP Address: 10.254.6.251

    • Port: 1433

At this point we are ready to start the AlwayOn Availability Group wizard

For this test only one database will be used

 

Next is to choose the secondary instance which we’ll host our highly available databases

 

We can also specify how the transactions should be committed if sync/async and if the Secondary Replica (Primary Replica is sitting on the FCI instance) should be read-able or not or with read intents. There are many configuration scenarios that can be easily adapted to in-site and cross-site deployments

 

Ideally we can always choose to use the Secondary Replica to offer the Backup option not affecting resources on the FCI cluster during working hours

 

We can optionally specify a Listener (highly recommended) to address the AAG. Make sure forward / reverse naming resolution on both Hostname / FQDN are working as expected

 

Assuming we have no Backups (Full + TLogs) the first option is highly recommended. Also the network share should be reachable Read/Write from all members of the cluster

 

In the validation step it is crucial to pass all checks

 

At this point we can connect to the FCI instance and verify the status of the AAG operations

 

As expected the end-points and necessary configurations have been created along with the Secondary Replica sitting on the secondary instance (synchronized)

 

Automatic/Manual access to the data on the Secondary Replica depends on the settings chosen on the AAG configuration wizard. It is also important to mention that as soon as we find a suitable configuration for our needs we can simply add existing Databases to pre-created AAG groups.

Also depending on available resources it is possible to configure and fine tune the dependencies on the cluster to reflect stringent requirements. Whereas SQL Services, SQL instances and SQL Databases have to be protected and highly available definitely the Failover Cluster Instance integrated with AlwaysOn Availability Groups represent the most sophisticated solution out of the box to address such scenarios.

Michele Domanico

Passionate about Virtualization, Storage, Data Availability and Software Defined Data Center technologies. The aim of Domalab.com is sharing with the Community the knowledge and experience gained with customers, industry leaders and like minded peers. Always open to constructive feedback and new challenges.

Leave a Reply