[feature request] AzureSQL secondary - elastic pools monitor

[FuriousDBA]

Hi,
just an idea.
Lets say we have a primary AzureSQL with a few elastic pools. Elastic pools which contain our databases added to the config file are seen in the DBADash. If these databases are replicated to secondary AzureSQL, they are added to different elastic pools with different elastic pools storage limits.
It would be nice to monitor only these elastic pools on secondary AzureSQL, but not databases itself. So I think about a feature that when (only) master database is added to DBADash it should grab all the elastic pool and monitor them.
Is it feasible to implement that?
Thx

[DavidWiseman]

It should already work like this. The AzureDBElasticPoolResourceStats collection only runs from master and will collect all pools.
Note: I don't have much need to monitor secondary Azure SQL DBs so anything in this area will be very low priority for me.

[FuriousDBA]

@Date parameter is a CollectionSchedule interval?

In DBADash I was able to see only one random elastic pool, but looks like if elastic pools are not busy (like in a secondary server) not all pools are visible in that DMV in last 3 or 5 minutes.

Is there any benefit running that collection often, like every 3minute? Do I lose some granularity if running every 10 minutes?

[DavidWiseman]

It's hard coded to current UTC-60min.

https://github.com/trimble-oss/dba-dash/blob/main/DBADash/DBCollector.cs#L893
https://github.com/trimble-oss/dba-dash/blob/main/DBADash/DBCollector.cs#L98

As long as you are running more frequently than every 60min, it should backfill. Increasing the frequency will make the data available in DBA Dash sooner but it shouldn't impact the granularity.

[FuriousDBA]

That is weird..
I've checked the master database audit, DBADash query returns data for more than one elastic pool,
I see the pools data in [dbo].[AzureDBElasticPool] and [dbo].[AzureDBElasticPoolResourceStats] DBADashDB tables..
Unfortunately GUI shows only one elastic pool under server tree on left pane and only one in Azure Summary tab.
That need to be related to the fact that only one database (master) is added to monitored instances...

[FuriousDBA]

Hi @DavidWiseman
I realized now that DBADash is assigning master database to an (user) elastic pool but this is not true..

[DavidWiseman]

The query DBA Dash is using is:

SELECT edition,
       service_objective,
       elastic_pool_name 
FROM sys.database_service_objectives
WHERE database_id = DB_ID()

This is similar to the example given here in the docs, using DB_ID() to get the ID of the current database.
https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-service-objectives-azure-sql-database?view=azuresqldb-current

On master, this seems to be incorrect due to DB_ID() returning a different value than database_id from sys.databases. The docs state that this is joinable to sys.databases, so this might be the best option.

[DavidWiseman]

The DB_ID() fix will be included in the next release. I've also got a PR to fix the example in MS Docs.

[DavidWiseman]

Elastic pools will now be visible even if there are no associated instance. Included in 3.31 🚀