Foglight for SQL Server Getting Started Guide

Foglight™ for SQL Server 5.7.0

Getting Started Guide

© _{2014 Dell 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 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

This product is protected by U.S. Patents #: 7,979,245 and 8,175,862. Additional Patents Pending. For more information, go to http://software.dell.com/legal/patents.aspx.

Trademarks

Dell, the Dell logo, and Foglight, IntelliProfile, PerformaSure, and Tag and Follow are trademarks of Dell Inc. "Apache Tomcat" and "Tomcat" are trademarks of the Apache Software Foundation. AIX, IBM and WebSphere are trademarks of International Business Machines Corporation, registered in many jurisdictions worldwide. HP is a registered trademark that belongs to Hewlett-Packard Development Company, L.P. Linux is the registered trademark of Linus Torvalds in the United States, other countries, or both. Microsoft, Windows, Active Directory, Internet Explorer, Hyper-V, SharePoint, and SQL Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Oracle, Oracle Solaris, Java, and WebLogic are registered trademarks of Oracle and/or its affiliates. MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries. Novell and eDirectory are registered trademarks of Novell, Inc., in the United States and other countries. Ubuntu is a registered trademark of Canonical Ltd. Mozilla and Firefox are registered trademarks of the Mozilla Foundation. Mac OS and Safari are registered trademarks of Apple Inc. Google and Chrome are registered trademarks of Google Inc. The X Window System and UNIX are trademarks of The Open Group. VMware, ESX, ESXi, vSphere, and vCenter are registered trademarks or trademarks of VMware, Inc. in the United States and/or other jurisdictions. Symantec and Veritas are trademarks or registered trademarks of Symantec Corporation or its affiliates in the U.S. and other countries. Other names may be trademarks of their respective owners. OpenLDAP is a registered trademark of the OpenLDAP Foundation. "Red Hat", "JBoss" and the JBoss logo are registered trademarks of Red Hat, Inc. 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.

Foglight for SQL Server Getting Started Guide Updated - October 2014

Software Version - 5.7.0 Cartridge version 5.7.0

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.

Contents

Introduction to this Guide . . . 4

Installing Foglight for SQL Server . . . 4

Installation Prerequisites . . . 4

Foglight for SQL Server Permissions . . . 5

Granting Permissions to SQL Server Users . . . 5

Running the Grant Permissions Script . . . 6

Installing Foglight for SQL Server . . . 8

Installing Foglight for SQL Server and Foglight Management Server Together . . . 8

Installing the Cartridge by Adding it to a Cartridge Inventory of an Existing Foglight Management Server . . . 8

Configuring Foglight for SQL Server . . . 9

SQL Performance Investigator Extension . . . .10

Operating System Extension . . . .10

VMWare Extension . . . .11

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.

This guide is intended for SQL Server database administrators.

Installing Foglight for SQL Server

Foglight for SQL Server is Dell 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.

This chapter describes the installation of Foglight for SQL Server, from the planning stage up to the actual installation.

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 5. • 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 for SQL Server Management Server License screen, which requires specifying the type of license to be installed.

• Using an available external database

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

IMPORTANT: The available external database requirement is relevant only if you plan to install a new Foglight Management Server:

• 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 9), 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.

Foglight for SQL Server Permissions

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

• Instance-level Permissions on page 5 • Database-level Permissions on page 5 • Object-specific Permissions on page 6

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-User-defined Collections section in Managing SQL Server Database Systems: User and Reference 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

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.

Figure 1. Click on a specific row on the Databases > Indexes pane to retrieve data from these collections.

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:

• At the end of the installation process, when the Status column of the instance table displays the status Insufficient database privileges, clicking this text brings up the Insufficient Database Privileges dialog

box. For details, see section Carrying out the Advanced Instance Installation Wizard in the Managing SQL Server Database Systems: User and Reference 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 Managing SQL Server Database Systems: User and Reference Guide).

Figure 2. If instance verification fails, an error is dislayed.

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

To manually run the Grant Permissions script:

1 Go to Dashboards > Administration > Cartridges > Components for Download. 2 Double-click DB_SQL_Server_Cli_Installer.

3 Click Download.

4 Extract the DB_SQL_Server_Cli_Installer.zip file. 5 Copy the following files:

• MSSQLPermissionsCheck.sql • MSSQLPermissionsGrant.sql

6 Open the SQLServerGrantPrivilegesScript.sql file in SQL Server Management Studio (SSMS). 7 Find the Select@LoginName = ? section in the beginning of this file.

8 Replace the question mark with the login name to which the requested permissions are to be assigned. 9 Execute the script.

Repeat step 1 to step 4 for each instance to be monitored.

IMPORTANT: Running this file requires having the sysadmin or securityadmin server role. View script

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

• 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 8.

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.

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 (VersionNumber.car; for example, DB_SQL_Server-5_6_7_508.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. 5 Click Install Cartridge.

IMPORTANT: 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_11_0-FoglightForSQL-Server-5_6_7_508_windows-x86_64.exe

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 you to monitor the database performance on each server where the application is installed. The agent installer allows monitoring of existing and running instances for Foglight monitoring.

To discover database instances, when no instance is monitored: 1 On the navigation panel, under Homes, click Databases.

A blank screen appears, with a message indicating that the agent installer should be used for setting up the monitored environment.

2 Click Add > SQL Server in the upper left corner of the Databases View. The Monitor SQL Server Instance dialog box apears.

3 Choose the agent manager on which the agent will be running The default is the agent manager with the least agents installed.

a Click the Monitoring agent will run from host <agent manager> link located in the bottom left corner of the dialog box.

A dialog box appears with a list of all agent managers connected to the Foglight management server.

b Select the appropriate host name and click Set.

4 On the Monitor SQL Server Instance pane, provide connection details by typing:

• Server name — for the default instance, specify the host name. If you have more than one instance on the server you must name the instances using the format: domain\user name. • Port — Optional. This field can be left empty, unless the TCP/IP connection port is not the default

port: 1443.

5 Specify the SQL Server credentials using one of the following authentication methods:

• Windows Authentication — log in using a Windows user account. The user name should be typed in the following format: domain\user name

• SQL Server Authentication — log in using a SQL Server account • Use the Windows account running your agent

6 Optional — In the Monitoring Extensions pane, click the SQL PI monitoring extension. You are prompted to choose the Agent Manager on which the SQL PI repository will be installed.

7 Optional —In the Monitoring Extensions pane, click the Operating System link.

To configure the extension, choose the connection details of the host on which the SQL Server instance is running:

• Log in to the host using the same account used for monitoring SQL Server — This option is set by default when the SQL Server connection details are of types: Windows Authentication or Using the Windows account running your agent manager.

• Log in to the host using different login credentials - logging in through a Windows user account. The user name should be entered in the domain\username format (for example,

COLUMBIA\JSmith).

8 Optional — In the Monitoring Extensions pane, click Collect VM statistics.

To configure the extension, choose the connection details of the vCenter or ESX on which the SQL Server instance is running:

• The name of IP address of the vCenter server that hosts the SQL Server instance virtual machine, or the name of its parent ESX server

• The port number used by the vCenter server system or by ESX server for listening to the connections from the vSphere Client (default: 43).

• The name and password of the user that has the privileges required for connecting to the vCenter server or ESX server and retrieving information.

9 Click Monitor.

10 When the installation completes successfully, the Monitoring Initialized Successfully dialog box appears. Click Add another Database or Finish to exit.

SQL Performance Investigator Extension

SQL Performance Investigator allows you to rapidly identify bottlenecks, anomalies, and application trends by focusing on top resource consumers and providing multi-dimensional SQL domain drilldowns. SQL PI allows you to:

• Monitor real-time SQL Server database performance at a glance • Gather and diagnose historical views

• Identify and ananticipate performance issues • Analyze and optimize execution plan changes

• Compare day-to-day values to identify anomalies and application changes

Operating System Extension

Monitoring the operating system allows you to identify resource consumption and provides a full view of the server health. An Infrastructure agent, which is created automatically as part of the monitoring process, monitors the operating system.

IMPORTANT: If the monitoring verification fails click the message that is displayed on the Status column and resolve the issue according to the instructions that appear in the dialog box. For example, (for example: insufficient privileges, incorrect credentials or an Agent Manager that reached its full monitoring capacity.

NOTE: The Use this option to discover your SQL-Server instances link is for discovering and enabling several instances in one monitoring process. By searching and discovering the Instances on the network through an IP range, Host's or specific IP's or by importing from SQL Server predefined Regserv file.

NOTE: SQL Performance Investigator requires a license. If you are using a trial version and would like to request pricing, contact https://software.dell.com/register/57891

SQL PI requires a repository database that is installed automatically on the Agent Manager.

VMWare Extension

Monitoring the VMWare system allows you to identify resource consumption and provides a full view of the data center and ESX health when the server is part of a VMWare environment.

Layout of Foglight for SQL Server Dashboard

• Global Administration View • Reporting Services

• Foglight for SQL Server Drilldowns

Table 1. The available Foglight for SQL Server drilldown paths

Drilldown Panels within Drilldown

SQL Performance Investigator

• Investigate, which contains: • Performance Tree • History • Change Tracking • Lock Analysis • Execution Plan • Compare Memory • Summary • Buffer Cache • Plan Cache Activity • SQL Instance Summary • SQL IO Activity • Sessions • Locks • Blocking (Current) • Deadlocks • IO by File Databases • Databases Chart

• Database Details, which contains: • Summary

• File Groups • Data Files • Transaction Logs • Log Files

• Tables and Indexes • Disk Space

For details on using the SQL Server dashboard, see the Managing SQL Server Database Systems User and Reference Guide. Services • Services Status • SQL Agent Jobs • SQL Agent Alerts • DTC

• Full Text Search • Reporting Services HADR • Log Shipping • Cluster • Mirroring • Always On Logs

• SQL Server Error Logs • SQL Agent Error Logs • Foglight Agent Error Logs

Configuration • SQL Server Configuration

User-defined • Performance Counters

• Collections

About Dell

Dell listens to customers and delivers worldwide innovative technology, business solutions and services they trust and value. For more information, visit www.software.dell.com.

Contacting Dell

Online support

Product questions and sales: (800) 306-9329

Email:

[email protected]

Technical support resources

Technical support is available to customers who have purchased Dell software with a valid maintenance contract and to customers who have trial versions. To access the Support Portal, go to

https://support.software.dell.com/.

The Support Portal provides self-help tools you can use to solve problems quickly and independently, 24 hours a day, 365 days a year. In addition, the portal provides direct access to product support engineers through an online Service Request system.

The site enables you to:

• Create, update, and manage Service Requests (cases) • View Knowledge Base articles

• Obtain product notifications

• Download software. For trial software, go to Trial Downloads. • View how-to videos

• Engage in community discussions • Chat with a support engineer