Alert for sql elastic pool

Question

How to create an alert for sql elastic pool in Azure that runs if the elastic pool getting full regarding number of databases?

Thank you.

Answer 1

Hi anonymous user-8780 ,

I thought query DMVs (master DB) is possible from SQL Management Studio only.

This makes no sense, even so it is a common mistake

SQL Server Management Studio (SSMS), is an external application like any application that people develop. It connect the server like any other application using connection string and it has the same permissions as other applications (depend on the connected LOGIN and the related USERs).

In the past, Microsoft released the SSMS as part of the SQL Server release in the same file - this make it even more confusing and make people thought that the SSMS is part of the SQL Server product - the fact is that it has nothing to do with the SQL Server product.

Well... I mean that SQL Server is build as set of services which running in the background and it has no GUI for managing the databases (it comes with a GUI only to manage the server - SQL Server Configuration Manager). SSMS is a client side application. Moreover, it is highly recommended not to install the SSMS (like any other client application) in the production server, but in your client machine and connect the server from your client machine.

Anything that you can do with SSMS can be done with direct queries as SSMS do the same and simply send queries to the SQL Server.

is there any tools to query the master database in Azure Portal directly?

Yes there is :-)

Navigate to one of your Azure SQL Database in the portal, and on the left menu you have option "Query editor (preview)". Click it and you can manage the database directly in your browser in the portal

Answer 2

As per the internal team, they do not have any immediate work which would address this on the SQL metrics front. For mitigation purposes, you could consider implementing a custom mechanism to alert them. To get the count of databases per pool on a logical server, you can use a SQL query like the below:

select DSO.elastic_pool_name, count(*) as DBsPerPool
from sys.database_service_objectives AS DSO
join sys.databases AS D
on DSO.database_id = D.database_id
where DSO.service_objective = 'ElasticPool'
and Edition != 'System'
group by DSO.elastic_pool_name

OR

you could use PowerShell to get the equivalent information on a per-logical-server basis:

$allPools = Get-AzSqlElasticPool -ResourceGroupName "someRG" -ServerName "someServer"
$allPools | % { $dbs = (Get-AzSqlElasticPoolDatabase -ResourceGroupName ($.ResourceGroupName) -ServerName $.ServerName -ElasticPoolName $.ElasticPoolName); Write-Host $.ElasticPoolName $dbs.Count }

While both examples print the counts of DBs in respective pools, you can use them for the foundation of a custom monitoring and alerting mechanism.

We’ll investigate adding something natively in the future but this is what we suggest the above workaround.

Regards
Geetha