• Redgate Monitor
• Database Performance Monitoring

SQL Monitor Roadtrip: Preparation

SQL Monitor is a SQL Server monitoring and alerting tool, designed to allow Ops teams to supervise many SQL Server instances, even when they are geographically dispersed across different networks, hosted on ‘bare metal’ servers, in containers or Virtual Machines, or in the cloud.

It is atypical of server-based monitoring tools in that it doesn’t require the installation of an Agent service on each monitored server. Instead, SQL Monitor simply uses the existing industry-standard systems for accessing remote computers, using WMI, and it interrogates the SQL Server service conventionally using T-SQL via TDS. The WMI can either use DCOM or WinRM for transport.

SQL Monitor will generally work happily ‘out of the box’ in a conventional Windows domain where ‘remoting’ is a familiar tool. Occasionally, however, you can hit problems. These are more common in a heterogeneous environment where many devices share a ‘stand-alone’ network. If you are having connection problems with your monitored servers in your network, these can sometimes show up when you are configuring SQL Monitor. However, they are unlikely to be the fault of SQL Monitor, at least not directly.

In this article we’ll run through a brief description of how SQL Monitor works, to define some of the terms and try to clarify what’s required, then we’ll look at the aspects of connectivity that can cause problems and offer suggestions for fixes.

How SQL Monitor works

When you install SQL Monitor, you will need to set up three logical components, as follows:

• The Base Monitor (also called the Monitoring service) – a Windows service that continuously monitors the SQL Servers, wherever they are located. It is responsible for sampling, managing and interpreting the monitoring data (metrics and alerts), and performs other tasks such as user authentication.
• SQL Monitor database (also called the data repository) – a SQL Server database, called RedGateMonitor by default, for storing the sampled metric and alert data, and SQL Monitor configuration settings. It is managed by the base monitor.
• Web server – hosts the web browser, which presents the metric and alert data and allows users to manage monitored servers, configure SQL Monitor and so on. The Base Monitor retrieves and interprets the stored monitoring data and, in response to requests from the Web server, sends it the data streams required to populate and refresh the various graphs, sparklines, projections, tables, reports and charts the web interface. The web server also provides an interface with the PowerShell scripting API.

A simple installation will look something like this:

Basic SQL Monitor architecture with one base monitor

It can get more complicated in order to meet local requirements or to scale up to heavier use. For example, when monitoring many servers, geographically dispersed, or on a heterogeneous network, it’s wise to create secondary base monitors, one for each location, region or type of network. Each base monitor can handle around 250 monitored instances.

All the base monitors connect to the Web Server, which builds its overview by pulling together the information from every base monitor. The speed of SQL Monitor is limited by the communication time with the slowest base monitor.

SQL Monitor architecture with multiple base monitors

It’s also possible to have multiple UI dashboards. one per base monitor so, for example, a team can view data just for servers in their location, and with one web console that provides the global view of every server in every location.

To add a base monitor, you use the ‘Configuration’ tab. Under ‘Application options, you then select ‘Base Monitor connection’. The server which runs the SQL Monitor web site must be able to contact all servers running base monitors via the TCP port specified in the base monitor installation. If there are firewall or similar restrictions between these servers, then they will need to be configured accordingly.

The base monitor service

The base monitor service collects data about your SQL Servers and so must be able to connect to the SQL Servers you want to monitor. It uses WMI, issuing WMI Query Language (WQL) via WinRM (http/https) or DCOM to sample machine monitoring data, including virtual machine and virtual machine host metrics. This includes time series metrics for CPU, memory and disk usage, and details of system processes. It also uses WMI to discover SQL Server cluster nodes.

It collects instance and database-level time series counters, query information and so on using a mixture of WMI, plus T-SQL queries that access various SQL Server Dynamic Management Views and system catalog views. SQL Monitor uses Extended Events sessions for some data (e.g. deadlocks) but if Extended Events are not available, it will fall back to SQL.

The base monitor stores and manages the collected monitoring data in its SQL Monitor database.

