How to Collect Cluster Information Using TSQL

Context

Sometime back, I was involved in a project that consisted in collecting information for the servers we are supporting – creating an inventory of servers. Logging into each server and collecting information is a bit tedious when you have hundreds of servers in your environment.  I have spent sometime creating a script that does this work for me; however, I still need to connect to the server – but using SSMS/Query Window – and execute the script. And then the script will provide me the details I need.  Please remember this script is enabling xp_cmdshell and disabling it back, before you run this script if this is acceptable in your environment.

List

This inventory should have details like below for cluster and stand alone instances:

Server Name

OS Name

OS Edition

OS Patch Level

SQL Server IP

Is Clustered

Node1_Name

Node1_IP

Node2_Name

Node2_IP

SQL Server Edition

SQL Server Patch Level

Server Time Zone

SQL Server Version

SQL Server Platform

Processor Core

Physical Memory

Service Account Name

Domain

Looks good ?  

Below is the version 1 of this script:


IP address portion using : https://www.sqlservercentral.com/Forums/Topic150196-8-1.aspx
REMOVE sp_configure parameters if you are executing this script on SQL Server 2000
Created By    :    Hemantgiri S. Goswami | https://www.sql-server-citation.com
Date        :    24th March 2013
Version        :    1.0
Tested ON:
Windows Server     >>    2003, 2008, 2008 R2
SQL Server        >>    2000, 2005, 2008, 2008 R2, 2012
*/
sp_configure ‘show advanced options’,1
RECONFIGURE WITH OVERRIDE
GO
sp_configure ‘xp_cmdshell’,1
RECONFIGURE WITH OVERRIDE
GO
DECLARE    @TimeZone            NVARCHAR(100)
,@ProductVersion    SYSNAME
,@PlatForm            SYSNAME
,@Windows_Version    SYSNAME
,@Processors        SYSNAME
,@PhysicalMemory    SYSNAME
,@ServiceAccount    SYSNAME
,@IPAddress            SYSNAME
,@DOMAIN            SYSNAME
,@MachineType        SYSNAME
,@SQLServerIP        VARCHAR(255)
,@CMD                VARCHAR(100)
,@Node1                VARCHAR(100)
,@Node2                VARCHAR(100)
,@Node1IP            VARCHAR(100)
,@Node2IP            VARCHAR(100)
,@OSEdition            VARCHAR(100)
,@OSVersion            VARCHAR(100)
,@OSName            VARCHAR(100)
,@OSPatchLevel        VARCHAR(100)
CREATE TABLE #TempTable
(
[Index] VARCHAR(2000),
[Name] VARCHAR(2000),
[Internal_Value] VARCHAR(2000),
[Character_Value] VARCHAR(2000)
) ;
INSERT INTO #TempTable
EXEC xp_msver;
— Replace @Value_Name to N’TimeZoneKeyName’ when running on Windows 2008
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’SYSTEM\CurrentControlSet\Control\TimeZoneInformation’,
@value_name   = N’StandardName’,
@value        = @TimeZone output
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’SYSTEM\CurrentControlSet\Services\MSSQLServer’,
@value_name   = N’ObjectName’,
@value        = @ServiceAccount output
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’SYSTEM\CurrentControlSet\Control\ProductOptions’,
@value_name   = N’ProductType’,
@value        = @MachineType output
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’SYSTEM\CurrentControlSet\Services\Tcpip\Parameters’,
@value_name   = N’Domain’,
@value        = @DOMAIN output
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’CLUSTER\NODES\1′,
@value_name   = N’NodeName’,
@value        = @Node1 output
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’CLUSTER\NODES\2′,
@value_name   = N’NodeName’,
@value        = @Node2 output
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’SOFTWARE\Microsoft\Windows NT\CurrentVersion’,
@value_name   = N’ProductName’,
@value        = @OSName output
create table #OSEdition (VALUe varchar(255),OSEdition varchar(255), data varchar(100))
insert into #OSEdition
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’SYSTEM\CurrentControlSet\Control\ProductOptions’,
@value_name   = N’ProductSuite’
SET @OSEdition = (SELECT TOP 1 OSedition  FROM #OsEdition)
EXEC   master.dbo.xp_regread
@rootkey      = N’HKEY_LOCAL_MACHINE’,
@key          = N’SOFTWARE\Microsoft\Windows NT\CurrentVersion’,
@value_name   = N’CSDVersion’,
@value        = @OSPatchLevel output
set @cmd = ‘ping ‘ + @Node1
create table #Node1IP (grabfield varchar(255))
insert into #Node1IP exec master.dbo.xp_cmdshell @cmd
set @cmd = ‘ping ‘ + @Node2
create table #Node2IP (grabfield varchar(255))
insert into #Node2IP exec master.dbo.xp_cmdshell @cmd
set @cmd = ‘ping ‘ + @@servername
create table #SQLServerIP (grabfield varchar(255))
insert into #SQLServerIP exec master.dbo.xp_cmdshell @cmd
SET        @SQLServerIP    =    (
SELECT substring(grabfield,  charindex(‘[‘,grabfield)+1, charindex(‘]’,grabfield)-charindex(‘[‘,grabfield)-1)
from #SQLServerIP  where left(grabfield,7) = ‘Pinging’
)
SET        @Node1IP            =    (
SELECT substring(grabfield,  charindex(‘[‘,grabfield)+1, charindex(‘]’,grabfield)-charindex(‘[‘,grabfield)-1)
from #Node1IP  where left(grabfield,7) = ‘Pinging’
)
SET        @Node2IP            =    (
SELECT substring(grabfield,  charindex(‘[‘,grabfield)+1, charindex(‘]’,grabfield)-charindex(‘[‘,grabfield)-1)
from #Node2IP  where left(grabfield,7) = ‘Pinging’
)
SET        @ProductVersion =    (SELECT Character_Value from #TempTable where [INDEX]=2)
SET        @Platform        =    (SELECT Character_Value from #TempTable where [INDEX]=4)
SET        @Windows_Version=    (SELECT Character_Value from #TempTable where [INDEX]=15)
SET        @Processors        =    (SELECT Character_Value from #TempTable where [INDEX]=16)
SET        @PhysicalMemory    =    (SELECT Character_Value from #TempTable where [INDEX]=19)
SELECT
ServerName            =    @@SERVERNAME
,OSName                =    @OSName
,OSEdition            =    @OSEdition
,OSPatchLevel        =    @OSPatchLevel
,SQLServerIP        =    @SQLServerIP
,IsClustered        =    SERVERPROPERTY(‘IsClustered’)
,Node1_Name            =    @Node1
,Node1_IP            =    @Node1IP
,Node2_Name            =    @Node2
,Node2_IP            =    @Node2IP
,SQLServerEdition    =    SERVERPROPERTY(‘Edition’)
,SQLServerLevel        =    SERVERPROPERTY(‘ProductLevel’)
,ServerTimeZone        =    @TimeZone
,SQLServerVersion    =    @ProductVersion
,SQLServerPlatform    =    @PlatForm
,ProcessorCore        =    @Processors
,PhysicalMemory        =    @PhysicalMemory
,ServiceAccountName    =    @ServiceAccount
,WKS_Server            =    @MachineType
,Domain                =    @DOMAIN
GO
DROP TABLE #Node1IP
DROP TABLE #NODE2IP
DROP TABLE #SQLServerIP
DROP TABLE #TempTable
DROP TABLE #OSEdition
GO
sp_configure ‘xp_cmdshell’,0
RECONFIGURE WITH OVERRIDE
GO
sp_configure ‘show advanced options’,0
RECONFIGURE WITH OVERRIDE
GO

SQL Server Database Consulting Services

Ready to optimize your SQL Server Database for the future?