Foglight for SQL Server (Cartridge) 5.9.7.20 - Deployment Guide

SQL PI configured

SQL PI is available only for Oracle, SQL Server, SQL Server BI (Analysis services), and Azure SQL Database. These tables define the Hardware requirements for each server based on the number of monitoring agents

After locating your hardware requirements in each table, ensure that you complete the manual JVM Setting configuration as described in:

CPUs (2.4GHz)* — for a virtual machine the CPU reservation is highly recommended for better performance. The reservation is expressed in MHz.

RAM*- for a virtual machine the memory reservation is highly recommended for better performance.

IMPORTANT: If you are monitoring more than 70 agents running on Windows systems, the monitored hosts should be configured to use WinRM. Monitoring Analysis services is supported only on Agent Managers running on Windows which must have a version of .NET 3.5 installed. It is highly recommended to deploy the SQL PI agents to the Agent Manager where database agents are hosted. IMPORTANT: For each PI domain manager agent, additional 1024MB RAM and JVM are required. The resource for PI repository manager agent can be ignored. IMPORTANT: For the environment with SQL PI configured, a single FglAM can only connect to a single PI repository database per domain and monitor agent number no exceed to 300 in a single domain.

CPUs (2.4GHz)* - for a virtual machine the CPU reservation is highly recommended for better performance. The reservation is expressed in MHz.

RAM*- for a virtual machine the memory reservation is highly recommended for better performance.

Microsoft SQL Server 2016 for Windows* - Requires SP1 or SP2 for SQL Server 2016 Standard edition.

Amazon RDS for SQL Server 2016* - Requires SP1 or SP2 for SQL Server 2016 Standard edition.

NOTE: Support to install PI repository on SQL Server Always On Availability Group. For more information, see KB326158.

Antivirus exclusions is required for the Microsoft SQL Server. For more information refer to the Microsoft’s official article How to choose antivirus software to run on computers that are running SQL Server.

NOTE: When there are multiple repository databases, it is recommended for each to use a separate set of disks. NOTE: For Oracle RAC, only the individual nodes for number of agents are counted instead of the RAC itself.

RAM*- for a virtual machine the memory reservation is highly recommended for better performance.

Hard Drive Space* - the disk RPM is 15K. For 100 DB agents or above, RAID 5 disk (IOPS is above 1.2K) is required if the performance is downgraded. For 600 DB agents, 9K IOPS disk is required.

Raw Data* - raw performance data which is stored in 1-minute SQL PI tables.

Long-Term Data* - the low granularity data roll-up from raw data. Long-term (non 1-minute) data is stored in the primary filegroup in the SQL PI Repository. It is highly recommended to store database transaction log and temp database in a different disk with long-term data for better disk IO.

Transaction Log* - The frequency of the transaction log backup job is 2 hours.

All-in-One Installation

In the All-in-One installation, the Foglight Agent Manager is embedded. The Foglight Repository and SQL PI Repository are in the same SQL Server instance.

CPUs (2.4GHz)* - for a virtual machine the CPU reservation is highly recommended for better performance. The reservation is expressed in MHz.

RAM*- for a virtual machine the memory reservation is highly recommended for better performance.

Hard Drive Space* - the disk RPM is 15K. Extra hard drive space is needed for operating system storage.

SQL PI not configured

The table defines the Hardware requirements based on the number of monitoring agents.

After locating your hardware requirements in the tables, ensure that you complete the manual JVM Setting configuration as described in:

IMPORTANT: For Foglight for DB2 LUW, the number of agents that can be hosted on a Management Server depends on the size of the environment and the amount of memory allocated for the Management Server. Instances, databases, and members each count toward the number of agents. For example, one instance with two databases and two members counts as five agents (1+2+2=5). Therefore, refer to the first column for the size of the environment and the amount of memory required. If you require more agents than can be supported by one Management Server, add additional Management Servers.

CPUs (2.4GHz)* - for a virtual machine the CPU reservation is highly recommended for better performance. The reservation is expressed in MHz.

RAM*- for a virtual machine the memory reservation is highly recommended for better performance.

IMPORTANT: If you are monitoring more than 70 agent running on Windows system, the monitored hosts should be configured to use WinRM. IMPORTANT: For Foglight for DB2 LUW, the number of agents that can be hosted on an Agent Manager depends on the size of the environment and the amount of memory allocated on an Agent Manager. Instances, databases, and members each count toward the number of agents. For example, one instance with two databases and two members counts as five agents (1+2+2=5). Therefore, refer to the first column for the size of the environment and the amount of memory required. If you require more agents than can be supported by one Agent Manager, add additional Agent Managers.

CPUs (2.4GHz)* - for a virtual machine the CPU reservation is highly recommended for better performance. The reservation is expressed in MHz.

RAM*- for a virtual machine the memory reservation is highly recommended for better performance.

Manual configuration requirement for SQL PI Repository

NOTE: Starting with version 5.9.5.20, the back-end Performance Investigator (PI) repository, Infobright is no longer supported, and a new enhanced PI repository based on SQL Server is introduced. For more information about SQL Server based PI repository installation, refer to KB 289910. For how to migrate SQL Server and Oracle database agents from Infobright to SQL Server based PI repository, refer to KB 313191.

Log in to the SQL Server through SSMS client. Edit the Maximum server memory in Server Properties dialog. For SQL PI Agents number less than 50, set the Maximum SQL Server Memory to 16384 MB. For All-in-One installation, refer to Table 4 row Maximum SQL Server Memory. For others, refer to Table 3 row Maximum SQL Server Memory.

Edit the server.config file located under the <Foglight installation directory>\config directory.

For 4096MB, add the following lines:

server.vm.option0 = "-Xms4096m";

server.vm.option1 = "-Xmx4096m";

Edit the baseline.jvmargs.config file located under the <Agent Manager installation directory>\state\default\config\ directory

For 2048MB, add the following lines:

vmparameter.0 = "-Xms2048m";

vmparameter.1 = "-Xmx2048m";

For number of agents > 50, edit these settings: