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

Configure uploading to Spotlight Cloud

Spotlight data collection and storage > Spotlight Cloud > Configure uploading to Spotlight Cloud

Enable functionality to monitor Spotlight connections from the Spotlight web site or Spotlight Mobile. Upload performance data for health check analysis.

Open this screen from the Spotlight Client

Click Configure | Spotlight Cloud.

Select the Diagnostic Server

For federated Spotlight Diagnostic Server you will be prompted to select the Spotlight Diagnostic Server to configure. Each Spotlight Diagnostic Server is independently configured for Spotlight Cloud.

 

Upload data to Spotlight Cloud

Select Enable functionality to monitor Spotlight connections from the Spotlight web site or Spotlight Mobile. Upload performance data for health check analysis.
Clear Spotlight Cloud services are disabled for this Spotlight Diagnostic Server. Spotlight connections cannot be monitored from the Spotlight web site or Spotlight Mobile. Performance data for health check analysis is not uploaded to the Spotlight Cloud.

 

Spotlight Cloud services account details

Email or Username

Password

Enter Spotlight Cloud (Spotlight Essentials) account details. This section must be filled in when uploading of data to the Spotlight Cloud is enabled. Once filled in, the email (or username) signed in under is provided.

Click Register Now to create a Spotlight Cloud account if you do not already have one. Registration is free.

 

Additional references

Web Site Reference Description
Spotlight Cloud Services The Spotlight web site.
Spotlight Mobile Download, setup and install Spotlight Mobile.
Register now Register for a Spotlight Cloud Services account
Spotlight terms of service Spotlight Services Agreement

 

Important: If you monitored your Spotlight on SQL Server connections on a mobile device in the past (Spotlight 11.0 or earlier) then you must uninstall the Spotlight Web Publisher via Windows | Control Panel | Programs and Features. The Spotlight Web Publisher was required in the past to monitor SQL Server connections on a mobile device. It is now important that you uninstall it. By default the Spotlight Web Publisher was installed on the same computer as the Spotlight Diagnostic Server.

Maintenance plan for Spotlight Statistics Repository and Playback Database

Spotlight data collection and storage > Maintenance plan for Spotlight Statistics Repository and Playback Database

It is recommended that a maintenance plan for both the Spotlight Statistics Repository and the Playback Database is implemented.

Both the Spotlight Statistics Repository and Playback Database support a large number of monitored servers and automatically maintain the age of data available. Due to the nature of the automatic purging of old data, the data may become fragmented and this may decrease the efficiency of data retrieval operations over time.

This topic provides guidance on how to configure and automate the maintenance of the Spotlight Statistics Repository and the Playback Database.

Database configuration

The Spotlight Statistics Repository and the Playback Database do not require the database to be run under the Full Recovery model. However, since Spotlight uses the model system database to create the repository, your Spotlight repository may inherit the Full Recovery setting. Unless you are required to use the Full Recovery model and are prepared to perform regularly scheduled log file backups, we recommend you change the repository recovery model to Simple. Doing so will help maintain a considerably smaller transaction log file.

To change the repository database to Simple recovery, run the following command:

ALTER DATABASE [SpotlightStatisticsRepository] SET RECOVERY SIMPLE;

Fragmentation and index performance

The Spotlight Statistics Repository implements a star schema because of the data warehousing-style simplicity it offers for data storage and retrieval. The star schema implements a primary fact table that references a number of dimension tables. The fact table holds collections and alarm data, and the dimension tables hold definition information on everything from instance information to collection names. Spotlight maintains the age of the data in the repository, but the continuous INSERT and DELETE operations performed for data insertion and purging can lead to fragmentation. Fragmentation will eventually lead to increased CPU and I/O resource consumption.

Although the Playback Database uses a simpler schema, its data will also become fragmented over time as new data is inserted and old data purged.

To best address performance concerns, fragmentation should be minimized and index statistics should be kept up-to-date. Spotlight's scheduled maintenance feature does this automatically. By default, the Spotlight Diagnostic Server runs maintenance procedures daily at 3am for the Playback Database and Spotlight Statistics Repository. The maintenance schedule can be changed in Spotlight Options. See the online help for more information.

Database backup

The Spotlight Statistics Repository and Playback Database should be included in the list of important databases which have a disaster recovery plan associated with them. The implementation of this task is dependent on the policies and infrastructure of your organization.

Backup Spotlight data

Spotlight data collection and storage > Backup Spotlight data

Backup all Spotlight configuration and saved collection data regularly and before upgrade or uninstall.

Spotlight Diagnostic Server

Back up the Diagnostic Server\Agent folder.

C:\Program Files\Quest Software\Diagnostic Server\Agent

TIP: To backup just the Spotlight configuration (configuration of connections, alarms and scheduling), backup the Diagnostic Server\Agent\Conf folder.

Playback Database

Backup the Playback Database. The Playback Database is deployed on SQL Server. The backup procedure is the same as for any other SQL Server database.

Spotlight Statistics Repository

If a Spotlight Statistics Repository is deployed in your environment then back it up. The Spotlight Statistics Repository is deployed on SQL Server. The backup procedure is the same as for any other SQL Server database.

Log of user actions

Spotlight maintains an audit log of user actions. This log is a historical record of who changed what configuration when.

The log is located in the Spotlight Diagnostic Server installation folder:

...\Agent\log\UserActionLog.csv

The data for the log is retrieved from the Playback Database.

For the following user actions the log records:

  • The Spotlight user making the change.
  • The time the change was made.
  • The configuration that was changed (if applicable).
  • The configuration value before and after the change (if applicable).

Table 7: Log of user actions - Changes to Configure | Spotlight

Configure Ribbon Tab

Connections

Manage the connections monitored by Spotlight. The log records all applied changes.

Alarms

Set the thresholds and severities that determine when an alarm is raised. The log records all changes as applied to a monitored connection or template.

Alarm Actions

Set actions for Spotlight to take when an alarm is raised. The log records all saved changes.

Scheduling

Spotlight collects data according to set schedules. The log records all changes to these schedules as applied to a monitored connection or template.

Planned Outage

Changes to Planned Outage are recorded in the log of user actions.

SQL Analysis

The SQL Analysis dialog is used to determine what data is collected and displayed in the SQL Analysis - Workload View and the SQL Server | SQL Activity Drilldown | SQL Analysis page. The log records all changes to the SQL Analysis dialog as applied to a monitored connection or template.

SQL Server Response Time

The log records changes to the SQL statement used to measure SQL Server response time as applied to a monitored connection or template.

Custom Counters

The log records changes to the configuration of custom counters as applied to a monitored connection or template.

Error Log Entries

The log records changes to the error log entries Spotlight is configured to scan the SQL Server error log for, as applied to a monitored connection or template.

Monitored Files

The log records changes to the list of files tracked for size by Spotlight as applied to a monitored connection or template.

Spotlight

Changes to the configuration of the Spotlight Client are not recorded in the log of user actions. The Spotlight Client is configured by changes to:

  • Configure | Spotlight | Change Display
  • Configure | Spotlight | Troubleshoot Spotlight

Diagnostic Server

The log does not record changes to the Spotlight Client connected to the Spotlight Diagnostic Server. The log does not record changes made to the Select a Diagnostic Server dialog.

The log does record changes to the configuration of the Diagnostic Server:

  • Configure the database maintenance schedule
  • Configure PagerDuty
  • Configure the auto-update facility
  • Configure the Diagnostic Server's mail server
  • Configure the Playback Database
  • Configure the SNMP Trap
  • Configure the Spotlight Statistics Repository
  • Configure the use of Extended Events
  • Federate Diagnostic Servers

Spotlight Cloud

The log records changes to the configuration - upload data to the Spotlight Cloud.

User Experience

Changes to User Experience are not recorded in the log of user actions.

Configuration Templates

Changes to Configuration Templates (Save, Delete, Rename) are recorded in the log of user actions.

Table 8: Log of user actions - Changes to the Spotlight License

Help | About | Product license

Help | About | Product license

Changes to the product license are recorded in the log of user actions.

Table 9: Log of user actions - When an alarm is acknowledged, snoozed or ignored

Monitor | Alarms | Action

Acknowledge

The log records when an alarm is acknowledged.

Snooze Alarm

The log records when an alarm is snoozed.

Ignore Alarm

The log records when an alarm is ignored.

Table 10: Log of user actions - on monitored SQL Server

Monitor | SQL Server Drilldowns

SQL Activity

The log records when a user kills a session from the Spotlight Client. That is command Sessions | Kill Session.

Databases

The log records when a user runs the Update Statistics command on selected indexes from the Spotlight Client. This command is run from the Indexes page of the Databases drilldown.

Support Services

The log records when a user starts a SQL agent job from the Spotlight Client. That is command SQL Agent Jobs | Start Job.

The log records when a user changes the running state (Start / Stop) of a service from the Spotlight Client. This change is actioned from the Service Status page of the Support services drilldown.

The log records when a user takes the following actions on Cluster Services from the Spotlight Client:

  • Take Offline - Makes a cluster resource or cluster group unavailable.
  • Bring Online - Starts a cluster resource or group.
  • Move Group - Moves a cluster group to another node of the cluster.

Configuration

The log records when a user changes a configuration parameter from the Spotlight Client.

Error Log

The log records when a user archives the current error log file and opens a new log file from the Spotlight Client. That is command Cycle Error log.

Table 11: Log of user actions - on monitored Windows Server

Monitor | Windows Drilldowns

Processes

The log records when a user:

  • Terminates a process from the Spotlight Client.
  • Changes the running state (start, stop, pause, resume) of a service or device from the Spotlight Client.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating