- About LogicMonitor
- Cloud Monitoring
- Dashboards and Widgets
- Getting Started
- LM Service Insight
- Backup and Recovery Systems
- Cloud Resources
- Networking & Firewalls
- Fortinet FortiWLC Monitoring
- Fortinet FortiWeb Monitoring
- Fortinet FortiSwitch Monitoring
- Fortinet FortiManager Monitoring
- Fortinet FortiMail Monitoring
- Fortinet FortiAuthenticator Monitoring
- Fortinet FortiADC Monitoring
- Ruckus ZoneDirector Monitoring
- Cisco VoIP Monitoring
- Cisco UCS Monitoring
- Cisco Wireless Monitoring
- Brocade Application Delivery Controllers
- Checkpoint Firewalls
- Cisco APIC Monitoring
- Cisco ASA/ASR
- Cisco Device SNMP and NTP Configuration
- Cisco Firepower Chassis Manager Monitoring
- Cisco IP SLA Monitoring
- Citrix NetScalers
- Dell Switch Monitoring
- F5 BIG-IP Monitoring
- Fortinet FortiGate Monitoring
- Infoblox Monitoring
- Interface Status alerting and Bandwidth Utilization
- Juniper SRX
- Kemp LoadMaster Load Balancers
- Meraki Cloud Access Controllers
- NetFlow Monitoring
- Palo Alto Firewalls
- pfSense Firewalls
- Sonicwall Firewalls
- Operating Systems & Virtualization
- VMware Horizon Monitoring
- Citrix XenServer Monitoring
- Citrix XenApp/XenDesktop Monitoring
- ESXi Servers and vCenter/vSphere Monitoring
- Linux Disk Performance
- Linux File Systems reporting more than 100% usage
- Linux Inodes
- Linux Interface Bandwidth Utilization
- Linux NFS Server
- Monitoring a Domain Controller (DC)
- Monitoring Remote Linux Files
- NTP Configuration
- NTP Monitoring
- Nutanix HyperConverged Infrastructure
- SNMP v1/v2 Configuration
- SNMPv3 Configuration
- Solaris Monitoring
- Troubleshooting Perfmon Access
- Troubleshooting SNMP
- Troubleshooting WMI
- VMware vCenter Server Appliance (VCSA) Monitoring
- Windows Cluster Monitoring
- Windows Firewall Issues
- Windows Server 2000
- Windows XP
- Applications & Databases
- Atlassian Statuspage (statuspage.io) Monitoring
- Microsoft Office 365 Monitoring
- OpenMetrics Monitoring
- Zoom Monitoring
- Windows Server Failover Cluster (on SQL Server) Monitoring
- Slack Status Monitoring
- Unomaly Monitoring
- Apache Monitoring
- Cassandra Monitoring
- ConnectWise Monitoring
- Email Service Monitoring
- Java Applications
- Lighttpd Monitoring
- Microsoft Exchange Monitoring
- Microsoft SQL Server Monitoring
- MongoDB Monitoring
- MySQL Monitoring
- Nginx Monitoring
- Oracle Monitoring
- Pick & D3
- Postfix Monitoring
- PostgreSQL Monitoring
- RabbitMQ Monitoring
- Redis Monitoring
- Twilio Monitoring
- Varnish HTTP Accelerator
- Server & Operations Hardware
- Storage Systems
- Cisco HyperFlex Monitoring
- Dell SC Monitoring
- Apache Hadoop Monitoring
- EMC ECS
- EMC Isilon Monitoring
- EMC Unity Monitoring
- EMC VMAX
- EMC VNX/Clariion SAN
- EMC VNXe
- EMC VPLEX
- EMC XtremIO
- HPE 3PAR Storage
- HP MSA / StorageWorks / P2000
- HP P4000/Lefthand SANs
- NetApp E/EF-Series Monitoring
- NetApp Monitoring
- Nimble Storage
- Panzura Cloud Storage
- Quantum Small Tape Libraries
- VMware vSAN Monitoring
- Rest API Developers Guide
- RPC API Developers Guide - Deprecated
- Servicenow CMDB Integration
- Terminology and Syntax
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.
LogicMonitor’s Microsoft SQL Server package is confirmed to be compatible with SQL Server 2005 through 2019.
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.
As Microsoft releases newer versions of SQL Server, LogicMonitor will test and extend coverage as necessary.
- Collector version 27.0 and higher. The Collector(s) assigned to your SQL Server hosts should be version 27.0 or higher. Older Collectors may carry older Microsoft JDBC drivers that are not compatible with some of our Microsoft SQL Server DataSources.
- 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.
Add Resources Into Monitoring
Add your SQL Server hosts into monitoring. For more information on adding resources into monitoring, see Adding Devices.
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:
GRANT VIEW ANY DEFINITION TO <USERNAME> GRANT VIEW SERVER STATE TO <USERNAME> GRANT VIEW ANY DATABASE TO <USERNAME> GO 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>
Note: As best practice, 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.
- 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 (Azure, Docker, or Linux (or Windows if Integrated Security Is Unavailable))
For direct SQL authentication, 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.
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:
- Unload the counters by running unlodctr mssql$ from Run Command.
- Identify the file path where performance counters are located. The default path is HKLMSYSTEMServicesCurrentcontrolsetServicesMSSQL$Performance.
- 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. For full coverage, please ensure that all of these LogicModules are imported into your LogicMonitor platform.
When setting static datapoint thresholds on the various metrics tracked by this package, LogicMonitor follows the technology owner’s best practice KPI recommendations. If necessary, we encourage you to adjust these predefined thresholds to meet the unique needs of your environment. For more information on tuning datapoint thresholds, see Tuning Static Thresholds for Datapoints.
In this Article: