List All Azure SQL Databases and Properties with PowerShell Script

By: Jim Evans | Updated: 2024-03-25 | Comments | Related: > Azure SQL Database

Problem

Some DBAs support many Azure SQL Database Servers across several subscriptions. These Azure SQL Database Servers may be for various environments like Dev, Test, QA, and Production. Occasionally, I want to see if any new Azure SQL Servers were created or removed. How do I get a list of these Azure SQL Database Servers and their attributes to review and maintain a fresh inventory of my Azure SQL Database Servers?

Solution

One solution is to use a PowerShell script to produce a list of Azure SQL Database Servers. This tip's example will return the Server Name, Subscription, Resource Group, Location, Administrator Login, Entra Admin, and CreateDate for all Azure SQL Database Servers. This PowerShell script requires the Az.Accounts and Az.Sql PowerShell modules to be installed. In addition to getting a list of Azure SQL Database Servers, we will show how to get a list of subscriptions and Azure SQL Database Servers and how to create an output file in a format that can be opened in Microsoft Excel.

Review PowerShell Module Commands

To get a list of Azure SQL Database Servers, the script will require the installation of PowerShell modules AZ, which includes many service modules. Or you can install Az.Accounts and Az.Sql, which are smaller parts of the Az module, if not already installed. Below are the commands from each module to be used in this script.

AZ.Accounts Module
- Connect-AzAccount: To login to Azure. This will pop up the Azure Sign-in. Here, you will enter your Azure account credentials. See the picture below.

Figure 1: Azure Sign-in pop up from Connect-AzAccount

Get-AzSubscription: Get a list of subscriptions that you have access to.
Set-AzContext: Sets the current session to use this subscription. In this case, if you can access multiple subscriptions, we look through each subscription, getting a list of SQL Servers for each.
Az.Sql Module:
- Get-AzSqlServer: Gets the list of SQL Servers hosting Azure SQL Databases. This will also bring back any Azure SQL Servers hosting Synapse.
- Get-AzSqlServerActiveDirectoryAdministrator: Gets the Microsoft Entra Account for each SQL Server.
- Get-AzSqlDatabase: Here, we pass the 'master' database to get the create date, the date the Azure SQL Server was created.

Pre-Steps for PowerShell

Launch PowerShell Integrated Scripting Environment (ISE) with Run as Administrator. For this article, I'm running Windows PowerShell 5.1.

Verify the required PowerShell modules are installed. To see a list of installed modules, run the following command:

# List Installed Modules
Get-Module -ListAvailable

If Az.Accounts and Az.Sql are NOT installed, run these commands to install them:

# Install Modules
Install-Module -Name Az.Accounts -Force
Install-Module -Name Az.Sql -Force

The modules Az.Accounts and Az.SQL must be loaded into memory. To list modules currently loaded, run:

# List Modules loaded in Memory
Get-Module

To load the modules Az.Accounts and Az.SQL into memory, run:

# Load Modules
Import-Module -Name Az.Accounts
Import-Module -Name Az.Sql

Build Out the PowerShell Script

The script below will go through the following major steps:

Set your Output file and create the file with a tab delimited header.
Connect to Azure, which will pop up a 'Sign In' screen to enter your account credentials.
Get a list of all subscriptions that you have access to.
Loop through the list of Subscriptions to get the lists of Azure SQL Database Servers.
Get a list of Azure SQL Database Servers.
Loop through all SQL Servers per Subscription.
Get attributes for the report.
Write the output to the file. In the output, I use “ `t “ (without the quotes) to add tabs between the columns.

### PS Script to get a List of Azure SQL Database Servers and attributes
# 1. Define the Outputfile
$OutFile = "C:\temp\AzureSQLDB_LIST.csv"
 
# Output file Table delimited Header; Should be able to out in excel and save as name.xlsx!
# Subscription, Server Name, Resource Group, Location, Administrator Login, Entra Admin, CreateDate
Write-Output "Subscription`tServer Name`tResource Group`tLocation`tAdministrator Login`tEntra Admin`tCreateDate" > $OutFile
 
 
# 2. Connect to Azure. This will popup a 
Connect-AzAccount
 
# Optional: Suppress Breaking Change Warnings
Update-AzConfig -DisplayBreakingChangeWarning $false
 
# 3. Get all Subscriptions
$subscriptions = Get-AzSubscription
 
# 4. Loop Thru Subscriptions
foreach ($subscription in $subscriptions) {
 
      # Set your context to the Subscription
        Set-AzContext -SubscriptionId $subscription.Id
 
      # 5. Get all Azure SQL Servers for the set Subscription
      # Note: this also pulls in Synapse Workspace SQL Pool Servers
      #    Also for synapse Server we are seeing potentially an old ResourceName which is diff from the Name shown in the AZ Console! 
        $servers = Get-AzSqlServer
 
      # 6. Loop through each server
      foreach ($server in $servers) {
           
          # 7. Get the Server Attributes for the Report
          # Get the server's resource group
          # $resourceGroup = Get-AzResourceGroup -Name $server.ResourceGroupName
 
          # Get the Microsoft Entra Account for the SQL Server
          $ADADmin = Get-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName $server.ResourceGroupName -ServerName $server.ServerName
 
           # Get the master database creation date
           $SQLMaster = Get-AzSqlDatabase -ResourceGroupName $server.ResourceGroupName -ServerName $server.ServerName -DatabaseName "master"
 
 
            #Use `t for Tab Delimited to open in Excel
            # 8. Write to the Output File 
            Write-Output "$($subscription.Name)`t$($server.FullyQualifiedDomainName)`t$($server.ResourceGroupName)`t$($server.Location)`t$($server.SqlAdministratorLogin)`t$($ADADmin.DisplayName)`t$($SQLMaster.CreationDate)" >> $OutFile
        }
}

Run the Script

After launching PowerShell ISE with Run as Administrator and ensuring the required modules are installed, paste the above script into a new script window. Note the two run buttons in PowerShell ISE! The first will run the entire script. The second will run just the highlighted portion of the script. In this case, you should run the whole script at once.

Running the script will show the output of each subscription iterated through. The Server information for all the Azure SQL Database Servers will be written to the file specified.

View the Output

After the script is completed, you can review the results by opening the output file in Excel. Typically, a CSV file will easily open in Excel. Since the file output is tab-delimited, the file will open with the data laid out in columns. From here, you can choose File, Save As, save the file as an XLSX file, and add formatting as you please. Below is an example of the results in Excel.

Wrap Up

This tip provides a way to pull a list of Azure SQL Database Servers and some relative attributes from multiple Azure subscriptions. Review Microsoft to learn about the PowerShell modules and commands to find additional server attributes.

Next Steps

Review other PowerShell Scripts and learn more tips for running scripts across multiple SQL Servers or databases:

Use PowerShell to Query Multiple Azure SQL Databases and Save Results Centrally
Review the MSSQL PowerShell Library: SQL Server PowerShell Tips
Combine Multiple SQL Server Query Results with Registered Servers
Query All Databases Across Multiple SQL Servers and Store Results in Table
Learn about Azure SQL Database vs Managed Instance: Azure SQL Managed Instance vs. Azure SQL Database
Is Azure SQL Database a Good Choice for your Database Applications?
Introduction to Azure SQL Database Managed Instances

About the author

Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-03-25