The Base Monitor will, as well as connecting to monitored machines and the SQL Monitor database, also need connections to perform the following tasks:

• Authenticating SQL Monitor users – If the base monitor is within the same domain as the user, the Base Monitor Windows User is validated via Active Directory, otherwise it is by UserID and password.
• Sending alert data to other systems – it will need to connect to email systems, Slack channels, or via webhooks, to service management systems such as ServiceNow, Microsoft Teams, Jira or ZenDesk.
• Communication with Redgate infrastructure services – to perform license activations, check for SQL Monitor updates, send general usage telemetry, and retrieve data on new SQL Server patches.

If you have more multiple Base Monitors, as depicted previously, the ‘primary’ Base Monitor merely handles user authentication.

The web server service and web interface

The web interface is hosted on a web server which can be a separate machine, or the same machine as the base monitor server. The UI console is merely an authenticated web service.

For monitoring busy production servers, it is best to configure your own IIS server with SSL, but it requires IIS7.0 or later. In this case, you’ll use the IIS web service (called the World Wide Web Publishing Service). You will need to ensure that Port 7399 is open for secure RPC from the web server to the base monitor server. Where there is light traffic, you can, alternatively, install the default SQL Monitor web server, a self-contained server that uses the .NET 4.7.2 runtime and will run as a web service named SQL Monitor Web Service.

In either case, the web service will need to connect over secure RPC to its base monitor to collect the monitoring data, authenticate users and other tasks. It is important to get a robust hardware and software installation because you will not be able to log in to the web front-end if its associated Base Monitor cannot be reached.

The browser that you use to access the web server must have JavaScript enabled, because the application is heavily reliant on its use.

Planning a SQL Monitor installation

When you run the SQL Monitor installer, you’ll have the option either to install the SQL Monitor web server and the base monitor on the same computer, or the base monitor on a different computer, in the same network, using a separate installer.

Once you’ve installed the components, you’ll need to ensure that the base monitor can connect to all servers from which it needs to collect data. As noted earlier, SQL Monitor use WMI to access remote computers, collect and transmit data, and you’ll need to configure and test WMI connections, either over DCOM or WinRM.

Deploying the SQL Monitor components

If you need to quickly learn or appraise SQL Monitor, it is possible to get started on a single physical machine, installing the web server, base monitor, and SQL Monitor database all on the same machine as the SQL Server instance that you wish to monitor. This avoids most connection problems. However, you’ll outgrow this quickly, as you need to start connecting to remote monitored machines.

For monitoring a full cohort of machines, you’ll need, at the very least, one additional machine to host the web server, base monitor and SQL Monitor database. However, the overhead on a single server of running the base monitor and web server, as well as writing all the data to the repository, could affect the performance of the monitoring service. The recommended starter configuration, generally, is two separate servers, one to host the base monitor and web server and one to host the SQL Monitor database. If you plan to monitor more than 50 servers, then it is a good idea to install each of the three SQL Monitor components (Base Monitor, SQL Monitor Database and Web Server) on separate unmonitored machines.

You can run the SQL Monitor components on virtual machines, but you should make sure your VM host can deliver the required resources (CPU and RAM). If your physical machine hosts multiple VMs, for example, this will limit the resources available to SQL Monitor, which might slow performance.

There are alternatives to a standard SQL Server for the SQL Monitor database. It can be on a local SQL Server Express server but If you do that, you will need to set short data retention windows for SQL Monitor, such as one week for data you want to view trends for, and three days for troubleshooting data. You have a practical limit with Express of 10-15 monitored machines. The database needs to be robust and resilient (backed up), though, because if it fails, you won’t be able to access SQL Monitor easily. In an enterprise, the database is probably best hosted in an Availability Group or a SQL Server Failover Cluster instance. It isn’t generally a good idea to use Azure SQL Database for the SQL Monitor Database. If you want to use a SQL Server instance running on a VM for this purpose, it is better to use a physical disk or partition rather than a virtual disk for the data. To use mapped hardware in Hyper-V, see http://msdn.microsoft.com/en-us/library/cc768529(v=bts.10).

Connecting to remote monitored servers

In general, WMI, either over DCOM or via the more generally used WinRM, is becoming more important; particularly in heterogeneous networks. When it uses WinRM, it requires SOAP over HTTPS for automation: when over DCOM, it uses RPC/TPC.

When automation became key to DevOps processes, PowerShell with ‘remoting’ over WinRM became the obvious way of maintaining and servicing the separate components of a busy and changing IT infrastructure, especially when used with WMI. It is a standard SOAP-based (Simple Object Access Protocol), firewall-friendly protocol that allows hardware and operating systems, from different vendors, to interoperate Your server estate is best accessed via WinRM so it is worth aiming for this.

If you run into difficulties connecting to network-based devices, it is best to create a ‘lab’ environment, entirely unconnected to the internet and isolated from your main network and containing servers as similar as practically possible to production. You can now temporarily relax the most stringent precautions built into firewalls, servers, WMI and WinRM, and quickly get to a point where everything is working. Then you can put back the security devices one by one until something breaks. Then fix the issue and carry on. Once you have connections and production-level security, you can then assume that the only likely source of other potential problems are firewall-related.

It is worth knowing, before you get too far with setting up SQL Monitor in general, that you can look at the logs. Whereas the errors displayed by the GUI can be rather tight-lipped ‘access-denied’ messages, the logs can be very revealing if you are having any connection problems. If you particularly enjoy looking at the output of logs you can get the whole log zipped up. Fortunately, for more immediate use, the screen at Configuration > Monitored Servers is likely to provide you with just the information you need.

Viewing the SQL Monitor logs

Adding and Managing SQL Monitor users

Once you’ve installed SQL Monitor, the first action you will need to supply details of the user account that will act as administrator. Administrators can then assign other users to one of the available user roles (administrator, Standard user, Read only user, Reports user), according to what they need. These roles are merely the way that the application allows access to the accounts you create.

In a windows Domain, you’ll ideally set SQL Monitor to authenticate users with their Active Directory credentials. This will allow administrators to control not only what actions a user can perform in SQL Monitor, via role membership, but also restrict which servers each user can access.

If this isn’t possible, SQL monitor uses a ‘fallback’ mode where the one userid is shared, but each role is matched to a different password, which the Base Monitor assigns to the different user roles. The initial administrator password is initially set to be the one used on installing SQL Monitor. If another Ops person needs administrative access, you simply supply them the appropriate password according to their role.

Finally, SQL Monitor assigns authentication tokens that can be used to authenticate processes that are scripted in PowerShell. These are derived from the user account and has the privileges assigned to the user account by its role.

Accounts for the SQL Monitor Components

When you install and set up SQL Monitor, you need to provide credentials for the Web Service Account and the Base Monitor Service Account, with permissions as required to run each of these services (described shortly).

You will also need to provide credentials for an account that the base monitor service can use login to each remote monitored machine and to any SQL Server instances on each machine. This remote machine login could be the Base Monitor Service Account or a different one, and an account to login to the instances could be the same account as used to login to the machine, or a different one.

The “Configuration > Monitored servers | Edit Credentials” screen in SQL Monitor

Naturally, when you are checking the tool out on one machine, you can use one user account for all these services components, but as you expand the monitoring system, you’ll want to deploy them to different servers. In some cases, it may be possible to still use a single Windows domain account, ideally a group managed service account, to run both services, and connect to the host machines and instances. Other times, you’ll also need separate user accounts for the machines you monitor.

You can review the Permissions page of the documentation for further details, but I’ve summarized the accounts and permissions in the following figure and will describe the basic requirements.

SQL Monitor Connections, ports, accounts, permissions

Web server service account

This is the account that’s used to run the web server service, either the IIS web service or the SQL Monitor web service. It can, and probably should, be a local user. If you choose IIS, you can subsequently move this service to a different machine.

