Microsoft SQL Server Monitoring

Last updated on 03 June, 2024

​LogicMonitor comes ready to monitor your Microsoft SQL Server database infrastructure, whether you’re running it on Windows, Linux, Docker containers, or hosted at Azure.

LogicMonitor’s SQL Server monitoring primarily uses SQL database queries to poll internal SQL monitoring tables. Connections are made through JDBC connection strings using Windows authentication or SQL Server authentication.

Note: LogicMonitor can monitor Windows Server Failover Clusters (WSFCs) on SQL Server. See Windows Server Failover Cluster (on SQL Server) Monitoring for details on using our SQL Server monitoring and WSFC monitoring in parallel.

Requirements

  • WMI (for SQL Server on Windows). LogicMonitor uses the WMI protocol for automated SQL-named instance discovery and SQL services (browser, reporting services, agent, and so on) discovery.
  • SNMP (for SQL Server on Linux). LogicMonitor uses the SNMP protocol for automated SQL Server discovery.
  • LogicMonitor’s Microsoft SQL Server package is compatible with SQL Server 2005 through 2022 using Windows or SQL Server Authentication.

Note: Due to the many potential configurations of SQL Server, LogicMonitor may not be able to provide out-of-the-box compatibility in all environments. It may be necessary for you to manually configure some aspects of the solution.

Adding Resources Into Monitoring

Add your SQL Server hosts into monitoring. For more information on adding resources into monitoring, see Adding Devices.

Credentials

LogicMonitor must be able to provide the appropriate credentials in order to successfully access each instance of SQL Server. These credentials must belong to a user account with the following minimum SQL Server permissions:

/* make sure to put your complete username in the form of DOMAIN\USERNAME
If the user already exists for a database there will be errors displayed.  These can be ignored
*/
DECLARE @username VARCHAR(100)
 
--Enter username here
select @username = '<MonitorUserName>'
 
DECLARE @dbname VARCHAR(50)  
DECLARE @statement NVARCHAR(max)
Use master;
SELECT @statement = 'CREATE USER ['+@username+'] FOR LOGIN ['+@username+'];
GRANT VIEW ANY DEFINITION TO ['+@username+'];
GRANT VIEW SERVER STATE TO ['+ @username+'];
GRANT VIEW ANY DATABASE TO ['+ @username + '];
USE MSDB;
CREATE USER ['+ @username + '] FOR LOGIN ['+ @username + '];
GRANT SELECT ON SYSJOBS TO ['+ @username + '];
GRANT SELECT ON SYSJOBHISTORY TO ['+ @username + '];
GRANT SELECT ON SYSJOBACTIVITY TO ['+ @username + '];'
exec sp_executesql @statement
 
/* this section will create access for Microsoft_SQLServer_Storage datasource only. 
Not needed if not using this module.
*/
 
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR 
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution') -- ignore system databases
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @dbname 
WHILE @@FETCH_STATUS = 0 
BEGIN 
SELECT @statement = 'use ['+@dbname +'];'+ 'CREATE USER ['+@username+']
FOR LOGIN ['+@username+']; '
exec sp_executesql @statement
FETCH NEXT FROM db_cursor INTO @dbname 
END 
CLOSE db_cursor 
DEALLOCATE db_cursor

Recommendation: Do not use a user account with system administrator privileges for monitoring.

As discussed next, the user account that LogicMonitor uses to connect with SQL Server will be authenticated by either Windows or SQL Server, depending upon the operating system upon which SQL Server is running.

Integrated Security (Windows)

By default, LogicMonitor assumes Integrated Security is used and will attempt to connect with SQL Server using Windows authentication, not SQL authentication. LogicMonitor uses the Windows credentials that are associated with the user on the Collector; therefore, the user on the Collector must have the minimum SQL Server permissions mentioned previously. There is no way to update the user associated with the Collector to an alternate user with Integrated Security.

If Integrated Security is not configured, connection can be authorized via JDBC credentials, as discussed in the following section. Otherwise, JDBC credentials, even if present as properties on the Collector, are ignored.

