Querying the SQL Server Instance and OS Level Details

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.

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 22^nd character, we will use the SUBSTRING () function to capture the next 4 characters starting on the 22^nd 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:

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.