Monitoring

SQL Server

LogicMonitor comes ready to monitor your MSSQL database infrastructure. Depending on what metrics you are specifically looking to monitor, our Microsoft SQL Server monitoring suite will rely on WMI, Perfmon, and/or JDBC protocol availability to function.

Before proceeding, please ensure that you have run through our Perfmon troubleshooting steps and WMI troubleshooting steps, and verify that you are able to successfully establish a remote Perfmon & WMI connection to your Windows device.  

 

Monitoring SQL Server Connections

LogicMonitor can measure whether your SQL database is processing new connections with the SQL Server Connections datasource. For the SQL Server Connections datasource to function, no specific rights are needed except the ability to connect to the database.  If the user that your collector is running under does not have rights to connect to the database, you can grant it rights thus:

CREATE LOGIN [domain_name\account_name] FROM WINDOWS
go 

Declaring Credentials for SQL Server Connections Monitoring

Out-of-box, LogicMonitor assumes that you will be running with Windows based (integrated) authentication. This means that Windows will assume the permissions for the user account that the LogicMonitor collector service is running as to make the SQL Server JDBC connection.

Without Integrated Mode Authentication:

If you are having issues getting your SQL Server Connections datasource to work, and if you have verified that WMI or Perfmon connectivity itself is not the issue, it may be that your SQL Server is denying the collector access based on invalid credentials. If you are not using integrated mode authentication, then you will need to declare the following 3 device properties on your device:

  1. jdbc.mssql.user
  2. jdbc.mssql.pass
  3. dbname.

Please follow our documentation on defining device properties for more information on how to add these properties to your device or device group.

Adding Instances

SQL Server Connections datasource instances should be added manually according to these instructions. You will need the name and wildcard value for each instance, where the wildcard value for each instance should be: \instance:portnumber

SQL Server Monitoring on 32-bit/64-bit Operating Systems

We cannot currently monitor 32-bit SQL Server performance data from 64-bit operating systems. A 32-bit SQL server application running on a 64-bit operating system will only report its performance data to 32-bit versions of Perfmon. You may work around this issue by monitoring your 32-bit SQL Server from a 32-bit collector machine.
 

Missing Performance Counters ?

SQL Server Performance Objects and counters are installed automatically as part of the installation. If these objects and counters are missing, it is likely that the installation process experienced an error which resulted in the counters not properly installed.

A definitive way to check if failures occurred is to search for Error: 3409 in your SQL setup logs.

To reinstate the counters, you must:
  1. Unload the counters by running 'unlodctr mssql$' from Run Command.
  2. Identify the file path to where performance counters are located.
    1. Default Path : 'HKLM\SYSTEM\Services\Currentcontrolset\Services\MSSQL$\Performance\'
  3. Reload the counters by running 'lodctr ' from Run Command.
Additional performance counter troubleshooting here