We are excited to announce the public preview of viewing Databases for Azure Arc-enabled SQL Server.
The feature surfaces all the active databases and their configurations for each of the Arc enabled SQL Servers in Azure.
Customer Scenario
Today, customers and partners manage hundreds or even thousands of active databases. For each of these databases, it is essential to be able to create an accurate mapping of the active configurations. This could be for inventorying or even reporting purposes. Centralizing this database inventory in Azure using Azure Arc allows you to create a unified view of all your databases in one place regardless of which infrastructure those databases might be located on – in Azure, in your datacenter, in edge sites, or even in other clouds.
Partner Scenario
With the public preview of viewing SQL Server Databases for Azure Arc-enabled SQL Server, partners can leverage this information in building a service map and providing migration readiness analysis for their customers.
“Being able to view SQL Server Databases in Azure Arc is essential for our team when it comes to creating migration readiness analyses for our customers. Additionally, this feature allows our Insights platform to understand inventory and activity levels of existing databases, which directly tie to and drive cost savings.” said Mike De Luca, Global Lead - Hybrid Cloud, at Avanade.
This image shows how Avanade is leveraging the feature of SQL Server Databases - Azure Arc in their Isotope tool. The databases are now visible in the left navigation pane and the diagram shows the relationship between Arc-enabled SQL Server instances and the Arc-enabled server that they are running on. This helps them visualize the dependency graph between applications, servers, and databases to help plan out migrations to Azure. The data for populating Isotope or other tools can be queried through the Azure Resource Manager APIs or the Azure Graph API.
How to Leverage Azure Resource Graph to Query Data
Here are some example scenarios showing how you use Azure Resource Graph to query data which is available with the public preview of viewing Databases for Azure Arc-enabled SQL Server.
Scenario 1: Get 10 databases just to see the results and what properties are available for querying:
Resources
| where type =~ 'Microsoft.AzureArcData/sqlServerInstances/databases'
| limit 10
Many of the most interesting properties to query on are in the “properties” property. You can explore the available properties by running this query and then clicking on ‘See details’ on a row. This will show the properties in a json viewer on the right side.
Resources
| where type =~ 'Microsoft.AzureArcData/sqlServerInstances/databases'
| project properties
You can navigate the hierarchy of the properties json by using a period in between each level of the properties json.
Scenario 2: Get all the databases that are not encrypted:
Resources
| where type =~ 'Microsoft.AzureArcData/sqlServerInstances/databases'
| where properties.databaseOptions.isEncrypted == false
Scenario 3: Obtain the count of databases which are encrypted vs not encrypted:
Resources
|extend isEncrypted =properties.databaseOptions.isEncrypted
|where type contains("microsoft.azurearcdata/sqlserverinstances/databases")
|project name,isEncrypted
|summarize count() by tostring(isEncrypted)
| order by ['isEncrypted'] asc
Scenario 4: Show all the databases which are not encrypted:
Resources
|extend isEncrypted =properties.databaseOptions.isEncrypted
|where type contains("microsoft.azurearcdata/sqlserverinstances/databases") and isEncrypted ==false
|project name,isEncrypted
Scenario 5: Get all the databases in westus3 location with compatibility level of 160:
Resources
| where type =~ 'Microsoft.AzureArcData/sqlServerInstances/databases'
| where location == "westus3"
| where properties.compatibilityLevel == "160"
Scenario 6: Show the SQL Server version distribution:
Resources
|extend SQLversion =properties.version
|where type contains("microsoft.azurearcdata/sqlserverinstances")
|project name,SQLversion
|summarize count() by tostring(SQLversion)
Scenario 7: SQL Server version, edition and license type
Resources
|extend SQLversion =properties.version
|extend SQLEdition =properties.edition
|extend lincentype =properties.licenseType
|where type contains("microsoft.azurearcdata/sqlserverinstances")
|project name,SQLversion,SQLEdition,lincensetype
Scenario 8: Show a count of databases by compatibility level ordered by the compatibility level:
Resources
| where type =~ 'Microsoft.AzureArcData/sqlServerInstances/databases'
| summarize count() by tostring(properties.compatibilityLevel)
| order by properties_compatibilityLevel asc
You can also create charts and pin them to dashboards.
Getting Started With Viewing SQL Server Databases in Azure Arc
Please begin by completing the prerequisites to ensure that you can enable this feature.
Inventorying the Databases
- Start by finding the Azure Arc-enabled SQL Server instance in Azure Portal and then select the given SQL Server resource.
- In the Azure Portal, you will see a section called Data Management. Below this, you will see a databases tab. Click on the databases tab.
- From here, you will see all the databases associated with the given SQL Server – Azure Arc instance and you have the ability to search for any given database with a default set of properties like status, creation time, earliest restore time as can be seen in the image below.
Viewing Properties for a Given Database
Once you are on the databases tab in the Azure Portal, proceed by clicking on the database for which you would like to view database properties.
As seen in the image below, you can now access over 15 active database properties in the Azure Portal directly as can be seen in the image below.
Additionally, after you create, modify, or delete a database in SQL Server Management Studio, you will now be able to see a reflection of those changes in Azure Portal within an hour as well.
Getting Started
Get started today with viewing your SQL Server Databases - Azure Arc today using the Azure Portal.
Click on the Feedback button in the Azure Portal to provide us feedback on this new feature!
Nikita Takru
Product Manager at Microsoft, Azure Data