Manage inventory of SQL Server resources with Azure Arc
Applies to: 
SQL Server
When SQL Server engine instances or associated services are enabled by Azure Arc, you can use Azure to manage your inventory.
Prerequisites
Verify that the SQL Server service is
- Version SQL Server 2014 (12.x) or later.
- On a physical or virtual machine that's running the Windows operating system.
- Connected to Azure Arc. See Connect your SQL Server to Azure Arc.
- Connected to the internet directly or through a proxy server.
Inventory databases
To inventory SQL Server databases, make sure that database names adhere to naming conventions and don't contain reserved words. For a list of reserved words, see Resolve errors for reserved resource names.
To inventory databases:
- Locate the instance of SQL Server enabled by Azure Arc in the Azure portal.
- Select the SQL Server resource.
- Under Data management, select Databases.
- Use the SQL Server databases - Azure Arc area to view the databases that belong to the instance.
To view the database size and space available, make sure that the built-in SQL Server login NT AUTHORITY\SYSTEM is a member of the SQL Server sysadmin server role for all the SQL Server instances running on the machine.
View database properties
To view properties for a specific database, select the database in the portal.
After you create, modify, or delete a database, changes appear in the Azure portal within an hour.
The Databases pane shows the following information:
- Information about the data collection and upload:
- Last collected time
- Upload status
- Information about each database:
- Name
- Status
- Creation time
- Earliest restore point
When you select a specific database, all the properties for that database appear. These properties are also visible in SQL Server Management Studio.
Use Azure Resource Graph to query data
Here are some example scenarios that show how you use Azure Resource Graph to query data that's available when you're viewing Azure Arc-enabled SQL Server databases.
Scenario 1: Get 10 databases
Get 10 databases and return properties that are available to query:
resources
| where type == 'microsoft.azurearcdata/sqlserverinstances/databases'
| limit 10
Many of the most interesting properties to query are in the properties property. To explore the available properties, run the following query and then select See details on a row. This action returns 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 between each level of the JSON.
Scenario 2: Get all the databases that have the database option AUTO_CLOSE set to ON
| where (type == 'microsoft.azurearcdata/sqlserverinstances/databases' and properties.databaseOptions.isAutoCloseOn == true)
| extend isAutoCloseOn = properties.databaseOptions.isAutoCloseOn
| project name, isAutoCloseOn
Scenario 3: Obtain the count of databases that are encrypted vs. not encrypted
resources
| where type == 'microsoft.azurearcdata/sqlserverinstances/databases'
| extend isEncrypted = properties.databaseOptions.isEncrypted
| summarize count() by tostring(isEncrypted)
| order by ['isEncrypted'] asc
Scenario 4: Show all the databases that aren't encrypted
resources
| where (type == 'microsoft.azurearcdata/sqlserverinstances/databases' and properties.databaseOptions.isEncrypted == false)
| extend isEncrypted = properties.databaseOptions.isEncrypted
| project name, isEncrypted
Scenario 5: Get all the databases by region and compatibility level
This example returns all databases in the westus3 location with a 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
| where type == 'microsoft.azurearcdata/sqlserverinstances'
| extend SQLversion = properties.version
| summarize count() by tostring(SQLversion)
Scenario 7: Show a count of databases by compatibility
This example returns the number of databases, ordered by the compatibility level:
resources
| where type == 'microsoft.azurearcdata/sqlserverinstances/databases'
| summarize count() by tostring(properties.compatibilityLevel)
| order by properties_compatibilityLevel asc
Inventory associated services
SQL Server associated services include:
- SQL Server Database Engine
- SQL Server Analysis Services (SSAS)
- SQL Server Integration Services (SSIS)
- SQL Server Reporting Services (SSRS)
- Power BI Report Server (PBIRS)
Each installation of an associated service is represented in Azure Resource Manager (ARM) as a SQL Server instance with serviceType property showing the specific service. The property is defined as follows:
"serviceType": {
"type": "string",
"enum": [ "Engine", "SSAS", "SSIS", "SSRS", “PBIRS” ],
"default": "Engine"
}
Create inventory dashboard
You can also create charts and pin them to dashboards.
Known issues
Resources deleted on-premises might not be immediately deleted in Azure. For example, if you delete a database, the image of the database on Azure remains until the server resource synchronizes.