View SQL Server databases - Azure Arc

Applies to: SQL Server

You can inventory and view SQL Server databases in Azure.

Prerequisites

Before you begin, verify that the SQL Server instance that hosts the databases:

• Is hosted on a physical or virtual machine running Windows operating system.
• Is SQL Server 2014 (12.x) or later.
• Is connected to Azure Arc. See Connect your SQL Server to Azure Arc.
• Is connected to the internet directly or through a proxy server.
• Make sure that database names adhere to naming conventions and don't contain reserve words. For a list of reserved words, see Resolve errors for reserved resource names.
• To view the database size and space available, provide the following permission:
  • The built-in SQL Server login NT AUTHORITY\SYSTEM must be a member of the SQL Server sysadmin server role, for all the SQL Server instances running on the machine.

Inventory databases

1. Locate the SQL Server enabled by Azure Arc instance in the Azure portal.
2. Select the SQL Server resource.
3. Under Data management, select Databases.

The Azure portal shows SQL Server databases - Azure Arc. Use this area to view the databases that belong to the instance.

View database properties

To view database properties for a specific database, select the database on the portal.

After you create, modify, or delete a database, changes are visible in the Azure portal within an hour.

On the Database page, you see 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, you see all the properties for that database, which are also visible in SQL Server Management Studio (SSMS).

How to use Azure Resource Graph to query data

Here are some example scenarios showing how you use Azure Resource Graph to query data that is available when viewing SQL Server enabled by Azure Arc databases.

Scenario 1: Get 10 databases

Get 10 databases and return what properties 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 this query and then select See details on a row. This 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 in between each level of the properties json.

Scenario 2: Get all the databases that have 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 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
| 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

You can also create charts and pin them to dashboards.

Known issues

Databases deleted on-premises might not be immediately deleted on Azure. There's no impact on how database CRUD operations happen on-premises.

Related content

• Protect SQL Server enabled by Azure Arc with Microsoft Defender for Cloud
• Configure best practices assessment on a SQL Server enabled by Azure Arc instance