Monitoring SQL Server Failover Cluster Instances and AlwaysOn Availability Groups hosted in Azure IAAS
Published 17 August 2020
SQL Monitor versions 7.1.6 and above support SQL Server Failover Cluster Instances and AlwaysOn Availability Groups hosted in Azure. Your environment may vary, but we shall describe the steps for adding a FCI built in Azure as described here:
or for an availability group built as described here:
If you have an availability group containing FCIs, in the following instructions we recommend that you use the domain name and IP address that corresponds to the availability group.
SQL Monitor uses the WMI protocol to collect system-level performance information from your Failover Cluster Instance or availability group. Before you can add the cluster or availability group to SQL Monitor, you should follow the steps below to forward the WMI traffic through the Azure load balancer that reflects the active instance. It is also necessary to ensure that the Base Monitor that connects to your FCI or AG is either:
- A virtual machine on the same virtual network as your FCI or AG (a separate Base Monitor can be set up for this and connected to a single web front-end as described for Multiple Base Monitors), or
- Is connected to that network through a VPN. Example instructions for setting up a point-to-site connection can be found here: https://docs.microsoft.com/en-us/azure/vpn-gateway/vpn-gateway-howto-point-to-site-resource-manager-portal.
Steps you need to follow in Azure
If you have followed the steps in the link above, you will have an internal load balancer (ILB) configured that forwards SQL Server traffic to the active node.
Make a note of the port that you're planning to use for WMI: by default, SQL Monitor uses DCOM, so this is port 135. If you're using WinRM/HTTPS this will be 5986 and if you're using WinRM/HTTP this will be 5985. You can select the WMI protocol when you add the cluster:
Create a new load balancing rule:
- It forwards from the load balancer's frontend IP address (which should correspond to the cluster resource name of the SQL Server FCI or the SQL Server AG).
- It forwards from the frontend WMI port (by default, 135) to the same backend port (135 by default).
- The backend pool is the pool of machines participating in the failover cluster instance.
- The health probe is the one created to track the active instance.
- There should be no session persistence.
- Floating ip (direct server return) should be disabled.
Adding the machine in SQL Monitor
The normal reference points within SQL Monitor for clusters are the domain names associated with Windows Server Failover Clusters (WSFCs). Since these are not resolvable (and currently cannot be configured to be resolvable through a load balancer), we instead allow access through the domain name that references the SQL Server Failover Instance or the SQL Server Availability Group (the availability group listener name). If your WSFC holds more than one FCI or AG, you can use the name of any of them at this point: the important point is that it always refers to an active machine.
- On the Add SQL Server configuration page, enter the domain name of your FCI or AG listener:
- Click the Edit properties link under Machine. Deselect auto-detect cluster name
- Click OK and then Add
SQL Monitor will detect all machines within the WSFC associated with the FCI or availability group and will begin monitoring them.