Spotlight on SQL Server Enterprise Deployment Guide

(1)

Spotlight™ on SQL Server® Enterprise

11.2

(2)

This guide contains proprietary information protected by copyright. The software described in this guide is furnished under a software license or nondisclosure agreement. This software may be used or copied only in accordance with the terms of the applicable agreement. No part of this guide may be reproduced or

transmitted in any form or by any means, electronic or mechanical, including photocopying and recording for any purpose other than the purchaser’s personal use without the written permission of Dell Inc.

The information in this document is provided in connection with Dell products. No license, express or implied, by estoppel or otherwise, to any intellectual property right is granted by this document or in connection with the sale of Dell products. EXCEPT AS SET FORTH IN THE TERMS AND CONDITIONS AS SPECIFIED IN THE LICENSE AGREEMENT FOR THIS PRODUCT, DELL ASSUMES NO LIABILITY WHATSOEVER AND DISCLAIMS ANY EXPRESS, IMPLIED OR STATUTORY WARRANTY RELATING TO ITS PRODUCTS INCLUDING, BUT NOT LIMITED TO, THE IMPLIED

WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT. IN NO EVENT SHALL DELL BE LIABLE FOR ANY DIRECT, INDIRECT, CONSEQUENTIAL, PUNITIVE, SPECIAL OR INCIDENTAL DAMAGES (INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF PROFITS, BUSINESS INTERRUPTION OR LOSS OF INFORMATION) ARISING OUT OF THE USE OR INABILITY TO USE THIS DOCUMENT, EVEN IF DELL HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. Dell makes no representations or warranties with respect to the accuracy or completeness of the contents of this document and reserves the right to make changes to specifications and product descriptions at any time without notice. Dell does not make any commitment to update the information contained in this document.

If you have any questions regarding your potential use of this material, contact: Dell Inc.

Attn: LEGAL Dept 5 Polaris Way Aliso Viejo, CA 92656

Refer to our web site (software.dell.com) for regional and international office information.

Patents

Protected by U.S. Patent #6,901,582. Additional patents pending.

Trademarks

Dell, the Dell logo, Toad, Toad World and Spotlight are trademarks of Dell Inc. and/or its affiliates. Microsoft, Windows, Windows Vista, Windows Server, Internet Explorer, Hyper-V and SQL Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. VMware and ESX Server are either registered trademarks or trademarks of EMC Corporation in the United States and/or other countries. Citrix® and Xen® are trademarks of Citrix Systems, Inc. and/or one or more of its subsidiaries, and may be registered in the United States Patent and Trademark Office and in other countries. Pentium is a trademark of Intel Corporation in the U.S. and/or other countries. Other trademarks and trade names may be used in this document to refer to either the entities claiming the marks and names or their products. Dell disclaims any proprietary interest in the marks and names of others.

Legend

CAUTION:A CAUTION icon indicates potential damage to hardware or loss of data if instructions are not followed.

WARNING:A WARNING icon indicates a potential for property damage, personal injury, or death.

IMPORTANT NOTE,NOTE,TIP,MOBILE, orVIDEO:An information icon indicates supporting information.

Spotlight on SQL Server Enterprise Deployment Guide Updated - March 2015

(3)

Windows account permissions 33 How to configure WMI with minimum required user permissions 34 1. Setup permission to read data 34 2. Grant permissions to get information about services 35 3. Provide access to the Registry keys used by Spotlight 36 4. Run Windows Component Services 36 Configure COM security 36 Configure DCOM security 37 5. Configure WMI permissions 37

Windows domains 38

Enable disk counters 38

Network ports and Windows connections / Windows hosts 39 How to limit the number of ports used by WMI 39

Troubleshooting WMI 39

WMI Test 1 40

WMI Test 2 40

WMI Test 3 40

WMI errors 41

Monitoring the Spotlight Diagnostic Server 45

Security in the deployment 46

Demilitarized zones (DMZ) 46

(5)

Permissions required during installation 48

Install 48

Upgrade 50

Monitoring your Spotlight on SQL Server connections on a mobile device 51 Preservation of configuration information 51

Uninstall 51

Configure 53

Configure | Connections 54 Configure the Playback Database 54 Configure the Spotlight Statistics Repository 55 Configure uploading to Spotlight Essentials 57 Upload health check data to Spotlight Essentials 57 Upload mobile monitoring data to Spotlight Essentials 57 Spotlight Essentials account details 57 Spotlight diagnostic user groups 58 Spotlight user privileges within Spotlight 58 Membership to the Spotlight Diagnostic User Groups 61

Maintenance 62

Back up Spotlight configuration data 62 Start and stop the Spotlight Diagnostic Server 62 Maintenance plan for Spotlight Statistics Repository and Playback Database 62 Database configuration 63 Fragmentation and index performance 63

Database backup 63

About Dell 64

Contacting Dell 64

Technical support resources 64

(6)

1

Welcome to Spotlight™ on SQL Server®

Spotlight™ on SQL Server® monitors the activity of SQL Server instances. Spotlight displays a visual

representation of the status of the databases in your enterprise, enabling you to drill down to graphical flows that illustrate the rate at which data is moving between server components. Icons display the value of key statistics and measurements. A range of visual graphs and tabular grids provide you with detailed information about your SQL Server instance.

The components and dataflows change color to show you the source of any problem. When Spotlight on SQL Server detects a condition that it considers is a potential problem, it not only informs you about it, but advises you what you could look at to diagnose the problem further and suggests corrective actions.

Spotlight on SQL Server seamlessly combines data from several disparate sources into a single user interface. It collects and combines data from Windows performance counters, SQL Server system tables, SQL Server commands and the Windows registry, and presents them in logically related screens.

Editions of Spotlight on SQL Server

This is a guide for Spotlight on SQL Server Enterprise.

Features Spotlight on SQL Server Enterprise Spotlight on SQL

Server Standard

Architecture Deployable with separate Spotlight

Diagnostic Server and multiple clients.

Single installation

Heat Map Yes Yes

Playback Database Yes Yes

Spotlight Statistics Repository and Spotlight Reporting and Trending

Yes No

Email notifications on alarms Yes No

Upload data to Spotlight™ Essentials Yes Yes

Monitored Connections

Microsoft® SQL Server Yes Yes

Microsoft SQL Server Replication Yes No

(7)

Features Spotlight on SQL Server Enterprise Spotlight on SQL Server Standard

