Monitor does not collect SQL statistics

andomar

For our cluster of two nodes, Red Gate monitor collects machine statistics:



However, it does not collects SQL Server statistics:



We have done all connection checks suggested in this document:
http://www.red-gate.com/supportcenter/C ... QL_Monitor\help\2.3\SM_Testing_data_collection.htm&p=SQL%20Monitor

Any suggestions for further troubleshooting would be appreciated.

Brian Donahue

Hello,

In the first screenshot, you are being shown the machine names of two nodes in a clutser, and in the second, it's trying to show SQL instance names. Are there names instances of SQL on nl-sql1-cluster.nl.tiscali.com?

If so the problem seems to be more related to a failure to get the list of servers than a failure to collect performance counter data.

There really isn't enough information to reach any useful conclusion, though. I'll send you an email and request your SQL Monitor Base Monitor log files.

*edit* It may be that the SQL Counters need rebuilding, but hopefully that information will be in the log.

andomar

Hi, I've mailed in the logs yesterday. Have you received the logs, and if so, do they show anything useful?

Thanks for your reply,
Andomar

Brian Donahue

Hello, yes we did reply to you via email at the other address you gave us. Basically the logs said there is a remote registry issue.

\\nl-acsdb.nl.tiscali.com\HKEY_LOCAL_MACHINE returns Windows error 5 (access is denied.)

You may want to do the regedit test as described here: http://www.red-gate.com/supportcenter/C ... %20Monitor

andomar

Hi, thanks for the reply. I have not received your email.

The error line you're referring to is for the NL-ACSDB database. We added that as a test but it's not fully configured yet. The issue is with the NL-SQL1 cluster.

Can you see from the logs why we are not receiving SQL statistics for the NL-SQL1 cluster?

Kind regards,
Andomar

Brian Donahue

No, no I can't. One log file is 1KB and the other is 2KB and the only error mentions that server. Is the problem that there is no alert data, or just that, as in the screenshot, there are no nodes shown for the cluster?

Because according to the docs, you add one of the cluster nodes to the server list rather than the SQL cluster name. Could that be the problem?

andomar

Yeah, for SQL counters, there are no nodes shown. For Windows counters there are. That's basically the problem.

Here's a screenshot of the config:
[/img]

When we add `nl-sql1`, it automatically expand the 2 nodes. We have three license keys installed.

Brian Donahue

Well I didn't see any indication of it in the log but -- maybe the SQL Performance counters need to be rebuilt?

http://blogs.msdn.com/b/john_daskalakis ... 93110.aspx

(Note, not a Red Gate article so we don't take any responsibility)

andomar

Hi Brian,

Our administrator checked on the server, and the SQL performance counters are available. Here, we've added Batch Requests/sec to perfmon:



We requested a counter on the Sql Monitor server, and that also worked.

Are we correct in concluding that the perfmon counters are present on the server?

-Andomar

Brian Donahue

Okay, aside from the first screenshot do you get performance data from SQL Monitor in Global Overview->nl-sql1-cluster.nl.tiscali.com?

In the first screenshot I think you don't see any nodes because these particular counters come from the shared cluster address and not the individual nodes.

Brian Donahue

It doesn't look like SQL Monitor has detected any instances of SQL on the cluster, just the nodes. Unless you have cropped out the second screenshot.

andomar

Here's a screenshot of Global Overview:



-Andomar[/img]

Brian Donahue

Is it possible you have no clustered instances of SQL on the cluster, but you have installed nonclustered incidences locally on one or both nodes? This is possible to do I've done it myself by accident before.

andomar

Here's a screenshot when I select the cluster:



Does this confirm your suspicion? If so, would you have a suggestion on how to fix it?

Our client applications do connect successfully to the cluster IP.

Brian Donahue

Not really. If you go into SSMS and get the server properties for the instance, the last line should say "IsClustered=true"

andomar

Looks like that's set correctly:



I'll go back to the log files tomorrow-- we removed the server that was incorrectly set up, perhaps this one will now stand out.

Brian Donahue

The developers are telling me that the problem is failure to detect the SQL Server instance rather than getting perf data. That said, there are tons of errors when trying to get into the performance registry in the logs.

Wondering if you can query WMI from the SQL Monitor server and see if you get the instance names back.
On the SQL Monitor base repository computer:

• Open a console and run wbemtest.exe
• Open namespace \\nl-sql1c\root\MSCluster
• Run query: SELECT PrivateProperties FROM MSCluster_Resource WHERE Type="SQL Server"
• In the result, you should see "InstanceName" properties on each object. If the InstanceName is mssqlserver then it uses the VirtualServername property instead

andomar

Looks like the instances are not there:


The query without where and with *:


We're hoping you can provide us with a hint on what the issue and possible solutions are.

P.S. Would you know what error codes 6 and 1726 mean? From earlier in the log.

Brian Donahue

How about wbemtest again - using namespace root\cimv2 and running query select * from Win32_Service WHERE Name like 'MSSQL%'

andomar

Running with "WHERE Name Like 'MSSQL%'" gives no results. Running query without the Where clause shows there are no results containing MSSQL:

Brian Donahue

That's a bit strange. Assuming you are querying a node of the cluster, there should be an MSSQL service. Another tip is that when adding the server, try using the wildcard character to add all instances, for instance nl-sql1c\*

andomar

Hi Brian,

The screenshot was for the server that runs SQL Monitor-- no surprise that it didn't find a SQL Server service. We've now queried the cluster and cluster nodes. I'll continue the conversation by mailing support@red-gate.com, which is a bit easier than forum posts!

-Andomar

Brian Donahue

Looks like adding <node name>\* fixed the issue. After that it had detected one instance (local).