After a Windows update, the WMI Microsoft SQL Server 2005 sensor does not work anymore. What is the reason for this?
This article applies as of PRTG 22
Deprecated sensor type WMI Microsoft SQL Server 2005
With the latest updates of SQL Server 2005, which were delivered by Microsoft in August 2012, you cannot monitor it with PRTG anymore. Therefore, monitoring SQL Server 2005 is discontinued. The following updates cause this issue:
- Security Update for SQL Server 2005 Service Pack 4 (KB2716429)
- Security Update for SQL Server 2005 Service Pack 4 (KB2716427)
We have made a reasonable effort to fix this from our side, but were unable to do so. Please ask the vendor for support in this matter.
You can still monitor your SQL Server 2005 with Custom Sensor PTF.SQLspXML
This sensor parses -multi channel- data from your SQL Server to PRTG.
First create a new Stored Procedure on your SQL Server using the code below:
CREATE PROCEDURE spSQLCounters AS BEGIN SELECT counter_name AS Channel ,cntr_value AS Value ,1 AS IsInt ,'#' AS Unit FROM sys.dm_os_performance_counters WHERE counter_name IN ( 'Active Temp Tables' ,'Connection Reset/sec' ,'Event Notifications Delayed Drop' ,'HTTP Authenticated Requests' ,'Logical Connections' ,'Logins/sec' ,'Logouts/sec' ,'Mars Deadlocks' ,'Non-atomic yield rate' ,'Processes blocked' ) ORDER BY counter_name END
This will return a result set like:
|Active Temp Tables||187||1||#|
|Event Notifications Delayed Drop||0||1||#|
|HTTP Authenticated Requests||0||1||#|
|Non-atomic yield rate||2830017||1||#|
PTF.SQLspXML will consume this result set and literally parse it into PRTG. More examples and how to setup the sensor is described in the download.
Note: you can use
SELECT * FROM sys.dm_os_performance_counters
To get a complete listing of all counters that you can monitor.
Is there a way to connect with PTF.SQLspXML to other than default SQL instance running on a different TCP port?
Please see the table below.
It's not necessary to specify an instance name when specifying the port