No-Hassle SQL Server Remoting with PowerShell V2

Although, PowerShell give the whole remoting experience, you may still need to ask you network administrator to let you configure remoting on all the machines you need to access. Of course, unless you hold the keys of the kingdom. But, it is much simpler without PowerShell Remoting.

When you use the SQLPS Module it automatically let you connect to any of your SQL Servers on your network. So, YES!! As long as you have permission to manage these servers, SQLPS already gives you the ability to remotely connect to other machines. There’s no need to setup the Remoting with Enable-PSRemoting or Set-WSManQuickConfig commands.

Simple Remoting #1

So, How can I change from my SQL Server local prompt to another instance? Here’s an example how:

4. Now you can continue and navigate through your SQL Server drive like in DOS filesystem.

Dir Databases -force

5. Finally, to remote connect top another session just change the “Your-Machine-name”, and your “Your-InstanceName”.

Simple Remoting #2

One thing you need to understand, after you load SQLPS module, now you have access to all SMO (SQLServer Management objects) .NET Classes. This will help you build short and very effective .NET that extend the scripting of PowerShell. Also, you don’t need to be at the SQLServer: drive to use it.

Here’s a basic example using SMO:

1. First load the SQLPS Module:

Import-Module SQLPS

2. Create a variable to hold your local SQL Server objects:

$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server

3. Now that your .NET object has been create you need to see all what you can grab from it:

$MySQL | Get-member | Out-GridView

4. I decided to grab the “Information” property so I can display all the information, and verify that this is my local SQL instance:

$MySQL.Information

5. Finally, to remote connect to another machine, I just add the SQL Server InstanceName to the line creating my variable: