Extract SQL Server Instance details using SERVERPROPERTY() function

The SERVERPROPERTY() function of SQL Server allows you to retrieve the information related to that instance. Let's check out this function.

There are multiple ways to check server-level properties in SQL Server. However, SQL Server offers a system function that can return property information for a SQL Server instance. We can get SQL Server instance-specific properties like default data and log directories, collation type, authentication mode using this system function SERVERPROPERTY(). Keep reading this tutorial to learn about various server-level properties for a SQL Server instance.

Retrieve Product Build, Version, and Edition for the SQL Server Instance

SELECT  

  SERVERPROPERTY('MachineName') AS [Server Name],

  SERVERPROPERTY('InstanceName') AS [SQL Server Instance Name], 

  SERVERPROPERTY('Edition') AS [Edition],

  SERVERPROPERTY('ProductVersion') AS [Product Version],  

  SERVERPROPERTY('ProductLevel') AS [Product Level];  

GO

Note: The above script returns NULL for SQL Server instance column in case of default instance MSSQLSERVER.

Retrieve product Major, Minor, and database engine details for the SQL Server Instance

SELECT  
 SERVERPROPERTY('MachineName') AS [Server Name],
 SERVERPROPERTY('ProductVersion') AS [Product Version],  
 SERVERPROPERTY('ProductMajorVersion') AS [Product Major Version],
 SERVERPROPERTY('ProductMinorVersion') AS [Product Minor Version],
 SERVERPROPERTY('EngineEdition') AS [Database Engine Edition] 
GO

The Database Engine Edition can have the following values.

• 2 = Standard (This is returned for Standard, Web, and Business Intelligence.)

• 3 = Enterprise (This is returned for Evaluation, Developer, and Enterprise editions.)

• 4 = Express (This is returned for Express, Express with Tools, and Express with Advanced Services)

• 5 = SQL Database

• 6 = Microsoft Azure Synapse Analytics

• 8 = Azure SQL Managed Instance

• 9 = Azure SQL Edge (This is returned for all editions of Azure SQL Edge)

• 11 = Azure Synapse serverless SQL pool

What is the default Data, Log, and Backup Directories for my SQL Server Instance

The following SQL script returns the default directories for data files, log files, and backups in SQL instance

SELECT  
 SERVERPROPERTY('MachineName') AS [ComputerName],
 SERVERPROPERTY('InstanceDefaultBackupPath') AS [Default Backup Directory],
 SERVERPROPERTY('InstanceDefaultDataPath') AS [Default Data Directory],  
 SERVERPROPERTY('InstanceDefaultLogPath') AS [Default Log Directory];  

How to check if you have following services - PolyBase, Full-text search, Advanced Analytics, TempDB Metadata Memory optimized, In-Memory OLTP, Windows failover clustering, SQL Server Always On, Authentication type in SQL Server instance

SELECT  
  SERVERPROPERTY('ServerName') AS [Server Name], 
  SERVERPROPERTY('IsPolyBaseInstalled') AS [Polybase Installed],  
  SERVERPROPERTY('IsFullTextInstalled') AS [Full Text Search Installed],
  SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS [Advanced Analytics Installed],
  SERVERPROPERTY('IsTempDbMetadataMemoryOptimized') AS [TempDB Enabled for Memory Optimized Tables],  
  SERVERPROPERTY('IsXTPSupported') AS [IN Memory OLTP Supported],
  SERVERPROPERTY('IsClustered') AS [Clustered],  
  SERVERPROPERTY('IsHadrEnabled') AS [HADR],
  SERVERPROPERTY('IsIntegratedSecurityOnly') AS [Authentication Type];
GO

The above query returns following column values.

PolyBase:

Returns whether the server instance has the PolyBase feature installed.

• Value 1: PolyBase is installed.

• Value 0: PolyBase is not installed

Full-Text Search

It returns whether the full-text search components are installed on the current instance of the SQL Server.

• Value 1 = Full-text and semantic indexing components are installed.

• Value 0 = Full-text and semantic indexing components are not installed.

Advanced Analytics

• Value 1: if the Advanced Analytics feature was installed during setup;

• Value 0: if Advanced Analytics was not installed.

TempDB Metadata Memory optimized

• Value 1: If TempDBhas been enabled to use memory-optimized tables for metadata;

• Value 0: if TempDB is using regular, disk-based tables for metadata.

In-Memory OLTP

• Value 1: Server supports In-Memory OLTP.

• Value 0= Server does not support In-Memory OLTP.

Cluster

• 1 = Clustered.

• 0 = Not Clustered.

HADR

• 0 = The Always On availability groups feature is disabled.

• 1 = The Always On availability groups feature is enabled.

Authentication type

• 1 = Integrated security (Windows Authentication)

• 0 = Not integrated security. (Both Windows Authentication and SQL Server Authentication.)