Spotlight on SQL Server 10.0.3 - Getting Started Guide

Spotlight on SQL Server and Firewalls

Running Spotlight on SQL Server in an environment with a firewall may require ports to be opened between the Spotlight Diagnostic Server and the monitored SQL Servers, or ports to be opened between the Spotlight Diagnostic Server and the Spotlight clients.

If the firewall is on the same machine as the Spotlight Diagnostic Server

When a software firewall is installed on the same machine as the Spotlight Diagnostic Server, ensure the following port is open on the firewall.

If the Spotlight Diagnostic Server is outside a DMZ; the monitored SQL Servers are inside a DMZ

To setup Spotlight on SQL Server in your environment

Step 1: SQL Server firewall connectivity

Open ports as outlined in the Microsoft KBase article that describes SQL Server firewall connectivity issues.

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

Step 2: WMI Ports

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

Permissions

SQL Server permissions required on the Spotlight Diagnostic Server

SQL Server account permissions:

Ensure each SQL Server connection from the Spotlight Diagnostic Server has access to a SQL Server account that is a member of the sysadmin server role. This can be a SQL Server login (such as 'sa'), or the Spotlight Diagnostic Server can be installed to run under a Windows account that is trusted by SQL Server.

Alternatively, run the following SQL script (as sysadmin) to grant the required permissions to user TrustedUser.

use master

grant ALTER TRACE to TrustedUser

grant VIEW SERVER STATE to TrustedUser

grant VIEW ANY DEFINITION to TrustedUser

USE [master]

GO

CREATE USER [TrustedUser] FOR LOGIN [TrustedUser]

GO

USE [msdb]

GO

CREATE USER [TrustedUser] FOR LOGIN [TrustedUser]

GO

grant VIEW DATABASE STATE to TrustedUser

use msdb

EXECUTE sp_addrolemember

@rolename = 'SQLAgentReaderRole',

@membername = 'TrustedUser'

use msdb

EXECUTE sp_addrolemember

@rolename = 'TargetServersRole',

@membername = 'TrustedUser'

 

grant select on dbo.log_shipping_monitor_history_detail to TrustedUser

grant select on dbo.log_shipping_monitor_primary to TrustedUser

grant select on dbo.log_shipping_monitor_secondary to TrustedUser

grant select on dbo.log_shipping_primary_databases to TrustedUser

grant select on dbo.log_shipping_secondary_databases to TrustedUser

grant select on dbo.log_shipping_primary_secondaries to TrustedUser

grant select on dbo.log_shipping_primaries to TrustedUser

grant select on dbo.log_shipping_secondary to TrustedUser

grant select on dbo.log_shipping_secondaries to TrustedUser

grant select on dbo.sysjobs to TrustedUser

grant select on dbo.sysalerts to TrustedUser

grant select on dbo.sysjobhistory to TrustedUser

grant execute on dbo.sp_help_jobhistory to TrustedUser

grant select on msdb.dbo.syssessions to TrustedUser

grant select on msdb.dbo.sysjobactivity to TrustedUser

use master

grant EXECUTE on xp_servicecontrol to TrustedUser

grant EXECUTE on xp_enumerrorlogs to TrustedUser

grant EXECUTE on xp_readerrorlog to TrustedUser

grant EXECUTE on xp_sqlagent_enum_jobs to TrustedUser

grant execute on xp_regread to TrustedUser

The data must be retrievable over your network.

Note: See "Spotlight on SQL Server and Firewalls" (page 1) for more information on running Spotlight on SQL Server in an environment with firewalls.

Windows permissions required on the Spotlight Diagnostic Server

To retrieve performance counter information, the Spotlight Diagnostic Server executes WMI queries against the Windows server on which SQL Server is currently running. To do this, the Spotlight Diagnostic Server needs to have access to a Windows account that can retrieve this information. This account will normally be a member of the Administrators group on the server. If you do not have access to an Administrator account, have your Windows administrator assign you the required permissions on the server.

Note: To monitor the Windows machine that hosts the Spotlight Diagnostic Server, Windows requires that the Spotlight connection use Windows authentication. When creating a Windows connection to the Spotlight Diagnostic Server machine, select the Use Diagnostic Server credentials option.

Unable to collect data

If, after making a connection, alarms are raised related to inability to collect data, check the following:

• The domain is included in the user name when specifying Windows OS (not SQL Server) connection details. For example, “domain\johnsmith”, instead of “johnsmith”.
• If the monitored host is the same as the machine running the Spotlight Diagnostic Server, select the Windows authentication option (under OS Connection Details) on the Details page, which is displayed when you create a new connection.

• If the monitored host is in a different domain to the machine running the Spotlight Diagnostic Server, specify the host name as a fully qualified address (for example, machine1.domain.company.corp). You can specify the host name on the Details page when you create a new connection.

  Tip: If the host name is not fully qualified, the connection may work but only intermittently. It is recommended that the host name be fully qualified

• If you get errors executing "LogicalDisk" or "PagingFile" queries, start a command prompt on the monitored machine and execute the command "diskperf -y" then reboot.
• Do not use Windows authentication for connections remote to the Spotlight Diagnostic Server machine unless you have configured the Spotlight Diagnostic Server process to run under a user with access to the remote machine.

If you have done all of the above and there are still connection issues, reboot the Spotlight Diagnostic Server machine (this is sometimes needed due to credentials conflicts in cached Windows connections). This must be a full machine reboot, not just a restart of the Spotlight Diagnostic Server.

Enabling Performance Data

Enable performance counters

Spotlight on SQL Server uses the SQL Server virtual table sysperfinfo (SQL Server 2000) and sys.dm_os_performance_counters (SQL Server 2005 and above) to retrieve data for many of its displays. In some rare cases, this table may not contain information. When this is the case, Spotlight on SQL Server will not be able to collect the data it requires, and will display "0" for many of its components. Most obvious will be the Memory icons on the home page, which will show 0 MB of memory used by SQL Server. Also, many of the flows on the home page will show no activity, and many drilldowns will show incomplete information.

Spotlight on SQL Server raises an informational alarm shortly after connecting if it detects that the sysperfinfo or sys.dm_os_performance_counters table contains no data.

You can confirm the existence of this problem by running the following SQL in Query Analyzer for SQL Server 2000:

select * from master..sysperfinfo

or by running the following SQL in SQL Server Management Studio for SQL Server 2005, 2008, 2012 or 2014:

select * from sys.dm_os_performance_counters

If this query returns no records, then your SQL Server performance counters are missing and Spotlight on SQL Server will not be able to operate correctly.

Sometimes, for a variety of reasons, the SQL Server Performance Monitor counters will not show up as they should. Often, but not always, this problem can be fixed by following these steps.

To enable performance counters

1. At the command prompt, type the following:

   unlodctr.exe MSSQLServer

2. Then type:

   lodctr.exe <SQL Server path>\binn\sqlctr.ini

3. Reboot the server.

More information can be found in the SQL Server Knowledge Base at http://msdn.microsoft.com.

Enable disk counters

Spotlight on SQL Server cannot display some disk information properly unless the machine being monitored has disk performance counters enabled. This affects the Disk Activity component on the home page, various I/O charts on the SQL I/O Activity tab and Windows Activities drilldowns, and disk graphs on the Databases drilldown.

Spotlight on SQL Server raises the Disk Counters alarm if it detects that disk counters are not enabled on the machine being monitored.

To enable disk data collection

1. Start a command line window on the server machine.
2. Type the following at the command prompt: diskperf -y
3. Restart the machine.