sp_Blitz: warn if read-only routing for an Always On availability group is probably intended but broken

[ReeceGoding]

Is your feature request related to a problem? Please describe.
The documentation says

For each readable secondary replica that is to support read-only routing, you need to specify a read-only routing URL.

However, it is entirely possible to set up a replica to only allow read-only connections even though you do not have a read-only routing URL set. I presume the intended use case is for people who want to connect to a replica directly and query the replica that way, but don't want the node in the routing list due to concerns like licencing. In my experience, I find it more likely that anyone with this set up did it by mistake and wrongly thinks that they have read-only routing enabled.

Describe the solution you'd like
If sys.availability_replicas says a node is READ_ONLY but has read_only_routing_url as NULL, have sp_Blitz give a very low priority "Are you sure?" warning.

It would be really nice if I could set up a Docker demonstration that read-only routing is indeed broken in the case I describe.

Describe alternatives you've considered
Just leave it alone.

Are you ready to build the code for the feature?
As much as we'd love to build everything that everyone wants for free, we need your help. Open source is built with your help and code. Are you ready to commit time to this project? Have you got existing code you can help contribute to solve the problem?
Sure. My only worries are that this may not be the only way to break read-only routing or even a good way to confirm it is broken.

[BrentOzar]

Interesting! I haven't done any AG work in years, so I can't speak to anything about how likely this is, or whether it's the right way to catch it. That also means I can't do any support on it if questions come in from end users, and I always get kinda nervous about adding alerts for stuff I can't support - especially if I don't have any kind of testbed for it.

However, if this scenario has bit you in production, and you wanna have a check in sp_Blitz to catch it faster going forward, I'm cool with accepting the pull request.

A few thoughts about it:

• You wanna make sure it doesn't report false problems on clusterless AGs on Linux, or on Azure SQL DB Managed Instances (because I don't know if/how the URL is set on those)
• For the more-details URL, I have no idea what to put there.
• Out of the clients I have using read-only replicas, many/most of them don't use the built-in routing. They use DNS aliases instead, because they wanna route clients to the closest read-only replica. For example, their app servers in datacenter 1 want to query the database servers in that same data center, but app servers in datacenter 2 want to query the database servers in data center 2.

[ReeceGoding]

Sure, I'll get to work on it.

if this scenario has bit you in production

It has!

You wanna make sure it doesn't report false problems on clusterless AGs on Linux

I'll be testing this in Docker, so we'll both know how Linux reacts.

Azure SQL DB Managed Instances

I don't have a way to test that.

For the more-details URL, I have no idea what to put there.

How about the documentation link I've given?

They use DNS aliases

Huh. That's pretty smart!

[ReeceGoding]

Got clusterless AGs in Docker with read-only routing working. sp_Blitz PR soon, probably at the weekend.