Installing and Configuring Agents

This section provides information about installing and configuring the agents for monitoring Azure SQL Database systems.

This section covers the following key areas:

• About Monitoring Extensions
  
  • SQL Performance Investigator Extension
• Installing and Monitoring a Single Azure SQL Database
• Installing and Monitoring Azure SQL Elastic Pool

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

About Monitoring Extensions

During the installation process you can choose to install and configure one or more of the monitoring extensions. The monitoring extensions provide a more in-depth analysis of the monitored database and the environment it is running on, creating a whole and unified status.

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 Azure SQL Database performance at a glance
• Gather and diagnose historical views
• Identify and anticipate performance issues
• Analyze and optimize execution plan changes

Installing and Monitoring a Single Azure SQL Database

Enabling Foglight Cloud to monitor Azure SQL databases requires the creation of the Foglight agents that monitor these databases and ensuring that these agents communicate properly with Foglight Cloud . Foglight for Azure SQL Database provides a graphic, intuitive method for creating and configuring multiple agents.

To run the database installation wizard:

1. On the Welcome page, click Monitor Databases or click Databases in the navigation pane. On a fresh installation, no instances are listed for Azure SQL in the Databases dashboard. The Monitor Azure SQL Database 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.

2. Select the agent manager to which agent will be installed. 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.

3. Specify the name of Azure SQL Database to be monitored in the Connection Details section:
   • Server name - Specify the host name.
   • Database Name - Specify SQL database name
   • Port - (Optional) This field can be left empty, unless the TCP/IP connection port is not the default port:1443.
4. Select between SQL Server Authentication and Azure Active Directory (AD) Authentication. Specify the username and password to be used for monitoring the Azure SQL Database in the Login Credentials section.
5. Use the automatically generated agent name by selecting the checkbox in Configuration section. Or specify a unique agent name by deselecting the checkbox.

6. (Optional) - Disable SQL Performance Investigator extension. It is not recommended for optimal benefits.
   

   SQL Performance Investigator extension is enabled by default.

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

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

Installing and Monitoring Azure SQL Elastic Pool

Azure SQL DB Elastic Pools introduce a dynamic resource allocation model that adapts to the varying needs of databases within the pool. Databases experiencing high workloads can dynamically pull resources from the shared pool, ensuring optimal performance during peak times. Conversely, databases with minimal or no activity utilize no resources, resulting in efficient resource allocation and cost savings.

Foglight for Azure SQL DB can monitor elastic pools to diagnose resource consumption issues of all databases using the pool. To start monitoring, install an elastic pool agent.

To install an elastic pool agent:

1. On the navigation panel, click Databases.
2. On the Databases page, under All Instances section, click Monitor. OR
   Click Azure SQL tile. Under Azure SQL section, click Elastic Pool sub-tab, and then click Monitor. Continue to follow from step 5.
3. Click Azure SQL from the drop down list.
4. Click Azure SQL Elastic Pool from the options displayed. The Monitor Azure SQL Elastic Pools dialog box appears.
5. Enter the Server name, Port (optional), Username, and Password.
6. Click Monitor.

Once the Elastic Pool agent is installed, the global view displays the following details:

• Sev- The instance’s highest severity alarm, which determines the instance’s health state.
• Name- Name of the instance
• Host- Name of the computer that hosts the instance.
• Pricing Tier
• DB Alarms- The number of warning, critical, and fatal alarms for the instance.
  • Storage utilization alarm- It is triggered when elastic pool storage utilization percent exceeds the threshold limit.
  • CPU utilization alarm- It is triggered when an elastic pool (vCore) is approaching its CPU threshold limit.
  • DTU utilization alarm- It is triggered when an elastic pool (DTU) utilization exceeds a threashold.
  • Data IO utilization alarm- It is triggered when an elastic pool (vCore) is approaching its Data IO threshold limit.
  • Log IO utilization alarm- It is triggered when elastic pool (vCore) is approaching its Log IO threshold limit.
  • Elastic Pool unresponsive alarm- It is triggered when Foglight connection to elastic pool has failed.
• CPU (%)
• Data IO (%)
• Log IO (%)
• Storage (%)
• Max Workers (%)
• Max Sessions (%)
• Storage Limit (MB)
• CPU/DTU limit
• DTU (%)
• Databases Count
• Agent- Operational status of the monitoring agent.

For more details related to each elastic pool, refer to Viewing Azure SQL Elastic Pool Overview Dashboard. The Azure Databases sub-tab also displays the names of elastic pools for the respective databases.