5.6.4
for SQL Server
Foglight
®
© 2012 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, and vFoglight 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 May 2012
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
Foglight for SQL Server Permissions... 7
Granting Permissions to SQL Server Users... 8
Running the Grant Permissions Script ... 9
Installing Foglight for SQL Server ... 12
Installing Foglight for SQL Server and Foglight Management Server Together... 12
Installing the Cartridge by Adding it to a Cartridge Inventory of an Existing Foglight Management Server ... 12
Configuring Foglight for SQL Server...13
Configuring the Connection to StealthCollect... 14
Layout of Foglight for SQL Server Dashboard ... 16
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.
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 http://www.quest.com/support.
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: http://www.quest.com/ support.
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
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 7.
• 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.
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 13), 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.
Managing SQL Server Database Systems Getting Started Guide 8 Chapter 1—Installing Foglight for SQL Server
Granting Permissions to SQL Server Users
Permissions are granted on several levels, as described in the following sections: • “Instance-level Permissions” on page 8
• “Database-level Permissions” on page 8 • “Object-specific Permissions” on page 9
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
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:
Managing SQL Server Database Systems Getting Started Guide 10 Chapter 1—Installing Foglight for SQL Server
• In the Connection Details global administration screen (see section Global Administration
> Connection Details in the Foglight for SQL Server User Guide).
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.
View script link
Chapter 1—Installing Foglight for SQL Server
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 12.
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 Managing Microsoft SQL Server Database Systems.
To run the combined installation file:
1 Copy the installation file appropriate to 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>_install_<operating system>-<instruction set><bit
level>_SQL_Server<.exe/.bin>
For example: install_windows-ia32_SQL_Server.exe or Foglight-5_5_8-install_solaris-sparc64_SQL_Server.bin
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 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 Discover more databases link at the upper right corner of the Databases view. 3 Select SQL Server as the database type to configure.
4 Select the option Discover database instances and click Next.
Note If your monitored environment is utilizing Performance Analysis StealthCollect
technology, the other operation that can be selected via this screen, namely: configuring the connection to StealthCollect, should be carried after the instance discovery is completed. For further details, see “Configuring the Connection to StealthCollect” on page 14.
5 Select the Foglight Agent Manager (FglAM) to be used for monitoring the discovered instances, and click Next.
6 Select the requested method for discovering database instances:
• 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.
• 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.
Managing SQL Server Database Systems Getting Started Guide 14 Chapter 2—Configuring Foglight for SQL Server
• Importing SQL Server connections definitions from a user-created file—this method requires advanced creation of a CSV 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 Click Next.
8 In the Instance Connectivity Settings screen that appears now, add the instances via either of the following methods:
• Manual addition via the Add instance button—if this option was selected in the previous screen
• Discovery via the Start discovery button—for all other options 9 Select the database instances that you want to monitor.
10 Specify the credentials of the selected database instances, both Operating System login and Database login.
11 Verify the requested instances’ connectivity. 12 Start monitoring the selected instances.
Configuring the Connection to StealthCollect
If Performance Analysis is installed, it is possible to benefit from using the StealthCollect 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 StealthCollect connection:
1 In the Specify Operation screen (the first screen of the database discovery wizard), select the option Configure StealthCollect connection.
2 Click Next.
A progress bar appears, while the wizard loads the instances for which StealthCollect can be configured, which can be either of the following:
• The newly monitored instances that had just been discovered (if configuring the connection to StealthCollect is carried out as the last stage of the database discovery wizard);
or
• All of the currently monitored Foglight for SQL Server instances (if the connection to StealthCollect is selected from the Specify Operation screen).
After the loading process is complete, the Connection to StealthCollect screen appears, displaying all of the newly monitored or currently monitored instances.
Chapter 2—Configuring Foglight for SQL Server
3 Select the check boxes near the instances that are to be connected to StealthCollect. By default, the connection to StealthCollect is carried out via port 3566.
To search for StealthCollect on this port, click Detect StealthCollect. To configure
connection to StealthCollect via another port, click the link Click to change and use the Set StealthCollect Ports dialog box to type the numbers of the requested ports.
4 If StealthCollect was not detected in some of the selected agents, as indicated in the Status column, ensure that these agents indeed have StealthCollect installed and running, in which case try to set another StealthCollect port; otherwise clear the check box near these agents. 5 Click Enable StealthCollect.
The progress bar Updating StealthCollect Details in Agents appears.
At the end of the update process, the StealthCollect column indicates that the agent is configured for connection with StealthCollect.
Managing SQL Server Database Systems Getting Started Guide 16 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.
Index
C
configuration drilldown panels SQL Server configuration 16D
database-level permissions CREATE USER 8 db_datareader 8 db_ddladmin 8 databases drilldown databases chart 16 databases details 16E
error log drilldown panels
Foglight agent error logs 16 SQL agent error logs 16 SQL Server error logs 16
F
Foglight for SQL Server agent 13
Foglight for SQL Server dashboard navigation paths dashboard drilldowns 16 configuration 16 databases 16 error log 16 memory 16 SQL activity 16 support services 16 user metrics 16
global administration view 16 Foglight license
using a license that allows cartridges to be installed 7
I
instance-level permissions
ALTER TRACE 8
VIEW ANY DEFINITION 8 VIEW SERVER STATE 8
M
memory drilldown panels buffer cache 16 procedure cache 16 summary 16methods for discovering database instances adding a single instance manually 13 searching by host name 13
monitoring deadlocks using ALTERTRACE 8
O
object-specific permissions execute 9 select 9S
SQL activity drilldown panels blocking 16 IO by file 16 locks 16 sessions 16 SQL IO activity 16 SQL summary 16 wait statistics 16 SQL Server agentdiscovering existing and running agents 13 SQLServerGrantPrivilegesScript.sql file
downloading
from the automatic discovery wizard 9 from the connection details screen 10
Managing SQL Server Database Systems Getting Started Guide 18 Index
support 5
support services drilldown panels
cluster services 16 DTC 16
full text search 16 log shipping 16 mirroring 16 service status 16 SQL agent alerts 16 SQL agent jobs 16
T
technical support 5 tracing a specific sessionusing ALTERTRACE 8
U
user metrics drilldown panels