The Properties screen for the web server service showing the Log On tab

The account needs to have permission to ‘Log on as service’, either by its group membership or explicitly. It also needs ‘Full Control’ over various file paths to which it writes. If the user is not a local admin (why risk unnecessary surface area for attack?), it should be able to reserve the port that the web service is configured to use.to do this, run in an admin command console on the server:

where <SQMport> is the port you are using to access SQL Monitor (I don’t use the default 8080), and <WebServiceAccount> is the name of the Web Service Account.

If this account doesn’t have membership of the local administrators’ group, you’ll also need to give this account permission to read:

%ProgramData%\Red Gate\SQL Monitor and %ProgramFiles%\Red Gate\SQL Monitor\Web

Base Monitor service account

This account runs the SQL Monitoring service. It needs to have permission to ‘Log on as service’, either by its group membership or explicitly. It also needs ‘Full Control’ over file path:

%programdata%\Red Gate\Logs\SQL Monitor

This will usually resolve to ‘C:\Documents and Settings\All Users\Application Data\Red Gate\Logs\SQL Monitor’, although on old installations it was usually ‘C:\ProgramData\Red Gate\Logs\SQL Monitor‘

Since the base monitor stores and manages the sample monitoring data, the login should be a member of the db_owner database role on the Data Repository database (called RedGateMonitor by default).

Accounts for connecting to Monitored Servers

For each remote server that you wish to monitor, you need to provide the base monitor service with need a login, and a means by which SQL Monitor can authenticate the account.

The base monitor service will need to collect diagnostic data from the Windows machine and from each of the monitored SQL Server instances. As such, ideally, the base monitor service will run as a domain account that is a Windows administrator on the remote machine.

If you are using a non-domain account, the requirements are different.

Windows Domain

If you are within a domain, you will need a windows login account to the hosting Windows Server for each monitored SQL Server machine. If possible, this login should be an administrator account. The domain account should also have sufficient rights locally on the machine where the base monitor is installed to enable SQL Monitor to authenticate the account.

If you are using a domain, then the account that you use can be in Active directory. You add the user account to SQL Monitor on the Configuration > Monitored servers page, and a domain user is entered with the domain qualifier, as with Phil.Factor\MyCompany.com

Stand-alone

If, instead, you are connecting to a stand-alone server that isn’t in a domain then, due to the nature of how SQL Monitor authenticates to the remote machines (SQL Monitor needs to be able to authenticate the user locally to the machine where the Base Monitor is installed), you need to have the same local user on both the base station and the remote monitored one. Both the name and password need to be the same. Again, the account ideally will be an administrator on the remote machine.

A stand-alone remote server will need the user to be added as .\UserName1 so that it will use the user local to both the base monitor and remote machine.

Depending on the version of the operating system, You may also need to add the registry key specified in this article to stop the UAC from reducing the users privilege when accessing the machine you want to monitor remotely.

Within an Azure VM

To monitor an instance of SQL Server running on an Azure virtual machine (IAAS), the virtual machine can be added to SQL Monitor in the usual way.

Azure SQL Server and Elastic Pool

When adding an Azure SQL Server to monitor. You need to use the server admin account as was used to create the Azure SQL Server. You must have a server-level firewall rule that allows communication between the Base Monitor and the Azure SQL Server. This can be done through the Azure portal or PowerShell. If you have SSMS installed on the Base Monitor machine, attempting to connect through that will walk you through adding the required firewall rules.

Within a VMWare VM

SQL Monitor will access SQL Server running in a virtual machine under VMWare. It will be able to monitor both the guest virtual machine and the physical host. You will need User login credentials for the vSphere endpoint. The user should have at least read-only permission across the endpoint. For vCenter, this means read-only access across the vCenter.

Adding SQL Server instances

The login account that the base monitor service uses to monitor your SQL Server instance should ideally be a member of the sysadmin fixed server role, on the SQL Server instance, because it needs to, for example, run the DBCC DBINFO command to determine the last integrity check for a database (required for the Integrity check overdue alert) as well as enabling the deadlock trace flag to collect deadlock information (not required when using Extended Events for deadlock collection).

