Redgate Monitor 14

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

This page describes:

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

Redgate 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 Redgate Monitor database, the most popular being:

Availability Group.
SQL Server Failover Cluster instance.

Web Server

When choosing a Web Server to use with Redgate Monitor, there are two installation options: default Redgate 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 Redgate 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 Redgate 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.
Create the encryption keys file. This should be in a shared location accessible to all nodes. See Managing the encryption keys file for details on changing the location of this file.
- Alternatively, you can use a separate encryption keys file for each node, but you will need to ensure they always contain the same keys.
Copy the Redgate Monitor installer to all nodes on your Windows Failover Cluster.

Run the Redgate 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:

{
  "keyStore": {
      "path": "<path to the encryption keys file you created earlier>"
  }
  "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 Redgate Monitor Base Monitor and Redgate Monitor Web Server services.
For each service, open Properties and set the Start type to Manual.
Stop both the Redgate Monitor Base Monitor and Redgate Monitor Web Server services.

Run the Redgate Monitor installer, configuring the same options as when installing onto the passive node.

Open %ProgramData%/Red Gate/SQL Monitor/appSettings.json and add the following:

{
  "keyStore": {
      "path": "<path to the encryption keys file you created earlier>"
  }
}

When the installation has completed, load the web interface and enter the Administrator password.
Close the web interface.
Open Services.msc.
Locate the Redgate Monitor Base Monitor and Redgate 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 Redgate Monitor Base Monitor service from the services list.
Complete the wizard.
Repeat the above for the Redgate Monitor Web Server service.
With the role selected, open the Resources tab from the bottom pane.
For each Redgate Monitor resource, right-click and choose Bring online.
Attempt to connect to the Redgate 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 Redgate Monitor when the base monitor is clustered across multiple subnets

If an alternative approach is taken to make the website part of Redgate Monitor highly available, the Redgate 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, Redgate 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, Redgate Monitor will automatically use both IP addresses.

Upgrade guide

When configuring Redgate 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 Redgate Monitor Base Monitor and Web Server resources in your role.
Right-click each resource and Take offline.

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

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

Using Redgate Monitor website with a load balancer

Redgate 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"
}