SQL Monitor 9

Overview

SQL Monitor's distributed monitoring architecture allows Managed Service Providers (MSPs) to collect and store monitoring data within each client’s separate network, while allowing that data from all clients to be combined into a single integrated web interface for consumption.

A typical environment might look like Figure 1. Each client site has a Base Monitor, which connects to any SQL Server instances & machines being monitored. Each of these base monitors has a SQL Server database to use as its repository, ensuring that data belonging to that client is stored within the same network. In the MSP network, the Primary Base Monitor is installed, along with a repository. This Base Monitor does not itself monitor any servers, but is used for logging in to the Web Server. The Web Server, also in the MSP network, connects to the Base Monitors at each client site over a single configurable port, and consolidates data from each client into a single user interface.

This configuration is typical of a multi-base-monitor setup, and is also commonly used for monitoring environments where servers are distributed geographically, or when an organisation has servers in isolated networks where communication between them is difficult. It also serves well when security requirements demand that monitoring data not to be stored outside a particular network. More information about setting up this kind of environment can be found here.

Figure 1.

In some situations, it can be valuable to give clients direct visibility into the information available from SQL Monitor. Though in some cases this can be achieved by providing access to the MSP's Web Server and restricting user access only to that client's machines, this does present challenges around manageability, and leaves open the risk that a misconfiguration will accidentally grant a user from client A visibility into servers belonging to client B. For that reason, a different approach is preferred.

SQL Monitor allows multiple Web Servers to be connected to a single Base Monitor, as in Figure 2. The retains the MSP's unified view of all their clients' estates, while allowing each client to connect to a separate Web Server which will only have access to their own data.

Figure 2.

Configuration

To use this architecture:

Firstly perform a standard single-base-monitor SQL Monitor installation at each client site, as well as inside the MSP network.
On each client SQL Monitor installation (not the MSP network installation) add SQL Server machines to be monitored. This step can also be done later.
On the SQL Monitor installation in the MSP network, open %ProgramData%\Red Gate\SQL Monitor\RedGate.SqlMonitor.AuthorizedClients.config. This file contains a thumbprint identifying a Web Server installation, which determines whether a particular Web Server is permitted to connect to a Base Monitor. Copy this thumbprint.
Navigate to this same file on the Base Monitor machine in client A’s network. Add the thumbprint copied in step 3 to this file on a new line. Save this file, then restart the monitoring service (SQL Monitor 9 Base Monitor). The Base Monitor for client A will now accept connections from its own Web Server as well as the Web Server in the MSP network. Repeat this step for each client.
Open the SQL Monitor web interface in the MSP network. Navigate to Configuration > Base Monitor Connections. Add the connection details for each client's Base Monitor, retaining the MSP Base Monitor as the primary (this will not monitor any servers, but is used for authentication).

Important considerations

Upgrades

SQL Monitor requires that a Web Server and Base Monitor be running the same version. In effect, this means it becomes necessary for upgrades to SQL Monitor at all client sites to be performed at the same time. If SQL Monitor is upgraded in client B’s environment, it will continue to collect data and be accessible via client B’s separate web server, but the MSP network’s Web Server will not function until it and all other client installations of SQL Monitor have also been upgraded.

Permissions & concurrent access

When multiple users use multiple Web Servers to make changes to a single Base Monitor, the results could be unpredictable. It is therefore strongly recommended that only one web server is routinely used to write changes back to SQL Monitor. This includes administration tasks such as adding / removing servers, as well as day to day tasks like clearing alerts.

To this end, we recommend that the MSP retain administrative control over the Web Server within the client network, and issue the client with Read Only users (either through Active Directory or SQL Monitor built in accounts). This will provide the client with complete visibility into their own estate, but without them being able to make changes which could potentially conflict with competing changes being made by the MSP.