Monitoring Read-Replicas in a managed environment - heads-up

[alin-selicean]

Hi all - not sure if I am doing something wrong or not, but here it goes. Our environment is comprised of several SQL Managed Instances (Azure, of course :) ) in Business Critical tier, having "attached" to them 1 read-replica (among others). We, of course, want to monitor those as well. I modified the json config file to add those connections with a distinct ConnectionID (and the ApplicationIntent keyword set).

All is good and fine in the GUI, they show up and everything. But when it comes to collect the slow and running queries, the service throws this error (quoting from memory): Cannot create the extended event session because the master database is read-only.

Explanation from MS is simple: on a SQL MI (and Azure DB), the master DB we are connecting to, is not the actual master DB, but rather a replicated master (on both replicas), while the real master DB on the read replica is read-only (being replicated...).

If what I've done is correct, then probably this cannot be worked-around it. Or at least how I see it... Just a heads-up for anyone going the same route.

@DavidWiseman, if there's anything else that I can do to go around it, I really appreciate it :)

[DavidWiseman]

It seems like the XE session is replicated from the primary to the read replica. They can be stopped/started on the read replica but can't be created:

https://learn.microsoft.com/en-us/azure/azure-sql/database/read-scale-out?view=azuresql#monitor-read-only-replicas-with-extended-events

It might be possible to get this working (solution is untested). In the service config tool in the Source tab, enable the Persist XE Sessions on the read replica it will use the existing session instead of trying to drop and recreate it. If the session has been created on the primary it should be able to start & stop the existing session.

[alin-selicean]

Hi @DavidWiseman ! Thanks for the tip, it did the trick.

[DavidWiseman]

That's great - thanks for letting me know it worked. I'd like to improve the support for monitoring read replicas but at least it works for now with some workarounds.