How to create a list of installed SQL Server instances on a cluster

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:

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.

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.



This entry was posted in Powershell. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *