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
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.
I spoke in Columbus, GA and Edmonton, um…, somewhere in Canada on back to back nights this Tuesday and Wednesday. (I’m kidding, I know it’s in Alberta, and you guys like the Jets right? 
) I used the same slide deck for both sessions; well, for Edmonton I didn’t even use a slide deck, but the scripts I covered were a little different. 
I’ve recently learned that if you only have PowerPoint 2007 you’ll need to download this viewer because I built the slides in PowerPoint 2010. Here are the scripts for Columbus and Edmonton.
and of course I can’t finish without saying: If you need some help getting started from scratch using PowerShell with SQL Server check out my 
