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.
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
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:
Exit the session:
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
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
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.