Chat now with support
Chat with Support

Foglight for SQL Server (Cartridge) 5.9.5.10 - User Guide

Introduction to this Guide Using Foglight for SQL Server
Viewing the Databases Dashboard Overview Dashboard Advisories Dashboard Monitoring Data Replication Monitoring SQL Performance Reviewing Memory Usage Reviewing the Instance Activity Reviewing Database Usage Reviewing the Services Using the HADR Drilldown Using the Logs Drilldown Reviewing Configuration Settings Viewing User-defined Performance Counters and Collections
Monitoring Business Intelligence Services Administering Foglight for SQL Server
Configuration Settings Managing Foglight for SQL Server Agent Settings Reviewing Foglight for SQL Server Alarms Generating Reports Monitoring SQL Server instances on VMware servers
Glossary
A
Access methods Adhoc SQL Plans Alarm Alert Allow updates Anonymous subscription Authentication AutoClose AutoGrow Automatic Discovery AutoShrink B Batch BCP (Bulk Copy Program) Blocking Books Online Bound trees Buffer Buffer cache Buffer pool Bulk copy Bulkinsert Bulk load C Cache CAL Calibration Cardinality Cartridge Chart Checkpoint process Client network utility CLR Compile Connect Connection Connectivity software CPU Usage Cursors D Data access components Data file Data storage engine Database Database object DataFlow DBCC DBID DBO Deadlock Disk queue length Disk transfer time Disk utilization DiskPerf Distributing instance Distributor DMO Drilldown DTC DTS E Error log Event alert Execution contexts Extended stored procedures Extent External procedures F File Filegroup File cache Foglight Agent Manager Foglight Management Server Foreign key Forwarded records Free buffers Free list Free pages Free potential Free space Freespace scans Full text search G GAM Ghosted records Growth increment GUID H Hash buckets Hashing Heap Hit rate Hit ratio Host name Host process I I/O Index Indid Integrated security Intent Locks J Job K Kernel memory Kernel mode Kill L Latch Lazy writer Licensing Lightweight pooling Lock Lock area Lock escalation Lock mode Log Log cache Log writer Logical I/O LRU LSN M Master MaxSize MDAC Metric Misc.normalized trees Model Monitor page file N Named pipes Net library NIC Null O OBID Object plans OLAP OLAP service OLTP Optimizer Optimizer cache osql P Page life expectancy Paging Panel Parse Parser Per seat licensing Per server licensing Performance alert Physical I/O Physical read Physical write PID Pinned Plan Plan cache Potential growth Prepared SQL plans Primary key Privileged mode Procedure cache Procedure plans Process Profiler Publication database Publisher Publisher databases Publishing server Pull subscription Pulse Push subscription Q Query plan R RAID Random I/O Read ahead Recompile Referential integrity Relational data engine Replication procedure plans Role Rollback S sa Schema locks Sequential I/O Session Severity SGAM Shared locks Show advanced options SMP Sort, Hash, Index Area SPID Spike Spinner SQL Agent Mail SQL Mail SQL Plans SQL Server Agent SQL Server authentication SQL Server books online Standard deviation Stolen pages Stored procedure Support service SYSADMIN role T TDS TempDB Temporary tables and table variables Threshold Torn page detection Transaction Trigger Trigger plans Truncate Trusted U UMS Unused space User connection area User mode V Virtual log file VLF W Waitfor Windows authentication mode Working set
Reference
SQL PI Repository Cold Backup Procedure SQL Performance Investigator Metrics
Active Time All SQL Agents CPU Usage All SQL Agents Resident Memory Usage Availability Average Physical I/O Operations Average SQL Response Time Backup Recovery Wait Blocked Lock Requests Checkpoint Pages CLR Wait CPU Usage CPU Wait Cursor Synchronization Wait Database Replication Wait Deferred Task Worker Wait Degree of Parallelism Disk Utilization DTC CPU Usage DTC Resident Memory Usage Distributed Transaction Wait Executions Ended Executions Started External Procedures Wait Full Scans Full Text Search CPU Usage Full Text Search Resident Memory Usage Full Text Search Wait Free Buffer Wait Hosted Components Wait IO Bulk Load Wait IO Completion Wait IO Data Page Wait IO Wait Latch Buffer Wait Latch Wait Latch Savepoint Wait Lazy Writes Lock Wait Lock Bulk Update Wait Lock Exclusive Wait Lock Intent Wait Lock Requests Lock Schema Wait Lock Shared Wait Lock Update Wait Lock Wait Log Buffer Wait Log Flushes Log Other Wait Log Synchronization Wait Log Wait Log Write Wait Memory Wait Network IO Wait Network IPC Wait Network Mirror Wait Network Wait Non SQL Server CPU Usage Non SQL Resident Memory Usage OLAP CPU Usage OLAP Resident Memory Usage OLEDB Provider Full Text Wait Other CPU Usage Other Miscellaneous Wait Other Wait Overall CPU Page Life Expectancy Page Splits Parallel Coordination Wait Physical I/O Physical Memory Used Physical Page Reads Physical Page Writes Probe Scans Plan Cache Hit Rate Range Scans Rec Ended Duration Remote Provider Wait Run Queue Length Samples Service Broker Wait Session Logons Session Logoffs SQL Agent CPU Usage SQL Agent Resident Memory Usage SQL Executions SQL Mail CPU Usage SQL Mail Resident Memory Usage SQL Recompilations SQL Response Time SQL Server Background CPU Usage SQL Server Cache Memory SQL Server Connections Memory SQL Server Connections Summary SQL Server Foreground CPU Usage SQL Server Resident Memory Usage SQL Server Swap Memory Usage Synchronous Task Wait Table Lock Escalation Target Instance Memory Total CPU Usage Total Instance Memory Virtual Memory Used
Rules Collections and Metrics
SQL Server Agent's Default Collections Access Methods Agent Alert List Agent Job List Always On Availability Groups Backup Locations Blocking History Blocking List Buffer Cache List Buffer Manager CLR Assemblies Cluster Summary Configuration Database Index Density Vectors Database Index Details Database Index Fragmentation Info Database Index Histogram Database Index List Database Information Database Properties Database Sessions (Session List) Database Summary Database Tables List Databases Deadlock DTC Information Error Log Error Log List Error Log Scan File Groups File Data Flow Statistics File Groups Files Files Drive Total Files Instance Summary Full Text Catalog InMemory OLTP (XTP) Instance Wait Categories Instance Wait Events Job Messages Latches and Locks Lock Statistics Locks List Log Shipping Log Shipping Error Logical Disks Memory Manager Mirroring Mirroring Performance Counters Missing Indexes Plan Cache Distribution Plan Cache List Replication Agents Replication Agent Session Actions Replication Agent Session Merge Articles Replication Agent Sessions Replication Agent Sessions by Type Replication Available Replication Publications Replication Subscriptions Reporting Services Resource Pool Session Data Session Trace SQL PI Instance Statistics SQL Server Connections Summary SQL Server Global Variables SQL Server Host SQL Server Load SQL Server Services SQL Server Throughput SQL Server Version Info SSIS OS Statistics SSIS Summary Statistics Top SQLs Top SQL Batch Text Top SQL Long Text Top SQL Plan Top SQL Short Text Top SQL Summary Traced SQL PA Usability User-defined Performance Counters User-defined Queries Virtualization XTP Session Transactions Statistics

