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

Deployment over the Windows network

Deployment over the Windows network

A Spotlight on SQL Server deployment consists of many components that may be spread over a wide network.

Section Description

Network ports

Spotlight's ability to function, to collect and display data, may depend on account permissions granted over the network and specific open network ports.

Spotlight diagnostic user groups

Spotlight diagnostic user groups are Windows groups created on install of Spotlight. Spotlight uses these groups to authenticate Spotlight Client access to the Spotlight Diagnostic Server. Membership of these groups affects the Spotlight Client's ability to configure Spotlight and execute actions on monitored Windows Server and SQL Server instances.

Troubleshooting WMI

The Spotlight Diagnostic Server uses WMI queries to retrieve performance counter information from Windows server hosts. Verify WMI is working and returns data properly.

How to limit the number of ports used by WMI

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.

Network ports

A Spotlight on SQL Server deployment consists of many components that may be spread over a wide network. Spotlight's ability to function, to collect and display data, may depend on account permissions granted over the network and specific open network ports.

Table 18: Network ports on the Spotlight Diagnostic Server

Network port Description

TCP 3843

This port is used by the Spotlight client to communicate with the Spotlight Diagnostic Server. This port must be open for incoming connections on the Spotlight Diagnostic Server host.

TCP 3166

This port is used by the Spotlight Diagnostic Server to communicate with the Spotlight OOP Collector process on the same host. No external connections are required on this port.

TCP 443

This port is used by the Spotlight Diagnostic Server to communicate with Spotlight Cloud. This port must be open for outgoing connections on the Spotlight Diagnostic Server host when Configure uploading to Spotlight Cloud is enabled.

TCP 40403

This port is used by the Spotlight client to communicate with the Spotlight Diagnostic Server. This port must be open for incoming connections on the Spotlight Diagnostic Server host.

 

TIP: The Spotlight Diagnostic Server uses WMI queries to retrieve performance counter information from monitored Windows Server and hosts of database connections. Verify TCP port 135 is open on monitored Windows server and hosts of database connections.

SQL Server uses UDP 1434 to locate the SQL Server instance port number. If UDP 1434 is closed then the SQL Server instance port number must be included in the connection string used to connect Spotlight to the SQL Server instance.

Checks to verify ports are open

  • Verify a firewall is not blocking port traffic.
  • Verify no other service is using the port.
  • Verify the port is not configured as an ephemeral port. This issue may arise if you have configured your Windows ports beyond Windows Factory settings or your Windows host is Windows 2008 with Exchange Server 2007. For more information on ephemeral (dynamic) ports, see https://support.microsoft.com/en-us/kb/929851

Spotlight diagnostic user groups

Deployment over the Windows network > Spotlight diagnostic user groups

The Spotlight diagnostic user groups are Windows groups created on install of Spotlight. Spotlight uses membership of these groups to authenticate Spotlight Client access to the Spotlight Diagnostic Server. There are three groups. The level of membership affects the user's right to configure Spotlight and execute actions on monitored Windows Server and SQL Server instances.

Group Description

Spotlight Diagnostic Users

Members of this group are granted user privileges to Spotlight. They can do the usual diagnostic tasks. For example, they can view the home pages, the drilldown pages, browse the playback data and change alarm thresholds.

Spotlight Diagnostic Administrators

Members of this group are granted administrator privileges in addition to user privileges. They can kill database sessions and change sensitive configuration items.

Note: Administrative changes are logged. The logged entry includes the date, time, connection name, user and client IP address, a brief description of the action, and whether it succeeded or not. The log file is: ..\Agent\log\admin-audit.log in the Spotlight Diagnostic Server installation folder.

Spotlight Diagnostic Read-Only

Members of this group can view the home pages, the drilldown pages, the playback data and alarm cases. They cannot make changes to Spotlight's operation. For example, they cannot alter the state of Spotlight on SQL Server and Monitored Servers.

Add members, increase / decrease your level of membership

Your Network Administrator can add members, increase and decrease your membership as required.

Members can be Windows users or Windows domain groups. Aliases are not supported.

