CODEWITHSUNDEEP
sqlsql-serverazure
user8351574
user8351574

Reputation:

Get remote server version MS SQL

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?

Upvotes: 1

Views: 1592

Answers (2)

Thom A
Thom A

Reputation: 95544

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.

Upvotes: 2

user847990
user847990

Reputation:

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

Upvotes: 0

Related Questions