Monitoring SQL Server Failover Cluster Instances and AlwaysOn Availability Groups hosted in Azure IAAS
Published 09 October 2017
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, it is necessary to follow the steps described 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. 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 port 135 to backend port 135
- 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. 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:
- Click the Edit properties link under SQL Server instances. Make sure that TCP/IP is selected for communication and select port 1433. Importantly, select Fix 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.