Hyper-V® Server Yes Yes

Microsoft Windows® Yes Yes

Microsoft SQL Server Analysis Services Yes Yes

Microsoft SQL Azure Database Yes Yes

VMware® Server Yes Yes

NOTE:The Spotlight on SQL Server Diagnostics Drilldown is available only in Spotlight on SQL Server Enterprise.

(8)

2

Systems in the Deployment

The components of a deployment are as follows. Component Description

Spotlight Client

View Spotlight data in real time, in playback mode and generate reports. Comprehensively configure Spotlight. A Spotlight Client is installed on a Windows server. Many Spotlight Clients can connect to one Spotlight Diagnostic Server.

Spotlight Essentials

View Spotlight data in real time in your favorite web browser. View performance analytics. One Spotlight Essentials account services all systems connected to the Spotlight Diagnostic Server.

Spotlight Mobile

View Spotlight data in real time on your favorite mobile device. Configure Spotlight Mobile with Spotlight Essentials credentials. Enable uploading of data to Spotlight Mobile in the Spotlight Client.

Spotlight Diagnostic Server

The Spotlight Diagnostic Server is at the core of the Spotlight on SQL Server architecture. All Spotlight data passes through the Spotlight Diagnostic Server. The Spotlight Diagnostic Server is a Windows service. It runs under Windows credentials.

Playback Database

Recent history is stored in the Playback Database. The Playback Database is deployed on SQL Server. One Playback Database services all systems connected to the Spotlight Diagnostic Server.

Spotlight Statistics Repository

Long term history for reporting and trending is stored in the Spotlight Statistics Repository. The Spotlight Statistics Repository is deployed on SQL Server. One Spotlight Statistics Repository services all systems connected to the Spotlight Diagnostic Server. Monitored

systems in the

deployment

The Spotlight Diagnostic Server collects data from monitored connections that is then

supplied in real time to Spotlight Clients and where applicable Spotlight Essentials through to Spotlight Mobile.

(9)

3

Size of the deployment

How many SQL Servers Instances do you expect to monitor? Number Description

1 - 25 SQL Server Instances

The Spotlight Diagnostic Server, playback database and Spotlight Statistics Repository can all be installed on the same machine, as long as that machine meets the minimum hardware

requirements listed in the release notes. More than

25

SQL Server Instances

It is recommended that the playback database and Spotlight Statistics Repository be installed on a machine other than the Spotlight Diagnostic Server.

The machine(s) that host the playback database and the Spotlight Statistics Repository should be configured with an appropriate amount of I/O bandwidth to meet demand.

If your environment uses a storage area network (SAN) it may be possible to install the Spotlight Statistics Repository and the playback database on the same server.

Maximum Spotlight on SQL Server is designed to monitor a maximum of 100 SQL Servers, Analysis Services or Replication instances and 100 Windows servers. Exceeding this recommended limit on 32 bit environments may result in poor performance or product instability, due to the 1 Gb memory limit. On 64 bit environments theoretically more connections can be monitored as the 1 Gb limit no longer applies, however testing of this has been limited.

Co-existence with Performance Analysis

If you are using Spotlight on SQL Server and Performance Analysis, observe the following.

l Spotlight on SQL Server and Performance Analysis (middleware) must be on separate disk volumes. This

prevents out of disk space conditions which may affect monitoring.

l Spotlight on SQL Server and Performance Analysis repositories can co-exist when more than 25 SQL

Server instances are monitored provided the hardware specifications of the machine that the repositories are installed on are adequate.

(10)

4

Spotlight Client

View Spotlight data and comprehensively configure Spotlight in a Spotlight Client.

Installation

The Spotlight Client is installed on a Windows server.

The Spotlight Client is installed with Spotlight on SQL Server Enterprise. For more information, seeInstallon page48. A Spotlight Client can be installed on the same Windows server as the Spotlight Diagnostic Server but does not have to be. Many Spotlight Clients can connect to one Spotlight Diagnostic Server.

Deployment

Use a Spotlight Client to configure the Spotlight Diagnostic Server and other components of the deployment. Spotlight

Diagnostic Server

Use the Spotlight Client to Configure the Spotlight Diagnostic Server. Where there are multiple Spotlight Diagnostic Server the Spotlight Client can connect to (configure) one Spotlight Diagnostic Server (deployment) at a time.

Spotlight Essentials

Use the Spotlight Client toConfigure uploading to Spotlight Essentials. This Spotlight Essentials configuration services all systems connected to the Spotlight Diagnostic Server. Playback

Database

Use the Spotlight Client to configure the Playback Database. One Playback Database services all systems connected to the Spotlight Diagnostic Server.

Spotlight Statistics

Use the Spotlight Client to configure the Spotlight Statistics Repository and generate reports. One Spotlight Statistics Repository services all systems connected to the Spotlight Diagnostic

(11)

Repository Server. Monitored

systems in the

deployment

Use the Spotlight Client to manage monitored connections. The Spotlight Diagnostic Server collects data from monitored connections that is then supplied in real time to Spotlight Clients and where applicable Spotlight Essentials through to Spotlight Mobile.

Spotlight Client requirements

Table 1: Client requirements

Requirement Details

Platform 1 GHz Pentium® 4 PC

Memory 1 GB RAM

Hard disk space 200 MB

This space should exist on a local disk drive rather than a network drive. Operating system Microsoft® Windows® 10 (32-bit and 64-bit)

Microsoft Windows 8.1 (32-bit and 64-bit) Microsoft Windows 8 (32-bit and 64-bit) Microsoft Windows 7 (32-bit and 64-bit) Microsoft Windows Vista® (32-bit and 64-bit) Microsoft Windows Server® 2012 R2 (64-bit) Microsoft Windows Server 2012 (64-bit) Microsoft Windows Server 2008 R2 (64-bit) Microsoft Windows Server 2008 (32-bit and 64-bit) Screen resolution Minimum Resolution: 1280x1024

DPI scaling is supported for large fonts, Windows 8.1 and above. CD-ROM drive or Internet

connection

This is required for installation only.

Table 2: Additional software requirements

SQL Server Client Tools Required to use the Discovery Wizard to locate SQL Servers on your network. SQL Server Client Tools can be found on the Microsoft SQL Server installation media.

Client Connectivity Tools - Required to monitor an Analysis Services server.

(12)

.NET It is recommended that the latest .NET be installed.

Minimum .NET 4.5.

.NET is required for the Performance Health drilldowns and the Spotlight Report Viewer.

Microsoft Internet Explorer® 6.0 or later.

Microsoft Data Access Components (MDAC)

MDAC includes the ODBC and other components that enable a connection to be established to a SQL Server database. The database may be local or remote. Your client machine must have MDAC 2.7 (or later) installed. MDAC is available from the Microsoft website:

www.microsoft.com/downloads

To find out the version of MDAC that you are currently running, you can download an application from the Microsoft website called the

Component Checker Tool. Review the following Microsoft Knowledge Base article for more information:http://support.microsoft.com/kb/301202 Microsoft Report Viewer 2010 Ensure the Microsoft Report Viewer is installed and operational. The

redistributable package is available from: http://www.microsoft.com/en-us/download/details.aspx?id=6442.

The Report Viewer is used to view the Reporting and Trending | Reports.

Permissions

During operation, the Spotlight Client requires write access to the following file locations:

l C:\ProgramData\Quest Software\Spotlight l C:\ProgramData\Quest Software\Shared

(13)

Spotlight Essentials

View Spotlight data in your favorite web browser. Monitoring features on the Spotlight Essentials web site include a heat map, an alarms list, alarm details and the ability to snooze and acknowledge alarms. Sign in with Spotlight Essentials credentials towww.spotlightessentials.com

Configuration

1. Create a Spotlight Essentials account if you do not already have one. Go to the Spotlight Essentials web site.

2. Use aSpotlight ClienttoConfigure uploading to Spotlight Essentials. Data is uploaded from the Spotlight Diagnostic Server to Spotlight Essentials.

TIP:In a corporate enterprise your Spotlight Essentials account may receive a request to join an organization. You may create an organization through your Spotlight Essentials account. Many Spotlight Essentials accounts can join and leave an organization. Performance analysis data is uploaded to the organization and is available to all members of the organization. Members of the organization see a combined heat map and alarms list.

Deployment

One Spotlight Essentials account can be configured to multiple Spotlight Diagnostic Server (deployments). Spotlight

Diagnostic Server

Data is uploaded from the Spotlight Diagnostic Server to Spotlight Essentials. Ensure the Spotlight Diagnostic Server has access to the Internet. For more information, seeAccess to the Interneton page19.

Use theSpotlight ClienttoConfigure uploading to Spotlight Essentials. A Spotlight Diagnostic Server can be configured to one Spotlight Essentials account. The address of the Spotlight

(14)

Diagnostic Server is managed in theSpotlight Client. Playback

Database

One Playback Database services all systems serviced by the Spotlight Diagnostic Server. Use a Spotlight Client to configure the Playback Database.

Use aSpotlight Clientto access the Spotlight Statistics Repository.

Monitored systems in the

deployment

Use aSpotlight Clientto manage the connections monitored by the Spotlight Diagnostic Server.

(15)

Spotlight Mobile

Monitor Spotlight connections on your favorite mobile device. The Spotlight Mobile app is available for native Windows 8, Windows 8 Phone, iPad, iPhone and android app. For more information see theSpotlight

Essentials web site.

Monitoring features include a heat map, alarms list and alarm details. You can snooze and acknowledge alarms.

Configuration

1. Create a Spotlight Essentials account if you do not already have one. Go to the Spotlight Essentials web site.

2. Use aSpotlight ClienttoConfigure uploading to Spotlight Essentials.

3. Download and Install Spotlight Mobile. Refer to theSpotlight Essentials web site. 4. Sign in to Spotlight Mobile with your Spotlight Essentials account.

Deployment

Spotlight Mobile monitors data from all Spotlight Essentials accounts configured to the Spotlight Mobile device. Spotlight

Diagnostic Server

The address of the Spotlight Diagnostic Server is managed in theSpotlight Client. Use the Spotlight Clientto stop / start monitoring the Spotlight Diagnostic Server (deployment) through Spotlight Essentials byConfigure uploading to Spotlight Essentials.

Playback Database

One Playback Database services all systems serviced by the Spotlight Diagnostic Server. Use a Spotlight Clientto configure the Playback Database.

Use aSpotlight Clientto access the Spotlight Statistics Repository.

Monitored systems in the

deployment

Use aSpotlight Clientto manage the connections monitored by the Spotlight Diagnostic Server.

(16)

5

Spotlight Diagnostic Server

The Spotlight Diagnostic Server is at the core of the Spotlight on SQL Server architecture. All Spotlight data passes through the Spotlight Diagnostic Server.

The Spotlight Diagnostic Server collects data regardless of whether a Spotlight client is connected. As data is collected only once, there is no unnecessary replication of data. Data is evaluated on the Spotlight Diagnostic Server so that all Spotlight clients receive consistent information. Historical data is saved in one place, avoiding unnecessary replication of data.

Installation

The Spotlight Diagnostic Server is a Windows service.

The Spotlight Diagnostic Server is installed with Spotlight on SQL Server Enterprise. For more information, see Installon page48.

Consider installing the Spotlight Diagnostic Server on a computer that is always switched on. Internet access is required where Spotlight Essentials is in use or Auto Update enabled.

Deployment

Spotlight Client Spotlight Essentials Spotlight Mobile

The Diagnostic Server supplies data in real time to Spotlight Client(s) and where applicable Spotlight Essentials through to Spotlight Mobile.

Use a Spotlight Client to Configure Spotlight | Diagnostic Server and configure the uploading of data to Spotlight Essentials. Where Spotlight Essentials is in use the Spotlight Diagnostic Server requires access to the Internet.

Playback Database One Playback Database services all systems

connected to the Spotlight Diagnostic Server. Use a Spotlight Client to configure the Playback Database. Spotlight Statistics Repository Enable use of the Spotlight Statistics Repository in a

Spotlight Client. One Spotlight Statistics Repository services all systems connected to the Spotlight Diagnostic Server.

(17)

Monitored systems in the deployment The Spotlight Diagnostic Server collects data from monitored connections that is then supplied in real time to Spotlight Clients and where applicable Spotlight Essentials through to Spotlight Mobile. Use a Spotlight Client to manage the monitoring of connections.

Additional

Access to the Internet The Spotlight Diagnostic Server requires access to

the Internet when using Auto Update or Spotlight Essentials.

Network ports on the Spotlight Diagnostic Server Ensure in the deployment of Spotlight on SQL Server that the following ports are open.

Back up Spotlight configuration data Spotlight configuration data is stored in the conf folder on the Spotlight Diagnostic Server. You can perform backups of the Spotlight configuration data by backing up this folder regularly.

Start and stop the Spotlight Diagnostic Server The Spotlight Diagnostic Server is a Windows service and can be started and stopped via the Windows Control Panel.

Spotlight Diagnostic Server requirements

Table 3: Spotlight Diagnostic Server requirements

Platform 2 GHz Pentium Dual Core

Memory 2 GB RAM

Hard disk space 300 MB

If the Diagnostic Server’s connection to the Spotlight Statistics Repository or Playback database is interrupted, the Diagnostic Server will cache data to the local disk. In this event, an extra 2 MB per hour, per monitored server is required.

Operating system Microsoft Windows 10 (32-bit and 64-bit) Microsoft Windows 8.1 (32-bit and 64-bit) Microsoft Windows 8 (32-bit and 64-bit) Microsoft Windows 7 (32-bit and 64-bit) Microsoft Windows Server 2012 R2 (64-bit)

(18)

Microsoft Windows Server 2012 (64-bit) Microsoft Windows Server 2008 R2 (64-bit) Microsoft Windows Server 2008 (32-bit and 64-bit)

NOTE:The Diagnostic Server is not supported on a cluster or High Availability Always On.

.NET .NET 3.5 or later.

Table 4: Additional software requirements

SQL Server Client Tools Required to use the Discovery Wizard to locate SQL Servers on your network. SQL Server Client Tools can be found on the Microsoft SQL Server installation media.

Client Connectivity Tools - Required to monitor an Analysis Services server.

SQL Server Management Tools Required to monitor an Analysis Services server, a SQL Server Replication environment, and the use of Session Trace and SQL Analysis when monitoring a SQL Server.

The SQL Server Management Tools are available from any SQL Server 2005, SQL Server 2008, SQL Server 2012 or SQL Server 2014 edition except SQL Server Express and SQL Server Compact. The version required corresponds to the latest version of SQL Server that you are monitoring. Note, if you are monitoring SQL Server 2000 servers, you need the Management Tools from SQL Server 2005.

Internet Access Required by Auto Update and Spotlight Essentials and monitoring of your Spotlight connections from your favorite mobile device.

Account permissions on the Spotlight

Diagnostic Server

l During operation the Spotlight Diagnostic Server requires write access to the PROGRAM FILES folder. l The minimum Windows privilege level required to run the Spotlight Diagnostic Server is Power User. l If the Spotlight Diagnostic Server is in a different domain to the domain that the Spotlight Clients are in,

the domain that the Spotlight Diagnostic Server is in must trust the domain that the Spotlight Client users are in.

(19)

Network ports on the Spotlight Diagnostic

Server

Port Description

3843 This is the default port for retrieving data on the network. This port must be open on the Spotlight Diagnostic Server. TCP

3166

This is the default port for the data collection subsystem of the Spotlight Diagnostic Server. No external connections are required on this port.

If a firewall is installed on the Spotlight Diagnostic Server then ensure this port is open. TCP

443

This port is used by Spotlight Essentials. Ensure this port is open when Spotlight Essentials is enabled. For more information, seeConfigure uploading to Spotlight Essentialson page57.

If the Spotlight Diagnostic Server and Spotlight Client are

installed on different host computers

Port Description TCP

40403

Open port TCP 40403 if you intend to use the Alarm Actions feature of Spotlight. For more information, seeConfigureon page53.

Access to the Internet

The Spotlight Diagnostic Server requires access to the Internet:

l When Auto Update is enabled.

l When uploading of performance data to Spotlight Essentials. For more information, seeConfigure

uploading to Spotlight Essentialson page57.

l When monitoring of your Spotlight connections from your favorite mobile device. For more information,

seeConfigure uploading to Spotlight Essentialson page57.

Windows Control Panel | Internet Options | Connections

On the computer hosting the Spotlight Diagnostic Server:

1. Open theControl Panel | Internet Options. 2. Select theConnectionstab.

3. ClickLAN Settings.

4. Ensure the settings on this screen are appropriate to the settings of your local environment. NOTE:After the Spotlight Diagnostic Server is installed, restart the Spotlight Diagnostic Server after changes are made. For instructions seeStart and stop the Spotlight Diagnostic Server.

(20)

Windows Control Panel | Internet Options | Advanced

On the computer hosting the Spotlight Diagnostic Server:

1. Open theControl Panel | Internet Options. 2. Select theAdvancedtab.

3. EnsureUse HTTP 1.1andUse HTTP 1.1 through proxy connectionsare selected.

NOTE:After the Spotlight Diagnostic Server is installed, restart the Spotlight Diagnostic Server after changes are made. For instructions seeStart and stop the Spotlight Diagnostic Server.

Verification

Verify access to the Spotlight Essentials web site. Use a web browser to look up the following URLs.

Look up URL Description

https://www.spotlightessentials.com Display of the Spotlight Essentials home page confirms HTTPS port 443 is open.

https://api.spotlightessentials.com Display of the text “Welcome to Spotlight Web API!” confirms access for uploading.

(21)

6

Playback Database

Recent history is stored in the playback database.

Deployment

The Playback Database is deployed on SQL Server.

The Playback Database is created on install of Spotlight on SQL Server. For more information, see Install on page 48.

One Playback Database services all systems connected to theSpotlight Diagnostic Server.

Configuration

Use aSpotlight ClienttoConfigure the Playback Database.

Additional

Maintenance plan for Spotlight Statistics Repository and Playback Database

Spotlight can automatically run maintenance procedures for the Playback Database. Due to its nature, it is highly recommended that regular maintenance is performed to maintain efficient data retrieval.

Playback Database requirements

Table 5: Playback database requirements

Database sizing The MS Model database is used. The database size usually starts at about 3MB (data size) and 1MB (log size).

(22)

and the corresponding monitored Windows host on which the SQL Server instance resides. Retains 7 days of historical data for each connection in the playback database by default. This is configurable.

This estimate assumes default configuration in an average environment. Actual space used will depend on your environment and any data collection/storage configuration changes that are made. For more

information, see the Spotlight on SQL Server Sizing Wizard. You can access the wizard from the Spotlight Installation Center.

SQL Server The playback database can be installed on:

l SQL Server 2014 l SQL Server 2012 l SQL Server 2008 R2 l SQL Server 2008 l SQL Server 2005

NOTE:Due to the size of the playback database, it is

recommended that this database is not installed on a SQL Express instance. The playback database is not supported on a cluster or High Availability Always On. The playback database cannot be installed on SQL Server 2000.

Other requirements The SQL Browser service must be running on the machine where the playback database is installed. Alternatively you can use the host and port number of the (named) SQL Server instance you want to connect to, when creating a connection and connecting to the playback database. Use the following syntax:

HOST,PORT

Where HOST is the machine name and PORT is the port for the (named) SQL Server instance. For more information, seeConfigure the Spotlight Statistics Repositoryon page55.

The TCP protocol must be enabled on the instance where the playback database resides.

Maintenance The Diagnostic Server runs maintenance procedures daily at 3am for the playback database. This is configurable.

It is highly recommended that regular maintenance is performed on the playback database. The recommended maintenance plan is outlined in the online help and in this guide.

For more information, seeMaintenance plan for Spotlight Statistics Repository and Playback Databaseon page62.

(23)

7

Spotlight Statistics Repository

NOTE:This feature is available only in Spotlight on SQL ServerEnterprise.

Long term history for reporting and trending is stored in the Spotlight Statistics Repository.

Deployment

The Spotlight Statistics Repository is deployed on SQL Server.

Enable use of the Spotlight Statistics Repository in theSpotlight Client. Create the Spotlight Statistics Repository in the Spotlight Client.

One Spotlight Statistics Repository services all systems connected to theSpotlight Diagnostic Server.

Configuration

Use aSpotlight Clientto enable, create andConfigure the Spotlight Statistics Repository.

Additional

Maintenance plan for Spotlight Statistics Repository and Playback Database

Spotlight can automatically run maintenance procedures for the Spotlight Statistics Repository. It is highly recommended that regular maintenance is performed to maintain efficient data retrieval.

(24)

Spotlight Statistics Repository

requirements

Table 6: Spotlight Statistics Repository Requirements

Requirement Details Database

sizing

The MS Model database is used. The database size usually starts at about 3MB (data size) and 1MB (log size).

Hard Disk Space

Approximately 1.2 GB for each monitored SQL Server instance and the corresponding monitored Windows host on which the SQL Server instance resides.

This estimate assumes default configuration in an average environment. Actual space used will depend on your environment and any data collection/storage configuration changes that are made. For more information, see the Spotlight on SQL Server Sizing Wizard. You can access the wizard from the Spotlight Installation Center.

SQL Server The Spotlight Statistics Repository can be installed on:

l SQL Server 2014 l SQL Server 2012 l SQL Server 2008 R2 l SQL Server 2008 l SQL Server 2005

NOTE:Due to the size of the Spotlight Statistics Repository, it is recommended that this database is not installed on a SQL Express instance. The Spotlight Statistics Repository is not supported on a cluster or High Availability Always On. The Spotlight Statistics Repository cannot be installed on SQL Server 2000.

Other Requirements

The SQL Browser service must be running on the machine where the Spotlight Statistics Repository is installed. Alternatively you can use the host and port number of the (named) SQL Server instance you want to connect to, when creating a connection and connecting to the Spotlight Statistics Repository.

Use the following syntax: HOST,PORT

Where HOST is the machine name and PORT is the port for the (named) SQL Server instance. For more information, seeConfigure the Spotlight Statistics Repositoryon page55.

The TCP protocol must be enabled on the instance where the Spotlight Statistics Repository resides.

Maintenance By default, the Diagnostic Server runs maintenance procedures daily at 3am for the Spotlight Statistics Repository.

It is highly recommended that regular maintenance is performed on the Spotlight Statistics Repository. The recommended maintenance plan is outlined in the online help and in this guide.

For more information, seeMaintenance plan for Spotlight Statistics Repository and Playback Databaseon page62.

(25)

Monitored systems in the deployment

Spotlight monitors the following categories of connections.

Connections Description

Monitored SQL Servers Connect Spotlight to SQL Server instances.

Database cloud service Connect Spotlight to SQL Azure. Monitored Windows servers and hosts of

database connections

Connect Spotlight to Windows servers including the hosts of monitored SQL Server instances.

Virtualization Connect Spotlight to virtual environments and virtual guest machines in a virtual environment.

NOTE:

Spotlight on SQL Server is designed to monitor a maximum of 100 SQL Servers, Analysis Services or Replication instances and 100 Windows servers. Exceeding this recommended limit on 32 bit

environments may result in poor performance or product instability, due to the 1 Gb memory limit. On 64 bit environments theoretically more connections can be monitored as the 1 Gb limit no longer applies, however testing of this has been limited.

Configuration

TheSpotlight Diagnostic Servercollects data from monitored connections. Use aSpotlight Clientto manage the connections monitored by Spotlight.

Deployment

Spotlight Client Spotlight Essentials Spotlight Mobile

View Spotlight data on Spotlight Client(s) and where configured on the Spotlight Essentials web site and Spotlight Mobile. Configure the Spotlight deployment in a Spotlight Client.

Spotlight Diagnostic Server The Spotlight Diagnostic Server collects data from

monitored connections that is then supplied in real time to Spotlight Clients and where applicable Spotlight Essentials through to Spotlight Mobile. The Spotlight Diagnostic Server collects data regardless of whether a Spotlight client is connected. As data is

(26)

collected only once, there is no unnecessary

replication of data. Data is evaluated on the Spotlight Diagnostic Server so that all Spotlight clients receive consistent information.

Playback Database Recent history is stored in the playback database.

One Playback Database services all systems connected to the Spotlight Diagnostic Server. Spotlight Statistics Repository Long term history for reporting and trending is stored

in the Spotlight Statistics Repository. One Spotlight Statistics Repository services all systems connected to the Spotlight Diagnostic Server.

(27)

8

Monitored systems supported

Table 7: Spotlight on SQL Server is able to monitor the following connections

SQL Server Spotlight can monitor SQL Server instances of the following versions:

l SQL Server 2014 (32-bit and 64-bit) l SQL Server 2012 (32-bit and 64-bit) l SQL Server 2008 R2 (32-bit and 64-bit) l SQL Server 2008 (32-bit and 64-bit) l SQL Server 2005 (32-bit and 64-bit)

Spotlight on SQL Server 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 continues to monitor SQL Server 2000 instances Service Pack 3 or later, however no further support (fixes) will be provided.

