Cmdlets/Get-SdtSQLInstance.ps1
|
Function Get-SdtSQLInstance { <# .SYNOPSIS Retrieves SQL server information from a local or remote servers. .DESCRIPTION Retrieves SQL server information from a local or remote servers. Pulls all instances from a SQL server and detects if in a cluster or not. .PARAMETER Computername Local or remote systems to query for SQL information. .NOTES Name: Get-SdtSQLInstance Author: Boe Prox Version History: 1.5 //Boe Prox - 31 May 2016 - Added WMI queries for more information - Custom object type name 1.0 //Boe Prox - 07 Sept 2013 - Initial Version .EXAMPLE Get-SdtSQLInstance -Computername SQL1 Computername : SQL1 Instance : MSSQLSERVER SqlServer : SQLCLU WMINamespace : ComputerManagement10 Sqlstates : 2061 Version : 10.53.6000.34 Splevel : 3 Clustered : True Installpath : C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL Datapath : D:\MSSQL10_50.MSSQLSERVER\MSSQL Language : 1033 Fileversion : 2009.100.6000.34 Vsname : SQLCLU Regroot : Software\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER Sku : 1804890536 Skuname : Enterprise Edition (64-bit) Instanceid : MSSQL10_50.MSSQLSERVER Startupparameters : -dD:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eD:\MSSQL1 0_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lD:\MSSQL10_50.MSSQLSERV ER\MSSQL\DATA\mastlog.ldf Errorreporting : False Dumpdir : D:\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\ Sqmreporting : False Iswow64 : False BackupDirectory : F:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup AlwaysOnName : Nodes : {SQL1, SQL2} Caption : SQL Server 2008 R2 FullName : SQLCLU\MSSQLSERVER Description ----------- Retrieves the SQL information from SQL1 #> [OutputType('SQLServer.Information')] [cmdletbinding()] Param( [parameter(ValueFromPipeline=$True)] [string[]]$Computername = 'G13' ) Process { ForEach ($Computer in $Computername) { # 1 = MSSQLSERVER $Filter = "SELECT * FROM SqlServiceAdvancedProperty WHERE SqlServiceType=1" $WMIParams=@{ Computername = $Computer NameSpace='root\Microsoft\SqlServer' Query="SELECT name FROM __NAMESPACE WHERE name LIKE 'ComputerManagement%'" Authentication = 'PacketPrivacy' ErrorAction = 'Stop' } Write-Verbose "[$Computer] Starting SQL Scan" $PropertyHash = [ordered]@{ Computername = $Computer Instance = $Null SqlServer = $Null WmiNamespace = $Null SQLSTATES = $Null VERSION = $Null SPLEVEL = $Null CLUSTERED = $Null INSTALLPATH = $Null DATAPATH = $Null LANGUAGE = $Null FILEVERSION = $Null VSNAME = $Null REGROOT = $Null SKU = $Null SKUNAME = $Null INSTANCEID = $Null STARTUPPARAMETERS = $Null ERRORREPORTING = $Null DUMPDIR = $Null SQMREPORTING = $Null ISWOW64 = $Null BackupDirectory = $Null AlwaysOnName = $Null } Try { Write-Verbose "[$Computer] Performing Registry Query" $Registry = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $Computer) } Catch { Write-Warning "[$Computer] $_" Continue } $baseKeys = "SOFTWARE\\Microsoft\\Microsoft SQL Server", "SOFTWARE\\Wow6432Node\\Microsoft\\Microsoft SQL Server" Try { $ErrorActionPreference = 'Stop' If ($Registry.OpenSubKey($basekeys[0])) { $regPath = $basekeys[0] } ElseIf ($Registry.OpenSubKey($basekeys[1])) { $regPath = $basekeys[1] } Else { Continue } } Catch { Continue } Finally { $ErrorActionPreference = 'Continue' } $RegKey= $Registry.OpenSubKey("$regPath") If ($RegKey.GetSubKeyNames() -contains "Instance Names") { $RegKey= $Registry.OpenSubKey("$regpath\\Instance Names\\SQL" ) $instances = @($RegKey.GetValueNames()) } ElseIf ($regKey.GetValueNames() -contains 'InstalledInstances') { $isCluster = $False $instances = $RegKey.GetValue('InstalledInstances') } Else { Continue } If ($instances.count -gt 0) { ForEach ($Instance in $Instances) { $PropertyHash['Instance']=$Instance $Nodes = New-Object System.Collections.Arraylist $clusterName = $Null $isCluster = $False $instanceValue = $regKey.GetValue($instance) $instanceReg = $Registry.OpenSubKey("$regpath\\$instanceValue") If ($instanceReg.GetSubKeyNames() -contains "Cluster") { $isCluster = $True $instanceRegCluster = $instanceReg.OpenSubKey('Cluster') $clusterName = $instanceRegCluster.GetValue('ClusterName') $clusterReg = $Registry.OpenSubKey("Cluster\\Nodes") $clusterReg.GetSubKeyNames() | ForEach { $null = $Nodes.Add($clusterReg.OpenSubKey($_).GetValue('NodeName')) } } $PropertyHash['Nodes'] = $Nodes $instanceRegSetup = $instanceReg.OpenSubKey("Setup") Try { $edition = $instanceRegSetup.GetValue('Edition') } Catch { $edition = $Null } $PropertyHash['Skuname'] = $edition Try { $ErrorActionPreference = 'Stop' #Get from filename to determine version $servicesReg = $Registry.OpenSubKey("SYSTEM\\CurrentControlSet\\Services") $serviceKey = $servicesReg.GetSubKeyNames() | Where { $_ -match "$instance" } | Select -First 1 $service = $servicesReg.OpenSubKey($serviceKey).GetValue('ImagePath') $file = $service -replace '^.*(\w:\\.*\\sqlservr.exe).*','$1' $PropertyHash['version'] =(Get-Item ("\\$Computer\$($file -replace ":","$")")).VersionInfo.ProductVersion } Catch { #Use potentially less accurate version from registry $PropertyHash['Version'] = $instanceRegSetup.GetValue('Version') } Finally { $ErrorActionPreference = 'Continue' } Try { Write-Verbose "[$Computer] Performing WMI Query" $Namespace = $Namespace = (Get-WMIObject @WMIParams | Sort-Object -Descending | Select-Object -First 1).Name If ($Namespace) { $PropertyHash['WMINamespace'] = $Namespace $WMIParams.NameSpace="root\Microsoft\SqlServer\$Namespace" $WMIParams.Query=$Filter $WMIResults = Get-WMIObject @WMIParams $GroupResults = $WMIResults | Group ServiceName $PropertyHash['Instance'] = $GroupResults.Name $WMIResults | ForEach { $Name = "{0}{1}" -f ($_.PropertyName.SubString(0,1),$_.PropertyName.SubString(1).ToLower()) $Data = If ($_.PropertyStrValue) { $_.PropertyStrValue } Else { If ($Name -match 'Clustered|ErrorReporting|SqmReporting|IsWow64') { [bool]$_.PropertyNumValue } Else { $_.PropertyNumValue } } $PropertyHash[$Name] = $Data } #region Always on availability group if ($PropertyHash['Version'].Major -ge 11) { $splat.Query="SELECT WindowsFailoverClusterName FROM HADRServiceSettings WHERE InstanceName = '$($Group.Name)'" $PropertyHash['AlwaysOnName'] = (Get-WmiObject @WMIParams).WindowsFailoverClusterName if ($PropertyHash['AlwaysOnName']) { $PropertyHash.SqlServer = $PropertyHash['AlwaysOnName'] } } else { $PropertyHash['AlwaysOnName'] = $null } #endregion Always on availability group #region Backup Directory $RegKey=$Registry.OpenSubKey("$($PropertyHash['RegRoot'])\MSSQLServer") $PropertyHash['BackupDirectory'] = $RegKey.GetValue('BackupDirectory') #endregion Backup Directory }#IF NAMESPACE } Catch { } #region Caption $Caption = {Switch -Regex ($PropertyHash['version']) { "^13" {'SQL Server 2016';Break} "^12" {'SQL Server 2014';Break} "^11" {'SQL Server 2012';Break} "^10\.5" {'SQL Server 2008 R2';Break} "^10" {'SQL Server 2008';Break} "^9" {'SQL Server 2005';Break} "^8" {'SQL Server 2000';Break} Default {'Unknown'} }}.InvokeReturnAsIs() $PropertyHash['Caption'] = $Caption #endregion Caption #region Full SQL Name $Name = If ($clusterName) { $clusterName $PropertyHash['SqlServer'] = $clusterName } Else { $Computer $PropertyHash['SqlServer'] = $Computer } $PropertyHash['FullName'] = ("{0}\{1}" -f $Name,$PropertyHash['Instance']) #emdregion Full SQL Name $Object = [pscustomobject]$PropertyHash $Object.pstypenames.insert(0,'SQLServer.Information') $Object }#FOREACH INSTANCE }#IF } } } |