Configuring Multiple Instances for Monitoring using Silent Installation

To add multiple agents using the silent installation through a command line interface:
1
Navigate to Administration > Cartridges > Components for downloads.
2
Click the DB_SQL_Server_CLi_Installer name to download the installer.
The Download Message dialog box appears.
3
Review the details in this dialog box, and click Download.
The downloaded ZIP file contains the following files:
•
README.txt file
•
mssql_cli_installer.groovy — a groovy script file that runs the silent installation. This file should be copied to the <FMS_HOME>/bin directory.
•
silent_installer_input_template.csv — a template file that should serve as the basis for inserting contents into the input CSV file. This file can be copied to any folder of your choice, if the path indicated by the <csv_instances_file_name> parameter (see below) points to the selected path. For details about the contents of this file, see Contents of the Input CSV File .
•
MSSQLPermissionsCheck.sql — this file contains the SQL that the user should run on the monitored instance to check permissions needed for monitoring
•
MSSQLPermissionsGrant.sql — this file contains the SQL that the user should run on the monitored instance in order to grant the instance the required permissions for monitoring.
4
Extract the ZIP file to a directory of your choice.
5
Copy the mssql_cli_installer.groovy file to the <FMS_HOME>/bin directory.
6
Go to the command line and run the command: <FMS_HOME>/bin/fglcmd -srv <fms_host_name> -port <fms_url_port> -usr <fms_user_name> -pwd <fms_user_password> -cmd script:run -f mssql_cli_installer.groovy fglam_name <fglam_name> instances_file_name <csv_instances_file_name> lockbox_name <lockbox_name> lockbox_password <lockbox_password>
The descriptions of the flags and parameters in this command are as follows:
•
<FMS_HOME>: The Foglight Management Server installation directory.
•
<fms_host_name>: Name of the host where the Foglight Management Server is installed.
•
<fms_url_port>: The Foglight Management Server port.
•
<fms_user_name>: The user name used for connecting to the Foglight Management Server.
•
<fms_user_password>: The password of the specified user.
•
<fglam_name>: The name of the selected FglAM where new agents are to be added.
•
<csv_instances_file_name>: The full path and the name of the input CSV file.
•
<lockbox_name>: Optional — specifies the name of an existing lockbox to be used.
•
<lockbox_password>: Optional — the selected lockbox's password.
7
After the installation process completes, review the CSV files that are created in the same folder where the input file resides:
•
A file with the input file's name and _status suffix (for example: if the input file is named input, this file is named **input_new_-_**).
This file indicates the monitoring situation of all SQL Server instances listed in the input CSV file. If monitoring failed for one or more of the instances, the file specifies the error message.
The _status file includes the name of the monitored Foglight for SQL Server agent, the result of the monitoring validation process — MONITORED, FAILED, or AGENT EXISTS — and the error message, in case the agent creation failed.
 
