I recently spoke to a user of Spotlight on SQL Server Enterprise who wanted to be able to drill into the individual SQL Server instances that are participating in an Availability Group. The customer had successfully created a connection to the Availability Group listener, but he couldn't figure out how to further diagnose problems that were occurring on a specific member of that group. I love it when customers provide topics for my blog.
As you might know, Spotlight on SQL Server Enterprise provides monitoring for many SQL Server technologies. Availability Groups and the SQL Server instances are just two connection types supported by the solution. Spotlight treats these as different connections, which means they have specific data collection intervals, alarms relevant to the connection type, and related drilldowns for diagnostics.
If you've built a connection to the Availability Group listener, you may see something similar in your Spotlight console:
At a high-level, we can see the status of the Availability Group, the synchronization health, and the nodes participating in the Availability Group. There are specific alarms associated with this connection type. These alarms will be flagged with type "Availability Groups" in the Configure->Alarms menu (subset of the alarms are below):
In order for me to drill in further to the SQL Server instances that are participating in the Availability Group, I must create SQL Server connections to those nodes in Spotlight. This is just like creating SQL Server connections to any SQL Server instance you wish to monitor. By creating those connections, I can navigate to the affected node by simply choosing it from the Availability Group:
This will direct me to the Home page of the selected SQL Server instance:
Now, I have the ability to utilize all of Spotlight's SQL Server drilldowns to further diagnose the issues and take corrective action.
Because Spotlight treats connections to Availability Groups and SQL Server instances separately, you can choose to monitor the most important aspects of your SQL Server environment. This may very well be a subset of the SQL Server stack you've chosen to implement. Spotlight does permit investigations that flow from the Availability Group down to the individual SQL Server instances participating in that group. In order to achieve this workflow, you must create a connection to the Availability Group listener AND a connection to the SQL Server instances that participate in the group.
To learn more about Spotlight on SQL Server Enterprise, please visit our product page.
