Problem
SQL Server has several different server property settings and in this tutorial, we look at a quick way to query this information using the serverproperty function.
Solution
There are multiple ways to check server level properties in SQL Server, but Microsoft also offers a system function which can be used to return property information for a SQL Server instance. We can get SQL Server instance specific properties like default data and log directories, which version of .NET CLR is installed, collation type, authentication mode or whether your instance is SQL Server express for example, using this one system function SERVERPROPERTY(). Keep reading this tutorial to get various server level properties for a SQL Server instance.
Check Various Server Lever Properties of SQL Server using SERVERPROPERTY()
I will present various use cases of this function in the below sections. You can copy and paste these T-SQL statements to get similar output for your system.
List Product Build, Version and Edition for the SQL Server Instance
First thing I will show is how to check SQL Server instance name, server name, edition of SQL Server, SQL Server version and product level by running the below command.
--List product level information of 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
Here is the output of the above T-SQL statement. One thing noticeable is that the SQL Server instance name shows as NULL. This function displays a NULL value for the default instance of SQL Server. We can also see the product level shows as RTM along with the version and edition.
Now, suppose you have to get similar information for more than one server or instance. To do this, you can create a local server group in Registered Servers in SQL Server Management as shown in left pane in the below screenshot. Then execute this same query on all instances at once that have been registered as part of the group. The same login will be used to execute the script that you have used to register the instance. You can see there are two servers registered in left side pane and now I will run the script on this server group. You can see the output for both servers below.
This way you can save lot of time and easily get server level property information for more than one server. All of the queries in this article can be run on one instance or using registered servers.
List Major, Minor and Build Numbers for the SQL Server Instance
Let’s say you have a requirement to gather all servers running on your environment and then see which are running SQL Server 2019 or 2017. Or you might need to get the major or minor build versions. Run the below command to get SQL Server major, minor, build number and database engine edition.
--List product level information of 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('ProductMajorVersion') AS [Product Major Version],
SERVERPROPERTY('ProductMinorVersion') AS [Product Minor Version],
SERVERPROPERTY('ProductBuild') AS [Product Build],
SERVERPROPERTY('EngineEdition') AS [Database Engine Edition],
GO
You can see the output of above command in below screenshot.
The SQL Server version has four parts. Product Version, Major Version, Minor Version and the Product Build. This information is very useful and required from time to time.
Another output below is the Database Engine edition which shows as 3. If your SQL Server instance is running Developer, Enterprise or Evaluation edition then you will be getting its edition as 3. See the below list of values.
1 – Personal or Desktop Engine (Not available in SQL Server 2005 (9.x) and later versions.)
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 (formerly SQL Data Warehouse)
8 – Azure SQL Managed Instance
9 – Azure SQL Edge (this is returned for both editions of Azure SQL Edge)
List Default Data, Log and Backup Directories for the SQL Server Instance
Another use case of this function is to get the default backup, data and log file directories of your SQL Server instance. You can use the below T-SQL statement to get this very useful information. The default backup directory is newly added property in this system function whereas default data and log directly were added in SQL Server 2012.
--List default backup, data and log directories of SQL Server Instance
SELECT
SERVERPROPERTY('MachineName') AS [ComputerName],
SERVERPROPERTY('InstanceName') AS [SQL Server Instance Name],
SERVERPROPERTY('InstanceDefaultBackupPath') AS [Default Backup Directory],
SERVERPROPERTY('InstanceDefaultDataPath') AS [Default Data Directory],
SERVERPROPERTY('InstanceDefaultLogPath') AS [Default Log Directory];
You can see the screenshot displays the output with all these default directories. You can use this code in any of your scripting requirements where you want to fetch the default data, log or backup directory.
Check ResourceDB Version and its Last Update Date
Sometimes we need to check the version of the ResourceDB database because some of the functionality stops working because of a version issue if you recently upgraded your SQL Server instance to a higher version. This function will help you to check the existing running version of the ResourceDB database along with the data when the ResourceDB database was last updated.
--List resourcedb version and its last update date
SELECT
SERVERPROPERTY('InstanceName') AS [SQL Server Instance Name],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductVersion') AS [Product Version],
SERVERPROPERTY('ResourceLastUpdateDateTime') AS [ResourceDB Last Updated On],
SERVERPROPERTY('ResourceVersion') AS [ResourceDB Version];
GO
Below we see the output which includes the dates when this database was last updated.
Check TempDB is Enabled for Memory Optimized Tables along with IN Memory OLTP Support
This code displays details about the IN Memory OLTP feature support for your installed version of SQL Server and whether your TempDB is enabled and configured for Memory optimized tables or not. This is a very generic requirement for a DBA to check whether TempDB is enabled for memory optimized tables and how many instances in your environment have Tempdb enabled to use memory optimized tables.
--Check IN Memory OLTP Support & TempDB configuration for Memory Optimized Tables
SELECT
SERVERPROPERTY('InstanceName') AS [SQL Server Instance Name],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductVersion') AS [Product Version],
SERVERPROPERTY('IsTempDbMetadataMemoryOptimized') AS [TempDB Enabled for Memory Optimized Tables],
SERVERPROPERTY('IsXTPSupported') AS [IN Memory OLTP Supported];
GO
Below is the description of these options.
- 'IsTempDbMetadataMemoryOptimized': This option returns 1 if Tempdb has been enabled to use memory-optimized tables for metadata and if you will get a 0 then it means Tempdb is using regular disk-based tables for metadata.
- 'IsXTPSupported': Below are the values you will get in your output for In-Memory OLTP support.
- 1 – Server supports In-Memory OLTP
- 0 – Server does not support In-Memory OLTP
- NULL – Input is not valid, an error, or not applicable
Here, we can see details.
Check Polybase, Full Text Search and Advanced Analytics is Installed on the SQL Server Instance
This function is very useful in checking whether some features have been installed for SQL Server. Let’s say you want to check whether FullText search support has been installed. You can also check if the Polybase module is installed for SQL Server 2019 and later. I used the below T-SQL code to check whether the below three modules have been installed as part of the installation for the SQL Server instance or not:
- FullText Search – The full-text and semantic indexing components are installed on the current instance of SQL Server.
- 1 – Full-text and semantic indexing components are installed.
- 0 – Full-text and semantic indexing components are not installed.
- NULL – Input is not valid, an error, or not applicable.
- Polybase – Below is the values through which you can interpret whether PolyBase feature has been installed or not on your SQL Server instance.
- 0 – PolyBase is not installed.
- 1 – PolyBase is installed.
- Advanced Analytics – If this value returns 1 it means the Advanced Analytics feature was installed during setup but if get 0 then it means Advanced Analytics was not installed.
- 1 – Advanced Analytics feature is installed
- 0 – Advanced Analytics feature is installed
Let’s go head and execute the below T-SQL to see if these components are installed.
--Check Polybase, FullText and Advanced Analytics features have been installed or not
SELECT
SERVERPROPERTY('ServerName') AS [Server Name],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductVersion') AS [Product Version],
SERVERPROPERTY('IsPolyBaseInstalled') AS [Polybase Installed],
SERVERPROPERTY('IsFullTextInstalled') AS [Full Text Search Installed],
SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS [Advanced Analytics Installed];
GO
Here is the output.
Check Various SQL Server Instance Related Properties
Here, I will show you how to check the remaining server level configurations. I have also given their values that can be used to interpret the output of the configured value.
- Big Data Cluster: This option is newly added and introduced in SQL Server 2019 (15.x) beginning with CU4.
- 1 – If instance is SQL Server Big Data Cluster
- 0 – If Instance is not Big Data Cluster
- Cluster Instance: The output of this option shows whether your SQL Server instance is configured in a failover cluster or not. Below are the values for clustered and non-clustered instances.
- 1 – Clustered.
- 0 – Not Clustered.
- NULL – Input is not valid, an error, or not applicable.
- SQL Server Express LocalDB: Below output shows whether a server is an instance of SQL Server Express LocalDB.
- 1 – SQL Server Express LocalDB
- 0 – This is not SQL Server Express LocalDB
- NULL – Input is not valid, an error, or not applicable.
- HADR Manager Status: It indicates whether the Always On Availability groups manager has started on this SQL Server instance or not. Have a look at its possible values to interpret your instance configuration.
- 0 – Not started, pending communication.
- 1 – Started and running.
- 2 – Not started and failed.
- NULL – Input is not valid, an error, or not applicable.
- Authentication Mode: Check whether the server is configured in Windows security mode or integrated security mode.
- 1 – Integrated security (Windows Authentication)
- 0 – Not integrated security. (Both Windows Authentication and SQL Server Authentication)
- NULL – Input is not valid, an error, or not applicable.
- Single User Mode: Validate whether your server is in single-user mode or not.
- 1 – Single user mode
- 0 – Not single user mode
- NULL – Input is not valid, an error, or not applicable.
- HADR (Always On Availability Group): This option shows whether Always On Availability Groups are enabled on this SQL Server instance or not.
- 0 – The Always On Availability Groups feature is disabled.
- 1 – The Always On Availability Groups feature is enabled.
- NULL – Input is not valid, an error, or not applicable.
I executed the below T-SQL to get the configured values of my SQL Server instance.
--Check configured values for various server level properties of SQL Server Instance
SELECT
SERVERPROPERTY('InstanceName') AS [SQLServer InstanceName],
SERVERPROPERTY('FilestreamConfiguredLevel') AS [Filestream],
SERVERPROPERTY('HadrManagerStatus') AS [HADR Manager Status],
SERVERPROPERTY('IsBigDataCluster') AS [Big Data Cluster],
SERVERPROPERTY('IsClustered') AS [Clustered],
SERVERPROPERTY('IsHadrEnabled') AS [HADR],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [Authentication Type],
SERVERPROPERTY('IsLocalDB') AS [SQL Express],
SERVERPROPERTY('IsSingleUser') AS [SQL Server Single User Mode];
GO
You can get the output of all above configurable options and interpret them as per their configured value.
This function is very helpful in checking server level collation as well for your SQL Server instances. Here you can see the collation type of your installed instance of SQL Server.
--List configured values for server level collation of SQL Server Instance
SELECT
SERVERPROPERTY('IsHadrEnabled') AS [HADR],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [Authentication Type],
SERVERPROPERTY('Collation') AS [Collation Type]
GO
We can see the server level collations for both instances in the below screen.
The last example I would like show is checking which version of .NET CLR is configured during SQL Server installation and if you run this T-SQL statement you will get Version of the Microsoft. NET Framework common language runtime (CLR) that was used while building the instance of SQL Server. If you get a NULL value it means your input is not valid or is not applicable.
--List configured values of server level collation of SQL Server Instance
SELECT
SERVERPROPERTY('MachineName') AS [Server Name],
SERVERPROPERTY('InstanceName') AS [SQL Server Instance Name],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version],
GO
Next Steps
- Use this system function to get various server level properties in your scripts or to fulfill your requirements.
