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 AlwaysOn 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 AlwaysOn cluster?
In these and other cases we can definitely leverage the SQL 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 SQL 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 the 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
- This will be a Host(A) Record and using default port. In my case the DNS record is
At this point we are ready to start the AlwayOn Availability Group wizard.
For this test only one database will be used. It is important to mention that only Databases in Full Recovery Model and with at least one Full Backup completed can be selected from this view.
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. The wizard includes a quick description summary of the options.
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 with Read/Write permissions from all members of the cluster.
In the validation step it is crucial to pass all checks as per screenshot below.
At this point we can connect to the SQL Failover Cluster 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 SQL AlwaysOn Availability Groups represents the most sophisticated solution out of the box to address such scenarios.
Add Comment