Sep 30 2019
- last edited on
Apr 08 2022
Good morning everyone,
Our organization is nearly done with our migration from SCOM to Azure Monitor--it's been quite a ride.
I've run into a challenge that I can't seem to find an answer for. We have some on-premise SQL clusters that we've added the MMA to and moved to Azure Monitor. However, they do not appear to be pulling SQL performance counters. I suspect this is due to the virtual nature of the SQL cluster:
SQL Server name = SERVERV001
Physical nodes = SERVERP001a and SERVERP002a
Since the virtual name--SERVERV001--is not added to Log Analytics, I have to rely on the physical nodes--SERVERP001a and SERVERP002a--to view performance counters--except SQL counters.
How does one circumvent this issue?
Thanks in advance!
Sep 30 2019 07:58 AM
Oct 03 2019 08:53 AMSolution
Sql is poorly designed when it comes to performance counters. Basically when you have SQL cluster you have to use named instance if I remember. Every named instance creates its own set of counters on the servers.
For example SQLSERVER:Access Methods becomes MSSQL$<instance name>:Access Methods
what you can do is to add the object name as MSSQL$*:Access Methods that way it will get the counter no matter what the name is of instance. so your end entry will be something like MSSQL$*:Access Methods(*)\Forwarded Records/sec
you get the idea for the rest of the counters.
Oct 04 2019 08:12 AM
@Scott Allison Yeah. It is more of SQL problem because there are similar problems when you deploy other services like reporting or integration services. Just bad architecture of their part. Thankfully LA data sources allows using wildcard as * to avoid adding counters per instance name. Not many people know that and I haven't blogged about it.