The privileges available to a user correspond to the highest Spotlight diagnostic user group that user is a member of. Spotlight diagnostic read-only users have the fewest privileges, but if a user is also a member of the Spotlight diagnostic administrators group then that user will have administrator privileges.

Any change to a user’s role by modifying these Windows groups will not take effect until that user restarts their Spotlight Client and it reconnects to the Spotlight Diagnostic Server. For this reason, it is recommended that the Spotlight Diagnostic Server be restarted if the role changes need to take immediate effect.

Using Spotlight

Your membership of the Spotlight diagnostic user groups impacts your ability to use Spotlight:

Component Description

Spotlight Client

To connect the Spotlight Client to the Spotlight Diagnostic Server, the Windows user on the Spotlight Client must be a member of at least one Spotlight diagnostic user group.

To use the Spotlight Client to configure Spotlight or execute a user action such as kill a session, the Windows user on the Spotlight Client must be a member of a Spotlight diagnostic user group that is allowed to perform that action. For details see Permissions for the Spotlight Client.

SCOM

The Spotlight Management Pack for SCOM is appropriate for organizations that use SCOM (System Center Operations Manager) as their centralized monitoring system and wish to use Spotlight as their tool of choice for SQL Server monitoring.

The Windows user(s) running the SCOM Console and SCOM Management Server must be member(s) of the Spotlight Diagnostic Administrators Group for each Spotlight Diagnostic Server. This ensures that a secure connection to the Spotlight Diagnostic Server can be made through Port 40403 and that Spotlight information can be retrieved. For details, see the Spotlight Management Pack for SCOM User Guide.

Troubleshooting WMI

Spotlight uses WMI queries to retrieve performance counter information from Windows Server (and SQL Server host). Spotlight needs access to this information before it can connect to Windows Servers (and SQL Server hosts).

 

WMI Test 1

This test checks that requests are reaching WMI.

Run this test from the Windows server being monitored.

  1. Click Control Panel | Administrative Tools | Event Viewer to open the Event Viewer.
  2. Click View | Show Analytic and Debug Logs to select this menu option.
  3. Click Applications and Service Logs
  4. Click to expand Microsoft | Windows | WMI-Activity
  5. Right click Trace | Enable Log.

    Tip: To save log entries, right click Trace | Save All Events As.

  6. If nothing is displayed then the request never reached WMI. The issue is a security or networking issue.

    If events with error messages are displayed then those events can be investigated. If you encounter WMI errors: For more information, see WMI errors.

 

WMI Test 2

This test checks that Microsoft tools can connect to WMI.

Run this test from the Spotlight Diagnostic Server.

  1. Login to the Spotlight Diagnostic Server under the account used to run the Spotlight Diagnostic Server.
  2. Click Control Panel | Administrative Tools | Computer Management.
  3. Right click Computer Management (Local) | Connect to another computer.
  4. Specify the \\HOSTNAME where HOSTNAME is the name of machine you want to monitor with Spotlight.
  5. Click Services and Applications.
  6. Right click WMI Control | Properties to open the WMI Control Properties dialog.
  7. Ensure the General tab is open.

If successful, try to monitor HOSTNAME with Spotlight again.

 

WMI Test 3

This test checks that WMI is working and returns data properly.

If you encounter WMI errors: For more information, see WMI errors.

 

  1. Run this command on the machine you want to monitor. Run this command locally from the command prompt.

    wmic path Win32_PerfRawData_PerfDisk_LogicalDisk get FreeMegabytes

     

  2. Run either of the following commands on the Spotlight Diagnostic Server.

    wmic /node: HOSTNAME /user: DOMAIN\USER path Win32_OperatingSystem get BuildNumber, Caption, CSDVersion, Version

    or

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

    HOSTNAME Identify the host computer you want to monitor with Spotlight. Use the fully qualified domain name, machine name or IP-address.
    DOMAIN\USER Valid Windows login credentials.

 

Additional testing

You may want to consider a WMI Diagnostic Utility provided by Microsoft. It is a utility to help system administrators diagnose and repair problems with the WMI service. See: http://www.microsoft.com/en-us/download/details.aspx?id=7684.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating