5.6.5.2
for SQL Server
Foglight
®
© 2013 Quest Software, Inc. ALL RIGHTS RESERVED.
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 Quest Software, Inc.
The information in this document is provided in connection with Quest 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 Quest products. EXCEPT AS SET FORTH IN QUEST'S TERMS AND CONDITIONS AS SPECIFIED IN THE LICENSE AGREEMENT FOR THIS PRODUCT, QUEST 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 QUEST 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 QUEST HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. Quest 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. Quest 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: Quest Software World Headquarters
LEGAL Dept 5 Polaris Way Aliso Viejo, CA 92656
www.quest.com
email: legal@quest.com
Refer to our Web site for regional and international office information.
Trademarks
Quest, Quest Software, the Quest Software logo, Foglight, IntelliProfile, PerformaSure, Spotlight, StealthCollect, TOAD, Tag and Follow, Vintela Single Sign-on for Java, vFoglight, and vOPS are trademarks and registered trademarks of Quest Software, Inc in the United States of America and other countries. For a complete list of Quest Software’s trademarks, please see http://www.quest.com/legal/trademark-information.aspx. Other trademarks and registered trademarks are property of their respective owners.
Third Party Contributions
Foglight contains some third party components. For a complete list, see the License Credits page in Foglight online help.
Getting Started Guide June 2013
Table of Contents
Introduction to this Guide...4
About Quest Software, Inc. ... 5
Contacting Quest Software ... 5
Contacting Quest Support ... 5
Installing Foglight for SQL Server ...6
Installation Prerequisites ... 7
Prerequisites for the Do It Yourself (DIY) Configuration... 8
Foglight for SQL Server Permissions... 8
Granting Permissions to SQL Server Users... 8
Running the Grant Permissions Script ... 10
Installing Foglight for SQL Server ... 12
Installing Foglight for SQL Server and Foglight Management Server Together... 12
Installing the DIY Configuration of Foglight for SQL Server ... 12
Installing the Cartridge by Adding it to a Cartridge Inventory of an Existing Foglight Management Server ... 13
Configuring Foglight for SQL Server...14
Configuring the Connection to Performance Analysis... 15
Layout of Foglight for SQL Server Dashboard ... 17
1
Introduction to this Guide
Welcome to the Foglight for SQL Server Getting Started Guide. This guide is a quick guide for the installation and use of Foglight for SQL Server, from the planning stage up to the actual
monitoring of database instances.
This document details first the installation prerequisites, including the required permissions, and then the actual monitoring of database instances.
Chapter 1—Introduction to this Guide
About Quest Software, Inc.
Established in 1987, Quest Software (Nasdaq: QSFT) provides simple and innovative IT management solutions that enable more than 100,000 global customers to save time and money across physical and virtual environments. Quest products solve complex IT challenges ranging from database management, data protection, identity and access management, monitoring, user workspace management to Windows management. For more information, visit www.quest.com.
Contacting Quest Software
Refer to our Web site for regional and international office information.
Contacting Quest Support
Quest Support is available to customers who have a trial version of a Quest product or who have purchased a Quest product and have a valid maintenance contract. Quest Support provides unlimited 24x7 access to our Support Portal at https://support.quest.com.
From our Support Portal, you can do the following:
• Retrieve thousands of solutions from our Knowledge Base • Download the latest releases and service packs
• Create, update, and review Support cases
View the Global Support Guide for a detailed explanation of support programs, online services, contact information, policies, and procedures. The guide is available at:
https://support.quest.com/Shared/Images/GlobalSupportGuide.pdf.
Email info@quest.com
Mail Quest Software, Inc.
World Headquarters 5 Polaris Way
Aliso Viejo, CA 92656 USA
1
Installing Foglight for SQL Server
Foglight for SQL Server is Quest Software's powerful diagnostic and problem resolution tool for Microsoft SQL Server databases. This Foglight cartridge delivers a global view that provides a visual instance health check, and enables a drill-down style diagnostic workflow to investigate the performance of monitored SQL Server instances and databases.
Chapter 1—Installing Foglight for SQL Server
Installation Prerequisites
Important For information relevant only to the DIY configuration, see “Prerequisites for the Do It Yourself (DIY) Configuration” on page 8.
Before starting the installation of Foglight for SQL Server, ensure that the following conditions are met:
• Having the required permissions; for details, see “Foglight for SQL Server Permissions” on page 8.
• Using a supported system and platform.
Foglight for SQL Server’s system requirements and supported platforms are identical to those of Foglight Management Server. For details, see Foglight System Requirements and
Platform Support Guide.
• Using a Foglight license that allows cartridges to be installed. Alternatively, use the embedded trial license.
Ensure that the pre-existing Foglight license that is being used allows cartridge installation. If a license that does not allow cartridge installation is used, the installation wizard cannot proceed beyond the Foglight Management Server License screen, which requires specifying the type of license to be installed.
• Using an available external database
Important The available external database requirement is relevant only if you plan to install a new Foglight Management Server:
If you plan to install Foglight Management Server on an external database, ensure that this database is running.
The supported external database platforms and versions are as follows: • SQL Server
• Oracle • MySQL
• Preparing in advance a SQL Server user to be used for monitoring the discovered instances. The SQL Server user is created exclusively for monitoring a specific database, and has limited execution capabilities. The details of this SQL Server user are entered in the Connection Details dialog box when running the Database Discovery wizard (see “Configuring Foglight for SQL Server” on page 14), and this user must be granted the privileges required for monitoring the database. These privileges can be granted only by a user with a SYSADMIN server role.
Managing SQL Server Database Systems Getting Started Guide 8 Chapter 1—Installing Foglight for SQL Server
Prerequisites for the Do It Yourself (DIY) Configuration
Installing the DIY configuration, which allows downloading and running a trial version of the bundled installer (a single executable file that allows installing Foglight Management Server together with Foglight for SQL Server), requires meeting the conditions below.
Important The DIY configuration is available for versions 5.6.5.xx and higher, and is only designed for new installations and not for upgrades.
• Having the required permissions; for details, see “Foglight for SQL Server Permissions” on page 8.
• Using a supported system and platform.
Foglight for SQL Server’s system requirements and supported platforms are identical to those of Foglight Management Server. For details, see Foglight System Requirements and
Platform Support Guide.
• Using a Foglight license that allows cartridges to be installed. Alternatively, use the embedded trial license.
• Using an available SQL Server external database (version 2008 and higher).
Important The available external database requirement is relevant only if you plan to install a new Foglight Management Server:
If you plan to install Foglight repository on an external database, ensure that this database is running.
• Preparing in advance a SQL Server user to be used for monitoring the discovered instances. The SQL Server user is created exclusively for monitoring a specific database, and has limited execution capabilities. The details of this SQL Server user are entered in the Connection Details dialog box when running the Database Discovery wizard (see “Configuring Foglight for SQL Server” on page 14), and this user must be granted the privileges required for monitoring the database. These privileges can be granted only by a user with a SYSADMIN server role.
Note In SQL Server 2000, monitoring the selected instances can be carried out only by a user with a SYSADMIN server role, in which case the Grant Privileges phase is unnecessary.
Foglight for SQL Server Permissions
Foglight for SQL Server can be used for granting permissions on several levels.
This following sections describe the permissions that can be granted to users of SQL Server at each level, and instruct how to manually run the grant privileges script.
Granting Permissions to SQL Server Users
Chapter 1—Installing Foglight for SQL Server
• “Instance-level Permissions” on page 9 • “Database-level Permissions” on page 9 • “Object-specific Permissions” on page 10
Instance-level Permissions
The following permissions are granted at the instance level: • VIEW ANY DEFINITION
• VIEW SERVER STATE
• ALTER TRACE—allows the following operations to be carried out:
• Tracing a specific session—the data retrieved by this operation is displayed on the SQL Activity > Sessions > Session Details > Session Trace pane
• Monitoring deadlocks—the data retrieved by this operation is displayed on the SQL Activity > Deadlocks panel
Database-level Permissions
The following permissions are granted at the database level:
• CREATE USER—the lowest permission level, which only allows accessing each database. • db_datareader—allows user-defined SQL queries to be created for monitoring purposes, via
the User-defined Collections global administration screen. For details, see the User-defined Collections section in Foglight for SQL Server User Guide.
• db_ddladmin—allows DBCC commands to be run for indexes. In the absence of this a permission, no data will be retrieved from the following collections:
• Database Index Density Vectors • Database Index Details
• Database Index Histogram
Managing SQL Server Database Systems Getting Started Guide 10 Chapter 1—Installing Foglight for SQL Server
Object-specific Permissions
The permissions listed below allow users holding them to access specific objects within the master and msdb databases:
• Execute—allows the following objects to be accessed within the master database: • xp_enumerrorlogs
• xp_readerrorlog
• Select—the following objects within the msdb database to be accessed: • log_shipping_monitor_primary • log_shipping_monitor_secondary • log_shipping_primaries • log_shipping_secondaries • sysalerts • syscategories • sysjobactivity • sysjobs • sysjobhistory • dbm_monitor_data
Running the Grant Permissions Script
The file used for granting permissions manually, SQLServerGrantPrivilegesScript.sql, can be downloaded by clicking the link View script under the Instances table, accessible via one of the following methods:
Chapter 1—Installing Foglight for SQL Server
Database Privileges dialog box. For details, see section Carrying out the Advanced Instance Installation Wizard in the Managing SQL Server Database Systems User Guide. In the Connection Details global administration screen, If some of the instances whose verification failed display a status of either Insufficient privileges, Click to Grant or Wrong
sysdba Credentials, clicking this text brings up the Insufficient Database Privileges dialog
box. For details, see section Global Administration > Connection Details in the Foglight
for SQL Server User Guide).
This dialog box allows specifying a SYSAdmin (System Administrator) user with sufficient privileges.
Enter a SYSAdmin user and password, and then click Grant Privileges. Alternatively, click the View script link, to the right of the Grant privileges button, to grant privileges manually via a script
Important Running this file requires having the sysadmin or securityadmin server role.
To manually run the Grant Permissions script:
1 Open the SQLServerGrantPrivilegesScript.sql file in SQL Server Management Studio (SSMS).
2 Find the Select@LoginName = ? section in the beginning of this file.
3 Replace the question mark with the login name to which the requested permissions are to be assigned.
4 Execute the script.
Repeat step 1 to step 4 for each instance to be monitored.
Managing SQL Server Database Systems Getting Started Guide 12 Chapter 1—Installing Foglight for SQL Server
Installing Foglight for SQL Server
Foglight for SQL Server runs on the Foglight Management Server, which is its operation framework. Therefore, Foglight Management Server must be installed before installing the cartridge.
The installation of Foglight for SQL Server can be carried out using either of the following scenarios:
• Installing both Foglight for SQL Server and Foglight Management Server. For details, see “Installing Foglight for SQL Server and Foglight Management Server Together” on page 12. • Installing Foglight for SQL Server as a cartridge from within an existing Foglight
Management Server. For details, see “Installing the Cartridge by Adding it to a Cartridge Inventory of an Existing Foglight Management Server” on page 13.
Installing Foglight for SQL Server and Foglight Management Server Together
Starting from version 5.5.8, a single executable file allows installing Foglight Management Server together with Foglight for SQL Server.
To run the combined installation file:
1 Copy the installation file suitable for the operating system and the bit level (32-bit or 64-bit) to a temporary directory on the requested host.
Note The combined installation file is named using the following convention: Foglight-<version_number>_FoglightForSQL-Server--<version_number>_<operating system>-<bit level><.exe/.bin>
For example: Foglight-5_6_4_0-FoglightForSQL-Server-5_6_4_1_windows-x86_64.exe
2 If planning to use an external database for the Foglight Management Server Installation, ensure that this database is running.
3 Follow the on-screen installation instructions.
Installing the DIY Configuration of Foglight for SQL Server
Starting from version 5.6.8, it is possible to download and install a self trial (DIY) version of Managing SQL Server Database Systems, via the bundled installer (a single executable file that allows installing Foglight Management Server together with Foglight for SQL Server).
To run the combined installation file:
1 Copy the installation file suitable for the operating system and the bit level (64-bit) to a temporary directory on the requested host.
Note The combined installation file is named using the following convention: Foglight-<version_number>_FoglightForSQL-Server--<version_number>_<operating system>-<bit level><.exe/.bin>
For example: Foglight-5_6_8-FoglightForSQL-Server-5_6_5_XX_windows-x86_64.exe
Chapter 1—Installing Foglight for SQL Server
3 Select the Standard installation type.
4 When requested to select a repository database, select a SQL Server repository and enter the required parameters.
5 After the repository installation stage is complete, follow the on-screen installation instructions.
6 When asked o select the requested number of monitored agents, select the option to monitor 1-30 agents.
7 Complete the installation by pressing Done.
Foglight is initialized, with the Databases dashboard as its home page.
Installing the Cartridge by Adding it to a Cartridge Inventory of an Existing
Foglight Management Server
To install Foglight for SQL Server as a cartridge:
1 Copy the cartridge’s car file (DB_SQL_Server-VersionNumber.car; for example,
DB_SQL_Server-5_5_8_127.car), which is included in the installation media, to your local computer.
2 Log in to the Foglight browser interface.
3 In the browser interface, on the navigation panel, click Dashboards > Administration > Cartridges > Cartridge Inventory.
4 On the Cartridge Inventory dashboard, click Browse to find the car file on your local computer.
2
Configuring Foglight for SQL Server
After successful installation of Foglight for SQL Server, when entering the Foglight Management Server, no instances are displayed.
The Foglight for SQL Server agent (DB_SQL_Server) allows monitoring the database performance on each server where the application is installed. The automatic discovery agent (DB_Auto_Discovery_SQL_Server) allows automatic discovery of existing and running instances, which you can then configure for Foglight monitoring. The automatic discovery is carried out via the Database Discovery wizard.
To discover database instances, when no instance is monitored: 1 On the navigation panel, click Homes > Databases.
A blank screen appears, with a message indicating that the Database Discovery wizard should be used for setting up the monitored environment.
2 Click the Monitor more instances button above the Databases view.
You can now choose between adding one instance or discovering one or more instances (recommended).
3 Under Discover one or more, select SQL Server as the database type to configure. 4 Select the Foglight Agent Manager (FglAM) to be used for monitoring the discovered
instances, and click Next. 5 Click Add instances.
6 Select the requested method for discovering database instances:
• Adding a single instance manually—the quickest, most efficient way to add a database instance for monitoring. Use the field provided to specify the instance details in the HOST\INSTANCE syntax.
• Discovering instances by hosts—the most efficient search method for discovering instances over a specified, usually small number of hosts. Use the semicolon-delimited list of the hosts to be scanned.
Chapter 2—Configuring Foglight for SQL Server
• Importing SQL Server connections definitions from a user-created file—this method requires advanced creation of a Regserv file that specifies the instances to be monitored. Note When importing many instances, the recommended convention is
host name-instance name(port). For example: if the host, instance and port names are brkfsx06, 0920864 and 2004, respectively, the agent should be assigned the name brkfsx06-0920864(2004).
7 Select the database instances that you want to monitor. 8 Click Set credentials.
9 Specify the credentials of the selected database instances, both Operating System login and Database login.
If the instance is running on a virtual host, edit the VMWare connection details. 10 Click Monitor to start monitoring the selected instances.
Configuring the Connection to Performance Analysis
If Foglight Performance Analysis is installed, it is possible to benefit from using the Performance Analysis technology (a Quest technology designed to collect SQL statements and performance indicators from SQL Server databases and host operating systems without imposing overhead on the target database itself).
To configure Performance Analysis connection:
1 Select from the Databases table an instance that has Foglight Performance Analysis installed and configured.
2 Click Agent Settings.
The Global Administration view opens.
3 From the menu on the left side of the view, click Connection Details.
The Connection Details global administration screen provides the following options for configuring the Foglight Performance Analysis connectivity for the selected agents:
• Detecting the Foglight Performance Analysis connection—either by using the default port or by specifying one or more ports. When this option is selected, the Foglight
Performance Analysis connectivity is configured to use the default credentials. • Setting user-defined details
Detecting Foglight Performance Analysis connection To detect Foglight Performance Analysis connection:
1 Click Detect Performance Analysis.
Managing SQL Server Database Systems Getting Started Guide 16 Chapter 2—Configuring Foglight for SQL Server
2 Use the field inside this box to replace the default port or to type additional ports. Click Ok when completed,.
3 Click Detect.
Setting user-defined details for the Performance Analysis connectivity. To configure Performance Analysis manually:
1 In the dialog box used for editing the instances’ credentials, click Edit Performance Analysis
settings.
The Edit Performance Analysis settings dialog box appears. 2 Select the check box Enable Performance Analysis.
3 Specify the following details: • The Middleware host • The Middleware port
• The Performance Analysis instance 4 Click Set.
Chapter 2—Configuring Foglight for SQL Server
Layout of Foglight for SQL Server Dashboard
The following diagram shows the available Foglight for SQL Server dashboard navigation paths.
For details on using the SQL Server dashboard, see the Managing SQL Server Database Systems
Managing SQL Server Database Systems Getting Started Guide 18
Index
C
configuration drilldown panels SQL Server configuration 17D
database-level permissions CREATE USER 9 db_datareader 9 db_ddladmin 9 databases drilldown databases chart 17 databases details 17E
error log drilldown panels
Foglight agent error logs 17 SQL agent error logs 17 SQL Server error logs 17
F
Foglight for SQL Server agent 14
Foglight for SQL Server dashboard navigation paths dashboard drilldowns 17 configuration 17 databases 17 error log 17 memory 17 SQL activity 17 support services 17 user metrics 17
global administration view 17 Foglight license
using a license that allows cartridges to be installed 7
I
instance-level permissions
ALTER TRACE 9
VIEW ANY DEFINITION 9 VIEW SERVER STATE 9
M
memory drilldown panels buffer cache 17 procedure cache 17 summary 17methods for discovering database instances adding a single instance manually 14 searching by host name 14
monitoring deadlocks using ALTERTRACE 9
O
object-specific permissions execute 10 select 10S
SQL activity drilldown panels blocking 17 IO by file 17 locks 17 sessions 17 SQL IO activity 17 SQL summary 17 wait statistics 17 SQL Server agentdiscovering existing and running agents 14 SQLServerGrantPrivilegesScript.sql file
downloading
from the connection details screen 11
Index
support services drilldown panels
cluster services 17 DTC 17
full text search 17 log shipping 17 mirroring 17 service status 17 SQL agent alerts 17 SQL agent jobs 17
T
technical support 5 tracing a specific sessionusing ALTERTRACE 9
U
user metrics drilldown panels
