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

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

PowerSQL – SQL Instance Configuration & Installation Information by Reading Registry Keys

Today I got a chance to retrieve configuration information of sql server 2005 instances. PowerShell provide a facility to read the registry information with ease. PowerShell treats the registry like any other location or directory. One critical difference is that every item on a registry-based Windows PowerShell drive is a container, just like a folder on a file system drive.

I’m simulating the sys.dm_server_registry DMV using PowerShell by reading registry entries. You can also use extended stored procedure to retrieve the same information.

This code can be run from any machine where PowerShell is installed and you have access to the server.

This code can be enhanced to various levels that meet your requirement and it’s applicable to SQL Server 2005 or its  higher version. You can download the code here RegistryRead

DMV – sys.dm_server_registry

It will display a configuration and installation information that is stored in the Windows registry for the current instance of SQL Server

SELECT * FROM sys.dm_server_registry

Image

PowerShell Script to read Registry data

function Get-RemoteRegistryKeyProperties

{

param(

$computer = $(throw “Please specify a computer name.”),

$path = $(throw “Please specify a registry path”),

$property = “*”

)

## Validate and extract out the registry key

if($path -match “^HKLM:\\(.*)”)

{

$baseKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey(

“LocalMachine”, $computer)

}

else

{

Write-Error (“Please specify a fully-qualified registry path ” +

“(i.e.: HKLM:\Software) of the registry key to open.”)

return

}

## Open the key

$key = $baseKey.OpenSubKey($matches[1])

$returnObject = New-Object PsObject

## Go through each of the properties in the key

foreach($keyProperty in $key.GetValueNames())

{

## If the property matches the search term, add it as a

## property to the output

if($keyProperty -like $property)

{

$returnObject |

Add-Member NoteProperty $keyProperty $key.GetValue($keyProperty)

}

}

## Return the resulting object

$returnObject

}

Function Get-ReadSQLRegistryEntries ($computer)

{

$OS = (Get-WmiObject Win32_OperatingSystem -computername $computer).caption

$SystemInfo = Get-WmiObject -Class Win32_OperatingSystem -computername $computer | Select-Object Name, TotalVisibleMemorySize, FreePhysicalMemory

$a=Get-RemoteRegistryKeyProperties $COMPUTER ‘HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL’ “MSSQLSERVER”

$path=’HKLM:\Software\Microsoft\Microsoft SQL Server\’+$a.MSSQLSERVER+ ‘\MSSQLServer\Parameters’

$Serverstartup=Get-RemoteRegistryKeyProperties  $COMPUTER ‘HKLM:\SYSTEM\CurrentControlSet\SERVICES\MSSQLSERVER’ “START”

$ServerAgentstartup=Get-RemoteRegistryKeyProperties  $COMPUTER ‘HKLM:\SYSTEM\CurrentControlSET\SERVICES\SQLSERVERAGENT’ “START”

$ServerAc=Get-RemoteRegistryKeyProperties  $COMPUTER ‘HKLM:\SYSTEM\CurrentControlSET\SERVICES\MSSQLSERVER’ “ObjectName”

$SAC=$ServerAC.ObjectName

$ServerAgAc=Get-RemoteRegistryKeyProperties  $COMPUTER ‘HKLM:\SYSTEM\CurrentControlSET\SERVICES\SQLSERVERAGENT’ “ObjectName”

$SAG=$ServerAgAC.ObjectName

$ErrorLog=Get-RemoteRegistryKeyProperties $COMPUTER $path “SQLArg1″

$E=$ErrorLog.SQLArg1

$DataPath=Get-RemoteRegistryKeyProperties $COMPUTER $path “SQLArg0″

$DP=$DataPath.SQLArg0

$LogPath=Get-RemoteRegistryKeyProperties $COMPUTER $path “SQLArg2″

$LP=$LogPath.SQLArg2

$Domain=Get-RemoteRegistryKeyProperties  $COMPUTER ‘HKLM:\SYSTEM\ControlSET001\Services\Tcpip\Parameters’ “DOMAIN”

$D=$Domain.Domain

$lib=Get-RemoteRegistryKeyProperties  $COMPUTER ‘HKLM:\SOFTWARE\Microsoft\MSSQLServer\Setup’ “SQLPath”

$SQL=$lib.SQLPath

$Port=Get-RemoteRegistryKeyProperties  $COMPUTER ‘HKLM:\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP’ “TcpPort”

$P=$Port.TcpPort

$registry_key = ‘HKLM:\Software\Microsoft\Microsoft SQL Server\’ +$a.MSSQLSERVER+ ‘\MSSQLServer’

$AuditLevel=Get-RemoteRegistryKeyProperties  $COMPUTER $registry_key “auditlevel”

$loginMode=Get-RemoteRegistryKeyProperties  $COMPUTER $registry_key “loginmode”

switch ($Serverstartup.start)

{

2 {$ServerStartup = “Automatic”}

3 {$ServerStartup = “Manual”}

4 {$ServerStartup = “Disabled”}

}

switch ($ServerAgentstartup.start)

{

2 {$ServerAgentstartup = “Automatic”}

3 {$ServerAgentstartup = “Manual”}

4 {$ServerAgentstartup =”Disabled”}

}

switch ($auditLevel.auditLevel)

{

0 {$Value=”None.”}

1 {$value=”Successful Logins Only”}

2 {$value=”Failed Logins Only.”}

3 {$value=”Both Failed and Successful Logins Only”}

}

switch($loginMode.loginmode)

{

1 {$Log=”Windows Authentication”}

2 {$Log =”SQL Server Authentication”}

}

$OutputObj  = New-Object -Type PSObject

$OutputObj | Add-Member -MemberType NoteProperty -Name serverName -Value $computer.ToUpper()

$OutputObj | Add-Member -MemberType NoteProperty -Name OS -Value $OS

$OutputObj | Add-Member -MemberType NoteProperty -Name SQLPATH -Value $SQL

$OutputObj | Add-Member -MemberType NoteProperty -Name DataPath -Value $DP

$OutputObj | Add-Member -MemberType NoteProperty -Name LOGPath -Value $LP

$OutputObj | Add-Member -MemberType NoteProperty -Name ERRORLOG -Value $E

$OutputObj | Add-Member -MemberType NoteProperty -Name Domain -Value $d

$OutputObj | Add-Member -MemberType NoteProperty -Name Port -Value $P

$OutputObj | Add-Member -MemberType NoteProperty -Name SERVERSTARTUP -Value $ServerStartup

$OutputObj | Add-Member -MemberType NoteProperty -Name AGENTSTARTUP -Value $ServerAgentstartup

$OutputObj | Add-Member -MemberType NoteProperty -Name STARTUPACCOUNTSERVER -Value $SAC

$OutputObj | Add-Member -MemberType NoteProperty -Name STARTUPACCOUNTAGENT -Value $SAG

$OutputObj | Add-Member -MemberType NoteProperty -Name AUDITDETAIL -Value $Value

$OutputObj | Add-Member -MemberType NoteProperty -Name LOGIN -Value $Log

$OutputObj

}

Function Call to  SQL Server 2005 instance

PS F:\PowerSQL\Installation>Get-ReadSQLRegistryEntries hqvd0026

Image

Function Call  to SQL Server 2008 R2 instance

PS F:\PowerSQL\Installation>Get-ReadSQLRegistryEntries aqdbpp16

Image


Comments

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

Loading comments...