Microsoft SQL Server adapter collection times out in Aria Operations

Products

• VCF Operations/Automation (formerly VMware Aria Suite)

Issue/Introduction

• Unable to Fetch Microsoft SQL Server Instance Data In Aria Operations Manager in large memory SQL Server environments
• Sometimes the dashboards will show empty. 

Environment

Aria Operations 8.x and above

Cause

This issue occurs because some queries executed by the Microsoft SQL Server Management Pack take a long time to complete, causing data collection delays or failures.

Resolution

To workaround this issue, exclude specific long-running queries by updating the microsoftsqlserver.properties file. Do doing this techically it would just disables them.

Steps: 

1. SSH into the Aria Operations node where the SQL Server adapter is configured.
2. Open the microsoftsqlserver.properties file using a text editor:
       vi /usr/lib/vmware-vcops/user/plugins/inbound/sql_server_adapter/conf/microsoftsqlserver.properties
3. Add the following line in the file:
       excluded_queries=Low Page Life Expectancy\,Buffer Alone\,Effective Memory\,Buffer Pool Size
4. Save the file.

Note:

If the Buffer Alone query is excluded, the following metrics will not be collected:

• Buffer Pool Size on Microsoft SQL Server Instance resources
• Buffer Ideal Page Life Expectancy on Microsoft SQL Server Database resources
• Memory|Effective Server Total on Microsoft SQL Server Database resources
• Memory|Effective Used on Microsoft SQL Server Database resources
• Memory|Effective Used Pages on Microsoft SQL Server Database resources
• MS SQL Server Low Page Life Expectancy alert

Follow the below to address the Widget Interactions to reflect the data

1. Try unselecting and reselecting the VM in the VMs Running Microsoft SQL widget.
2. On macOS, hold the Command (⌘) key while clicking the VM.
3. On Windows, try using Ctrl or Alt while clicking.
4. If a tooltip appears while reselecting, click on "Internal interactions" to refresh the widget.