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:
1. First load the SQLPS Module:
[sourcecode language=”powershell”]
Import-Module SQLPS -DisableNameChecking
[/sourcecode]
2. Confirm you have the SQL Server drive available:
[sourcecode language=”powershell”]
Get-PSDrive SQL*
[/sourcecode]
3. Let’s navigate to your SQL Server Instance:
[sourcecode language=”powershell”]
cd SQLServer:\SQL\Your-Machine-name\Default #or
cd SQLServer:\SQL\Your-Machine-name\Your-InstanceName
[/sourcecode]
4. Now you can continue and navigate through your SQL Server drive like in DOS filesystem.
[sourcecode language=”powershell”]
Dir Databases -force
[/sourcecode]
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:
[sourcecode language=”powershell”]
Import-Module SQLPS
[/sourcecode]
2. Create a variable to hold your local SQL Server objects:
[sourcecode language=”powershell”]
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server
[/sourcecode]
3. Now that your .NET object has been create you need to see all what you can grab from it:
[sourcecode language=”powershell”]
$MySQL | Get-member | Out-GridView
[/sourcecode]
4. I decided to grab the “Information” property so I can display all the information, and verify that this is my local SQL instance:
[sourcecode language=”powershell”]
$MySQL.Information
[/sourcecode]
5. Finally, to remote connect to another machine, I just add the SQL Server InstanceName to the line creating my variable:
[sourcecode language=”powershell”]
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server "Your-Machine-name"Â
[/sourcecode]
Or
[sourcecode language=”powershell”]
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server "Your-Machine-name\Your-Instancename"
[/sourcecode]
Keep in mind, you can another variable holding your SQL Server name with the instancename and do something like:
[sourcecode language=”powershell”]
$SQLInstanceName = "Your-Machine-name\InstanceName"
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLInstanceName
$MySQL.Databases[$SourcedbName].tables | sort -desc dataspaceused | select Parent, `
@{Label="DisplayName";Expression={$_.Schema+"."+$_.Name};} | `
sort DisplayName;
[/sourcecode]
Again, no need to setup PSRemoting.
Simple Remoting #3
And this is the simplest of all, if you like to reuse your T-SQL scripts. This will be your most used PowerShell SQLPS cmdlet “Invoke-SQLcmd“.
1. Load the SQLPS module:
[sourcecode language=”powershell”]
Import-Module SQLPS
[/sourcecode]
2. Just start using the SQLPS “Invoke-SQLcmd” command: (basic form)
[sourcecode language=”powershell”]
Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "."Â #or
$SaveResult = Invoke-Sqlcmd -Query "SELECT * FROM [AdventureWorks].[dbo].[AWBuildVersion];" -ServerInstance "Your-Machine-name\InstanceName"
$SaveResult
[/sourcecode]
Again, there’s no need to be at the SQLServer: drive in order for this to work. As you can see, with just a few lines of code you can connect to another SQL Server instance.
Go ahead and try it!