* 
IMPORTANT: If all instances listed in the input CSV file passed the monitoring validation successfully, no additional file is created.
•
A file with input file's name and _new suffix (for example: if the input file is named input, this file is named input_status).
This file, which is only created if one or more instances failed the monitoring validation, contains all details about these instances.
Using this CSV file, the user only needs to fix the errors and re-run the script, this time by specifying this file as the input file, that is: the original filename with suffix _new. In the example used here, the new file should be named input_new_new.csv.

Contents of the Input CSV File

The input CSV file contains the following fields, which are used as columns in the resulting file:

 
 
Table 1. Input CSV File
Name

Description

Host name

The SQL Server host name.

Instance name

The name of the SQL Server instance.

DB port (Optional)

The SQL Server instance port. If no value is inserted in this field, the port number should match the instance name.

DB credentials Type (Optional)

DB authentication type. The possible values are:

•
Windows_Default_Account
•
Windows_Custom_Account
•
SQL_Server_Authentication

If this field is left empty, the Windows_Default_Account option is used.

DB user name (Optional)

The user name required for connecting to the database. If the Windows_Default_Account value was selected, this field can be left empty.

DB password (Optional)

The password required for connecting to the database. If the Windows_Default_Account value was selected, this field can be left empty.

DB power user credentials Type (Optional)

The type of database authentication of a user that can grant privileges to the regular database user. The possible values are:

•
Windows_Default_Account
•
Windows_Custom_Account
•
SQL_Server_Authentication.

If this field is left empty, the grant privileges script will not run.

DB power user name (Optional)

The user name of a user that can grant privileges to the regular database user required only if the regular user does not have sufficient privileges.

DB power password (Optional)

The password of the database power user name mentioned above; required only if the regular user does not have sufficient privileges.

Should monitor OS

Specifies whether to create an Infrastructure Cartridge agent for the given host name; the possible values are either True or False.

OS credentials Type (Optional)

OS authentication type; can have one of the following values:

•
Windows_Default_Account
•
Windows_Custom_Account
•
Windows_Local_User: log in to Windows with the username and password running FglAM.
•
UX_Login_Password: log in to Unix through SSH with username and password.

If this field is left empty, the Windows_Default_Account value is used by default.

OS user name (Optional)

The user name required for connecting to the OS if the Windows_Default_Account option was selected. The user name should be compatible with the selected authentication type.

If the Windows_Default_Account value was selected, this field can be left empty.

OS password (optional)

The password of the OS user specified above; if the Windows_Default_Account option was selected, this field can be left empty.

Credential Name (Optional)

When using this property, the 'OS User Name', 'OS Password', 'Private Key File Path' and 'Passphrase' properties will be ignored.

Enable VMware Collection (Optional)

