Foglight for SQL Server monitors SQL Server database activity by connecting to and querying the database. The provided agents monitor the SQL Server database system, and the dashboards included with the cartridge offer a visual representation of the status of the major components of the SQL Server agents. They enable you to identify potential bottlenecks in database performance.
This section covers the following key areas:
During the installation process, you can choose to install and configure one or more monitoring extensions. These extensions provide a more in-depth analysis of the monitored instance and the environment in which it is running, creating a comprehensive and unified status.
SQL Performance Investigator (PI) allows you to rapidly identify bottlenecks, anomalies, and application trends by focusing on top resource consumers and providing multi-dimensional SQL domain dashboards.
SQL PI allows you to:
The SQL-PI extension is installed by default.
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.
The Operating System extension is enabled by default.
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.
Enabling Foglight Cloud to monitor SQL Server instances requires the creation of the Foglight agents that monitor these instances and ensuring that these agents communicate properly with Foglight Cloud .
Foglight for SQL Server provides a graphic, intuitive method for creating and configuring multiple agents, which can be used instead of Foglight’s default method for creating agents and editing their properties using the Agent Administration dashboard.
Foglight for SQL Server allows running a wizard that provides a common entry point for adding and discovering all database instances within a user-specified range, and then configuring these instances for monitoring.
To run the instance installation wizard:
On the Welcome page, click Monitor Databases or click Databases in the navigation pane. On a fresh installation, no instances are listed for SQL in the Databases dashboard. The Monitor SQL Server Instance dialog box appears.
If a user-defined database group is selected, the databases table’s title displays the name of this group instead of All; however, all newly discovered or created databases are added to the general (All) group of databases.
Choose the agent manager on which the agent is running. The default is the agent manager with the least agents installed.
You have the option to set this host as the default for all future installations.
On the Monitor SQL Server Instance dialog box, enter the following details:
Specify the SQL Server credentials using either of the following authentication methods:
To authenticate with a gMSA account, select Use the Active Directory account running your agent manager. This states that your Agent Manager is configured to run as a Windows Service logged on with the gMSA account. The gMSA account requires access to the SQL instance.
Use SSL: Select whether an SSL connection should be used.
In the Configuration section, select the Alarm Template to use for this instance. The Factory Template will be applied by default. For more information, refer to Viewing, Creating, and Managing Alarm Templates.
(Optional) - In the Monitoring Extension 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:
(Optional) - In the Monitoring Extensions pane, click Collect VM statistics. To configure the extension, select the connection details of the vCenter or ESX on which the SQL Server instance is running:
Click Monitor.
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, insufficient privileges, incorrect credentials or an Agent Manager that reached its full monitoring capacity.
When the installation completes successfully, the Monitoring Initialized Successfully dialog box appears. Click Add another Database or Finish to exit.
You can use the instance installation wizard to discover existing instances. This option allows you to discover instances and monitoring them by entering several methods:
To discover additional instances to monitor:
Click the SQL Server tab in the middle of the Databases View, and then click Monitor. The Monitor SQL Server Instance dialog box appears.
Click the Use this option to discover multiple SQL-Server instances link. The Select an Agent Manager dialog box appears.
Choose an Agent Manager host, click Validate to validate the Agent Manager system resources, and then click Next.
At the bottom of the pane you can select the Set this Agent Manager as the default checkbox if you want a default agent manager for all future installations.
Click Add instances and select one of the following methods for adding instances:
After the discovery process is successfully completed, the newly discovered instances appear on the table, with the status ‘Set credentials’.
Click the check boxes beside the instances whose credentials are to be configured.
Click Set credentials to provide the instances' log in credentials and monitoring configuration.
Follow the steps described in Installing and Monitoring a Single SQL Server Instance, starting with Step 5 to enter the monitoring credentials and enable the monitoring extensions for this instance.
For Amazon RDS SQL Server and Azure managed instance, the method of discovering instances by hosts or by IP range is unsupported in step 4.
To use stored credentials when enabling the Operating System monitoring extension, click Operating System > Select from stored credentials link to open the Stored Credentials dialog box.Here you can review the log in credentials and authentication methods used for logging in to Foglight. Foglight stores encrypted credentials in lockboxes, which may be password-protected for added security. Database agents store all user log in credentials in a default lockbox called DB-Agent Lockbox. If credentials have already been entered in another lockbox, use the Lockbox list to select from that lockbox.
Click Monitor The Monitoring progress bar appears. At the end of this process, the Status column of the instance table displays either the status Monitored for the instances that connected successfully to the database, or a status that indicates failure of the connectivity verification process and the reason for the failure. Click this text to view a dialog box that allows changing the credentials or to grant privileges, depending on the message that appears on the Status column:
Click Insufficient Database Privileges. The Insufficient Database Privileges dialog box appears. This dialog box allows you to specify 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 with a script.
Click the message’s text. A dialog box that shows the message details appears. Click Show details to view the error description. Click Set credentials to display the dialog box used for entering the credentials, and enter the required changes.
To add instances manually:
If no port is specified, the wizard uses the port provided by the SQL Browser service (dynamic port), in which case this service must be started. After the instance monitoring configuration is complete, instances whose port was provided by the SQL Browser service have the number 0 displayed in their Port row.
Searching by host is the most efficient search method for discovering instances over a specified, small number of hosts.
To search by host:
Ensure that you enter host names and not IP addresses. IP addresses are supported only when searching by IP range, as detailed in section Discovering instances by IP range.
The table lists all the hosts known to Foglight, regardless of the cartridge type, and including virtual hosts if the VMware cartridge is installed and configured.
The recommended approach for discovering database instances across multiple hosts is to search by IP range. This method eliminates the need to specify host names and ports, streamlining the discovery process.
To search by IP range:
Ensure that the numbers entered are within the range of 0 to 255.
Ensure that the numbers entered are greater than those entered in the From field, and that the address range does not exceed 1000 addresses.