Complete Azure SQL Server inventory

Update : updated script to support Azure Az powershell module.

SQL Logical Server in Azure is a logical construct that acts as a central administrative point for multiple single or pooled databases. It is the hosted fully managed Platform as a Service (PaaS) database engine and is the parent resource for databases, elastic pools and data warehouses.

Refer here for more information on Azure SQL Logical servers. In addition, following document helps you to start with setup SQL servers – Azure SQL Server quick start.

Here is a sample powershell script to quickly generate Azure SQL Logical Server inventory in Azure Cloud environment. Once executed, it generates a csv file with basic information of Azure SQL Servers. This script executes against all Subscriptions within a Tenant that the logged in user can access.

Before start using this script, make sure you have Azure Az Module installed and imported on the system. 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?view=azps-5.5.0

$date = Get-Date -UFormat "%m-%d-%Y"
$currentDir = $(Get-Location).Path
$oFile = "$($currentDir)\Azure_SQLServer_Details_$($date).csv"
if(Test-Path $oFile){Remove-Item $oFile -Force}

"SubscriptionName,SQLServerName,SQLServerFQDN,ResourceGroupName,Location,SQLAdminLogin,Version" | 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 = $sqlsrvfqdn = $resourceGroup = $location = $sqladmin = $version = ""

        $sqlsrvName = $_.ServerName
        $sqlsrvfqdn = $_.FullyQualifiedDomainName
        $resourceGroup = $_.ResourceGroupName
        $location = $_.Location
        $sqladmin = $_.SqlAdministratorLogin
        $version = $_.ServerVersion  "$subscriptionName,$sqlsrvName,$sqlsrvfqdn,$resourceGroup,$location,$sqladmin,$version" | 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.