On a normal server, there are several Powershell ways to get a list of all installed SQL instances. Probably the most straightforward way is to query the registry:
|
1 2 |
Get-Itemproperty -path 'HKLM:\software\microsoft\Microsoft SQL Server' ` | Select-Object -expandproperty installedinstances |
You can subsequently use the output to query all instances on the server.
On a cluster, this method will also work, but the end result will not be as useful. To query a clustered instance, you’ll also need the virtual server name. In newer SQL Server versions, this is called the SQL Network Name, but apparently, under the hood the older name is still used.
There is no direct way I know of to get the full name: Virtualservername\instancename. But using the Failoverclusters module, you can retrieve the virtual server names of all the instances. Put these into an array, put the instance names into an other array and, provided you sorted both arrays the same way, concatenating both arrays will get a list of instances you can use.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
#Import module Import-Module failoverclusters $arrINST = Get-Clusterresource ` | Where-Object {$_.resourcetype -like 'sql server'} ` | Get-Clusterparameter "instancename" ` | Sort-Object objectname ` | Select-Object -expandproperty value $arrVSN = Get-Clusterresource ` | Where-Object {$_.resourcetype -like 'sql server'} ` | Get-Clusterparameter "virtualservername" ` | Sort-Object objectname ` | Select-Object -expandproperty value foreach ($i in 0..($arrINST.count-1)) {$arrVSN[$i] + "\" + $arrINST[$i]} |
A little explanation:
- Get-Clusterresource returns a list of cluster resources
- This needs to be filtered to get just the resources of the type SQL Server
- One array holds the value for instancename, the other for virtualservername
- Sort both lists by the same property, otherwise you’ll get rubbish
- Use -exandproperty to end up with a list of values rather than a table
- With a foreach loop, concatenate both values with a backslash in between.
Now, you get a result that you can use in an Invoke-SQLCommand.