Chat now with support
Chat with Support

Spotlight on SQL Server Enterprise 11.7 - Deployment Guide

Welcome to Spotlight on SQL Server The size and shape of your deployment Install / Upgrade Spotlight data collection and storage View data and configure Spotlight Monitored connections in the deployment Deployment over the Windows network Monitored SQL Servers Monitored Windows Servers and hosts of database connections

WMI errors

The following is a guide to troubleshooting WMI errors.

Error Solution

The RPC server is unavailable.

Exception from HRESULT: 0x800706BA

Spotlight on SQL Server is not able to establish a connection with the Windows server to be monitored.

Verify the following:

  • The address of the server is entered correctly. Verify correct host name or IP address. For more information, see Windows Server | Connection Details. Verify the host is available (not currently shut down).

  • The Remote Procedure Call (RPC) service is running on the Windows server to be monitored. Verity that "Remote Procedure Call (RPC)" is running and set to auto start after restart.

  • TCP Port 135 is open to internal traffic on the Spotlight Diagnostic Server and the Windows server to be monitored.

    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. For more information, see How to limit the number of ports used by WMI.

  • The Windows server to be monitored is not blocked by the firewall.

    Either configure WMI to use a fixed ports range (For more information, see How to limit the number of ports used by WMI.) or enable remote administration exception.

    Follow these steps to enable remote administration exception.

    On the Windows server to be monitored:

    1) Open the Group Policy Object Editor (gpedit.msc), open Computer Configuration | Administrative Templates | Network | Network Connections | Windows Firewall

    2) Open either Domain Profile or Standard Profile, depending on which profile you want to configure.

    3) Enable the following exceptions: "Allow Remote Administration Exception" and "Allow File and Printer Sharing Exception".

    https://msdn.microsoft.com/en-us/library/windows/desktop/aa394603(v=vs.85).aspx

  • The "TCP/IP NetBIOS Helper" service is running. Verity that "TCP/IP NetBIOS Helper" is running and set to auto start after restart.

  • The "Windows Management Instrumentation" service is running on the Windows server to be monitored. Verity that "Windows Management Instrumentation" is running and set to auto start after restart.

Access is denied.

Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)

The Windows user specified is unknown to the Windows server or does not have administrator rights.

For more information, see How to configure WMI with minimum required user permissions.

WMI connection time outs

The timeout value defaults to the value DCOM specifies (usually 60 seconds).

You can adjust this value via dcomcnfg.exe

WMI query failed: Invalid class.

[0x80041010]

The WMI class does not exist on the Windows server being monitored.

Solution: Recreate the WMI classes.

 

Windows Connections

Either of the following will recreate the WMI classes:

 

SQL Server Analysis Services Connections

Either of the following will recreate the WMI classes:

  • Open a command prompt window on the server 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 folder 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 folder 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:

    unamed instances

    unlodctr MSSQLServerOLAPService

    lodctr perf-MSSQLServerOLAPServicemsmdctr.ini

    named instances

    unlodctr MSOLAP$Your_SSAS_NamedInstanceName

    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 Windows server and refresh the performance counter classes on the Windows server:

    wmiadap /f

  5. Use wbemtest.exe to verify the WMI Classes exist.

WMI query failed: Invalid query.

[0x80041017]

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

Solution: On the Windows server to be monitored, update the WMI classes by running the following command at the command prompt.

wmiadap.exe /f

See https://msdn.microsoft.com/en-us/library/windows/desktop/aa394603(v=vs.85).aspx for more information.

0x80014064

User credentials cannot be used for local connections

This error occurs when wmic command is executed locally on the target machine and credentials specified

[0x800705af]

The paging file is too small for this operation to complete. For Windows Server 2008 R2 you may find the following link useful:

https://blogs.technet.microsoft.com/kevinholman/2010/06/09/wmi-leaks-memory-on-server-2008-r2-monitored-agents/

WMI query failed: Out of memory.

[0x80041006]

  1. At the command prompt run "wbemtest"
  2. Connect to the "root" namespace (not "root\default", just "root")
  3. Click Open Instance. Specify "__ProviderHostQuotaConfiguration=@"
  4. Select Local Only for easier readability. You will see the threshold values.
  5. Increase the MemoryPerHost value to something greater. For example, double it (256 MB)
  6. Save Property
  7. Save Object
  8. Click Exit.
  9. Restart WMI services.

Invalid verb

The wmic command has attempted to access a WMI class that does not exist.

Solution: Check the spelling of parameters on the wmic command.

Invalid Global Switch

The specified host, user or domain name contains special characters like '-' or '/'.

Solution: Modify the command by adding quotation marks.

wmic /node: 'MonHostFQDN' /user: 'DOMAIN\USER' path Win32_PerfRawData_PerfDisk_LogicalDisk get FreeMegabytes

How to limit the number of ports used by WMI

Deployment over the Windows network > How to limit the number of ports used by WMI

Windows WMI uses the RPC and DCOM subsystems in Windows. The ports that are used in WMI are auto-negotiated between hosts. In order to effectively use WMI between fire walled hosts, you can limit the number of ports used by the DCOM subsystem and only open those ports.

The following outlines instructions to limit the number of ports that DCOM will use.

Follow these instructions on each monitored host.

  1. Open regedt32.exe
  2. Navigate to HKEY_LOCAL_MACHINE\Software\Microsoft\Rpc
  3. If there is no subkey titled "Internet", create one.
  4. Inside the Internet key, create a REG_MULTI_SZ value named "Ports". Each line of the Ports value should specify a range of ports available to DCOM. For this example, add a single line that reads "3000-3100".
  5. Add a new REG_SZ value named "PortsInternetAvailable", set it to "Y"
  6. Add a new REG_SZ value named "UseInternetPorts", set it to "Y"
  7. Open up TCP port 135 to internal traffic. (It may also be necessary to open up UDP 135)
  8. Open up the DCOM port range (e.g. 3000-3100) to internal traffic.

See the following link for more information:

http://support.microsoft.com/default.aspx?scid=kb;en-us;154596

To resolve any problems using WMI, see Troubleshooting WMI

Monitored SQL Servers

Monitoring SQL Servers

The Spotlight Diagnostic Server collects data from the SQL Server instances it connects to and uses WMI queries to retrieve performance counter information from the Windows Server hosts (Monitoring Windows Servers and hosts of database connections). This data is then displayed in real time on a Spotlight Client or other Spotlight viewer (View data and configure Spotlight).

SQL Server | Connection Details

Monitored SQL Servers > SQL Server | Connection Details

Open this screen from the Spotlight Client

  1. Click Configure | Connections.

  2. Select the SQL Server connection type.
  3. Click Add a new connection or to edit, right click the connection and select Properties.

    TIP: Spotlight can add multiple SQL Server instances via discovery or by importing details from a file.

Specify the connection details for this SQL Server instance

Field Description

Address

The connect string used to link to the SQL Server (that is, the Server Name, Server Instance Name, or IP address).

For a Microsoft Cluster Server (MSCS) enter the virtual name of the cluster.

Authentication

The authentication for Spotlight to use to connect to the SQL Server instance.

Select Windows Authentication (using Diagnostic Server credentials) to use the Windows user configured to run the Spotlight Diagnostic Server. Ensure this account is trusted by the SQL Server.

Alternatively, fill in the Database User and Password fields. Ensure the database user has sufficient account permissions to retrieve performance data from the SQL Server instance and host by WMI. Typically the account will be a member of the sysadmin server role. It could be a SQL Server login (such as 'sa'). If this is not feasible in your environment, see How to grant SQL Server account permissions to a trusted user.

The connection will fail if the account permissions are insufficient to allow Spotlight to collect the data it needs.

Read Only Intent

Select this option if the SQL Server instance hosts a secondary replica of an Availability Group and for this secondary replica ApplicationIntent=ReadOnly. Failure to select this option when required will result in some data not being collected for the secondary replica; this will be most noticeable on the SQL Server | Databases drilldown.

Use Extended Events

Selected

The Spotlight Diagnostic Server will use Extended Events to collect data from the SQL Server instance. The data is used by:

  • SQL Server | Workload Analysis Drilldown
  • SQL Server | Wait Events Drilldown
  • Deadlock checks: SQL Server | SQL Activity Drilldown, Locks - Deadlocks Alarm.

If you select to use Extended Events (following a period of time when the use of Extended Events was deselected) the SQL Server | Workload Analysis Drilldown and SQL Server | Wait Events Drilldown may take a few minutes to repopulate with data.

Not Selected

The Spotlight Diagnostic Server will use SQL Server Trace to collect data for deadlock checks. The Spotlight Diagnostic Server will NOT collect data for the SQL Server | Workload Analysis Drilldown and SQL Server | Wait Events Drilldown.

If you set this value after the connection to the SQL Server is established then the change will not show up immediately on the user interface as the Workload Analysis Drilldown and Wait Events Drilldown will continue to show historical data till no data is available.

Note that the setting for Use Extended Events can be set collectively for all SQL Server on the Spotlight Diagnostic Server from Configure the use of Extended Events.

Connection

Select the Windows server hosting the SQL Server.

Do not monitor

Select Do not monitor if you do not want to monitor the Windows server.

Cluster (monitor active node)

Select Cluster (monitor active node) for a Microsoft Cluster Server (MSCS).

Spotlight uses the current host node name to select the operating system connection. Therefore each Windows node in the cluster must be monitored by Spotlight. Verify each Windows node is in the list of Windows server connections.

Click Create to add a Windows server to the list. This opens Windows Server | Connection Details.

TIP: When done, click Test to verify Spotlight can successfully establish a connection with the details provided. If there are connection problems see Monitoring SQL Servers.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating