Art of the DBA Rotating Header Image

Out With The SQLPS, In With The SqlServer

This month, the SQL Server tools team released an updated version of the SQL Server PowerShell module. It is a huge leap forward for using PowerShell and SQL Server together, with the tools team making a serious commitment to correcting the sins of the past. While we still have a long way to go, this is a promising path. At this time next year, I’m certain the PowerShell/SQL Server experience will be in the best place it has ever been.

Before I share my thoughts, let me give you some relevant links. You know, in case you missed something.

Not Playing Nice

Now for my turn! First off, I want to call out that with the July 2016 update we have a new module. SqlServer replaces SQLPS, which means that all the old functionality in the old module exists in the new one (along with some nice little fixes). What I discovered is that this also means you can’t use both modules at the same time. I ran into this because my profile automatically loaded SQLPS for me. When I went to import SqlServer, I got some unpleasant red text:

2016-07-09_12-39-26

Basically, it’s a typing conflict with the SMO. Now, there’s no real reason why you should be running both modules side by side, but be aware that if you have anything that automatically loads SQLPS, you’re going to have problems. Fortunately there’s an easy way to fix this without having to restart your session:

Remove-Module SQLPS
Import-Module SqlServer

However, there’s another monkey wrench, especially for those of us using PowerShell scripts in SQL Agent jobs. Even with the release of SQL 2016 (which was just before these changes were made), SQL Agent still runs sqlps.exe and loads the SQLPS module. To check this, I made a simple agent job that executes:

Get-Module | Out-File C:\Temp\AgentPSModules.txt

The resulting output shows us the issue plain as day:

2016-07-09_12-50-13

There are ways around this, of course. The key here is awareness and I’m sure that we’ll see the SQL tools team get an update to this when possible.

Invoke-SqlCmd: Now with more flexibility!

Enough of the bad news, let’s talk about some of the improvements. As I mentioned earlier, Laerte Junior blogged about some of the updates to Invoke-SqlCmd. These changes really help make the cmdlet more useful for managing and working with outputs. However, I want to look at one specific addition more closely: connection strings!

I’ve always found the biggest limitation of Invoke-SqlCmd was that I couldn’t specify connection string parameters. This was a significant issue when working with multi-subnet Availability Groups, because I couldn’t specify MultiSubnetFailover=True, a key connection string parameter. Now I can, along with a host of other options.

Using it is pretty simple. Just declare a connection string as you would with any other application and pass it to the -ConnectionString parameter:

$ConnectionString = 'Server=localhost;Database=tpcc;Trusted_Connection=true'
$sql = 'SELECT name,physical_name FROM sys.database_files'
Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql

2016-07-09_13-05-37

This new parameter opens up a whole new range of possibilities. I love that Microsoft is providing multiple paths to work with their tools. This means that if you’re a .NET guy who is more comfortable with connection strings, you don’t have to constantly keep looking up all the different flags and parameters of Invoke-SqlCmd. Also, if you have a situation which can not be addressed by those standard flags, you can always use the standard .NET connection string parameters and get the job.

(And yeah, I’m excited about this because it was my Connect item. :D )

Providing for the Provider

The last thing I want to touch on are a couple updates to the SQL Server provider. Now, the provider definitely has a bad name out there, but it can be a very useful tool for exploring and gathering SQL Server objects you want to work with. Since it is based on the SMO, it makes SQL Server object manipulation easier by giving you more options to collect your objects.

An important facet of the provider is that it behaves like a file system. How many of us have deleted files from the command line? Have you also used a PowerShell one-liner to delete old files, like backups? If you have, you might be familiar with two parameters: -WhatIf and -Confirm. These two switch parameters are extremely helpful because they can keep you from cutting yourself with that sharp PowerShell knife.

With the July 2016 update, the SQL Server provider now supports the use of these two switches. While using them may not be a common situation, it’s good to know that they are there. After all, it could be handy if you wanted to clean up some items from the command line, like maybe a junk database. Now you can both check what you’re going to do before you do it, along with getting a confirmation question when you go for the actual delete:

2016-07-09_13-21-52

While this may not seem like a big deal, it’s another example of how the SQL tools team is trying to get the SQL Server PowerShell module up to the standards of other PowerShell implementations. For all of us who struggle daily with technical debt, I’m sure you can understand how significant this is.

Early Days

I know the SQL Server PowerShell module still has a bad reputation with a lot of folks out there. Honestly, it’s well deserved. The SQL Server Tools team is out to fix that and everything we’ve seen over the last 3-4 months shows that commitment. A lot of this has to do with the effort put forth by the SQL Server community and the ongoing Trello board where the community is working with the tools team to suggest and prioritize changes. Want to help? Join us by contributing changes and voting up the current suggestions. Microsoft wants to make this your product and this is how we can help.