Conditional requirement:

  • SQL Server Browser service. Allows the Collector to communicate with SQL Server instances without the need for specifying ports. This is only required if one of the following conditions is present:
    • The SQL Server instance(s) are listening on non-standard ports (ports other than default 1434) and you have elected not to define these ports using the jdbc.mssql.port property (this property is discussed in the following Assign Properties to Resources section of this support article).
    • A SQL Server instance is set to listen on dynamic ports.

JDBC Credentials

For direct SQL authentication in Azure, Docker, or Linux (or Windows if Integrated Security Is Unavailable), JDBC credentials are required. These credentials are configured as properties (jdbc.mssql.user and jdbc.mssql.pass) on the host. If there are different authentications for individual SQL instances then the mssql.<instance name>.user and mssql.<instance name>.pass properties are used instead. For more information on these and other properties available for assignment on SQL Server hosts, see the next section of this support article.

Assign Properties to SQL Server Resources

The following properties can be set on the SQL Server resource within LogicMonitor. LogicMonitor strives to auto-discover all properties needed for SQL Server configurations. However, there may be use cases where you will need to manually enter properties or override auto-discovered properties. For more information on assigning properties, see Resource and Instance Properties.

PropertyDescriptionRequired?
mssql.onlyAlwaysOnDatabasesSet to true to identify a resource with multiple listeners as AlwaysOn Availability Group. Defaults to false.Optional
jdbc.mssql.user

jdbc.mssql.pass
SQL Server username and password. This cannot be a Windows user account. This account must have the minimum SQL Server permissions described in the previous Provide Credentials section of this support article.Required for Linux, Azure, and Docker operating systems that do not require unique authentication on a per-server instance (if per-instance authentication is required, use the mssql.<instance name>.user and mssql.<instance name>.pass properties instead). (Required for Windows operating systems if Integrated Security is not configured; If Integrated Security is configured, the values entered here will override it.)
jdbc.mssql.portPort numbers used by SQL Server. Multiple ports should be specified with a comma (for example, “1434, 2266, 51000”)Required if Docker containers are used as discovery of instances depends on this property containing all ports for Docker. For other operating systems, if the SQL Browser is running it will automatically assign ports to the connection string; if SQL browser isn’t running and no port is specified for this property, the default UDP port of 1434 will be used.
auto.sql_server_instances
or
mssql.sql_server_instances
If SQL Server instance names are automatically discovered, they will be assigned to the “auto.” version of the property. If instance names need to be manually assigned, use the “mssql.” version of the property. Instance values assigned manually (those assigned to mssql.sql_server_instances) will override those that were automatically assigned (those assigned to auto.sql_server_instances). Multiple instance names should be separated by commas (for example, “MSSQLServer, Inventory, Finance”). This property is not valid for Linux and Docker instances. These instances use ports and not names.Required
auto.<instance name>.mssql_url
or
mssql.<instance name>.mssql_url
The JDBC connection string for the instance. If SQL Server instance JDBC strings are automatically discovered, they will be assigned to the “auto.” version of the property. If URLs need to be manually assigned, use the “mssql.” version of the property. Instance values assigned manually will override those that were automatically assigned. JDBC connection strings should be formatted as follows: jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]. See Microsoft’s documentation system for more information on building JDBC connection strings.Required
mssql.<instance name>.user

mssql.<instance name>.pass
If unique authentications are required for each SQL Server instance, use these properties to assign credentials on a per-instance basis.Optional
mssql.<instance name>.portIf different ports are used for individual instances, use this property to assign ports on a per-instance basisOptional
mssql.deletenamesBy default, old instance names are never deleted in order to preserve history of instances. Set mssql.deletenames to true if you want to delete SQL server instances that no longer are in use. 

