Connection Issues
Spotlight on SQL Server retrieves its data from the SQL Server via the Spotlight Diagnostic Server. The Spotlight Diagnostic Server also uses WMI queries to retrieve performance counter information from the Windows server on which SQL Server is running.
The Spotlight Diagnostic Server cannot connect to a server unless it can access all of this information. This means you need to have the appropriate permissions on the server.
Also, the data must be retrievable over your network. Spotlight on SQL Server requires more than just a specific port TCP/IP connection to SQL Server to do this. The port on the Spotlight Diagnostic Server must be open. For more information, see the Spotlight on SQL Server Getting Started Guide. This document is available in PDF from Start | Quest Software | Spotlight.
Problems connecting to a SQL Server Instance
If you have trouble connecting to a SQL Server instance, check that you can:
- Connect to the SQL Server using another tool such as Microsoft's SQL Server Management Studio, or sqlcmd.
- Connect to WMI using another tool such as Microsoft's WMI CIM Studio, or wmic.
If you have problems with any of these checks, you will need to investigate and resolve these issues before you can connect to that server using Spotlight on SQL Server.
Monitor Multiple SQL Server Instances
This version of Spotlight on SQL Server is designed to monitor a maximum of 100 SQL Server or Analysis Services instances and 100 Windows servers. Monitoring more than this recommended limit may result in poor performance or product instability.
Spotlight Diagnostic User Groups
During installation, Spotlight creates the following local user groups on the diagnostic server.
| Group | Description |
|---|---|
| Spotlight Diagnostic Users | Members of this group are granted user privileges to the Diagnostic Server. They can do the usual diagnostic tasks. For example, they can view the home page, 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 |
| Spotlight Diagnostic Read-Only |
Members of this group can view the home page, the drilldown pages, the playback data and alarm cases. They cannot make changes to Spotlight's operation. For example, they cannot change the Diagnostic Server and Monitored Servers. |
Note: The Spotlight Diagnostic groups do not grant any Windows or SQL Server rights directly. They are used to determine what rights a Spotlight user has within Spotlight. When Spotlight executes a user action such as kill a session it does so via the connection the Diagnostic Server has made to the host or instance which always uses the account defined in the connection details. However these actions are only performed if the user from the Windows session is a member of a Spotlight Diagnostic group that is allowed to perform the action . Thus a user who is a member of the Spotlight Diagnostic Read-only group is prevented from performing actions within Spotlight that can alter the state of the monitored hosts and instances and from changing the configuration.
How do I become a member of the Spotlight Diagnostic User Groups?
Your Network Administrator adds members to the Spotlight Diagnostic User groups as required. Members can be Windows users or Windows domain groups. Aliases are not supported. The Windows user on the Spotlight client machine that installed the Diagnostic Server is automatically added to all groups.
What if I am not a member of a Spotlight Diagnostic User Group?
Contact your Network Administrator. You need to be a member of at least one Spotlight Diagnostic User Group to connect your Spotlight on SQL Server client to the diagnostic server.
What if I want to increase my level of membership?
Contact your Network Administrator. The privileges available to you corresponds to the highest Spotlight Diagnostic User group you are a member of. Spotlight Diagnostic Read-Only users have the fewest privileges, but if you are also a member of the Spotlight Diagnostic Administrators group then you will have administrator privileges.
Note: 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 Diagnostic Server. For this reason, it is recommended that the Diagnostic Server be restarted if the role changes need to take immediate effect.
Using Spotlight
These tables provide information on the levels of service available to the various user groups.
Note: Some of these icons, particularly those on the Configure Ribbon Tab, have multiple access locations. For example, you can open the Project Lucy icon from the Configure Ribbon Tab or the Project Lucy Ribbon Tab. The level of service available to the user group is the same, no matter where the icon is opened from.
| Configure Ribbon Tab | Administrator | User | Read-only | |
|---|---|---|---|---|
|
|
Add/Delete connection profiles | Yes | Yes | No |
| Add/Replace/Delete tags | Yes | Yes | No | |
| Enable/Disable monitoring connections | Yes | Yes | No | |
|
|
Configure | Yes | Yes | No |
| Ignore | Yes | Yes | No | |
| Run a program when a threshold is reached | Yes | No | No | |
|
|
Yes | Yes | No | |
|
|
Yes | Yes | No | |
|
|
Yes | No | No | |
|
|
Yes | Yes | No | |
|
|
Yes | Yes | No | |
|
|
Yes | Yes | No | |
|
|
Appearance | Yes | Yes | Yes |
| Assistance | Yes | Yes | Yes | |
| Start-up | Yes | Yes | Yes | |
| Troubleshooting | Yes | Yes | Yes | |
|
|
Change the Diagnostic Server address | Yes | Yes | Yes |
| Configure the auto-update facility | Yes | Yes | No | |
| Configure the mail server used by the Diagnostic Server | Yes | Yes | No | |
| Configure the maintenance schedule for the Playback and Spotlight Statistics Repository databases | Yes | Yes | No | |
| Configure the Playback database | Yes | Yes | No | |
| Configure the Spotlight Statistics Repository | Yes | Yes | No | |
| Configure Uploading data to Project Lucy | Yes | Yes | No | |
| Specify the location of your Web Reports server | Yes | Yes | No | |
|
|
Configure Uploading data to Project Lucy | Yes | Yes | No |
Connections
Alarms
Scheduling
SQL Analysis 
SQL Server Response Time 
Custom Counters 
Error Log Entries 
Monitored Files 
Spotlight
Diagnostic Server
Project Lucy
| Monitor | Alarms | Action | Administrator | User | Read-only | |
|---|---|---|---|---|
|
|
Yes | Yes | No | |
|
|
Yes | Yes | No | |
|
|
Yes | Yes | No | |
Acknowledge
Snooze Alarm
Ignore Alarm
| Monitor | SQL Server Drilldowns | Administrator | User | Read-only | |
|---|---|---|---|---|
|
|
Buffer Cache |  Clear Cache |
Yes | No | No |
Procedure Cache |  Clear Cache |
Yes | No | No | |
|
|
Sessions |  Kill Session |
Yes | No | No |
|
|
Indexes | Update Statistics | Yes | No | No |
|
|
SQL Agent Jobs |  Start Job |
Yes | No | No |
| Service Status | Start / Stop Service | Yes | No | No | |
|
Cluster Services | Take Offline Cluster Services | Bring Online Cluster Services | Move Group |
Yes | No | No | |
|
|
Change parameter | Yes | No | No |
|
|
|
Yes | No | No |
Memory
SQL Activity
Databases
Support Services
Configuration
Error Log
Cycle Error log
| Monitor | Windows Drilldowns | Administrator | User | Read-only | |
|---|---|---|---|---|
|
|
Processes | Terminate | Yes | No | No |
|
Services | Start Services | Stop Services | Pause Services | Resume |
Yes | No | No | |
Processes