- About LogicMonitor
- Cloud Monitoring
- Dashboards and Widgets
- Getting Started
- LM Service Insight
- Backup and Recovery Systems
- Cloud Resources
- Networking & Firewalls
- Cisco VoIP 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 Firewalls
- Infoblox Monitoring
- Interface Status alerting and Bandwidth Utilization
- Juniper SRX
- Kemp LoadMaster Load Balancers
- Meraki Cloud Access Controllers
- 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
- OpenMetrics Monitoring
- Apache Monitoring
- Cassandra Monitoring
- ConnectWise Monitoring
- Email Service Monitoring
- Java Applications
- Lighttpd Monitoring
- Microsoft Exchange Monitoring
- Microsoft SQL Server (Windows & Linux)
- 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
- 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
- NetApp Monitoring
- Nimble Storage
- Panzura Cloud Storage
- Quantum Small Tape Libraries
- VMware vSAN Monitoring
- Rest API Developers Guide
- RPC API Developers Guide
- Servicenow CMDB Integration
- Terminology Syntax
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.
- Logged in identity has permissions to access the SQL Server instance.
- WMI – discovers SQL Server instances as well as SQL Server services such as Browser, Reporting Services, Agent, etc.
- SQL Server Browser service – allows the collector to communicate with SQL Server instances without the need for specifying ports. This is only required if:
- The SQL Server instance(s) are listening on non-standard ports (i.e. ports other than default 1433) and you have elected to not define these ports using the jdbc.mssql.port property (this property is discussed in the following section)
- A SQL Server instance is set to listen on dynamic ports
JDBC Credentials (Windows or Linux)
When using JDBC credentials, the Collector will attempt to directly connect to the SQL Server instance using credentials (i.e. properties) configured on the host. Authentication via JDBC credentials is compatible with both Linux and Windows Collectors and hosts.
- Define the following database credentials and port(s) as properties on the host:
- 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.
- SNMP (snmpd) – used to automatically discover if SQL Server is running on the host.
- 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.
- 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
MSSQLSERVERwill 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
MSSQLSERVERor the default port
1433will automatically be tested.
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, you must:
- Unload the counters by running
unlodctr mssql$from Run Command.
- Identify the file path where performance counters are located.
- Default Path:
- Default Path:
- Reload the counters by running
lodctrfrom Run Command.
Additional performance counter troubleshooting here.
|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.|