SQLvariations: SQL Server, a little PowerShell, maybe some Hyper-V Rotating Header Image

Implicit Remoting with PowerShell

Last week I spoke to the Atlanta PowerShell User Group about Remoting and Background Jobs.  There are a couple different flavors of Remoting but the one I’m most excited to show people is called Implicit Remoting.  What if you were on a machine that only had SSMS 2005 and you had to run something against a remote server that had SSMS 2008?  Enter Implicit Remoting.

With Implicit Remoting, essentially you temporarily download the cmdlets from the remote machine onto your local machine. When you execute the cmdlet it runs against the remote machine.

Before we start I want to set the scene.  On your local machine you need to have PowerShell 2.0.  The remote machine needs PowerShell 2.0 and SSMS 2008 installed.

Setting up the Remove Server
Log into the remote server via Remote Desktop and create a profile (notepad $pshome\profile.ps1) that contains this:

add-pssnapin SqlServerCmdletSnapin100
add-pssnapin SqlServerProviderSnapin100
 

Next create a new Remoting Configuration to load your SQL Items into your profile:

(* I used the ISE profile)

Register-PSSessionConfiguration -Name SQLSupport `
-StartupScript C:\Users\YourUserNameHere!!!\Documents\WindowsPowerShell\Microsoft.PowerShellISE_profile.ps1

Setting Up Your Local Machine
Open a Power Shell window that has no profile loaded. Run the following:

$RemoteSession = New-PSSession -ComputerName MyRemoteServer -ConfigurationName SQLSupport

Connect to the remote session you just defined in the variable:

Enter-PSSession $RemoteSession

Run this command to test your setup. You will see the SQL Server cmdlets from the snapin that was loaded with the Remoting Configuration.

get-command invoke-sqlcmd

Exit the session:

Exit-PSSession

image

Now we want to Import invoke-sqlcmd.  This will allow you to run your scripts against the remote server session that you defined.  This might not seem like much, but if you are working over a VPN from home this could be very useful.  Plus you don’t have to keep switching between your local shell and the remote one (*more on that another day).

Import-PSSession $RemoteSession -CommandName invoke-sqlcmd
get-command invoke-sqlcmd

Putting it to Use
Now that all of this is done what command will you run?  Well anything.  Just because I wanted to see if it could be done I went ahead and ran Back Woodys ‘backup all user databases script’.   Run anything you want, but a good start is something simple like:

invoke-sqlcmd -query "sp_databases" -database master `
-serverinstance localhost | format-table
Your Environment
Now please think outside the box because the SQL cmdlets might not be the ones that you personally want to download.  I was just using them as a common example us SQL folks could talk about.  You might be more interested in downloading the Clustering cmdlets in your case.  Or, you might see this as a tool to centralize scripts that someone else in your company might ocassionally use but doesn’t need to worry about keeping  locally (think CodePlex projects).  You might even want to do the reverse for some reason, you might want to be able to download cmdlets from your local machine to a server for a one time use so that you don’t have to install anything.  Either way I hope you remember this as one of the tools available to you.

Happy scripting.

3 Comments

  1. Man – I think this may be exactly what I have spent much of this week looking for. Need to test to be sure. For my first serious Powershell endeavor I was attempting to build a script that would automate our SQL Server configuration checklist that we’ve always done manually after installing a new instance. I had gotten hung up on how to set Local Security Policy for Lock Pages in Memory and Perform Volume Maintenance Tasks. I was trying to find a way to do it in Powershell, from my PC. I found Set-Privilege in PSCX on CodePlex but didn’t want to have to install a CodePlex project on all of our production SQL Servers. From the sound of your post, if I am ok with running my config script from the server instead of my PC, I will be able to make use of CodePlex project on my PC without installing it on the server. That would be ok. Going to give it a try now.

  2. […] Aaron Nelson (@SQLvariant) posts about Implicit Remoting with PowerShell – SQLvariations: SQL Server, a little PowerShell, maybe some Hyper-V Posted on July 27, 2010 by sqlmashup Implicit Remoting with PowerShell […]

  3. […] This post was mentioned on Twitter by Jorge Segarra, jonathanmedd, ravikanth, Laerte Junior, Aaron Nelson and others. Aaron Nelson said: [blog] Implicit Remoting with PowerShell http://bit.ly/bzaGRZ #PowerShell #SQLPASS […]