Setting this property to true and then running the property source Microsoft_SQLServer_Connections cleans up old instances.  Delete the property after the instances are cleaned up.
Optional
auto.sql_browser_runningLogicMonitor will check for the existence of SQL browser and automatically assign a value of TRUE or FALSE.Optional
mssql.integrated_securityPrevents PropertySources from dynamically determining whether to use Windows or SQL authentication. This property is manually set to TRUE or FALSE. A value of TRUE instructs LogicMonitor to only use Windows authentication; a value of FALSE instructs LogicMonitor to only use SQL authentication. If this property is not used, Auto Discovery will attempt both authentication methods.Optional
system.categoriesIf system.categories contains “MSSQL” then the resource will be considered a SQL Server and the appropriate DataSources will apply. Typically, this value will be auto-assigned by the addCategory_MSSQL PropertySource. If it isn’t, it must be manually assigned.Required

Migration to Latest LogicModules

In February 2020, LogicMonitor’s SQL Server package received significant updates to enhance performance and have a lower impact on Collector and SQL Server load. As a result of these updates, some DataSources may create new instances with new names. This is the result of fixes to earlier DataSource versions that were creating non-conforming instance names. If new instances are created, the old instances will no longer collect data but will remain in place (until you manually delete them) for historical purposes.

In addition, the following DataSources were deprecated:

  • Microsoft_SQLServer_AgentJobs. This DataSource has been superseded by Microsoft_SQLServer_SystemJobs, which captures the same data.
  • WinSQLServer-. This DataSource has been superseded by Microsoft_SQLServer_GlobalPerformance, which captures the same data.
  • WinSQLServerConnection-. Used to report the roundtrip time for a connection, this data has largely been found to not be useful.

Troubleshooting: 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, perform the following steps:

  1. Unload the counters by running unlodctr mssql$ from Run Command.
  2. Identify the file path where performance counters are located. The default path is HKLMSYSTEMServicesCurrentcontrolsetServicesMSSQL$Performance.
  3. Reload the counters by running lodctr from Run Command.

Additional performance counter troubleshooting can be found in Microsoft’s documentation system.

LogicModules in Package

LogicMonitor’s package for SQL Server consists of the following LogicModules.

Recommendation: For full coverage, import all LogicModules into your LogicMonitor platform.

NameTypeDescription
addCategory_MSSQLPropertySourceAdds “MSSQL” as a value to the system.categories property for resources identified as running SQL Server. Doesn’t attempt validation.

Automatically finds AlwaysOn listener names when the module runs against a Virtual Network Name (VNN) resource. This module uses powershell to run MSFT specific cluster commands to identify nodes in the cluster and to identify listener names.
Microsoft_SQLServer_ConnectionsPropertySourceFinds instance names for SQL Server  and creates the JDBC connection strings used by all DataSources for connections. Adds the version and the edition of SQL Server to the device.
Azure_SQLServer_JDBCDataSourceMonitors the global performance metrics of the Azure SQL Server.
Microsoft_SQLServer_AlwaysOnAvailabilityGroupsDataSourceMonitors SQL Server AlwaysON Availability Group performance metrics.
Microsoft_SQLServer_AlwaysOnDatabaseReplicaClusterDataSourceMonitors Cluster-Wide SQL Server Database Replica failover ready state, database join state, and pending suspension.
Microsoft_SQLServer_AlwaysOnDatabaseReplicasDataSourceMonitors the SQL Server AlwaysOn database replica operating state, synchronization health and state, log activity, throughput, transmission and queue size.
Microsoft_SQLServer_AlwaysOnAvailabilityReplicasDataSourceMonitors the SQL Server AlwaysOn local availability replica Operating, Connection, Recovery and Synchronization state.
Microsoft_SQLServer_BackupAgeDataSourceNumber of hours since the last full or log backup. No alerts, since backup age requirements vary.
Microsoft_SQLServer_ConnectionStatusDataSourceChecks the ability of the collector to connect to the SQL database. Reports the round trip time for a select statement. 
Microsoft_SQLServer_DatabaseFilesDataSourceThe amount of space used by each database converted to bytes. Uses high-impact SQL commands and should not be run more than once every hour.
Microsoft_SQLServer_DatabasesDataSourceFor each database found on the SQL Server, monitors active sessions, log operations, throughput, cache, latency, file/log sizes, and so on.
Microsoft_SQLServer_DatabaseStorageDataSourceStorage statistics for databases. The PUBLIC permission is required for the monitoring user.  This permission cannot be automatically assigned to new databases.
Microsoft_SQLServer_GlobalPerformanceDataSourceMonitors the global performance metrics of the SQL Server instances.
Microsoft_SQLServer_MonitorUserDataSourceDisplays the number active and inactive sessions that the LM monitoring user has on the SQL instance. Alerts if the monitor user has too many connections open.
Microsoft_SQLServer_SystemJobsDataSourceMonitors all system jobs on the SQL Server.
Microsoft_SQLServer_TroubleshooterDataSourceAttempts to execute a handful of Microsoft SQL queries to ensure proper permissions are configured to retrieve all metrics.
WinSQLServices-DataSourceDisplays all the statuses of the SQL services running. Alerts can be configured if one of the services such as SQL Agent is down.
Microsoft_SQLServer_DatabaseNamesConfigSourceWill alert when a database is created, deleted or renamed

AlwaysOn Availability

AlwaysOn Availability Groups use SQL clusters and require the use of SQL Listeners on a Windows Cluster Virtual Network Name (VNN).

By default the AlwaysOn modules use one Listener. When only one Listener exists, the DataSources monitor the system databases on the active node for the Listener and monitor any databases not assigned to the Listener but on the currently active node. In situations with multiple Listeners, the resource property mssql.onlyAlwaysOnDatabases=true must be set on the VNN resource. When this resource is set, only databases in the AvailabilityGroup assigned to the Listener are monitored. System databases and databases not assigned to the AvailabiltyGroup are not monitored.

ConnectionStatus

The Microsoft_SQLServer_ConnectionStatus DataSource is used to check connectivity from the collector to an SQL Server database. By default, the DataSource attempts to connect to the SQL Server database three times using the following behavior before alerting:

  • First attempt fails: DataSource waits three seconds and then attempts to reconnect.
  • Second attempt fails: DataSource waits 12 seconds and then attempts to reconnect.
  • Third attempt fails: DataSource sends an alert.

You can use the ConnectionStatus properties to set the number of tries and the length of time between tries. To prevent the DataSource from hitting a timeout or exceeding the collection interval, keep in mind the following time constraints:

  • The collection interval times out at 60 seconds.

Recommendation: Keep the retries within the collection interval.

  • The timeout for DataSources defaults to 180 seconds, but can be changed in the collector properties.

Recommendation: Change the collection interval to three or more minutes if there is a slow, unreliable connection. For most servers, the default collection interval is sufficient.

When a connection to the database is successful, the DataSource runs the select statement SELECT getdate() as systemDate and reports the number of rows returned, as well as the time for execution. This select statement can be overridden with a device property. Properties are in the form of mssql.propertyName or mssql.instance.propertyName.  When mssql.instance.propertyName is used, the property will be used for a specific SQL instance name.  When there is no instance specified, the DataSource uses the same property for each SQL instance on the device.

ConnectionStatus Properties

Property NameDescriptionExampleDefault
mssql.sqltest

or

mssql.instance.sqltest
SQL statement run each time the connection test is run. The jdbc.mssql.user must have access to this table for the statement to succeed.SELECT name from master.sys.server_principalsSELECT getdate() as systemDate
mssql.max_retries

or

mssql.instance.max_retries
Number of retries before generating an alert. Setting this number too high may result in DataSource timeouts, and the alert will not be sent.
The combination of max_retries and wait_time
 needs to be less than 180 seconds.
53
mssql.wait_time

or

mssql.instance.wait_time
Wait time in seconds for the first try. Subsequent tries will be wait_time * retrycount. Setting this number too high will cause a collector timeout, as well as a possible missed alert.23

Sample Setup

In This Article