Complete Azure SQL Database inventory

Update : updated script to support Azure Az powershell module.

Azure SQL Database is a general-purpose relational database managed service. It enables creation of highly-available and high-performance data storage layer for the applications and solutions in Microsoft Azure cloud. With Microsoft’s cloud-first strategy, the newest capabilities of SQL Server are released first to SQL Database, and then to SQL Server itself. This approach provides you with the newest SQL Server capabilities with no overhead for patching or upgrading.

SQL Database delivers predictable performance with multiple resource types, service tiers, and compute sizes that provides dynamic scalability with no downtime. It provides built-in intelligent optimization, global scalability and availability, and advanced security options.

Refer here for more information on Azure SQL Database. Following document will help you get started with setup your Azure SQL Database –  Azure SQL Database quickstart guide.

Here is a sample powershell script to quickly generate an inventory of deployed Azure SQL Database in Azure Cloud. Once executed, it generates a csv file with basic information of Azure SQL Database. However, you may update script as per your requirements. This script executes against all Subscriptions within a Tenant that the logged in user can access.

Make sure you have Azure Az Module installed and imported on the system before using this script. For more information on how to install and configure Az module refer following article : https://docs.microsoft.com/en-us/powershell/azure/new-azureps-module-az?

$date = Get-Date -UFormat "%m-%d-%Y"
$currentDir = $(Get-Location).Path
$oFile = "$($currentDir)\Azure_SQLDatabase_Details_$($date).csv"

if(Test-Path $oFile){Remove-Item $oFile -Force}
"SubscriptionName,SQLServerName,ResourceGroupName,SQLDatabaseName,Location,Edition,CollationName,MaxSizeBytes,Status,CreationDate,ElasticPoolNamem,CurrentServiceObjectiveName,EarliestRestoreDate,ReadScale,ZoneRedundant,Capacity(DTU),Family,SkuName,LicenseType" | Out-File $oFile -Append -Encoding ASCII
Get-AzSubscription | ForEach-Object{
	$subscriptionId = $subscriptionName = ""
	$subscriptionId = $_.SubscriptionId
	$subscriptionName = $_.Name
        Set-AzContext -SubscriptionId $subscriptionId
        Get-AzSqlServer | ForEach-Object {
        $sqlsrvName = $resourceGroup = ""
        $sqlsrvName = $_.ServerName
        $resourceGroup = $_.ResourceGroupName
        Get-AzSqlDatabase -ServerName $sqlsrvName -ResourceGroupName 
        $resourceGroup | ForEach-Object {
            $sqlDbName = $location = $edition = $collationName = $maxBytes = 
            $status = $createDate = $soName = ""
            $ePoolName = $restoreDate = $readScale = $zoneRedundat = 
            $capacity = $family = $skuName = $licType = ""
            $sqlDbName = $_.DatabaseName
            $location = $_.Location
            $edition = $_.Edition
            $collationName = $_.CollationName
            $maxBytes = $_.MaxSizeBytes
            $status = $_.Status
            $createDate = $_.CreationDate
            $soName = $_.CurrentServiceObjectiveName
            $ePoolName = $_.ElasticPoolName
            $restoreDate = $_.EarliestRestoreDate
            $readScale = $_.ReadScale
            $zoneRedundat = $_.ZoneRedundant
            $capacity = $_.Capacity
            $family = $_.Family
            $skuName = $_.SkuName
            $licType = $_.LicenseType
            
"$subscriptionName,$sqlsrvName,$resourceGroup,$sqlDbName,$location,$edition,$collationName,$maxBytes,$status,$createDate,$ePoolName,$soName,$restoreDate,$readScale,$zoneRedundat,$capacity,$family,$skuName,$licType" | Out-File $oFile -Append -Encoding ASCII 
        }
    }
}

Download above script and save it with a .ps1 file extension. After that, open powershell console and login to your Azure account using – Login-AzAccount. It will prompt you to enter your Azure credentials.

After you login to Azure, run the powershell script saved in previous step.