Use PowerShell Remoting to Manage SQL Servers Efficiently

In all the years I've worked as a server administrator, I've always logged into each server I work on to gather data or make changes. On Windows servers, the standard way to do this is to use Remote Desktop (RDP), which gives you keyboard and monitor access to the server, and allows you to get work done. Sadly, this method has two problems: (1) only two people can be connected via RDP to any server at one time; and (2) the resources used by RDP are enough that performance of the server can be affected.

Related: Run Your SQL Server DBCCs and Check Your Errorlog with PowerShell

PowerShell 2.0 introduced a different way to manage your servers, called remoting. Remoting uses the lightweight http-based WSMan protocol to provide you the server access you need to get work done, but without the heavy footprint RDP requires. With remoting, there is no two login limit, so you don't have to hunt around to get that network admin to log off the server so you can get your work done.

PowerShell 2.0 is supported on Windows Server 2003 (SP2) and newer. Note that if you want to use remoting in a SQL Server Agent job on SQL Server 2008 or SQL Server 2008 R2, you'll have to use the Command Line task type and execute powershell.exe followed by the full path to your script. This is because those versions of SQL Server, while they support the PowerShell task type, use the PowerShell 1-based sqlps.exe to execute the PowerShell scripts, and that version doesn't support remoting. SQL Server 2012 and above use the version of PowerShell you have installed on your server.

Here's a simple diagram showing the components involved in PowerShell remoting.

Components involved in PowerShell remoting.

Your PowerShell session connects using the WSMan protocol to an http listener on the server. That listener directs the request to the PowerShell endpoint servicing remoting, and the server fires up a process called wsmprovhost.exe, which is PowerShell on the remote server. It's important to remember that each unique remoting session into a server gets its own copy of wsmprovhost, so while you can have more than two sessions connected to a server, the more you have the more resources will be consumed on the server for those processes.

If you're running Windows Server 2012 or Windows Server 2012 R2, remoting is already enabled and you don't have to do anything else but start using it. If you're on an earlier Windows server version, you'll have to enable the server for remoting, and you do that with the Enable-PSRemoting cmdlet. If you want to ensure that all the servers in your domain have remoting enabled, you can enable the "Allow Automatic Configuration of Listeners" privilege in the path "Computer ConfigurationAdministrative TemplatesWindows ComponentsWindows Remote Management (WinRM)WinRM service."

Once you've enabled remoting, you can enter an ad hoc session on the server by using the Enter-PSSession cmdlet. At that point, you're connected to the server as though you were directly logged into that server, and you'll stay there until you use the cmdlet Exit-PSSession.

Exit-PSSesion cmdlet

While ad hoc sessions are fine for quick tasks, the process of setting up a session on the server is costly enough that if you're going to be doing an extended amount of work on the server, you'll want to set up a persistent session, using the cmdlet New-PSSession. Then, you can enter and exit the session as you need it, and the objects you create there will remain until you clean up after yourself using the Remove-PSSession cmdlet.

Remove-PSSession cmdlet

These interactive sessions allow you to take care of problems without the overhead or limitations of RDP, but there's an even better value you'll get with remoting, and that comes when you start using the Invoke-Command cmdlet to run tasks against multiple servers. This cmdlet allows you to run the same script or script block on lots of servers — I know an administrator who manages thousands of servers around the country using this feature. Even better, Invoke-Command will run as many of these commands simultaneously as is reasonable. The default is 32, unless you specify the -ThrottleLimit argument with another value.

The Invoke-Command cmdlet takes an argument called –ComputerName, which allows you to specify the list of servers against which you'll run the command. This argument takes a single server name, a comma-separated list of server names, or an object with a collection of server names. You specify what the command is by either coding a script block after the -ScriptBlock argument, or specifying the full path to a script to run using the -filepath argument.

Script block after the -ScriptBlock argument

Now, using scripts you've written to gather information from SQL Server using PowerShell can be run against all of our servers simultaneously, getting the data much faster than we could by running a script against each server, one at a time. We can direct the results into a variable, and we can evaluate the contents of that variable to look for a problem we might be having. In this example, I'm looking for any databases that have the AutoShrink property set to True.

AutoShrink property set to True

So, remoting gives you a lightweight way to manage all of your servers, and you can manage them all at the same time. Hopefully, you'll find it as useful as I have.

Related: My Favorite Tool of 2012: Remote Desktop Manager