SQL Monitor 3

How do I test data collection methods?

SQL Monitor collects data from your servers using:

  • ping
  • remote registry (PerfMon & registry access)
  • WMI
  • remote file access
  • SQL

If you you're having connection problems in SQL Monitor, you can investigate the problem by testing each connection type independently.

Opening the command prompt

To test each data channel, you first need to open a command prompt as an administrator.

  1. Log in to the machine that is running the SQL Monitor Base Monitor service. See the About page (Configuration > About) for details of which machine is running the Base Monitor service.
  2. Run the command prompt as an administrator:
    1. From the Start menu, select All Programs > Accessories.
    2. Right-click on Command Prompt and select Run as administrator.

    3. A security warning may be displayed. Click Yes to continue.

Testing ping

At the command prompt, run:

ping myserver.example.com

where myserver.example.com is the name of the Windows server you are attempting to monitor.

For more information, including details about the most common error messages that may be displayed, see Testing Network Connections with Ping.

Testing remote registry (PerfMon)

  1. At the command prompt, run:

    runas /netonly /user:example.com\myaccount "perfmon" 

    where example.com\myaccount is the Windows account you are using to monitor the server. This is the account listed under Credentials on the Monitored servers page in SQL Monitor (Configuration > Monitored servers), as shown below:

  2. When prompted, enter the password for this account.
  3. Click on Performance Monitor in the left pane, then click the Add button:

  4. In the Select counters from computer box, enter the name of the server you are attempting to monitor.
  5. By default, Processor is selected in the list of counters and _Total in the Instances of selected object box.
  6. Click Add>>. The selected counter appears in the Added counters box on the right:

  7. To check another server you are attempting to monitor, type its name in the Select counters from computer box and repeat the steps above.

The connection test can fail if the remote registry is not running, not responding, or the interprocess communications (IPC) pipe is blocked by a policy or registry change. The affected server will report either of the following errors:

  • (PerfMon) Unable to connect to specified machine or machine is offline (-2147481648)
  • (Registry) The network path was not found (53)

To troubleshoot this, try the following solutions in turn and retest the remote registry connection after each attempt:

  • Check that the remote registry service is running. Click Start > Control Panel > Administrative Tools > Services, and make sure that the Remote Registry Startup Type setting is Automatic, and the Status is Started.
  • Restart the remote registry service.
  • Check that you have the necessary permissions to access the remote registry. For more information, see How to Manage Remote Access to the Registry.
  • Check that the Active Directory Domain Services do not contain a policy that blocks access to the Remote Registry service. See Active Directory Domain Services.
  • If your PerfMon library has become corrupt, rebuild it. For details, see  http://support.microsoft.com/kb/300956.

Testing remote registry

  1. At the command prompt, run:

    runas /netonly /user:example.com\myaccount "regedit"

    where example.com\myaccount is the Windows account you are using to monitor the server. This is the account listed under Credentials on the Monitored servers page in SQL Monitor (Configuration > Monitored servers), as shown below:

  2. When prompted, enter the password for this account.
  3. In the Registry Editor, select File > Connect Network Registry.
  4. Enter the name of the server you are attempting to monitor in the Select Computer dialog box:

  5. Click OK.  The name of the server should be displayed in the left pane.
  6. Beneath the name of the server, expand HKEY_LOCAL_MACHINE. You should see a list of registry keys:

  7. Repeat the steps above for other servers you want to test.

The connection test can fail if the remote registry is not running, not responding, or the interprocess communications (IPC) pipe is blocked by a policy or registry change. The affected server will report the following error:

  • The network path was not found (53)

To troubleshoot this, try the same solutions described for Testing remote registry (PerfMon) above.

Testing WMI

  1. At the command prompt, run: 
    wbemtest
  2. In the Windows Management Instrumentation Tester dialog box, click Connect.
  3. In the Namespace box, type \\myserver.example.com\root\cimv2 where myserver.example.com is the name of the Windows server you are attempting to monitor.
  4. Under Credentials, enter the Windows account you are using to monitor the server and the password. This is the account listed under Credentials on the Monitored serverspage in SQL Monitor (Configuration > Monitored servers).
  5. Click Connect.
  6. Click Query and enter the following query:

    SELECT Name FROM Win32_Service
  7. Click Apply. You should see a list of results similar to those shown below:

If connection fails and an "Access Denied" error message is displayed, see WMI Troubleshooting and WMI Isn't Working!.

Testing remote file access

  1. At the command prompt, run:

    runas /netonly /user:example.com\myaccount "explorer"

    where example.com\myaccount is the Windows account you are using to monitor the server. This is the account listed under Credentials on the Monitored servers page in SQL Monitor (Configuration > Monitored servers).

  2. When prompted, enter the password for this account.
  3. In the Explorer window address bar, type \\myserver.example.com\c$ where myserver.example.com is the name of the server you are attempting to monitor. You should see the contents of that server's C: drive displayed.

If remote file access fails and an "Access Denied" error message is displayed:

  • Check that the server you are attempting to monitor has security policy permissions set to allow remote access.
  • Check that the files you are attempting to access on the server's C: drive have file sharing permissions set to allow access.

Testing SQL connection

You can test SQL connectivity using SQL Server Management Studio.

Testing SQL connection using SQL Server authentication:

  1. From the Start menu, select All Programs > Microsoft SQL Server 2005/2008/2012 > SQL Server Management Studio.
  2. At the Connect to Server dialog, enter the fully qualified name of the SQL Server instance you are attempting to monitor in the Server name box.
  3. Select SQL Server Authentication.
  4. Enter the SQL Server login and password you are using to connect to the SQL Server instance and click Connect.

Testing SQL connection using Windows authentication:

  1. From the Start menu, select All Programs > Microsoft SQL Server 2005/2008/2012 to display a sub-menu.
  2. Hold down the Shift key, right-click on SQL Server Management Studio and select Run as different user.
  3. At the Windows Security dialog, enter the Windows user name and password you are using to connect to the SQL Server instance and click OK.
  4. At the Connect to Server dialog, enter the fully qualified name of the SQL Server instance you are attempting to monitor in the Server name box, and click Connect.

If connection fails, one of the following error messages is displayed:

  • Timeout expired
  • An existing connection was forcibly closed by the remote host
  • No process is on the other end of the pipe
  • Login failed for user <x>

A connection failure may also occur if the system administrator privileges used to access the database engine are inadvertently deleted. To resolve these issues, see Troubleshooting Database Engine Connectivity.


Do you have any feedback on this documentation?

Let us know at sqlmonitorfeedback@red-gate.com


Didn't find what you were looking for?