Capture Metrics from Readonly Replica on Azure SQL DB

[samplesty]

Some tiers of Azure SQL DB come with a read-only replica which appears to share the same server and DB name as the primary and is only connected to via the addition of ApplicationIntent=readonly into the connection string.

Per @DavidWiseman's request I tried adding this into the config manually and it did not appear to work correctly, CPU stats from the primary displayed but nothing else.

[DavidWiseman]

With multiple read replicas, the question is how to monitor a specific replica. It appears this is answered here:

https://learn.microsoft.com/en-us/azure/azure-sql/database/read-scale-out?view=azuresql

In Premium and Business Critical service tiers, only one of the read-only replicas is accessible at any given time. Hyperscale supports multiple read-only replicas.

So ApplicationIntent=readonly will connect you to the read replica and we shouldn't need to worry about which one it's connected to.

It's possible to identify if you are connected to a read replica by running:
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');

In theory, DBA Dash should be able to add a second connection to the read replica and monitor it. It needs to be identified separately from the primary though - some investigation is needed here.

[DavidWiseman]

It's possible to add the read replica to the service config tool but there are other issues that would need to be resolved to improve the experience.

To add the read replica:

• Edit the Json config directly. Copy/paste the source connection json.
• Edit the connection string to add ";Application Intent=ReadOnly" to the connection string.
• Specify a unique ConnectionID. You might need to add "ConnectionID": "azuredbserver|dbname#readonly" to the json.

Note: Individual DB connections would be required rather than a single connection to the master DB.

Issues:

• In the tree the DB name is duplicated with no easy way to tell which is the read replica. You could go to "Checks" node and the ConnectionID is shown in the CollectionDates tab.
• In the Azure Summary tab at root level, the DB name is also duplicated with no way to tell them apart.
• There could be other issues.

Possible workaround:

• Create another DBA Dash repository for monitoring ReadOnly replicas. GUI can now switch between repositories. GUI to work with multiple repositories #137

As a first step adding some visual indication if a AzureDB is readonly might be useful.

[jacobgexigo]

I am doing this now by creating a secondary repository that holds only the read-scale copies of the same connection strings I have for my main write connection. As you said, it was as easy as adding the ApplicationIntent=ReadOnly parameter to the connection strings in the config tool. By doing a second repo, I don't need to worry about duplicated connection names, etc.

[DavidWiseman]

Thanks @jacobgexigo. That seems like a good workaround. I'd like to add support for this feature at some point but it's not a priority as I don't currently have a requirement to monitor read replicas.