Why Taking Inventory is Crucial
Taking inventory of databases in Azure is a critical practice for any organization managing cloud resources. It ensures that all databases are accounted for, providing a comprehensive overview of the organization’s data assets. This visibility is essential for effective management and optimization of resources, as it allows for better planning and allocation based on actual usage and requirements.
An accurate inventory also plays a key role in identifying potential vulnerabilities and compliance issues. By knowing exactly what databases exist and where they reside, organizations can take proactive measures to secure sensitive data and adhere to regulatory standards.
Additionally, maintaining an up-to-date inventory facilitates efficient troubleshooting and maintenance. When issues arise, having detailed insights into the configuration and status of each database significantly reduces resolution time.
In short, keeping an accurate inventory of Azure databases supports operational efficiency, security, and compliance.
Who Does This Matter To
For roles such as product owners or operations managers, this task becomes even more critical. You may need to query the entire data estate to consolidate information for governance, reporting, or strategic planning. Without a centralized view, managing large-scale environments becomes challenging and prone to errors.
How Azure Resource Graph Explorer Helps
Azure Resource Graph Explorer is a powerful tool designed to extend Azure Resource Management by enabling efficient and performant resource exploration across multiple subscriptions. It allows users to:
- Query resources with complex filtering, grouping, and sorting by resource properties.
- Explore resources at scale across multiple subscriptions.
- Iterate queries based on governance requirements.
This makes ARG an ideal solution for managing and governing large-scale environments where visibility and control are paramount.
An Azure Resource Graph query like this one can help users to get an inventory of Azure SQL installations from data estates spanning across multiple subscriptions.
resources | where type =~ "microsoft.sqlvirtualmachine/sqlvirtualmachines" | project SubscriptionId = subscriptionId, ResourceGroup = resourceGroup, Name = name, type, Location = location, tags, elasticPoolId = "", ServiceType = "SQL VM", Edition=tostring(properties.sqlImageSku), Environment=tostring(tags.opEnvironment), PricingModel="vCores", ServiceTier=tostring(properties.sqlImageOffer), IsAHB = iff(tostring(properties.sqlServerLicenseType) =~ "AHUB", "Yes", iff(tostring(properties.sqlServerLicenseType) =~ "PAYG" and tostring(tolower(properties.sqlImageSku)) =~ "Developer", "Yes", "No")) | join kind=inner (resources | where ['type'] =~ 'microsoft.compute/virtualmachines' | project SubscriptionId = subscriptionId, ResourceGroup = resourceGroup, Name = name, Location = location, Num=tostring(properties.hardwareProfile.vmSize)) on Name, ResourceGroup, Location, SubscriptionId | project-away SubscriptionId1,ResourceGroup1,Name1,Location1 | union ( Resources | project SubscriptionId = subscriptionId, ResourceGroup = resourceGroup, Name = name, type, Location = location, tags, elasticPoolId = tolower(tostring(properties.elasticPoolId)), ServiceType = case(type =~ "microsoft.sql/managedinstances", "Azure SQL MI", type =~ "microsoft.sql/servers/databases", iff(tolower(properties.currentSku.tier)=~"datawarehouse", "Synapse", "Azure SQL DB"), type =~ "microsoft.sql/servers/elasticpools", "Azure SQL DB Elastic Pools", "Azure SQL VM"), Edition = case(type in~ ("microsoft.sql/managedinstances","microsoft.sql/servers/databases","microsoft.sql/servers/elasticpools"), iff(tolower(properties.currentSku.tier)=~"datawarehouse", "Dedicated Pools", "Azure SQL") , "SQL VM"), Environment=tostring(tags.opEnvironment), Num = tostring(case(type =~ "microsoft.sql/managedinstances", toint(sku.capacity), type =~ "microsoft.sql/servers/databases", toint(properties.currentSku.capacity), type =~ "microsoft.sql/servers/elasticpools", toint(properties.perDatabaseSettings.maxCapacity), 0)), PricingModel = case(type =~ "microsoft.sql/managedinstances", "vCores", type =~ "microsoft.sql/servers/databases", iff(tolower(properties.currentSku.tier)=~"datawarehouse", "DWU", iff((tolower(sku.tier) in ("basic","standard","premium")), "DTU", "vCores")), type =~ "microsoft.sql/servers/elasticpools", iff((tolower(sku.tier) in ("basic","standard","premium")), "DTU", "vCores"), "vCores"), ServiceTier = case(type =~ "microsoft.sql/managedinstances", sku.tier, tolower(properties.currentSku.tier)), MaxDatabaseSizeGB = iff(tostring(properties.currentSku.tier)=~"hyperscale", 1000,properties.maxSizeBytes/(1024 * 1024 * 1024)), IsAHB = case(properties.licenseType == 'BasePrice', "Yes", "No"), Is_Replica=iff(tostring(properties.secondaryType) in("Geo","Standby"), "Yes","No"), Is_Standby_Replica=iff(tostring(properties.secondaryType)=~ "Standby", "Yes","No") | where (type =~ 'Microsoft.Sql/servers/databases' or type =~ "microsoft.sql/managedinstances" or type =~ "microsoft.sql/servers/elasticpools") and ServiceTier !in~ ("system") | join kind=leftouter ( Resources | where type =~ 'microsoft.sql/servers/elasticpools' | project elasticPoolId = tolower(id), elasticPoolName = name, elasticPoolSize=tostring(properties.perDatabaseSettings.maxCapacity)) on elasticPoolId | project-away elasticPoolId1 ) | order by ['Name'] asc
This process can be further bolstered by automating the running of Azure Resource Graph queries and even acting on the results.
Let us know if any of these resonate with you or if have more suggestions!
Feedback
If you have feedback or suggestions for improving this data migration asset, please comment here or contact the Azure Databases SQL Customer Success Engineering Team (datasqlninja@microsoft.com) directly. Thanks for your support!
Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.