By: Diana Moldovan | Updated: 2011-09-06 | Comments (1) | Related: > PowerShell
Problem
PowerShell can be used to for many SQL administrative tasks and in this tip we walk through how to run T-SQL code and admin tasks on remote servers.
Solution
Some of the PowerShell V1 and V2 cmdlets - those which include a "ComputerName" parameter - include access to remote systems, implemented using Remote Procedure Call (RPC) and Distributed COM (DCOM). If your run this code you can obtain a list of these cmdlets:
Get-Command | Where-Object {$_.CommandType -eq 'Cmdlet' -and`
$_.Parameters.Keys -contains 'ComputerName' -and`
$_.Parameters.Keys -notcontains 'Session'}
Each cmdlet individually implements remoting, hence the implementation is not the same and has different requirements in each case.
PowerShell V2 comes with a new and consistent remoting technology, based on the WinRM service. PowerShell will execute your code in a separate session that runs on the remote system and returns the results to the calling system. Every cmdlet can be run remotely.
Prerequisites for PowerShell Remoting
As already mentioned, you'll need PowerShell V2 to benefit from the remoting feature. Check the PowerShell version you're currently running using the $psversiontable variable
PowerShell V2 remoting is based on the functionality offered by the WinRM service, which has to be "up and running".
By default PowerShell Remoting uses Kerberos authentication. However with additional configuration regarding "TrustedHosts" you can use it in a workgroup environment. Please refer to the guide I recommend in the "Next Steps" section for details on the additional configuration you'll need. I've run the examples form this tip in a domain environment.
You'll need no preparation on the "client" machine you use. However, on any computer you want to make accessible from other machines, you'll need to run Enable-PSRemoting. Enable-PSRemoting runs the Set-WSManQuickConfig cmdlet, which will:
- start the WinRM service
- set the startup type on the WinRM service to Automatic
- create a listener to accept requests on any IP address
- enable a firewall exception for WS-Management communications
- enable all registered Windows PowerShell session configurations to receive instructions from a remote computer.
By default you'll be prompted before making the changes, unless you run the cmdlet with the -Force parameter.
You will want to run Enable-PSRemoting from an elevated process - i.e. start Powrshell in the "run as admin" mode. Otherwise you'll receive an "access denied" error message.
Sessions
PowerShell remoting enables you to create sessions locally as well as on a remote machine. Many times you'll need only temporary access to the target. In this case you'll use Invoke-Command with the -ComputerName parameter. PowerShell will create a temporary connection that is used only to run the specified command and is then closed. If you need to establish persistent sessions, don't forget about the best practices described in the admin guide mentioned in the "Next Step" section.
WSMan configurations
If you run Get-PSDrive on your PowerShell V2 installation you'll notice in the list the WSman drive:
This drive contains the settings that the remoting currently uses. By default, it uses HTTP as transport protocol (using port 5985), accepts only Kerberos authentication and allows a maximum of 5 remote shells per user. Please refer to the admin guide if you need to change these default settings.
Example
Let's try to run a classical piece of TSQL code from Books Online against another machine in my domain. I'll use Invoke-Command and Invoke-Sqlcmd to run the piece of code against another computer in the domain.
On the target machine I've already run Enable-PSRemoting. In order to be able to use Invoke-Sqlcmd I need to do one more step. Even if I load the SQL Server snapins every time I start a PowerShell session locally, Invoke-Sqlcmd won't be recognized as a cmdlet when I try to use it against the target from a remote machine. I need to register a named session configuration on the target. This configuration will use a startup script that loads the SQL snapins.
Here is how I register the session configuration. Provide the full path of the script and enclose the path in double quotes if it contains white spaces.
Register-PSSessionConfiguration -Name SQLSession -StartupScript "C:\My Location\session_config.ps1"
The session_config.ps1 script will load the SQL snapins and for our example the script contains these lines of code:
Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100
Here is the code I've run on the calling machine:
Invoke-Command -ComputerName DEV2008-2 -ScriptBlock{
$qry = "SELECT SERVERPROPERTY('ServerName') AS ServerName,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition;"
Invoke-Sqlcmd -Query $qry} -ConfigurationName SQLSession
I'm using Invoke-Command in order to run Invoke-Sqlcmd on the target machine DEV2008-2. The ComputerName parameter specifies the machine on which I run the code. The ScriptBlock contains the commands I'm running on DEV2008-2, in this case an Invoke-Sqlcmd which runs this query:
SELECT SERVERPROPERTY('ServerName') AS ServerName,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition;
Since I do not specify any server instance, the query will be executed against the default instance of the SQL Server installed on DEV2008-2. Remember that I'm working in a domain environment. I connect to the DEV2008-2 instance under my domain account. Notice that I'm using the session configuration registered above on the target machine.
Here is the result I've obtained:
By default, the output contains the name of the remote machine, given by the the PSComputerName property. You can use Invoke-Command with the -HideComputerName parameter to hide it as shown below.
Invoke-Command -ComputerName DEV2008-2 -ScriptBlock{
$qry = "SELECT SERVERPROPERTY('ServerName') AS ServerName,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition;"
Invoke-Sqlcmd -Query $qry} -ConfigurationName SQLSession -HideComputerName
Next Steps
- In this article, Grant Fritchey will walk you through a more detailed example of using PowerShell remoting and Invoke-Sqlcmd. As far as I've seen, this is one of the very few resources about using Invoke-Sqlcmd and PowerShell remoting. Thank you, Grant.
About the author
Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-09-06
