In this article I show some methods to collect the computers you want to work with.
This is an essential step when moving towards automation and managing multiple computers or SQL Instances at the same time.

Gather Computers

When using PowerShell, one of the most interesting advantages is you can execute code against multiple computers in one go. When working with SQL Server, we can also address multiple SQL Instances at the same time. There are some different ways to supply a cmdlet or function with a collection of computers. I'll show you some easy methods right away:

Parameter ComputerName

Lots of existing cmdlets since PS V2 have a -ComputerName parameter. You can submit one or more computers to it:

PS> Get-WSManInstance -ResourceURI winrm/config/client -ComputerName SQL1

PS> Get-WSManInstance -ResourceURI winrm/config/client -ComputerName SQL1,SQL2,SQL3

Pipeline

If a cmdlet or function accepts pipeline input for the computer name, here's the way we use it:

PS> 'SQL1','SQL2','SQL3','FakeSQL4' | Get-WSManInstance -ResourceURI winrm/config/client

Expression

We can put the code to collect computers in as is. Here are two ways to do that:

for the pipeline

The code to collect the computer names we want, is provided to the pipeline:

PS> Get-ADComputer -SearchBase "OU=Central servers,OU=Computers MyCompany,DC=MYCOMPANY,DC=COM" -Filter "name -like 'SQL*'" | select -ExpandProperty name  | Get-WSManInstance -ResourceURI winrm/config/client

inline

We can also put that part of the code at the -ComputerName parameter and wrap it in parentheses.

PS> Get-WSManInstance -ResourceURI winrm/config/client -ComputerName $(Get-ADComputer -SearchBase "OU=Central servers,OU=Computers MyCompany,DC=MYCOMPANY,DC=COM" -Filter "name -like 'SQL*'" | select -ExpandProperty name)

using variables

To avoid summing up all your computers of interest, it can be handy to keep the list in a variable:

PS> $MyComputers = 'SQL1','SQL2','SQL3','FakeSQL4'

PS> Get-WSManInstance -ResourceURI winrm/config/client -ComputerName $MyComputers

from a text file

SQLHosts.txt:

SQL1
SQL2
SQL3

PS> $MyComputers = Get-Content -Path 'C:\MyDirectory\SQLHosts.txt'

PS> Get-WSManInstance -ResourceURI winrm/config/client -ComputerName $MyComputers

from a csv

SQLInstances.csv:

PS> $MyComputers = Import-Csv -Path 'C:\MyDirectory\SQLInstances.csv' | select ComputerName

PS> Get-WSManInstance -ResourceURI winrm/config/client -ComputerName $MyComputers

from Active Directory

When you're on a Domain Controller (bad idea!) or you have installed RSAT on your work station and activated the necessary features, you can use the AD-cmdlets.

PS> $MyComputers = Get-ADComputer -SearchBase "OU=Central servers,OU=Computers MyCompany,DC=MYCOMPANY,DC=COM" -Filter "name -like 'SQL*'" | select -ExpandProperty name

PS> Get-WSManInstance -ResourceURI winrm/config/client -ComputerName $MyComputers

as registered servers

In the dbatools module, there is a function that gets the servernames from a Central Management Server:

PS> $MyComputers = Get-DbaRegisteredServerName -SqlInstance MyCMS

PS> Get-WSManInstance -ResourceURI winrm/config/client -ComputerName $MyComputers

from a database

PS> $MyComputers = Invoke-Sqlcmd -ServerInstance 'MyPrecious' -Database sqlinfodb -Query "select hostName from dbo.hosts;" | select -ExpandProperty hostName

PS> Get-WSManInstance -ResourceURI winrm/config/client -ComputerName $MyComputers

making variables permanent

Now, once populated, we can reuse $MyComputers as much as we like. But when we continue in another session, we'll have to populate our variable again. I think that's still too much work, so we take it one step further. We're going to populate our variable in our PowerShell Profile:

SQLInstances.txt:

SQL1\Inst2K14
SQL2\Inst2K08R2
SQL3

C:\Windows\System32\WindowsPowerShell\v1.0\profile.ps1:

$AllMyComputers = Get-Content C:\scripts\SQL\SQLInstances.txt | ForEach-Object {$_.split('\')[0]} | Select-Object -Unique

$AllMyInstances = Import-Csv -Path 'C:\MyDirectory\SQLInstances.csv' | select @{label = 'SQLInstance';expression={$_.ComputerName + '\' + $_.InstanceName}}

super trick: the real profile

Because it's a burden to alter the content of the profile script, we can make our life easier. In profile.ps1 we dotsource another .ps1 that contains our real profile text. That way we can alter the content as much as we like and it will be loaded as soon as we start a new PowerShell session:

C:\Windows\System32\WindowsPowerShell\v1.0\profile.ps1:

. D:\scripts\MyRealProfile.ps1

D:\scripts\MyRealProfile.ps1:

$AllMyComputers = Get-Content C:\scripts\SQL\SQLInstances.txt | ForEach-Object {$_.split('\')[0]} | Select-Object -Unique

$AllMyInstances = Import-Csv -Path 'C:\MyDirectory\SQLInstances.csv' | select @{label = 'SQLInstance';expression={$_.ComputerName + '\' + $_.InstanceName}}

Remember, everything you declare in a profile will only be available if the profile is loaded. So those variables are not available when a script is launched in a scheduled task or job. Depending on the chosen profile, using another PowerShell host or a different user can also make them unavailable.

and what about sql instances?

All methods explained above can be used with sql instances too. Just make sure you use the right syntax for each purpose and submit computer names to -ComputerName and instance names to -SqlInstance and the like.

PS> $AllMyInstances | Get-DbaclientProtocol

Previous Post Next Post