title: SQLPS and its limitations
date: 2012-06-15 14:14:00 +0200 CEST
draft: false
author: John Roos
----
SQL Server PowerShell (SQLPS) enables some new possibilities when it comes to managing your instances, but it also comes with some limitations. These limitations are not always obvious and can sometimes be worked around. To understand the limitations you must first understand that SQLPS is not PowerShell V1, V2 or V3 but rather a mini shell on its own. It was created by Microsoft as a mini shell based on PowerShell V1 so the cmdlets available are based on V1. To get an idea of what is missing compared to newer versions of PowerShell there is a list of feature differences between versions on Wikipedia which could give some pointers.
You can still use all the PowerShell features from newer versions with some workarounds. When setting up jobs in SQL Server Agent for example you can select PowerShell as a job step type. This actually means that you will use SQLPS and not the regular PowerShell. If you want the job to run regular PowerShell commands from V2 or later (like Invoke-Command) you can use CmdExec as job step type instead and from there execute PowerShell with the PS script included, like this:
'Use with CmdExec
powershell.exe -Command "& {Invoke-Command -ComputerName MyServername -ScriptBlock {Stop-Service -DisplayName 'MyServiceDisplayName'}}"
When you run PS scripts in this way you will use the regular PowerShell from the operating system instead of SQLPS and it gives you all the features of newer versions of PowerShell in SQL Server Agent jobs. There is no difference in SQLPS in SQL Server 2008 and SQL Server 2008 R2. In SQL Server 2012 however, the PowerShell integration is much deeper and these kind of workarounds are much less needed.
SQLPS and its limitations
SQL Server PowerShell (SQLPS) enables some new possibilities when it comes to managing your instances, but it also comes with some limitations. These limitations are not always obvious and can sometimes be worked around. To understand the limitations you must first understand that SQLPS is not PowerShell V1, V2 or V3 but rather a mini shell on its own. It was created by Microsoft as a mini shell based on PowerShell V1 so the cmdlets available are based on V1. To get an idea of what is missing compared to newer versions of PowerShell there is a list of feature differences between versions on Wikipedia which could give some pointers.
You can still use all the PowerShell features from newer versions with some workarounds. When setting up jobs in SQL Server Agent for example you can select PowerShell as a job step type. This actually means that you will use SQLPS and not the regular PowerShell. If you want the job to run regular PowerShell commands from V2 or later (like Invoke-Command) you can use CmdExec as job step type instead and from there execute PowerShell with the PS script included, like this:
'Use with CmdExec
powershell.exe -Command "& {Invoke-Command -ComputerName MyServername -ScriptBlock {Stop-Service -DisplayName 'MyServiceDisplayName'}}"
When you run PS scripts in this way you will use the regular PowerShell from the operating system instead of SQLPS and it gives you all the features of newer versions of PowerShell in SQL Server Agent jobs. There is no difference in SQLPS in SQL Server 2008 and SQL Server 2008 R2. In SQL Server 2012 however, the PowerShell integration is much deeper and these kind of workarounds are much less needed.
- Written by John Roos on June 15, 2012