Specifies whether to monitor VMware metrics; the possible values are either True or False.

VMware Host (Optional)

The VMware host name.

VMware Port (Optional)

The VMware port. If no value is inserted in this field, the port number will use the default port number 443.

VMware User Name (Optional)

The user name required for connecting to the VMware.

VMware Password (Optional)

The password of the VMware user specified above.

Enable PI (Optional)

Indicates whether to enable PI; the possible values are True or False. Leaving this field empty indicates a False value.

A PI repository must be already installed and assigned to at least one agent on the monitoring FglAM.

Is Mounted (Optional)

Indicates whether the given monitored instance is in a mounted stand-by mode (data-guard); the possible values are True or False. Leaving this field empty indicates a False value.

Group Names (Optional)

The database group names you want to add the monitored instance to; Multiple group name should separate with ";". For example: groupA;groupB;groupC

When the group already exists, then the monitored instance will be attached to the existing group. When the group does not exist, then new group will be created under the default group "Database Groups".

Configuring the On Demand Data Port on the Agent Manager Concentrator

If a firewall is installed between the Management Server and the Agent Manager, a bidirectional On-Demand Data Port connection is not possible because On-Demand data requests require direct connection.

This issue can be resolved by creating an On-Demand Data Port on the concentrator — an Agent Manager instance that works similarly to an HTTP proxy. This On-Demand Data Port can be configured to accept On-Demand data requests from the Management Server and direct them to the Agent Manager instances (called downstream instances). For additional details about the concentrator, see Configuring an Agent Manager Instance as a Concentrator in the Agent Manager Installation Guide.

Figure 3. On-Demand Data Port connection with firewalls installed.

A concentrator agent, created specifically for Foglight for SQL Server, allows controlling the On-Demand Data Port so that the server is initialized (starts collecting data) when the agent starts, and stops when data retrieval by the agent ends. The agent, which the user creates manually, requires configuring the following parameters:

•
A list of the downstream hosts that are connected through the concentrator
•
The RMI port number

Each time the Foglight Management Server sends RMI request, the list of downstream hosts submitted by the concentrator agent is being searched, as follows:

•
Inclusion of the target RMI server host name within this list indicates that a FglAM concentrator exists, in which case a connection is made to the proxy RMI server.
•
If the list does not contain the target host name, the connection is made directly to the target On-Demand data port server.
To create the concentrator agent:
1
Go to Dashboards > Administration > Agents > Agent Status.
2
Click Create Agent.
3
Select the agent type DB_SQL_Server_Concentrator.
4
Enter a name of your choice.
5
Click Create.
6
After the creation process is completed successfully, click OK.
To edit the concentrator agent connection details:
1
Select the newly created concentrator agent.
2
Click Edit Properties.
3
Click Modify properties for this agent only.
4
Enter the number of the proxy On-Demand Data Port server port, or accept the default number.
5
Enter the concentrator Foglight Agent Manager host name (optional).
6
Click Edit to edit the list of downstream Agent Managers.
7
Click Add Row.
8
Enter the name of the requested Foglight Agent Manager host.
 
* 
IMPORTANT: When editing the properties of the Foglight Agent Manager concentrator (Dashboards > Administration > Agents > Agent Status), the name of the Foglight Agent Manager should be entered in the Downstream FglAMs section exactly as it appears in the topology, under Home > Agents > All agents > <Agent name> > RMI data > FglAM host property. The <Agent name> parameter refers to the Foglight for SQL Server agents that reside on the selected Foglight Agent Manager.
9
Repeat Step 7 to Step 8 as many times as required.
10
Click Save Changes.
11
Click Save at the lower right corner of the screen.
12
Click Back to Agent Status.
13
Select the newly created concentrator agent.
14
Click Activate.
15
Click OK to complete the process.

In an environment that includes a Foglight Agent Manager concentrator, when upgrading the environment where the concentrator does not contain any regular agents, the concentrator is not upgraded. Therefore, after the upgrade process takes place the Foglight Agent Manager concentrator has to be deployed manually.

 

 

Using Foglight for SQL Server

Foglight for SQL Server monitors the SQL Server database activity by connecting to and querying the SQL Server database. The agents provided monitor the SQL Server database system. The dashboards included with the cartridge provide a visual representation of the status of the major components of the SQL Server agents. They allow you to determine any potential bottleneck in database performance.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating