SQL Monitor 12

Requires SQL Monitor v12.0.6 or newer.

Having multiple components which span different technologies, there are several options when implementing SQL Monitor as a High Availability solution.

This page describes:

SQL Monitor components and popular High Availability options.
How to install SQL Monitor with High Availability.
Upgrading a SQL Monitor High Availability installation.
Using SQL Monitor website with a load balancer

SQL Monitor components and popular High Availability options

Data repository

Making a SQL Server database highly available is common and the same techniques can be used for the SQL Monitor database, the most popular being:

Availability Group.
SQL Server Failover Cluster instance.

Web Server

When choosing a Web Server to use with SQL Monitor, there are two installation options: default SQL Monitor Web Server (Kestrel) and your own IIS Server.

Regardless of the installation option, only one web service can control licensing via red-gate.com (see licensing docs). On all other web services, ensure that %ProgramData%/Red Gate/SQL Monitor/appSettings.json (create the file if it doesn’t exist), contains the following:

{
  "featureFlags": {
      "Licensing":"off"
   }
}

The webservice controlling licensing must not be down for more than 7 days. This is the maximum period licenses will be cached for. If this period elapses, licenses will be revoked and monitoring/alerting may stop. If the webservice controlling licensing fails over and becomes the passive node, this is okay, as licensing will run in the background providing the service is up. There is no need to fail back over, licensing can be run from a passive node.

Users choosing their own IIS Server can use existing techniques to make that IIS Server highly available.

If using the default SQL Monitor Web Server, this can be configured as a Windows Failover Cluster Resource (see below).

Monitoring service

This can be configured as a Windows Failover Cluster Resource (see below).

Installation guide

The steps required to configure SQL Monitor as a High Availability solution.
This example uses the following methods:

Data repository – Availability Group database.
Web server – Windows Failover Cluster resource.
Monitoring service – Windows Failover Cluster resource.

This guide requires a Windows Failover Cluster to have been pre-configured and SQL Server instances available to be part of an Availability Group, as well as prior knowledge of configuring Availability Groups.

Installation steps

To use an Availability Group database as part of the solution, create an empty database, Availability Group and Availability Group listener using your preferred method.
Copy the SQL Monitor installer to all nodes on your Windows Failover Cluster.

Run the SQL Monitor installer, selecting the default options of installing both the default Web interface and monitoring service and choose a port that the web interface will be accessible over.
When asked to specify a database for the repository, choose Select database and Use an existing database. Specify the Availability Group listener and database previously created.
Complete the remainder of the installation wizard and install.
Open %ProgramData%/Red Gate/SQL Monitor/appSettings.json and add the following:
```
{
  "featureFlags": {
      "Licensing":"off"
   }
}
```
When the installation has completed, load the web interface and specify an Administrator password.
Close the web interface.
Open Services.msc.
Locate the SQL Monitor Base Monitor and SQL Monitor Web Server services.
For each service, open Properties and set the Start type to Manual.
Stop both the SQL Monitor Base Monitor and SQL Monitor Web Server services.

Run the SQL Monitor installer, configuring the same options as when installing onto the passive node.
When the installation has completed, load the web interface and enter the Administrator password.
Close the web interface.
Open Services.msc.
Locate the SQL Monitor Base Monitor and SQL Monitor Web Server services.
For each service, open Properties and set the Start type to Manual.

Run the Failover Cluster Manager and connect to the Windows Failover Cluster.
Click Roles and select an existing role, or create a new empty role.
With the role selected, choose Add Resource > Generic Service from the Actions section on the right-hand side.
Select the SQL Monitor Base Monitor service from the services list.
Complete the wizard.
Repeat the above for the SQL Monitor Web Server service.
With the role selected, open the Resources tab from the bottom pane.
For each SQL Monitor resource, right-click and choose Bring online.
Attempt to connect to the SQL Monitor web interface using a browser and the Windows Cluster name (or Availability Group listener name if you selected an existing Availability Group role) and verify it is working.

Follow the instructions on Configuring Top Query Search to allow all nodes to share the search index data.

Improving the performance of SQL Monitor when the base monitor is clustered across multiple subnets

If an alternative approach is taken to make the website part of SQL Monitor highly available, the SQL Monitor website can be configured to access the base monitor service through the network address corresponding to the clustered base monitor service. When Windows Server Failover Cluster that runs in multiple subnets, for example because they are in different datacenters, the standard way that the network resource is presented may, depending on DNS configuration, result in the website being unable to communicate with the base monitor service for a long period of time.

To address this, SQL Monitor can take advantage of multiple IP addresses being associated with the cluster resource to avoid this delay. This is analogous to the way that an availability group / FCI listener registers multiple IP addresses to support the MultiSubnetFailover connection string parameter. To register multiple IP addresses for the base monitor network address:

This can only be configured through PowerShell.

PowerShell

Add-WindowsFeature RSAT-Clustering-PowerShell
Get-ClusterResource | Where-Object ResourceType -Eq "Network Name" 
#Identify the network name of the cluster resource from the output of the above - the OwnerGroup will be the base monitor's name
Get-ClusterResource <the name from above> | Set-ClusterParameter RegisterAllProvidersIP 1

Following a restart of the web service, SQL Monitor will automatically use both IP addresses.

Upgrade guide

When configuring SQL Monitor in this way, extra steps are needed to successfully upgrade the Web Server and Monitoring service.

Upgrade steps

Open Failover Cluster Manager and connect to the Windows Failover Cluster.
Locate the SQL Monitor Base Monitor and Web Server resources in your role.
Right-click each resource and Take offline.

Run the SQL Monitor installer and complete the installation.
Open Services.msc.
Locate the SQL Monitor Base Monitor and SQL Monitor Web Server services.
For each service, open Properties and set the Start type to Manual.
Stop both the SQL Monitor Base Monitor and SQL Monitor Web Server services.

Run the SQL Monitor installer and complete the installation.
Open Services.msc.
Locate the SQL Monitor Base Monitor and SQL Monitor Web Server services.
For each service, open Properties and set the Start type to Manual.
Stop both the SQL Monitor Base Monitor and SQL Monitor Web Server services.
Open Failover Cluster Manager and connect to the Windows Failover Cluster.
Locate the SQL Monitor Base Monitor and Web Server resources in your role.
Right-click each resource and Bring online.

Using SQL Monitor website with a load balancer

From version 12.1.0 SQL Monitor implements Microsoft's Data Protection mechanism. To use this you'll need to provide a shared network location that is accessible to all web servers behind the load balancer.

Edit the appsettings.json file in %ProgramData%\Red Gate\SQL Monitor and enter a new section named "deploymentSettings" with an element named "dataProtectionKeyLocation" that contains the UNC path to the shared network location you want to use.

The storage location configured with Data Protection should have its access limited to the app itself, similar to the way you would protect configuration files. For more information check Microsoft's documentation.

Example:

"deploymentSettings": {
    "dataProtectionKeyLocation": "\\\\Server-Name\\Shared-Location"
}