Monitoring

Microsoft SQL Server (Windows & Linux)

LogicMonitor comes equipped to monitor your Microsoft SQL Server database infrastructure whether you're running it on Windows or Linux.

Setup & Credentials

Depending on your environment, you will need to configure one of the following:

Integrated Security (Windows only)

By default, LogicMonitor assumes Integrated Security is used and will attempt to use the collector service user to access the SQL Server instance.

Requirements:

  1. Logged in identity has permissions to access the SQL Server instance.
  2. WMI - discovers SQL Server instances as well as SQL Server services such as Browser, Reporting Services, Agent, etc.
  3. SQL Server Browser Service Running - allows the collector to communicate with SQL Server instances without the need for specifying ports.

JDBC Credentials (Windows or Linux)

When using JDBC credentials, the collector will attempt to directly connect to the SQL Server instance using credentials configured on the host. Authentication via JDBC credentials is compatible with both Linux and Windows collectors and hosts.

Requirements:

  1. Database credentials and port(s)
    • jdbc.mssql.user
    • jdbc.mssql.pass
    • jdbc.mssql.port - Default port 1433 will be attempted if not specified. If you are running more than one SQL Server instance on the host, specify all ports separated by commas.
  2. SNMP (snmpd) - used to automatically discover if SQL Server is running on the host.
  3. WMI (Windows) - discovers SQL Server instances as well as SQL Server services such as Browser, Reporting Services, Agent, etc.

Note : Ensure your SQL Server user has VIEW SERVER STATE permissions. You may grant these permissions by executing the following command:

GRANT VIEW SERVER STATE TO INSERT_USERNAME_HERE ;

Additional information may be found here.


Multiple SQL Server Instances

Integrated Security ( Preferred )

  • Automatically initiates WMI query to discover all SQL Server instances on the host then proceeds to collect data.

JDBC

  • Must specify all port numbers, comma delimited, in the jdbc.mssql.port host property.
    • Example: 1433,1522,1434


Dynamic & Static Port Configurations

Dynamic ( Integrated Security )

  • Environments configured to be dynamic may change their port over time, so we cannot rely on port numbers. We must resolve the SQL Server instance names which can only be done using Integrated Security.
  • Default SQL Instance name MSSQLSERVER will automatically be tested.

Static ( Integrated Security or JDBC ) 

  • Environments configured to be static, we can reliably assume the port will not change.
  • Default SQL Instance name MSSQLSERVER or the default port 1433 will automatically be tested.


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 being properly installed.

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

To reset the counters, you must:

  1. Unload the counters by running unlodctr mssql$ from Run Command.
  2. Identify the file path where performance counters are located.
    • Default Path: HKLMSYSTEMServicesCurrentcontrolsetServicesMSSQL$Performance
  3. Reload the counters by running lodctr from Run Command.

Additional performance counter troubleshooting here.


LogicModules

Name Type Description
addCategory_MSSQL PropertySource Identifies whether the Windows/Linux host is running SQL Server and sets properties accordingly.
WinSQLServices- DataSource Monitors operating state and status of SQL Server Services.
Azure_SQLServer_JDBC DataSource Monitors the global performance metrics of the Azure SQL Server.
Microsoft_SQLServer_AgentJobs DataSource Monitors the SQL Server Agent jobs running, queued, success & failure.
Microsoft_SQLServer_AlwaysOnAvailabilityGroups DataSource Monitors SQL Server AlwaysON Availability Group performance metrics.
Microsoft_SQLServer_AlwaysOnAvailabilityReplicas DataSource Monitors the SQL Server AlwaysOn local availability replica Operating, Connection, Recovery and Synchronization state.
Microsoft_SQLServer_AlwaysOnDatabaseReplicaCluster DataSource Monitors Cluster-Wide SQL Server Database Replica failover ready state, database join state and pending suspension.
Microsoft_SQLServer_AlwaysOnDatabaseReplicas DataSource Monitors the SQL Server AlwaysOn database replica operating state, synchronization health & state, log activity, throughput, transmission and queue size.
Microsoft_SQLServer_Databases DataSource Monitors each database found on the SQL server, monitoring active sessions, log operations, throughput, cache, latency, file/log sizes, etc.
Microsoft_SQLServer_GlobalPerformance DataSource Monitors the global performance metrics of the SQL Server instances.
Microsoft_SQLServer_SystemJobs DataSource Monitors all system jobs on the SQL server.