Steps and best practice to monitor 2 nodes SQL Server Enterprise Edition – Always-On nodes?

After reading this thread: https://thwack.solarwinds.com/product-forums/server-application-monitor-sam/f/forum/45197/sql-failover-cluster-monitoring-best-practices

I've got a similar question, but in this case, I do not have the luxury of the Appinsight component.

Can anyone here please suggest what are the steps that I need to configure to monitor (MSCS 2016) SQL Server 2017 Enterprise Edition – Always-On nodes as VM?

I only have SAM, SRM, VMAN and DPA components licensed.

Find more posts tagged with

SQL Cluster

VMware

SQL Server

MSCS

cluster_monitoring

Accepted answers

All comments

jm_sysadmin

Add AppInsight for SQL to the cluster IP, between DPA and AppInsight you will have all the data you need.
Add AppInsight for SQL to an active SQL Server cluster node (solarwinds.com)

There are templates for SQL server Services, and windows server cluster that you may want to consider as well.

For the VM side you want to pay attention to Latency for the Guest overall, and the disk as SQL is pretty cranky when those aren't great. Memory usage should follow DPA's guidance, as the raw stats can be misleading.

Edit I just re-read it.. no AppInsight. Let me update this

jm_sysadmin

OK, so there still is a template for SQL performance stats, I would look at that as a start to replace AppSight info.

But lets look at stuff in AppInsight and I will talk about the areas so you can decide if there is anything you care about adding.

1. The App details are useful, but you know what you have. Sometimes knowing the edition can be helpful.

2. SQL Error log. You might have this in DPA or watch it another way. My DBA's do.

3. Database list: DPA has what you need there, but watching/alerting on the size of the file we absolutely use. If you have the SQL server drives set up to hold the various files you might not care as much, but the default disk space checks are 1 hour I think. This is much more frequent at 5 minutes by default.

4. Processes and services are in the other template.

5. Memory is also tracked in DPA.

6. Buffer and Cache sections are important when they need love, but these are performance counters. Its possible to live without these stats if you aren't pushing the server hard.

Area 7, 8, and 9 are the SQL performance Counters. Some of this is absolutely covered in DPA, but its honestly laid out differently there. Some of this is in the template for SQL performance. As a Windows Admin not a DBA, I like having these stats with thresholds so that I can baseline 'normal' and not normal when things are 'slow' DPA might be enough here, but its going to depend on your environment.

10, 11 and 12 you will likely have enough information in DPA.

13 As long as something is watching/dealing with Errors in teh Event logs, you will be fine. This could be added to any template as well.

14. The disk stats you have on the machine, and via VMan.

15 Expensive queries are better dealt with in DPA.

16 Check to see if the agent jobs have alerts in DPA, I know that we use the AppInsight. I personally do want alerts when these Jobs fail.

Hope that covers the "what" you need if you skip AppInsight and is more useful than "use the thing you can't use". Sorry about that.

I should also say that I don't use AppInsight on all my SQL servers, most are just too small and will never have a performance issue. If that changes I add it. All our big stuff (75-100 instances) is in DPA, and those have AppInsight for the most part. I skipped a shared SQL box that was a monster, the number of elements would tip over a polling engine if it got added.