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

An Azure SQL database requires an Azure SQL agent.

An Azure Managed Instance require as SQL Server agent.

How can you differentiate between the two?

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;

An explanation of the results are available from Microsoft's knowledgebase here where it describes the values of the SERVERPROPERTY

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.