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
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
Function Call to SQL Server 2008 R2 instance
PS F:\PowerSQL\Installation>Get-ReadSQLRegistryEntries aqdbpp16