Spotlight cannot monitor earlier versions of SQL Server (version 7.x and earlier).

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.

Database Cloud Service Spotlight can be used to monitor SQL Azure.

Windows Server Spotlight can monitor the following operating systems:

l Microsoft Windows 10 (32-bit and 64-bit) l Microsoft Windows 8.1 (32-bit and 64-bit) l Microsoft Windows 8 (32-bit and 64-bit) l Microsoft Windows 7 (32-bit and 64-bit) l Microsoft Windows Vista (32-bit and 64-bit) l Microsoft Windows Server 2012 R2 (64-bit) l Microsoft Windows Server 2012 (64-bit) l Microsoft Windows Server 2008 R2 (64-bit) l Microsoft Windows Server 2008 (32-bit and 64-bit)

l Microsoft Windows Server 2003 (32-bit and 64-bit), however as this

(28)

investigated by support.

On each monitored instance, Spotlight requires the network setting "File and Print Sharing" to be enabled and any firewall to be configured to:

l open port 1434 for UDP packets

l open the SQL Server TCP port (default is 1433)

Virtualization Spotlight can monitor virtual environments and virtual guest machines in a virtual environment. The following is supported:

l VMware® ESX infrastructure. VMware vCenter 2.5 (or later).

VMware ESX Server™ 4.0 (or later)

l Hyper-V® 6.2 and above.

The username used to connect to the virtual guest machine must have at least a read-only role.

Connection Limit Spotlight on SQL Server is designed to monitor a maximum of 100 SQL Servers, Analysis Services or Replication instances and 100 Windows servers. Exceeding this recommended limit on 32 bit environments may result in poor performance or product instability, due to the 1 Gb memory limit. On 64 bit environments theoretically more connections can be monitored as the 1 Gb limit no longer applies, however testing of this has been limited.

(29)

9

Monitored SQL Servers

This section refers to the SQL Servers that Spotlight monitors.

Section Description

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.

SQL Server performance counters

Spotlight on SQL Server uses sys.dm_os_performance_counters (sysperfinfo for SQL Server 2000) to retrieve data for many of its displays. In some rare cases, this table may not contain information.

Network ports and SQL Server connections

The port connection to the SQL Server must be open for data to be retrievable over the network.

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 userTrustedUser. 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

(30)

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

(31)

declare @dbname sysname declare @use nvarchar(4000) declare @Dell_dblist table (

row int identity, name sysname )

insert into @Dell_dblist (name)

select name from master.dbo.sysdatabases; set @dbnumber = @@rowcount

while @dbnumber > 0 begin

select @dbname =name from @Dell_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

SQL Server performance counters

Spotlight on SQL Server usessys.dm_os_performance_counters(sysperfinfo for SQL Server 2000) 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 the appropriate version of SQL Server Management Studio for SQL Server:

select * from sys.dm_os_performance_counters

or for SQL Server 2000 by running the following SQL in Query Analyzer: select * from master..sysperfinfo

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.

(32)

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 athttp://msdn.microsoft.com.

Network ports and SQL Server connections

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

(33)

10

Monitored Windows servers and hosts

of database connections

This section refers to the Windows servers that Spotlight monitors and the Windows servers that host the database connections that Spotlight monitors.

Section Description Windows

account permissions

The Spotlight Diagnostic Server executes WMI queries against the Windows server on which the SQL Server is currently running. 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.

Windows domains

Spotlight can only connect to Windows servers and databases hosted on Windows servers in a domain. Spotlight cannot connect to Windows servers in a workgroup.

Enable disk counters

Spotlight on SQL Server cannot display some disk information properly unless the Windows server host has disk performance counters enabled.

Network ports and Windows connections / Windows hosts

The port connection to the SQL Server must be open for data to be retrievable over the network.

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.

Monitoring the Spotlight Diagnostic Server

To monitor the Windows server that hosts the Spotlight Diagnostic Server, Windows requires that the Spotlight connection use Windows authentication.

Windows account permissions

To retrieve performance counter information, the Spotlight Diagnostic Server executes WMI queries against the Windows server on which the SQL Server is currently running. 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.

(34)

This account is defined when configuring the connection. For more information, seeConfigure | Connectionson page54.

You may experience problems with WMI. For more information, seeTroubleshooting WMIon page39.

How to configure WMI with minimum required user

permissions

In some environments it may not be feasible to connect to the Windows server with an account that is a

member of the Administrators group. The following instructions are provided for these environments. Note that there are known issues with this procedure as documented below.

1. Setup permission to read data

2. Grant permissions to get information about services 3. Provide access to the Registry keys used by Spotlight 4. Run Windows Component Services

5. Configure WMI permissions

Following are known issues with configuring WMI with minimum required permissions Known Issue Description

The

OpenSessions collection

The OpenSessions collection sends a list of the open Windows network sessions to your server. The list includes who is connected and from where, how long they have been connected and how many files they have open.

The OpenSessions collection is used in:

l The Windows Server | Home Page | Network panel | Open Sessions count. l The Network Drilldown | Sessions page.

Spotlight does not alarm on this data. The Physical

Disk Drive collection

The Physical Disk Drive collection populates the Disks Drilldown | Disk Summary page. This drilldown contains a list of the physical disks installed on the server. Its data is relatively static as it only changes when disks are added, removed or repartitioned.

Inside of this known issue a Generic Failure error will be returned.

1. Setup permission to read data

NOTE:This is step 1 of the procedure for how to Configure WMI with minimum required user permissions.

Spotlight Useris the windows user that will be used in the connection to the Windows server and/or Windows host of the database connection. For more information, seeConfigure | Connectionson page 54.

(35)

1. ClickControl Panel | Administrative Tools | Computer Managementto open the Computer Management dialog.

2. Double clickLocal Users and Groups | Groups.

3. Double clickPerformance Log Users. Add theSpotlight Userto this group. OK. 4. Double clickPerformance Monitor Users. Add theSpotlight Userto this group. OK.

2. Grant permissions to get information about

services

Retrieve the user SID

From the Windows command prompt, typepowershelland click Enter to open the Powershell.

Run the following command to retrieve the user SID of theSpotlight User. ReplacedomainNameanduserName

with the domain name and user name for theSpotlight Useraccount.

[wmi]"win32_useraccount.domain='domainName',name='userName'"

Retrieve the current SDDL for the Services Control Manager

From the Windows command prompt, run the following command to retrieve the current SDDL for the Services Control Manager. The SDDL is saved in the file calledfile.txt.

sc sdshow scmanager > file.txt

The SDDL looks something like this. For more information seeMicrosoft KB914392.

D:(A;;CC;;;AU)(A;;CCLCRPRC;;;IU)(A;;CCLCRPRC;;;SU)(A;;CCLCRPWPRC;;;SY)(A;;KA;;;BA)S: (AU;FA;KA;;;WD)(AU;OIIOFA;GA;;;WD)

Modify the SDDL

Copy the section of the SDDL that ends in IU (Interactive Users). This section is one complete bracketed clause ie (A;;CCLCRPRC;;;IU). Paste this clause directly after the clause you copied from.

In the new text, replaceIUwith the user SID of theSpotlight User. The new SDDL looks something like the following:

D:(A;;CC;;;AU)(A;;CCLCRPRC;;;IU) (A;;CCLCRPRC;;;S-1-5-21-214A909598-1293495619-13Z157935-75714)(A;;CCLCRPRC;;;SU)(A;;CCLCRPWPRC;;;SY)(A;;KA;;;BA) S:(AU;FA;KA;;;WD) (AU;OIIOFA;GA;;;WD)

Set the security credentials for accessing the Service Control Manager

The sdset command on sc sets the security credentials for accessing the Service Control Manager (scmanager). Note the permissions on scmanager are being replaced. Setting security credentials is not additive. That’s why

(36)

we needed to copy the existing permissions.

sc sdset scmanager "D:(A;;CC;;;AU)(A;;CCLCRPRC;;;IU)(A;;CCLCRPRC;;;SU)

(A;;CCLCRPWPRC;;;SY)(A;;KA;;;BA)(A;;CCLCRPRC;;;S-1-5-21-214A909598-1293495619-13Z157935-75714)S:(AU;FA;KA;;;WD)(AU;OIIOFA;GA;;;WD)"

3. Provide access to the Registry keys used by

Spotlight

On the Windows server and/or Windows host of the database connection in RegEdit open the following nodes. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session

Manager\Environment

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control On each node

1. Right click and selectPermissions. 2. Add theSpotlight User. OK.

3. Select theSpotlight User. SelectRead access.

4. Run Windows Component Services

From the Windows command prompt, typedcomcnfgand click Enter to open the Component Services dialog.

Configure COM security

1. From the Component Services dialog clickComputers | My Computer. 2. Right clickMy Computerand selectProperties.

3. From the Properties dialog, clickCOM Security. 4. From Access Permissions, clickEdit Limits.

a. Add theSpotlight User. b. AllowRemote Access.

(37)

c. Click OK to close the Access Permission dialog and save changes. 5. From Launch and Activation Permissions, clickEdit Limits.

a. Add theSpotlight User.

b. AllowRemote LaunchandRemote Activation.

c. Click OK to close the Launch and Activation Permission dialog. 6. Click Ok to close the Properties dialog and save changes.

Configure DCOM security

1. From the Component Services dialog double clickComputers | My Computer | DCOM Config | Windows Management and Instrumentation.

2. Right clickWindows Management and Instrumentation | Properties. 3. ClickSecurity | Launch and Activation Permissions | Edit.

a. Add theSpotlight User.

b. AllowRemote LaunchandRemote Activation.

c. Click OK to close the Launch and Activation Permission dialog and save changes.

4. Click OK to close the Windows Management and Instrumentation Properties dialog and save changes.

5. Configure WMI permissions

From the Windows command prompt, typewmimgmt.mscand click Enter to open the WmiMgmt dialog. 1. Right clickWMI Control (Local) | Properties.

2. ClickSecurity.

3. Expand theRootnode. Selectcimv2.

4. Click theSecuritybutton to open security settings for WMI on this computer. 5. ClickAdvancedto open the advanced security settings for this WMI namespace.

Add theSpotlight User. ClickEdit. Allow:

l Execute Methods l Enable Account l Remote Enable l Read Security

Ensure these permissions apply to this namespace and all the namespaces under it by selectingThis namespace and subnamespacesin theApply todrop down box.

(38)

Click OK to save the new permissions.

6. Click OK to close the Advanced Security Settings dialog. Click OK to close the Security for ROOT dialog. 7. Returning to theRootnode, selectDEFAULT.

8. Click theSecuritybutton to open security settings for DEFAULT. 9. ClickAdvancedto open the advanced security settings.

Add theSpotlight User. ClickEdit. Allow:

l Execute Methods l Enable Account l Remote Enable l Read Security

Ensure these permissions apply to this namespace and all the namespaces under it by selectingThis namespace and subnamespacesin theApply todrop down box.

Click OK to save the new permissions. 10. Click OK to close all dialogs.

Windows domains

Spotlight can only connect to Windows servers and databases hosted on Windows servers in a domain. Spotlight cannot connect to Windows servers in a workgroup.

To use Windows Authentication to connect to a SQL Server, and the SQL server is in a different domain to the Spotlight Diagnostic Server, the domain that the Spotlight Diagnostic Server account resides in must be trusted by the Domain that the SQL Server is in.

Enable disk counters

Spotlight on SQL Server cannot display some disk information properly unless the Windows server 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 Windows server being monitored.

To enable disk data collection

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

(39)

Network ports and Windows connections /

Windows hosts

Data must be retrievable over your network

The port connection to the SQL Server must be open for data to be retrievable over the network

TCP/IP 1433 by default

How to limit the number of ports used

by WMI

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

You may experience problems using WMI. For more information, seeTroubleshooting WMIon page39.

Troubleshooting WMI

Spotlight on SQL Server uses WMI queries to retrieve performance counter information from the Windows server on which SQL Server is running. Spotlight needs access to this information before it can connect to the Windows host and the SQL Server running on the Windows host.

In addition to the following tests, 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:

(40)

WMI Test 1

This test checks that requests are reaching WMI. Run this test from the Windows server being monitored.

1. ClickControl Panel | Administrative Tools | Event Viewerto open the Event Viewer. 2. ClickView | Show Analytic and Debug Logsto select this menu option.

3. ClickApplications and Service Logs

4. Click to expandMicrosoft | Windows | WMI-Activity 5. Right clickTrace | Enable Log.

Tip: To save log entries, right clickTrace | 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, seeWMI errorson page41.

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. ClickControl Panel | Administrative Tools | Computer Management.

3. Right clickComputer Management (Local) | Connect to another computer.

