SQL Monitor 9

Testing data collection methods

SQL Monitor collects data from your monitored servers by using:

  • WMI
  • SQL

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

You need to run these tests on the SQL Monitor Base Monitor host machine.

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's running the SQL Monitor Base Monitor service. For details about which machine is running the Base Monitor service, see the About page (Configuration > About).
  2. Run the command prompt as an administrator.

Testing WMI

  1. At the command prompt (see Opening the command prompt above), 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 servers page 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 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.

Useful PowerShell scripts

Work in progress

These scripts are a work in progress and will be added to over time.


Checking that the WMI service is running across a group of servers

[Array] $servers = "server1","server2","server3";
$service='winmgmt'
foreach($server in $servers) {
    Get-WmiObject -query "SELECT * FROM win32_service  WHERE name LIKE '$service' " -computername $server | Format-Table
}



Do you have any feedback on this documentation?

Let us know at sqlmonitorfeedback@red-gate.com


Didn't find what you were looking for?