Quest-Spotlight for SQL Server Expert

Owned by Phatchakorn Sirabenjaphakorn
Last updated: May 22, 2020
8 min read

1. System Requirements

1.1 Client Requirements

  • Operating Systems
    - Windows 7 (32-bit and 64-bit)
    - Windows 8, 8.1 (32-bit and 64-bit)
    - Windows 10 (32-bit and 64-bit)
    - Windows Server 2003 (32-bit and 64-bit)
    - Windows Server 2008 , 2008R2 (32-bit and 64-bit)
    - Windows Server 2012, 2012R2 (64-bit)
    - Windows Server 2016 (64-bit)
    - Windows Server 2019 (64-bit) and above

  • Network setting
    “File and Print Sharing” to be enabled and any firewall configured to open TCP Port 135.  

1.2 Database User

  • SQL Server connection requirements :
    Spotlight can monitor SQL Server instances of the following versions, editions Enterprise, Standard and Express:
    - SQL Server 2017 (64-bit)
    - SQL Server 2016 (64-bit)
    - SQL Server 2014 (32-bit and 64-bit)
    - SQL Server 2012 (32-bit and 64-bit)
    - SQL Server 2008 R2 (32-bit and 64-bit)
    - SQL Server 2008 (32-bit and 64-bit)
    - SQL Server 2005 (32-bit and 64-bit)

  • Partial support is provided for monitoring SQL Server 2017 connections on Linux hosts. For more information on monitoring SQL Server connections on Linux hosts, particularly SQL Server 2017 connections.

  • Spotlight Enterprise also supports MSDE (2000), SQL Express (2005) and SQL Server 2008 Express Edition. Note that use of these versions to host playback or Spotlight Statistics Repository databases is not supported.

  • Spotlight cannot monitor earlier versions of SQL Server (SQL Server 2000 and earlier). Spotlight does not support Database Compatibility Level: SQL Server 2000.

  • Spotlight supports all SQL Server sort orders, including case-sensitive and binary sort orders. Spotlight cannot monitor SQL Server instances where the instance name contains non-US ASCII characters.

  • On each monitored instance Spotlight requires the network setting “File and Print Sharing” to be enabled.

  • If the SQL Server instance is hosted on a Windows server and UDP port 1434 is closed then the port number must be included in the address used to connect Spotlight to the SQL Server instance.

  • Grant account permissions  

    SQL Server 2012 and above: Ensure the TrustedUser default database is set to master

    For SQL Server 2012 and above databases, follow these instructions to ensure the SQL Server account default database is set to master. 

    From SSMS (SQL Server Management Studio): Security | Logins | ‘Monitoring account’ | Properties | General | Default database select master as the default database. 

    Run this script to grant permissions to TrustedUser

    Run the following SQL script (as sysadmin) to grant the required permissions to user TrustedUser. Note the comment lines at the end of the script and un-comment as appropriate for your environment.  

    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

declare @dbnumber int  

declare @dbname sysname 

declare @use nvarchar(4000) 

declare @Quest_dblist table (row int identity,name sysname)

  insert into @Quest_dblist (name)  

select name from master.dbo.sysdatabases;

  set @dbnumber = @@rowcount

  while @dbnumber > 0

  begin

  select @dbname =name from @Quest_dblist where row = @dbnumber

  set @use = N'USE ' + quotename(@dbname)

  + N'CREATE USER [TrustedUser] FOR LOGIN [TrustedUser]';

exec (@use) 

set @dbnumber = @dbnumber - 1 

end

----un-comment the following line for SQL Server 2008 and above.

  --Grant CONTROL SERVER to TrustedUser

  ----un-comment the following line for SQL Server 2012 and above.

  -Grant ALTER ANY EVENT SESSION to TrustedUser

  •  Known issues with this script:
    The following monitoring functions require sysadmin privileges; hence have known issues when using the script:
    - Mirroring
    - Number of virtual log files (VLFs)
    - Jobs
    - The SQL Server | Databases drilldown | Open transactions count
    - The SQL Server | Databases drilldown | Indexes page | Index Statistics Summary page
    - The SQL Server | Support Services drilldown | Service Status page - shows no data