4. Specify the \\HOSTNAMEwhereHOSTNAMEis the name of machine you want to monitor with Spotlight. 5. ClickServices and Applications.

6. Right clickWMI Control | Propertiesto open the WMI Control Properties dialog. 7. Ensure theGeneraltab is open.

If successful, try to monitorHOSTNAMEwith Spotlight again.

WMI Test 3

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

If you encounter WMI errors: For more information, seeWMI errorson page41.

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

(41)

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

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

or

wmic /node:HOSTNAME/user:DOMAIN\USERpath 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.

WMI errors

The following is a guide to troubleshooting WMI errors.

Error Solution

The RPC server is unavailable. Exception from HRESULT: 0x800706BA

Spotlight on SQL Server is not able to establish a connection with the Windows server to be monitored.

Verify the following:

l The address of the server is entered correctly.Verify correct host name or IP

address. For more information, seeConfigure | Connectionson page54. Verify the host is available (not currently shut down).

l The Remote Procedure Call (RPC) service is running on the Windows server to be

monitored.Verity that "Remote Procedure Call (RPC)" is running and set to auto start after restart.

l TCP Port 135 is open to internal traffic on the Spotlight Diagnostic Server and

the Windows server to be monitored.

WMI opens an undetermined port in addition to port 135. This can be troublesome in a firewalled environment. Specifying a port range for WMI is recommended for this type of environment. For more information, seeHow to limit the number of ports used by WMIon page39.

l The Windows server to be monitored is not blocked by the firewall.

Either configure WMI to use a fixed ports range (For more information, seeHow to limit the number of ports used by WMIon page39.) or enable remote administration exception.

Follow these steps to enable remote administration exception. On the Windows server to be monitored:

1) Open the Group Policy Object Editor (gpedit.msc), open Computer Configuration | Administrative Templates | Network | Network Connections | Windows Firewall 2) Open either Domain Profile or Standard Profile, depending on which profile you want to configure.

(42)

3) Enable the following exceptions: "Allow Remote Administration Exception" and "Allow File and Printer Sharing Exception".

http://msdn.microsoft.com/en-us/library/aa389286(VS.85).aspx

l The "TCP/IP NetBIOS Helper" service is running.Verity that "TCP/IP NetBIOS

Helper" is running and set to auto start after restart.

l The "Windows Management Instrumentation" service is running on the Windows

server to be monitored.Verity that "Windows Management Instrumentation" is running and set to auto start after restart.

Access is denied. Exception from HRESULT: 0x80070005 (E_

ACCESSDENIE D)

The Windows user specified is unknown to the Windows server or does not have administrator rights.

For more information, seeWindows account permissionson page33.

WMI connection time outs

The timeout value defaults to the value DCOM specifies (usually 60 seconds). You can adjust this value via dcomcnfg.exe

WMI query failed: Invalid class. [0x80041010]

The WMI class does not exist on the Windows server being monitored. Solution: Recreate the WMI classes.

Windows Connections

Either of the following will recreate the WMI classes:

l Open a command prompt window and run the following command:

wmiadap /f

l Use the Microsoft Extensible Counter List (Exctrlst) utility to enable the following

counters:

Win32_PerfDisk, Win32_PerfNet, Win32_PerfOS and Win32_PerfProc.

http://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=7FF996 83-B7EC-4DA6-92AB-793193604BA4

A reboot of the Windows server is required.

SQL Server Analysis Services Connections

Either of the following will recreate the WMI classes:

l Open a command prompt window on the server hosting the SQL Server Analysis

(43)

wmiadap /f

l Unregister and re-register the WMI classes. To do so:

1. On the problematic machine with SQL Server Analysis Services installed, find out the SQL installation path. See below for the default installation path. Your installation path may be different.

SQL Server Analysis Services 2014 C:\Program Files\Microsoft SQL

Server\MSAS12.MSSQLSERVER\OLAP\bin\Counters SQL Server Analysis Services 2012

C:\Program Files\Microsoft SQL

C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin

Note:You are looking for the folder that contains the following files: For unamed instances:

MSSQLServerOLAPService

perf-MSSQLServerOLAPServicemsmdctr.ini For named instances:

MSOLAP$Your_SSAS_NamedInstanceName

perf-Your_SSAS_NamedInstanceNamemsmdctr.ini

If you need to check the named instance name, use services.msc. 2. At the command prompt, change folder to the installation path.

For example, cd C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\bin\Counters

3. Run the following commands to unload and load counters: unamed

instances

unlodctr MSSQLServerOLAPService

lodctr perf-MSSQLServerOLAPServicemsmdctr.ini named

instances

unlodctr MSOLAP$Your_SSAS_NamedInstanceName

lodctr perf-Your_SSAS_

NamedInstanceNamemsmdctr.ini

(44)

SQL Server Analysis Services named instance name. If you need to check the named instance name, use services.msc.

4. Run the following command to parse all the performance libraries on the Windows server and refresh the performance counter classes on the Windows server:

wmiadap /f

5. Use wbemtest.exe to verify the WMI Classes exist. WMI query

failed: Invalid query. [0x80041017]

The columns returned by the WMI class are incomplete or unexpected.

Solution: On the Windows server to be monitored, update the WMI classes by running the following command at the command prompt.

wmiadap.exe /f

Seehttp://msdn.microsoft.com/en-us/library/aa394528(VS.85).aspxfor more information. 0x80014064

User credentials cannot be used for local connections

This error occurs when wmic command is executed locally on the target machine and credentials specified

[0x800705af] The paging file is too small for this operation to complete. You may find the following link useful:

http://blogs.technet.com/b/kevinholman/archive/2010/06/09/wmi-leaks-memory-on-server-2008-r2-monitored-agents.aspx

WMI query failed: Out of memory. [0x80041006]

1. At the command prompt run "wbemtest"

2. Connect to the "root" namespace (not "root\default", just "root") 3. ClickOpen Instance. Specify "__ProviderHostQuotaConfiguration=@" 4. SelectLocal Onlyfor easier readability. You will see the threshold values.

5. Increase the MemoryPerHost value to something greater. For example, double it (256 MB)

6. Save Property 7. Save Object 8. ClickExit.

9. Restart WMI services.

Invalid verb The wmic command has attempted to access a WMI class that does not exist. Solution: Check the spelling of parameters on the wmic command.

Spotlight on SQL Server Enterprise Deployment Guide