Installing and Configuring Agents

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:

• About Monitoring Extensions
  
  • SQL Performance Investigator Extension
  • Operating System Extension
  • VMware Extension
• Installing and Monitoring a Single SQL Server Instance
• Discovering Multiple Instances to Monitor
  • Adding instances manually
  • Discovering instances by host name
  • Discovering instances by IP range

About Monitoring Extensions

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 Extension

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:

• Monitor real-time SQL Server database performance at a glance
• Gather and diagnose historical views
• Identify and anticipate 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.

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.

Installing and Monitoring a Single SQL Server Instance

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:

1. On the navigation panel, click Databases.

2. Click the SQL Server tab in the middle of the Databases View, and then click Monitor. 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.

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

   • Click the Agent Manager Host 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.
   • Select the appropriate host name and click Set.

     You have the option to set this host as the default for all future installations.

4. On the Monitor SQL Server Instance dialog box, enter the following details:

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

   • Active Directory (AD) Authentication - log in using the Active Directory account running on your agent manager or entering a new AD account. The user name should be entered in the following format: domain\user name.
   • SQL Server Authentication - log in using a SQL Server account
   • Azure Active Directory - log in using an Azure Active Directory account.

6. Use SSL: Select whether an SSL connection should be used.

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

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

   • 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: Active Directory (AD) Authentication or Using the Active Directory account running your agent manager.
   • Log in to the host using different log in credentials- logging in through either of the following authentication methods:
     • Windows: Log in through a Windows account. The user name should be entered in the domain\username format (for example, COLUMBIA\JSmith).
     • Local User: Log in through the same credentials that are used to run monitored software on the SQL Server host. The user name should be entered in the domain\username format (for example, COLUMBIA\JSmith).
     • SSH (login credentials): Log in through a user account on Linux.
     • SSH (RSA): Log in through a RSA key on Linux.
     • SSH (DSA): Log in through a DSA key on Linux.
       

9. (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:

   • 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: 443).
   • The name and password of the user that has the privileges required for connecting to the vCenter server or ESX server and retrieving information.

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

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

Discovering Multiple Instances to Monitor

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:

• By host name
• By IP range
• By entering a file with a predefined instance

To discover additional instances to monitor:

1. Click the SQL Server tab in the middle of the Databases View, and then click Monitor. The Monitor SQL Server Instance dialog box appears.

2. Click the Use this option to discover multiple SQL-Server instances link. The Select an Agent Manager dialog box appears.

3. 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 a default agent manager for all future installations.

4. Click Add instances and select one of the following methods for adding instances:

   • (Windows and Linux) Adding instances manually - refer to Adding instances manually.
   • (Windows only) Discovering instances by hosts - refer Discovering instances by host name.
   • (Windows and Linux) Discovering instances by IP range - refer to Discovering instances by IP range.

5. After the discovery process is successfully completed, the newly discovered instances appear on the table, with the status ‘Set credentials’.

6. Click the check boxes beside the instances whose credentials are to be configured.

7. Click Set credentials to provide the instances' log in credentials and monitoring configuration.

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

9. 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:

   • If the message is Insufficient database privileges, this issue can be resolved using the Insufficient Database Privileges dialog box that appears. For details, refer to the next step.
   • For all other messages, the Database Connection Failed dialog box appears. For details, refer to Step 11.
   
   

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

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

Adding instances manually

To add instances manually:

1. Click Add instances.
2. Click Add manually. The Add New SQL Server Instance dialog box appears.
3. Use this dialog box to enter the following details:
   • Server name - the SQL Server instance name. For default instance, specify the host name; for named instance, use the format: host name\instance name.
   • Port - Required for a SQL Server instance whose TCP/IP connection port is other than the default port (1433).

     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.

4. Click Finish. The newly added instance now appears on the table, with the status ‘Set credentials’.
5. Proceed with the wizard, starting from Step 5 mentioned in the section Discovering Multiple Instances to Monitor.

Discovering instances by host name

Searching by host is the most efficient search method for discovering instances over a specified, small number of hosts.

To search by host:

1. Click Add instances. The Discover SQL Server Instances by Hosts dialog box appears.
2. Enter the host names in the field below the first check box. To specify multiple host names, separate the values by comma.

   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.

   Alternatively, if Foglight already monitors one or more hosts in the selected environment, select the requested host from the table of hosts currently known to Foglight.

   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.

3. Click Discover. At the end of this process a table appears, displaying the newly discovered instances within the selected hosts.
4. Select the instances to be monitored.
5. Click Finish. The newly added instance now appears on the table, with the status ‘Set credentials’.
6. Proceed with the wizard, starting from Step 5 mentioned in the section Discovering Multiple Instances to Monitor.

Discovering instances by IP range

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:

1. Click Add instances.
2. Click Discover by IP. The dialog box Discover SQL Server Instance by IP Range appears. The IP range displayed by default is determined by the IP address and the subnet mask on the selected Foglight server.
3. In the From field, enter the IP Range.

   Ensure that the numbers entered are within the range of 0 to 255.

4. In the To field, enter the requested IP Range.

   Ensure that the numbers entered are greater than those entered in the From field, and that the address range does not exceed 1000 addresses.

5. Click Discover. At the end of this process a table appears, displaying the newly discovered instances within the selected hosts.
6. Select the instances to be monitored.
7. Click Finish. The newly added instance now appears on the table, with the status ‘Set credentials’.
8. Proceed with the wizard, starting from Step 5 mentioned in the section Discovering Multiple Instances to Monitor.