1

Is it ok to assume that if the server name ends with "database.windows.net" that the database is hosted on azure?

I tried executing this code on my azure db:

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

But it returned "SQL Database"...

How can I reliably discern if a database or sql server is hosted on azure?

I should be writing the code to determine this in c#

right now it might be something like:

if (serverName.Contains("database.windows.net"))
   {
            //is on azure 
   }
   else
   {
            //is not hosted on azure
   }
the1.9gpaProgrammer
  • 95
  • 1
  • 10
  • One could conceivably have a server name "database.windows.net.mysite.com" or even override their DNS so that "server.database.windows.net" points to an on-prem database. One could also host a database in Azure and not have a server name that ends in database.windows.net. Why do you need to know whether the database is hosted in Azure or not? – mason Nov 03 '22 at 13:41
  • 1
    `SQL Database` === `Azure SQL Database`. In other words, when the function returns 5, it's Azure SQL Database. Your code just shortens that and leaves out the 'Azure' for whatever reason. – Stuck at 1337 Nov 03 '22 at 13:44
  • `EngineEdition` will be reflect Standard, Enterprise, etc. when the SQL Server instance is hosted by an Azure VM. Not sure if you care where a physical machine or VM is hosted. – Dan Guzman Nov 03 '22 at 14:43
  • select @@version as version; of select serverproperty('Edition') as [edition]; – Poiter Nov 03 '22 at 14:58
  • I think this link can help you https://azure.microsoft.com/it-it/blog/checking-your-sql-azure-server-connection/ – Nopesound Nov 03 '22 at 16:41

1 Answers1

2

I tried with your code I also got the same issue. Image for reference:

enter image description here

We can replace 'Azure SQL Database' in place of 'SQL Database'.
I used below code to determine the database:

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

enter image description here

Bhavani
  • 1,725
  • 1
  • 3
  • 6