Azure PowerShell - Extracting Azure SQL Database information

Question

I'm looking for a way to extract specific information about Azure SQL Database: Used space, Allocated Space, and Maximum storage size. I know that information is available from Azure portal but I would like to use PowerShell to extract it. I've managed to pull one bit of information "Maximum storage size" using the command below but I cannot find other required information.

Code to extract information about database capacity (Maximum storage size)

Get-AzSqlDatabase -ServerName $ServerName -ResourceGroupName $ResourceGroupName | Select-Object ServerName, DatabaseName, @{Name="MaxSizeGB";Expression={$_.MaxSizeBytes/1GB}}

I know that that information is available using T-SQL but connecting to each server to run T-SQL it's not possible in this situation. https://learn.microsoft.com/en-us/azure/sql-database/sql-database-file-space-management

Does anyone know if there's another option to pull out that information utilizing Azure PowerShell? I've tried to use Get-AzMetric command to extract something but without luck.

Answer 1

You can get a database's max size using the Get-AzureRmSqlDatabase commandlet (from the AzureRM.Sql module). The commandlet returns an AzureSqlDatabaseModel object, which includes a MaxSizeBytes property.

To get ALL the data points you asked for you checkout the Get-AzureRmMetric commandlet. It lets you query specific metrics for an Azure Resource, such as an ASD. You can find a list of metrics organized by resource in the article Supported metrics with Azure Monitor under the Microsoft.Sql/servers/databases section. I believe the following maps metrics to what you're looking for...

• Used space => storage_percent
• Allocated Space => allocated_data_storage
• Maximum storage size => storage

You'd query the metric out with something like the following:

$my_db = Get-AzureRmResource -ResourceGroupName "..." -Name "server/dbname"
$my_db_metric_storage = $my_db | Get-AzureRmMetric -MetricName 'storage'
$my_db_size_bytes = $my_db_metric_storage.Data[$my_db_metric_storage.Data.Count - 2].Maximum
$my_db_size_bytes / 1KB

I'm pulling database size here, but you can get any of the metrics in your list.

Answer 2

Thanks, Adam you pointed me in the right place I think my first approach to extract that data was incorrect. I've also found out some issues with the Azure PowerShell version I am using along the way and reported it back to Microsoft support.

Get-AzResource -ResourceGroupName 'Test' -name 'ServerName/DatabaseName'

doesn't work correctly but it works if you remove ServerName eg.

Get-AzResource -ResourceGroupName 'test' -name 'DatabaseName'

My final code to extract the required data about the database:

Connect-AzAccount

$OutData = @()
$IgnoreDB = @('master', 'SSISDB')

$Subscription = Get-AzSubscription | Out-GridView -OutputMode 'Single'
if($Subscription){
    $Subscription | Select-AzSubscription

    $AzSqlServer = Get-AzSqlServer | Out-GridView -OutputMode Multiple
    if($AzSqlServer)
    {
        Foreach ($server in $AzSqlServer)
        {
            $SQLDatabase = Get-AzSqlDatabase -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName | Where-Object { $_.DatabaseName -notin $IgnoreDB }
            Foreach ($database in $SQLDatabase)
            {
                $db_resource = Get-AzResource -ResourceId $database.ResourceId

                # Database maximum storage size
                $db_MaximumStorageSize = $database.MaxSizeBytes / 1GB

                # Database used space
                $db_metric_storage = $db_resource | Get-AzMetric -MetricName 'storage'
                $db_UsedSpace = $db_metric_storage.Data.Maximum | Select-Object -Last 1
                $db_UsedSpace = [math]::Round($db_UsedSpace / 1GB, 2)

                # Database used space procentage
                $db_metric_storage_percent = $db_resource | Get-AzMetric -MetricName 'storage_percent'
                $db_UsedSpacePercentage = $db_metric_storage_percent.Data.Maximum | Select-Object -Last 1

                # Database allocated space
                $db_metric_allocated_data_storage = $db_resource | Get-AzMetric -MetricName 'allocated_data_storage'
                $db_AllocatedSpace = $db_metric_allocated_data_storage.Data.Average | Select-Object -Last 1
                $db_AllocatedSpace = [math]::Round($db_AllocatedSpace / 1GB, 2) 

                # Database VCore
                $db_VCoreMin = $db.MinimumCapacity
                $db_VCoreMax = $db.Capacity

                $Report = New-Object PSObject
                $Report | Add-Member -Name "ServerName" -MemberType NoteProperty -Value $server.ServerName
                $Report | Add-Member -Name "DatabaseName" -MemberType NoteProperty -Value $database.DatabaseName
                $Report | Add-Member -Name "UsedSpace" -MemberType NoteProperty -Value $db_UsedSpace
                $Report | Add-Member -Name "UsedSpaceProcentage" -MemberType NoteProperty -Value $db_UsedSpacePercentage
                $Report | Add-Member -Name "AllocatedSpace" -MemberType NoteProperty -Value $db_AllocatedSpace
                $Report | Add-Member -Name "MaximumStorageSize" -MemberType NoteProperty -Value $db_MaximumStorageSize
                $Report | Add-Member -Name "MinvCores" -MemberType NoteProperty -Value $db_VCoreMin
                $Report | Add-Member -Name "MaxvCores" -MemberType NoteProperty -Value $db_VCoreMax
                $OutData += $Report

            }
        }
        $OutData | Out-GridView
    }
}