how to find out if I am in azure or not?

All we need is an easy explanation of the problem, so here it is.

I am talking about sql server here.

I can connect to azure.
I can also manage permissions.

But while doing some automation, I need to know if I am in azure or normal sql.
how can I achieve that?

I have been using the @@version. However, it is said it is not accurate.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

One option could be EngineEdition .

Try:

SELECT CASE ServerProperty('EngineEdition')
         WHEN 1 THEN 'Personal'
         WHEN 2 THEN 'Standard'
         WHEN 3 THEN 'Enterprise'
         WHEN 4 THEN 'Express'
         WHEN 5 THEN 'SQL Database'
         WHEN 6 THEN 'Azure Synapse Analytics'
         WHEN 8 THEN 'Azure SQL Managed Instance'
         WHEN 9 THEN 'Azure SQL Edge'
         WHEN 11 THEN 'Azure Synapse serverless SQL pool'
       END

Note. There are different ways of getting the SQL Server version on the link above using SERVERPROPERTY.

Another simpler method:

SELECT CASE
         WHEN ServerProperty('Edition') = 'SQL Azure'
         THEN 'Azure'
         ELSE 'No Azure' END AS server_version;

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c10db6e4d5def947657b780ede48fee1

Method 2

at the moment July/2022 I am using the following script.
I have a few machines in azure, and sql server 2016,2017 and 2019.

set nocount on

DECLARE @version  varchar(20)
SELECT  @version = convert(varchar, serverproperty('ProductVersion'))

IF @version NOT LIKE '[0-9][0-9].%'
   SELECT @version = '0' + @version

PRINT @version


IF @version < '09.00.3042'
   RAISERROR('dbo.sp_showADGroups requires SQL Server 2005 SP2 or later', 16, 127)

select [version is] = @version, 
@@VERSION

SELECT @Version=left(@Version,2)

SELECT [@Version][email protected]

IF @VERSION = '12' 
   PRINT 'We are in Azure'
ELSE
   PRINT 'Not Azure'


 SELECT SERVERPROPERTY('EngineEdition')
 --this returns 8 in azure
 -- this returns 2 in sql 2019
 --this returns 3 in sql 2016

the query above returns the following – in azure:

how to find out if I am in azure or not?

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply