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.

Like this:

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.