I have a table which has a list of the added servers. But I want to add an extra column, that will identify is it Azure server or not. How can it be implemented?
2 Answers
I'll post this as an answer, as it uses a different parameter for SERVERPROPERTY, but this is very close to the answer I linked (Determine SQL Server version of linked server).
You'll want to instead use 'Edition' as the input parameter:
DECLARE @Edition sysname;
SELECT @Edition = CONVERT(sysname,Edition)
FROM OPENQUERY(YourLinkedServer,'SELECT SERVERPROPERTY(''Edition'') AS Edition;');
IF (CHARINDEX('Azure', @Edition)) > 0
SET @IsAzure = 1;
ELSE
SET @IsAzure = 0;
As per the documentation (SERVERPROPERTY (Transact-SQL)), one of the return values is 'SQL Azure'. That response "indicates SQL Database or SQL Data Warehouse", which I assume isn't a problem as you only want to know if the host is in Azure, not if it's a SQL Database or SQL Data Warehouse.
If, however, you do need to find out if it's a SQL Database, or SQL Data Warehouse (in Azure) you can use 'EngineEdition'; 5 indicates SQL Database and 6 SQL Data Warehouse.
Comments
To access whether a given VM is running from Azure or even AWS cannot be done from SQL Server it has no idea.
The only true way of determining this will be using PowerShell or other language of scripts to talk to the Azure Managed Instance metadata service. You can find that documented here: https://learn.microsoft.com/en-us/azure/virtual-machines/windows/instance-metadata-service
@@VERSIONreturns the version of the local instance. You can't use it to get the version of a remote instance.SERVERPROPERTYcan return more than just the number: SERVERPROPERTY (Transact-SQL) (Check the Edition option.)