If sysadmin membership isn’t possible, then a slightly more restricted functionality can be used instead. See this article and grant the following permissions:

• member of the db_datareader role on the msdb system database.
• member of SQLAgentReader role on the msdb system database.
• member of the db_ddladmin database role on all databases (needed to run sys.dm_db_index_physical_stats() required by the Fragmented index alert).
• VIEW ANY DEFINITION server permission.
• ALTER TRACE server permission (if you want to enable trace data).
• VIEW SERVER STATE and VIEW DATABASE STATE database permissions on all databases.
• member of the db_owner role on the tempdb database.
• EXECUTE on xp_readerrorlog.

What to try if you hit connection problems

The first thing to do is to make sure that you can interrogate via WMI each of the machines to which you wish to connect and monitor. To so this, you can of course do it by hand, logging into the machine remotely, using the credentials you provided to SQL Monitor. You can then try ‘remoting’ to the machine in PowerShell to check that the transport for WMI that you’ve chosen (DCOM or WinRM) is working.

However, this code I provide will test WMI with both DCOM and WinRM. I prefer to start using a single account with Administrative rights on each server. You’ll need to change the names in the list ‘Shadrach’, ‘Meshach’, ‘Abednego, ‘Daniel’ to the names of your servers! Note that these are the network names of the monitored machines, not the instances. This script should be run on the base station using the account that the base monitor service will use to connect to each monitored machine

This code just verifies that the specified computer name is valid, that the computer is accessible to WMI over the network, and that a firewall exception for the WinRM service is enabled and allows access from the base monitor computer to the machine being monitored. It should tell you the extent of the work you need to do.

If you cannot get WMI to work at all, the first port of call is the article Connecting to WMI on a Remote Computer.

Getting WinRM working can be more complicated. Various things can go wrong. You may have a public network on a workstation. This needs to be removed and made private because, by default, the WinRM firewall exception for public profiles limits access to remote computers within the same local subnet. This applies to both ends of the communication. This script will do it if you use it in an Administrator Powershell window

Is the WinRM firewall rule there and enabled? This is easy to check.

If it isn’t, then add it. For Windows Firewall you can use this command to allow access to the WinRM listener

You will first need to check that Port 135 (Remote Procedure Call -RPC) is open if you are using DCOM

Also, you can use the same technique to check that all the ports for TDS (1433 etc) are allowed through the firewall.

You can also check on the more sensitive settings of WinRM security

WinRM can be used via HTTP, but this is a security risk. A secure WinRM connection should be made via HTTPS. The most frustrating part of doing this is getting the necessary security certificate.

Of these settings, the TrustedHosts setting lists the hosts that can be connected to. However, this is only used if you use a raw IP address rather than a network name, which seems a scary thing to do. It is fine to set authentication to ‘basic’ within a DMV, though.

Summary

SQL Monitor was initially designed at a time when networks tended to be local, and SQL Server was hosted within a Windows Domain. SQL Monitor opted for the clean and robust approach of monitoring SQL Server machines and SQL Servers without requiring an ‘Agent’ to be installed on each machine. In the light of subsequent developments, this turned out to be a good choice. A third-party service on a database server is an increasingly tricky design approach to justify in the light of legislation on personal data, because it requires a security audit, and in some cases is entirely ruled out.

The downside is that SQL Monitor can be more sensitive to the network setup than an agent-driven monitoring system. Where network monitoring is already automated with remote scripting, there are few if any issues. Where the WS-Management protocol is already used in a domain or heterogeneous network, you are unlikely to hit problems. If you hit connectivity problems, it is generally quicker to set up a test cell in a DMV and install a working system from which one can expand a step at a time.

Tools in this post

Redgate Monitor

Real-time SQL Server and PostgreSQL performance monitoring, with alerts and diagnostics

Find out more