Monitor does not collect SQL statistics

andomarandomar Posts: 17
edited September 16, 2011 10:47AM in SQL Monitor Previous Versions
For our cluster of two nodes, Red Gate monitor collects machine statistics:

Vay1Q.png

However, it does not collects SQL Server statistics:

Uw9rR.png

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.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • 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 DonahueBrian Donahue Posts: 6,590 Bronze 1
    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
  • 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 DonahueBrian Donahue Posts: 6,590 Bronze 1
    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?
  • 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:
    EsC7r.png[/img]

    When we add `nl-sql1`, it automatically expand the 2 nodes. We have three license keys installed.
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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)
  • Hi Brian,

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

    tdFpq.png

    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 DonahueBrian Donahue Posts: 6,590 Bronze 1
    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 DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • Here's a screenshot of Global Overview:

    3LxdS.png

    -Andomar[/img]
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • Here's a screenshot when I select the cluster:

    w43DL.png

    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.
  • Not really. If you go into SSMS and get the server properties for the instance, the last line should say "IsClustered=true"
  • Looks like that's set correctly:

    5o2cs.png

    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.
  • 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
  • Looks like the instances are not there:
    nEFer

    The query without where and with *:
    QHcgW.png

    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.
  • How about wbemtest again - using namespace root\cimv2 and running query select * from Win32_Service WHERE Name like 'MSSQL%'
  • Running with "WHERE Name Like 'MSSQL%'" gives no results. Running query without the Where clause shows there are no results containing MSSQL:

    GvYPh.jpg
  • 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\*
  • 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
  • Looks like adding <node name>\* fixed the issue. After that it had detected one instance (local).
Sign In or Register to comment.