Now before I begin this post, a quick disclaimer\u2026<\/p>\n
SQLskills partners with SQL Sentry<\/a> on a couple of things. We periodically write guest-posts on SQLPerformance.com<\/a> and SQL Sentry also provides exclusive sponsorship of two of our Immersion Events. I am also a big fan of SQL Sentry Plan Explorer<\/a>, but that would have been the case regardless of the existing business relationships.<\/p>\n With that said, I wanted to write a post about SQL Sentry\u2019s new \u201cAlwaysOn Monitoring\u201d functionality integrated into SQL Sentry Performance Advisor<\/a> for SQL Server v7.5, highlighting a few areas that I think are useful after an initial, informal investigation.<\/p>\n I explored this new functionality in a VMware environment with ten availability groups configured on a four node Windows Server Failover Cluster (WSFC) with four standalone SQL Server instances.<\/p>\n Here were the top areas that stood out for me…<\/p>\n Let\u2019s say you\u2019re the new DBA on the team. Your SQL Server production environment is being monitored by SQL Sentry Performance Advisor and you want to get a quick look at the current availability group topologies\u2026<\/p>\n Steps I performed to do this:<\/p>\n This is what I saw by-default:<\/p>\n The zoom-level for the availability group topology visualizations was set to 150% and the layout style was set to \u201cWSFC Node\/Group Matrix\u201d. Scrolling down I was able to see all ten availability groups defined on SQL Server instances hosted on the WSFC.<\/p>\n There are various layout styles to choose from, including:<\/p>\n You can answer quite a few questions by just looking at the availability group animations. You\u2019re seeing static screen shots on this blog post\u00a0\u2013 but keep in mind that this view changes state dynamically. The image below shows me that \u201cAG-Test2\u201d has two replicas on two nodes out of the four node WSFC:<\/p>\n I can clearly see which replica is primary and which is secondary, and the spoke symbol (zoomed in below) lets me know which replicas are configured for automatic failover:<\/p>\n If a replica is set for read-only connection mode, we\u2019ll see the following symbol:<\/p>\n Hovering over the read-only symbol will clarify if it is read-intent only (SQL Sentry uses the word \u201cSpecified\u201d for read-intent and just \u201cAll\u201d when read-intent is not used).<\/p>\n If replicas are asynchronous, we\u2019ll see a double-pipe symbol by the associated replica (I put red boxes around them so you could see exactly what I\u2019m talking about):<\/p>\n The connecting pipelines also have inherent meaning. Let\u2019s say you\u2019ve heard about performance issues in the availability group topology and you want to narrow down quickly to the offending availability group, the bars within the pipeline will automatically be sized based on relative load of the other availability groups and you\u2019ll see the colors shift from green, to yellow to red for availability groups pushing more load:<\/p>\n Hovering over the pipelines will also provide information regarding KB to Replica\/Sec and the Recovery queue KB.<\/p>\n If animated topology layouts aren\u2019t your thing, you have a series of tabs below the layout pane that are responsive to the objects you click in the layout area. Tabs cover WSFCs, WSFC Members, Instances, FCIs, Availability Groups, Replicas, Databases, Database Replica States and Availability Group Listener information. Below shows an example of database replica states information:<\/p>\n There is a significant amount of information exposed in the grid section and based on initial impressions, I think it would be likely that I would most often view the Availability Groups tab and Database Replica States tabs.<\/p>\n Most of the grids are expandable. For example- expanding an availability group to see the availability databases and then clicking Replicas to see where they are hosted:<\/p>\n In the upper-right hand pane you\u2019ll see dynamic chart graphics depending on where you\u2019ve clicked. For example, selecting a specific WSFC Member on the WSFC Members tab shows the following KB From Replica\/Sec data:<\/p>\n In the same area as the Chart tab is an Error Log and Replica State Changes tab. This information isn\u2019t on by default, and you if you want this information you need to enable it explicitly (the interface makes this clear). The reason behind this is related to the monitoring noise that then gets added to the SQL Server Error Log, with an ongoing entry of \u201cUsing \u2018dbghelp.dll\u2019 version \u20184.0.5\u2019\u201d. For more information on this issue, see the Connect item \u201cxEvents: selecting System Health information records Using ‘dbghelp.dll’ version ‘4.0.5’ in Errorlog<\/a>\u201d.<\/p>\n But if you\u2019re okay with the additional noise in the SQL Server Error Log, these two information panes can be useful in order to see real-time availability group activity. For example \u2013 here is what we see after a controlled failover of an availability group for the Error Log:<\/p>\n And here is what we see for Replica State Changes:<\/p>\nGet your bearings quickly<\/h3>\n
\n
<\/a><\/p>\n\n
<\/a><\/p>\n\n
<\/a><\/p>\n\n
<\/a><\/p>\n\n
<\/a><\/p>\nAvailability Group Configuration Visualizations<\/h3>\n
<\/a><\/p>\n
<\/a><\/p>\n
<\/a><\/p>\n
<\/a><\/p>\n
<\/a><\/p>\nTabular Data<\/h3>\n
<\/a><\/p>\n
<\/a><\/p>\n
<\/a><\/p>\nCharts<\/h3>\n
<\/a><\/p>\nError Logs and Replica State Changes<\/h3>\n
<\/a><\/p>\n