How to check if a database is Azure SQL or Azure Managed Instance? (4377582)

Using SERVERPROPERTY for Platform Detection

The SERVERPROPERTY function is useful for identifying the platform, edition, and engine edition of the connected SQL Server instance. Here’s a query that will help you determine the platform:

SELECT 
    SERVERPROPERTY(‘ProductVersion’) AS ProductVersion,
    SERVERPROPERTY(‘Edition’) AS Edition,
    SERVERPROPERTY(‘EngineEdition’) AS EngineEdition,
    SERVERPROPERTY(‘MachineName’) AS MachineName;

Explanation of the results

• ProductVersion: Shows the version of SQL Server, which will be the same for both Azure SQL Database and Managed Instance, but not very helpful for platform differentiation on its own.
• Edition:
  • For Azure SQL Database, it will return “SQL Database”.
  • For Azure SQL Managed Instance, it will return “SQL Server”.
• EngineEdition:
  • 1: SQL Server (on-premises or Managed Instance).
  • 2: Azure SQL Database.
  • 3: Azure Synapse Analytics.
• MachineName: This might give a logical name but won’t differentiate between the platforms clearly. For Managed Instance, it can refer to the name of the SQL Server instance.

Users can also check for a SQL Server agent.

SQL Server Agent is only available in Azure SQL Managed Instance (not in Azure SQL Database). If you query system views related to SQL Server Agent and they return results, you’re likely using Managed Instance.

SELECT * 
FROM msdb.dbo.sysschedules;

• For Managed Instance: This will return the scheduled jobs.
• For Azure SQL Database: This query will likely return no rows or fail, as SQL Server Agent is not available in SQL Database.