How to test if database is hosted on SQL Azure?

Question

Is it possible to test whether a database is hosted on SQL Azure? I am looking at SqlAzureExecutionStrategy for EF6 and only want to apply if the database is actually SQL Azure database.

Currently I am testing if App is running within Azure. However we are looking at allowing clients to host DB themselves so would like some way to identify if DB is a SQL Server or Azure SQL.

Assume EF won't know as it is hiding the implementation details.

Could just have a config setting I guess. Just wondered if it was technically possible.

Can't you just run SELECT @@version - it will be something like Microsoft SQL Azure (RTM) – 11.0.1465.26 Aug 10 2011 22:54:49 Copyright (c) Microsoft Corporation — StuartLC
– StuartLC, Commented Feb 5, 2014 at 16:13

gvee · Accepted Answer · 2021-08-18 08:12:51Z

42

Updated August 2021 to align with updated documentation

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'
         ELSE 'Unknown'
       END

http://technet.microsoft.com/en-us/library/ms174396.aspx

edited Aug 18, 2021 at 8:12

answered Feb 5, 2014 at 16:14

gvee

17.2k42 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sam H · Accepted Answer · 2015-08-11 18:45:38Z

20

I've been using the same function as gvee, ServerProperty(), but with a different property name:

IF ServerProperty('Edition') = 'SQL Azure'
    PRINT 'true'

For me, this is easier to read and also self-documenting.

answered Aug 11, 2015 at 18:45

Sam H

9188 silver badges17 bronze badges

1 Comment

Lukas.Navratil Over a year ago

I was using this code as well, but then I found a case where it didn't work because of collation issue. Check dba.stackexchange.com/questions/317945/…

Collectives™ on Stack Overflow

How to test if database is hosted on SQL Azure?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related