SQL Server Instance Offline Monitoring

[Troy]

Hello - We are trying to monitor SQL Server instances being offline via the Microsoft SQL Server Extension.

As I can tell theres no easy metric for instance state. and the uptime does not seem to be 100% reliable. using:

timeseries state = sum(`sql-server.uptime`, default: 0), by: {instance, device.name}
| filter contains(instance, "HOST")

I see the uptime at 0 during a timeframe where our SQL Admin says there was no outage to the instance. 

Could someone please point me in the right direction for SQL Instance monitoring? We cannot monitor services either since there are clustered servers where the instance is offline on one server but not the other. 

[AntonPineiro]

Hi,

If you have OneAgent installed also, you can monitor SQL OS Service is up and running.

Best regards

[Troy]

They have one agent but as mentioned this is a problem since some are clustered - meaning one server has it on while the other has it off. causing it to alert. so we would like to monitor the instance state itself.

[rastislav_danis · Accepted solution]

If you have "Always on" type of MSSQL cluster, then all replicas should be available (even not primary one), maybe access to non-primary replicas need to be set up. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-onl....

How to monitor Always on is described in doc https://www.dynatrace.com/hub/detail/microsoft-sql-server-2/?query=mssql&filter=all (check part named "Are there any special considerations when monitoring Always On clusters?")

[Troy]

Thank you - I will take a look into this.