PowerShell to check available free space in Azure SQL Managed Instance

Question

I am working on a PowerShell script to restore databases dynamically from the Azure storage account to Managed instance. To add additional functionality I am looking for a PowerShell module that can help me to find free space from managed instances to avoid restoring failure at the last moment.

can anyone help me?

Answer 1

You can use PowerShell Get-AzMetric for that. See $SpaceMetric on below script.

$StorageAccounts = Get-AzResource -ResourceType Microsoft.Sql/managedInstances
        FOREACH($StorageAccount in $StorageAccounts){
            $ResourceId = $StorageAccount.ResourceId
            $Start = (get-date).AddDays(-7)
            $End = get-date         
            $SpaceMetric = Get-AzMetric -ResourceId $ResourceId -MetricName "storage_space_used_mb" -WarningAction Ignore
            $IOMetric = Get-AzMetric -ResourceId $ResourceId -MetricName "io_requests" -StartTime $Start -EndTime $End -WarningAction Ignore
            $CoreMetric = Get-AzMetric -ResourceId $ResourceId -MetricName "virtual_core_count" -WarningAction Ignore

            #Load Variables for SQL Statement.
            $TeamGroupName = $SubscriptionID.TeamGroupsName
            $ServiceName = $SubscriptionID.ServiceName
            $SubscriptionName = $SubscriptionID.SubscriptionName
            $SubID = $SubscriptionID.SubscriptionID
            $StorageAccountName = $StorageAccount.Name
            $StorageType = 'SQL-MI'
            $SQLMISize = ($SpaceMetric.Data | Select-Object -First 1).Average/1024
            $TotalRequests = ($IOMetric.Data | Select-Object -First 1).Average
            $CoreCount = ($CoreMetric.Data | Select-Object -First 1).Average
            $Region = $StorageAccount.Location
            $RefreshedAt = Get-Date