forked from proxb/PowerShell_Scripts
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathGet-PSSQLInstance.ps1
267 lines (254 loc) · 12 KB
/
Get-PSSQLInstance.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
Function Get-PSSQLInstance {
<#
.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-PSSQLInstance
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-PSSQLInstance -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') {
$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
$instanceValue = $regKey.GetValue($instance)
$instanceReg = $Registry.OpenSubKey("$regpath\\$instanceValue")
If ($instanceReg.GetSubKeyNames() -contains "Cluster") {
$instanceRegCluster = $instanceReg.OpenSubKey('Cluster')
$clusterName = $instanceRegCluster.GetValue('ClusterName')
$clusterReg = $Registry.OpenSubKey("Cluster\\Nodes")
$clusterReg.GetSubKeyNames() | ForEach-Object {
$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-Object {
$_ -eq ('MSSQL${0}' -f $instance)
} | Select-Object -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-Object ServiceName
$PropertyHash['Instance'] = $GroupResults.Name
$WMIResults | ForEach-Object {
$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
}
}
}