PowerShell –SQL Automate Tutorial-7

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

InterServer Web Hosting and VPS

Scenario: PS is most powerful and very helpful tool for windows and SQL server administrator and automation. So its today need to learn at least basic of some PS cmdlets.

In this series of tutorial, we will only walk through some of the basis and need to know cmdlets which will helps us in future in order to manage our SQL server.

For more details, you can google and look for good site.

Note:- Please click here to get 6th part of PowerShell –SQL Automate tutorial.

To get more information about PowerShell cmdlets, you can refer this link.

In this tutorial, we will walk through PowerShell Invoke-Command cmdlets.

Introducing PowerShell Remoting:-

When it comes to managing remote computers with PowerShell, you have essentially three options. You can open an interactive session with the Enter-PSSession cmdlet (One-to-One Remoting). An alternative is the Invoke-Command cmdlet, which allows you to run remote commands on multiple computers (which is why it is called One-to-Many Remoting). The third option is to use one of those cmdlets that offer a ComputerName parameter. In most cases, PowerShell Remoting isn’t involved then. To list those cmdlets, you can use this command:

Get-Command -ParameterName ComputerName
Executing cmdlets with Invoke-Command:-

Two ways exist to connect to remote computers with Invoke-Command. You usually use the ‑ComputerName parameter to manage Windows machines. Alternatively, you can pass the ‑ConnectionUri parameter to manage backend applications, such as Exchange, or cloud services such as Azure.The command below executes the Get-ChildItem cmdlet on the machine whose computer name is stored in the $RemoteComputer variable:

Invoke-Command -Computername $RemoteComputer -ScriptBlock { Get-ChildItem "C:\Program Files" }

PowerShell automatically creates a so-called PSSession on the remote computer, which essentially is a user-created PowerShell session as opposed to a session that a PowerShell host such as the PowerShell console or PowerShell ISE creates. The output of the executed commands in the script block is automatically relayed to your local session. As usual, you can interrupt the displaying of the output with CTRL+C.

As with the Enter-PSSession cmdlet, you have to be an administrator on the remote machine. Otherwise, you will receive an Access denied error message. If you are not logged on as domain administrator on your local machine, you have to provide sufficient credentials with the -Credential parameter.

You can pass multiple computers to the -ComputerName parameter by separating their names with commas. If you also want to include the local computer, you add “localhost” or simply “.” to the list.

To run multiple commands, you can separate them with semicolons in the script block. However, a better option is to use the -FilePath parameter instead of the –ScriptBlock parameter, which allows you to execute an entire PowerShell script:

Invoke-Command -ComputerName PC1,PC2,PC3 -FilePath C:\myFolder\myScript.ps1
Testing if Remoting is enabled:-

In particular, if you plan to execute commands on many machines, you might want to test their availability with the Test-Connection cmdlet first. This cmdlet sends ICMP echo request packets (pings, that is) to the remote computers:

If (Test-Connection -ComputerName $RemoteComputers -Quiet)
     Invoke-Command -ComputerName $RemoteComputers -ScriptBlock {Get-ChildItem “C:\Program Files”}

The -Quiet parameter suppresses the output of the cmdlet and returns $True if the remote machine answers the pings.

An alternative is to work with a try-catch block:

$RemoteComputers = @("PC1","PC2")
ForEach ($Computer in $RemoteComputers)
             Invoke-Command -ComputerName $Computer -ScriptBlock {Get-ChildItem "C:\Program Files"} -ErrorAction Stop
             Add-Content Unavailable-Computers.txt $Computer

Happy Learning!

Before you start some work always ask yourselves three questions why I am doing it, what the results might be and will I be successful. Only when you think deeply and find satisfactory answers to these questions, go ahead.


One thought on “PowerShell –SQL Automate Tutorial-7

  1. Pingback: PowerShell –SQL Automate Tutorial-8 – ADsql- An Independent DB Consultant

Comments are closed.