Need a SQL Query for finding OS Version, RAM, etc

Question

DBAs, I need some help. Is it possible to find below mentioned parameters through SQL query or else GUI (cluadmin, msinfo32, systeminfo, etc.) is the only option. I'm aware of finding below mentioned parameters through GUI, but based on the fact that working with query will save lot of time, I'm looking for a SQL query to get below details in SSMS.

OS Version - (eg., Windows 2012 Server Standard SP1)

RAM (GB) - (eg., 128GB)

Machine Type - (eg., Physical or Virtual)

Installed On - (When SQL instance is installed or built eg., 08 August, 2016)

If SQL instance is in cluster, I'm looking to get below details as well

SQL Virtual Name -

SQL Virtual IP -

Cluster Name -

Cluster IP -

Node1 -

Node1 IP -

Node2 -

Node2 IP -

Answer 1

 SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Machine Name]
 ,SERVERPROPERTY('InstanceName') AS [Instance Name]
 ,LOCAL_NET_ADDRESS AS [IP Address Of SQL Server]
 ,CLIENT_NET_ADDRESS AS [IP Address Of Client]
 FROM SYS.DM_EXEC_CONNECTIONS 
 WHERE SESSION_ID = @@SPID;
SELECT CONVERT(sysname, SERVERPROPERTY('servername')); 
GO 
-----To determine whether the SQL Server is configured in a failover cluster run the SQL command below :
SELECT SERVERPROPERTY('IsClustered');
Go
select cluster_name from sys.dm_hadr_cluster;
 Go
---The above DMV To Get the Windows Server Failover Clustering (WSFC) node that hosts an instance of SQL Server that is enabled for Always On Availability Groups has WSFC quorum, sys.dm_hadr_cluster returns a row that exposes the cluster name and information about the quorum. If the WSFC node has no quorum, no row is returned.
 ----Here Cluster_name is the Name of the WSFC cluster that hosts the instances of SQL Server that are enabled for Always On Availability Groups.
SELECT * FROM sys.dm_os_cluster_nodes;
Go

I hope the above TSQL will give your required information.

For your ref TO Determine Which node a clustered SQL Server is running on & For sys.dm_hadr_cluster

Answer 2

if you will launch the command: SELECT @@VERSION AS 'SQL Server Version'; you will receive:

• if SQL Server is installed into Windows OS (look at the end of the string): "Microsoft SQL Server 2017 (RTM-GDR) (KB5021127) - 14.0.2047.8 (X64) Jan 25 2023 08:29:55 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19045: )"

• if SQL Server is installed into Ubuntu OS (look at the end of the string): "Microsoft SQL Server 2017 (RTM-CU31) (KB5016884) - 14.0.3456.2 (X64) Sep 2 2022 11:01:50 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 18.04.6 LTS)"

Answer 3

Check out Glenn Berry's Diagnostic Scripts. He includes a whole range of queries in there for accessing server information, including a basic health check.

You will need to download the set of scripts appropriate for the version of SQL Server you're diagnosing.