Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to test if database is hosted on SQL Azure?

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.

like image 882
GraemeMiller Avatar asked Feb 05 '14 16:02

GraemeMiller


People also ask

How do I check if SQL database is in use?

Another way to see if your database is in use is to look and see if the indexes are being used. Information on index usage is held in the sys. dm_db_index_usage_stats table since the last server reboot, and can be queried using this statement which can be tailored to select the data you need.

Is Azure SQL Database on premise?

No. Azure SQL Database is a fully managed database service—Microsoft operates SQL Server for you and ensures its availability and performance.

Can you ping Azure SQL server?

The ping command is supposed to give you a timeout because SQL Azure servers never respond to ping requests, but above the timeout replies it tells you the actual IP of your SQL Azure server. Use that IP to telnet your SQL Azure server.


2 Answers

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

like image 164
gvee Avatar answered Oct 22 '22 11:10

gvee


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.

like image 12
Gyromite Avatar answered Oct 22 '22 12:10

Gyromite