CODEWITHSUNDEEP
azureazure-sql-databaseazure-sql-serverazure-monitoring
Dresse
Dresse

Reputation: 483

Azure SQL Server Monitoring/Alerting for Multiple Databases (v12)

What is the best way to monitor a certain metric and alert on it over multiple databases. For example I want to trigger an alert if a database size grows larger than 10 GB.

I know you can select a single database and configure this in Azure Monitor but when there are hundreds this doesn't seem viable.

My Databases are created with Azure Resource Management

Upvotes: 0

Views: 707

Answers (2)

Dresse
Dresse

Reputation: 483

After some more delving I found a somewhat satisfactory solution in Azure:

  1. Go to Azure Monitor Metric(preview)
  2. In my case I was using an sql elastic pool so I added this as Resource with metric "storaged_used"
  3. Add splitting by "Database ARM Resource Id" will give you storage used for each
    SQL database in your pool by Resource Id.

Upvotes: 0

pim
pim

Reputation: 12607

I think PowerShell, specifically the Get-AzureSqlDatabaseUsages command, is your best friend here. I've done this in the past as a modified "heartbeat". Whereby we also analyzed index fragmentation, and triggered a REBUILD if needed.

An example of the PowerShell command from above: 

C:\> Get-AzureSqlDatabaseUsages -ServerName "Server01" -DatabaseName "Database01"

This will require that you be authenticated, but that itself is simple enough as well.

You could execute the script on a schedule (i.e. either as a WebJob or a Scheduled Task) and report/notify based on certain metric thresholds.

EDIT: For v12 databases use the following query (which can be execute programmatically using PowerShell and sqlcmd.exe)

SELECT SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) AS DatabaseSizeInBytes,
       SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 AS DatabaseSizeInMB,
       SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 / 1024 AS DatabaseSizeInGB
FROM sys.database_files
WHERE type_desc = 'ROWS';

Upvotes: 1

Related Questions