10 Comments

  1. Keith Ramsey says:

    Mike,

    I think I know this answer, but I wanted to make sure. Are these new updates only available when on a Server with SQL Server 2016 installed or is there a way for me to put these on my older Servers with SQL 2008 R2, SQL 2012, SQL 2014?

    Thanks,
    Keith

    1. Mike Fal says:

      These updates are available with the SSMS 2016 July update, so you can install the updated module to your servers by installing the latest version of SSMS to your servers. However, that doesn’t necessarily mean they will make use of them (see my section on SQLPS and the SQL Agent). However, you can manage these servers from any machine remotely by using SSMS 2016 from another machine and that way they would work fine. We’re working on trying to get the Powershell module created as a separate install, but it’s not quite there yet.

  2. Cody says:

    “There are ways around this, of course. The key here is awareness and I’m sure that we’ll see the SQL tools team get an update to this when possible.”

    What ways around this are you talking about? Are you referring to changing agent jobs from PowerShell to CmdExec and having them spawn PowerShell within?

    1. Mike Fal says:

      I’d prefer to see the Powershell agent job step fixed so that it uses the installed version of PS. It’s a problem with SQLPS.exe (which is deprecated).

  3. Richie Lee says:

    That -ConnectionString is actually very cool. Will the SQLPowershell download for SQL Server 2016 Feature Pack be updated?

    I need just the sqlserver module on some Octopus Tentacles and I was wondering if there was any reason why I couldn’t post the module up to NuGet/personal repository (ie ProGet)?

    1. Mike Fal says:

      Richie, this is tough to say. I don’t know about the feature pack installation, but that would be useful. As for posting the module in your own personal repo, I haven’t tested it. It could be possible, but I’m nervous about dependencies that may not be readily visible. Shouldn’t be, but I’m a DBA. I’m paranoid.

      1. Richie Lee says:

        Ha, no worries. I’ve tested it out, and it looks like a NuGet package is totally do-able:

        I spun up an Azure VM, and got SMO/SQLCLR installed from the 2016 Feature Pack as they are dependencies for SQL PowerShell.

        Then I just copied the new module over from my local box to the VM, imported the SQLServer module, and ran “Invoke-sqlcmd -connectionstring” and it worked.

        As a way of provisioning the new module, this would be really handy if it came officially from Microsoft. I know SSDT for headless builds is now available via NuGet, hopefully they could add this too.

        1. Mike Fal says:

          Nice. Sounds like a blog post in the making!

  4. Linda LF says:

    You made a comment [There are ways around this, of course. ] regarding the use of the new SqlServer module and SQL Agent jobs. However, you don’t note those ways. Are you able to elaborate, or is the SqlServer module not available to be used with SQL Agent jobs at this time?

    Specific scenario : SQL2014/SP2-GDR environment with a SQL Agent job, jobstep type : PowerShell

    Whenever I attempt to issue the following in a SQL Agent job :

    Import-Module -Name SqlServer -DisableNameChecking

    I get the following result:

    A job step received an error at line 10 in a PowerShell script. The corresponding line is ‘Import-Module -Name SqlServer -disablenamechecking
    ‘. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘The following error occurred while loading the extended type data file:
    Error in TypeData “Microsoft.SqlServer.Management.Smo.NamedSmoObject”: The member DefaultKeyPropertySet is already present.
    Error in TypeData “Microsoft.SqlServer.Management.Smo.NamedSmoObject”: The member DefaultDisplayPropertySet is already present.
    Error in TypeData “Microsoft.SqlServer.Management.Smo.NamedSmoObject”: The member DefaultDisplayProperty is already present.

    I also found this reference [https://technet.microsoft.com/en-us/library/mt740629.aspx] which states:
    “The new version of SQL Server PowerShell included with SSMS does not update the version of Windows PowerShell that SQL Server uses. This means that scripts that SQL Server Agent runs are not able to use the new cmdlets.”

    1. Mike Fal says:

      The errors you are seeing is caused by the confusion of SMO objects across the modules. The tools team has tried to resolve this with more recent releases where the dependent SMO libraries are contained within the module (as opposed to shared folders).

      Also, as you state, the PowerShell module is embedded for SQL Agent Jobs and can’t be altered. The tools team is addressing this, but when they update it will only affect newer versions of SQL Server. I recommend looking at Derek Hammer’s post on running PowerShell via the SQL Agent: https://www.sqlhammer.com/running-powershell-in-a-sql-agent-job/

Leave a Reply to Mike Fal Cancel reply

Your email address will not be published. Required fields are marked *