Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP

Hemantgiri S. Goswami is working as a Consultant at Surat, INDIA. He has been a Microsoft SQL Server MVP three years running; he also moderates multiple SQL Server community forums including http://www.sql-server-performance.com and http://www.sqlserver-qa.net. He actively participates and speaks at local user group events organized under the aegis of www.surat-user-group.org, DotNetChaps and CSI Surat chapters, of which he is a founding and active member. He regularly publishes article on his blog space http://www.sql-server-citation.com. He has recently taken up a new initiative - blogging about SQL in his native language Gujarat through his blog http://sqlservercitation-gujarati.blogspot.com. He is an Author of the book SQL Server 2008 High Availability.

How to collect cluster Information using TSQL

Sometime back I was involved in a project which was to collect information for the servers we are supporting – creating inventory of servers. Logging into each server and collecting information is bit tedious when you have hundreds of server in your environment.  I have spent sometime and created 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 needed.
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 : http://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 | http://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
Please do post back your feed back for this script, I will try my best to update and post back new version for this script.
- Hemantgiri S. Goswami (http://www.sql-server-citation.com )
photo credit: Skimaniac via photopin cc

Comments

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...