Azure SQL Database is a powerful, scalable, and cloud-based relational database service offered by Microsoft Azure. Managing these databases efficiently in a large organization can be challenging. Keeping track of Azure SQL Servers, their configurations, and usage is crucial for effective management. PowerShell can be your ally in this task, helping you automate the process of generating reports on Azure SQL Servers in your organization.
In this blog post, I am sharing a PowerShell script that generates a comprehensive report on Azure SQL Servers within your organization across multiple subscriptions.
Prerequisites for this Solution:
Download the script from Git or PowerShell gallery from below links:
Git:
https://github.com/VikasSukhija/Downloads
CloudAzureSQLServerDataBaseReport.ps1
PowerShell Gallery:
https://www.powershellgallery.com/packages/CloudAzureSQLServerDataBaseReport
Install-Script -Name CloudAzureSQLServerDataBaseReport
Update the below section of the script with your own parameters:
Note: Script will auto generate encrypted password first time it is run and save it in the same folder as shown in below screenshot.
Report will be generated and send on the email id specified and saved in reports folder.
Logic of the script:
It first uses Azure Graph Query to fetch all the Subscriptions that host SQL servers.
After that it loops thru each subscription and get the relevant information about the hosted SQL server so that report can be generated.
Here is the sample report:
After running it for first time interactively you can schedule it to run via task scheduler as password has been stored as encrypted credentials inside the script folder.
Note: In case your organization, MFA is enabled and its does not work then you need to change the authentication piece to use app-based auth. (service principal auth)
Thanks for reading and downloading…
Tech Wizard
Tech Wizard 