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

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell and tagged , , . Bookmark the permalink.

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

  1. dvb t says:

    It is actually a nice and helpful piece of information. I am happy that you just shared this helpful info with us. Please keep us up to date like this. Thank you for sharing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s