1.3 Permissions required during installation

  • The Windows user installing Spotlight
    When installing the Spotlight Diagnostic Server, Windows administrator privileges are required to register the Spotlight Diagnostic Server as a Windows service and to create the Windows user groups that are used to authenticate Spotlight clients. When installing the Spotlight Diagnostic Server on a remote machine, the Windows account on the Spotlight client must have Windows administrator privileges on the Spotlight Diagnostic Server host. The local computer must be able to authenticate this user.

  • Elevated privileges
    In many cases, the Spotlight installer will run at elevated privileges in order to perform the required actions. These privileges can specifically be allowed or disallowed by system administrators by means of standard Microsoft Installer (MSI) policy settings. If the Spotlight Diagnostic Server user does not have privileges to start services, that privilege is granted by Spotlight during the installation process, if possible.

  • Multiple users of a system
    If multiple users of a system install Spotlight for use, the additional users must be local administrators.

2. Installation

Spotlight for SQL Server Expert

  1. Run Spotlight installer .exe file located on either your workstation or the machine.
    Otherwise, select Typical.

    Scenarios that may be involved with a typical install include:

    • Install the Spotlight Diagnostic Server on a computer networked to the current computer.

    • Install the Spotlight Client on the current computer to connect to a preexisting Spotlight Diagnostic Server.

2. Select Component to install Spotlight Client or Diagnostic Server. Click Next
The Spotlight Client : The Spotlight Client will be installed as follows, unless you indicate otherwise
C:\Program Files (x86)\Quest Software\Spotlight
Do not install the Spotlight Client in the same location as an existing.
If you install a different Spotlight after you have installed Spotlight Enterprise, do not install the new
Spotlight in the Spotlight Enterprise directory.
The Spotlight Diagnostic Server :
Default installation folder for the Diagnostic Server:
C:\Program Files\Quest Software\Diagnostic Server

Consider installing the Spotlight Diagnostic Server on a computer that is always switched on. Some Spotlight Diagnostic Server functionality requires Internet access.

3. To Accept the license agreement, Click Next

4. Accept Help improve Spotlight , Click Next

5. Select specify the computer to install, Click Next

6. Select Destination Folder, Click Next

7. Select and fill data for Diagnostic Server Account , Click Next
Diagnostic Server Account : The Spotlight Diagnostic Server will run under this Windows account.
Enter a domain user account or select the local system account. These credentials can later be used
to authenticate Spotlight connections to monitor SQL Server instances and Windows Server.

8. The Diagnostic Server will run as a Windows Service under whatever account you give us here.
Add User and give user permission level , Click, Next
Diagnostic Server Users : Spotlight uses the Spotlight diagnostic user groups to authenticate the
Spotlight Client to the Spotlight Diagnostic Server, to authenticate a user’s right to configure
Spotlight and execute actions on monitored systems. The Windows user installing Spotlight (on the
Spotlight Client) is automatically added to all Spotlight diagnostic user groups.
Add more users to the Spotlight diagnostic user groups if required. Members of these groups can be
Windows users or Windows domain groups. Aliases are not supported.

9. Enable Auto update Diagnostic Server, Click Next

10. Configure Playblack Database, enter authentication detail, Click Next
The Spotlight installer installs the Playback Database following installation of the Spotlight
Diagnostic Server.
Every Spotlight Diagnostic Server connects to its own Playback Database. The Spotlight installer will
ask you to provide the following information when it installs the Playback Database.

11. Spotlight needs a database (called SpotlightPlaybackDatabase by default) to store historical data, so
choose a non-production SQL instance where can create this. Click Create

12. Running Create Playblack database, Click OK

13. Select destination folder to install Spotlight client , Click Next

14. Finished choosing the options for the installation of the Diagnostic Server,
Click Back : to change the installation setting
Click Start : to Begin the installation

15. Installation Completed, Click Finish

3. Licensing

3.1 Activate Licnse Key- Spotlight for SQL Server

From a Spotlight Client connected to the Spotlight deployment:

  1. Click Help | About

  2. Ensure Product Information is to the front.

  3. Click license information.

  4. Click Change this license.

  5. Enter the Enter the new License Key and Site Message.

  • Authorization string has expired
    If you see this message then the trial authorization for Spotlight has expired. If the trial date has not passed, check that the date on your system is set correctly.

  • Invalid authorization string or site message
    If you see this message then the authorization string or the site message has been entered incorrectly.

Support Chanel : Support Portal