Select Page

Querying the SQL Server Instance and OS Level Details

Author: JP Chen | | November 10, 2015

Let’s say you’re part of SQL Server DBA team that supports 100+ instances of SQL Server ranging from SQL Server 2005 through SQL Server 2014 with different editions, service packs, and authentication modes. You have been tasked with the critical mission to develop an Excel report to show all the instance level details such as instance name, version, edition, service pack, server authentication mode, if it’s clustered, the current node name, server collation, # of CPUs, and memory of the Operating System. These are the similar details you will see on the “Server Properties” of the instance.

Querying 1

What do you need to know to get the required report completed quickly? The solution and the required queries are simpler than you might think but there’s only one caveat.

Here are the required system functions, expression, and Dynamic Management View (DMV) that you will need and brief explanations of each:

  • @@VERSION: Returns system and build information for the instance of SQL Server.
  • SUBSTRING (): Returns part of a character or text. In this case, the @@VERSION function will return the SQL Server version in numeric form starting on the 22nd character, we will use the SUBSTRING () function to capture the next 4 characters starting on the 22nd character. It will show 2005, 2008, 2010, 2012, 2014, and so on.
  • SERVERPROPERTY (): Queries the property information about the server instance. In this case, we will use this system built-in function to query the instance name, product version, edition, product level, authentication mode, if it is clustered, the current node name, and SQL Server collation.
  • CASE EXPRESSION: Evaluates a list of conditions and returns one of the possible result expressions. In this case, we will use the simple CASE expression.
  • SYS.DM_OS_SYS_INFO: Returns a set of information about the computer and the resources available to the SQL Server instance(s) on the Windows Server.

What is the one caveat that we need to be aware of? It is on the SYS.DM_OS_SYS_INFO DMV and more specifically on the memory data column. In SQL Server 2005, 2008, and 2008 R2, the memory is returned in bytes. The data column name is physical_memory_in_bytes. But, in SQL Server 2012 and later, the memory is returned in kilobytes. The data column name is physical_memory_kb. The different data column names for memory for the SYS.DM_OS_SYS_INFO DMV has been taken into consideration in the following script and will dynamically return details for the instances running below SQL Server 2012 and also for the instances running SQL Server 2012 or above.

declare @version varchar(4)
select @version = substring(@@version,22,4)

IF CONVERT(SMALLINT, @version) >= 2012
EXEC ('SELECT	
		SERVERPROPERTY(''ServerName'') AS [Instance Name],
		CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY(''ProductVersion'')),4) 
			WHEN ''11.0'' THEN ''SQL Server 2012''
			WHEN ''12.0'' THEN ''SQL Server 2014''
			ELSE ''Newer than SQL Server 2014''
		END AS [Version Build],
		SERVERPROPERTY (''Edition'') AS [Edition],
		SERVERPROPERTY(''ProductLevel'') AS [Service Pack],
		CASE SERVERPROPERTY(''IsIntegratedSecurityOnly'') 
			WHEN 0 THEN ''SQL Server and Windows Authentication mode''
			WHEN 1 THEN ''Windows Authentication mode''
		END AS [Server Authentication],
		CASE SERVERPROPERTY(''IsClustered'') 
			WHEN 0 THEN ''False''
			WHEN 1 THEN ''True''
		END AS [Is Clustered?],
		SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS [Current Node Name],
		SERVERPROPERTY(''Collation'') AS [ SQL Collation],
		[cpu_count] AS [CPUs],
		[physical_memory_kb]/1024 AS [RAM (MB)]
	FROM	
		[sys].[dm_os_sys_info]')
ELSE IF CONVERT(SMALLINT, @version) >= 2005
EXEC ('SELECT	
		SERVERPROPERTY(''ServerName'') AS [Instance Name],
		CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY(''ProductVersion'')),4) 
			WHEN ''9.00'' THEN ''SQL Server 2005''
			WHEN ''10.0'' THEN ''SQL Server 2008''
			WHEN ''10.5'' THEN ''SQL Server 2008 R2''
		END AS [Version Build],
		SERVERPROPERTY (''Edition'') AS [Edition],
		SERVERPROPERTY(''ProductLevel'') AS [Service Pack],
		CASE SERVERPROPERTY(''IsIntegratedSecurityOnly'') 
			WHEN 0 THEN ''SQL Server and Windows Authentication mode''
			WHEN 1 THEN ''Windows Authentication mode''
		END AS [Server Authentication],
		CASE SERVERPROPERTY(''IsClustered'') 
			WHEN 0 THEN ''False''
			WHEN 1 THEN ''True''
		END AS [Is Clustered?],
		SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS [Current Node Name],
		SERVERPROPERTY(''Collation'') AS [ SQL Collation],
		[cpu_count] AS [CPUs],
		[physical_memory_in_bytes]/1048576 AS [RAM (MB)]
	FROM	
		[sys].[dm_os_sys_info]')
ELSE 
SELECT 'This SQL Server instance is running SQL Server 2000 or lower! You will need alternative methods in getting the SQL Server instance level information.'

The following is an example of the output after executing on one of our demo SQL Server instances:

Querying 2

If you and your team need to query more than 100+ SQL Server instances, you will need to register all the instances into a group and then run a multi-server query using the script provided above.

This is probably the tip of the iceberg on what we can do on querying the SQL Server instance and Windows Server level details. Do you need more additional details from the instances or the Windows Servers? Do you have some unique scenarios or amazing scripts that you wish to share with our fellow SQL Server DBAs? Please feel free to leave a reply on the comment box below.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS