Spotlight on SQL Server 10.0.3 - Getting Started Guide

Cause

The Spotlight on SQL Server is not able to establish a connection with the target computer.

Solution

Check the following:

• The address of the server is entered correctly via the Spotlight Connection Manager.
• The Remote Procedure Call (RPC) service is running on the target computer.

• TCP Port 135 is open both to internal traffic on the Diagnostic Server and the target computer.

  WMI opens an undetermined port in addition to port 135. This can be troublesome in a firewalled environment. Specifying a port range for WMI is recommended for this type of environment. See "WMI connectivity" in the Spotlight on SQL Server Getting Started Guide for instructions on how to specify a port range. The use of port scanning software is sometimes required to locate the ports needed to be opened.

Cause

The Windows user specified to monitor the target computer is unknown to the target or doesn’t have administrator rights.

Solution

Check the following:

• If the connection is configured to use Windows Authentication, check that the user running the Diagnostic Server Service has administrator rights.
• If the connection is configured to use named credentials, check that the user has administrator rights.

Cause

The WMI class does not exist on the target machine

Solution

Try any of the following to recreate the WMI classes:

Windows Connections

• Recreate the WMI classes by opening a command prompt window and running the following command:

  wmiadap /f

• Use the Microsoft Extensible Counter List (Exctrlst) utility to enable the following counters:

  Win32_PerfDisk, Win32_PerfNet, Win32_PerfOS and Win32_PerfProc.

  See http://www.microsoft.com/downloads/details.aspx?familyid=7ff99683-b7ec-4da6-92ab-793193604ba4&displaylang=en for information.

  Note: A reboot of the target machine is required.

SQL Server Analysis Services Connections

• Recreate the WMI classes by opening a command prompt window on the machine hosting the SQL Server Analysis Services instance. Run the following command:

  wmiadap /f

• Unregister and re-register the WMI classes. To do so:

1. On the problematic machine with SQL Server Analysis Services installed, find out the SQL installation path. See below for the default installation path. Your installation path may be different.

   SQL Server Analysis Services 2014

   C:\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\bin\Counters

   SQL Server Analysis Services 2012

   C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\bin\Counters

   SQL Server Analysis Services 2008

   C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\bin\Counters

   SQL Server Analysis Services 2005

   C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin

   Note: You are looking for the directory that contains the following files:

   For unamed instances:

   MSSQLServerOLAPService

   perf-MSSQLServerOLAPServicemsmdctr.ini

   For named instances:

   MSOLAP$Your_SSAS_NamedInstanceName

   perf-Your_SSAS_NamedInstanceNamemsmdctr.ini

   If you need to check the named instance name, use services.msc.

2. At the command prompt, change directory to the installation path.

   For example, cd C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\bin\Counters

3. Run the following commands to unload and load counters:

   For unamed instances, run:

   unlodctr MSSQLServerOLAPService

   then

   lodctr perf-MSSQLServerOLAPServicemsmdctr.ini

    

   For named instances, run:

   unlodctr MSOLAP$Your_SSAS_NamedInstanceName

   then

   lodctr perf-Your_SSAS_NamedInstanceNamemsmdctr.ini

   Tip: Replace Your_SSAS_NamedInstanceName with the SQL Server Analysis Services named instance name. If you need to check the named instance name, use services.msc.

4. Run the following command to parse all the performance libraries on the system and refresh the performance counter classes on the problematic machine:

   wmiadap /f

Cause

The columns returned by the WMI class are incomplete or unexpected.

Solution

Recreate the WMI classes using:

wmiadap.exe /f

See http://msdn.microsoft.com/en-us/library/aa394528(VS.85).aspx for more information.